Discussion :

[Wachter]

Bonjour,

J'ai un indicateur qui doit se calculer sur deux niveaux hiérarchiques. Je prends un exemple : le produit « Vélo » doit être comptabilisé une fois au niveau « Ville » et également une fois au niveau « Pays ». Comme j'ai de gros volumes de données, je ne souhaite pas effectuer un COUNT(DISTINCT Produit) sur le reporting mais plutôt en amont dans la phase d'intégration de données.




En agrégeant par ville, le produit sera comptabilisé trois fois par pays sur le rapport. Le comptage est donc erroné. Les axes d'analyses « Pays » et Ville » doivent apparaître sur le rapport. Auriez-vous des pistes ?

Merci de votre aide.

[escartefigue]

Si j'ai bien compris le besoin (un exemple de résultat attendu aurait été le bienvenu), ceci devrait convenir :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
with T1(Pays, ville, produit) as
    (select 'france', 'paris', 'vélo'      union all
     select 'france', 'marseille', 'vélo'  union all
     select 'france', 'lyon', 'vélo'       union all
     select 'france', 'paris', 'autobus'   union all
     select 'france', 'marseille', 'taxi'  union all
     select 'france', 'lyon', 'autobus'    union all
     select 'espagne', 'madrid', 'vélo'    union all
     select 'espagne', 'huesca', 'taxi'    union all
     select 'espagne', 'leon', 'taxi'
    )
select pays
     , ville
     , produit
     , count(*) 
       over (partition by produit
             order by pays) as NB1
from T1
order by pays, ville, produit

Résultat :

pays	ville	produit	NB1
espagne	huesca	taxi	2
espagne	leon	taxi	2
espagne	madrid	vélo	1
france	lyon	autobus	2
france	lyon	vélo	4
france	marseille	taxi	3
france	marseille	vélo	4
france	paris	autobus	2
france	paris	vélo	4

[Wachter]

Merci pour la proposition, escartefigue. En fait, chaque occurrence d'un produit doit être comptabilisée une fois sur le rapport.

Voici le résultat attendu :

Pays	Ville	Produit	NB_PRODUIT_VILLE	NB_PRODUIT_PAYS
France	Paris	Vélo	1	1
France	Marseille	Vélo	1	1
France	Lyon	Vélo	1	1

[Waldar]

L'exemple avec que des 1 n'est pas très parlant.
Je suppose que vous voulez le nombre de produits distincts par ville et par pays.

En reprenant les données d'escartefigue et en modifiant un peu les données à Marseille :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
  select pays
       , ville
       , produit
       , dense_rank() over(partition by ville order by produit asc) + dense_rank() over(partition by ville order by produit desc) - 1 as nb_produit_dst_ville
       , dense_rank() over(partition by Pays  order by produit asc) + dense_rank() over(partition by Pays  order by produit desc) - 1 as nb_produit_dst_pays
    from (values ('france', 'paris'    , 'vélo'   )
               , ('france', 'lyon'     , 'vélo'   )
               , ('france', 'paris'    , 'autobus')
               , ('france', 'marseille', 'taxi'   )
               , ('france', 'marseille', 'taxi'   )
               , ('france', 'marseille', 'taxi'   )
               , ('france', 'lyon'     , 'autobus')
               , ('espagne', 'madrid'  , 'vélo'   )
               , ('espagne', 'huesca'  , 'taxi'   )
               , ('espagne', 'leon'    , 'taxi'   )) as t (pays, ville, produit)
order by 1, 2;
 
pays     ville      produit  nb_produit_dst_ville  nb_produit_dst_pays
-------  ---------  -------  --------------------  -------------------
espagne  huesca     taxi                        1                    2
espagne  leon       taxi                        1                    2
espagne  madrid     vélo                        1                    2
france   lyon       vélo                        2                    3
france   lyon       autobus                     2                    3
france   marseille  taxi                        1                    3
france   marseille  taxi                        1                    3
france   marseille  taxi                        1                    3
france   paris      vélo                        2                    3
france   paris      autobus                     2                    3

[Wachter]

Je suppose que vous voulez le nombre de produits distincts par ville et par pays.

Exactement, chaque occurrence d'un produit doit être comptée une seule fois par ville et par pays. Le code proposé ne semble pas produire le résultat attendu.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
pays     ville      produit  nb_produit_dst_ville  nb_produit_dst_pays
-------  ---------  -------  --------------------  -------------------
espagne  huesca     taxi                        1                    2
espagne  leon       taxi                        1                    2

« Taxi » doit être comptabilisé une fois par ville et par pays. L'utilisateur peut sélectionner un ou plusieurs pays, une ou plusieurs ville. Côté reporting, je peux mettre un simple COUNT(DISTINCT Produit) et l'indicateur sera calculé en fonction de la sélection active de l'utilisateur (m pays et n villes). Sauf que le volume de données tue un peu les performances.