Discussion :

[jejesh26]

Bonjour a tous,

cela fait des semaines que je recherche une solution a mon probleme.

Je voudrais utiliser le over partition by pour faire un comptage d'un aggregate.

Voici mon jeux de donnees

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
 
create table mytest
(DEPT_CODE int null,
 POST_GROUP int null,
 N1_N1_DIV_PROMO_DEBITS varchar(10) null);
 
 truncate table mytest
  insert into mytest values (12,1,'a');
  insert into mytest values (13,1,'a');
  insert into mytest values (14,1,null);
   insert into mytest values (12,1,'b');
  insert into mytest values (14,9,'a');
  insert into mytest values (21,1,'B');
 insert into mytest values (22,1,'C');
 insert into mytest values (23,1,'B');
 insert into mytest values (24,1,null);
   insert into mytest values (33,1,null);
 commit;
 
12	1	a
13	1	a
14	1	
14	9	a
21	1	B
22	1	C
23	1	B
24	1	
12	1	b
33	1

Pour les departements commencant par 1 et postgroup 1, j'ai deux valeurs distinctes : a et b
Pour les departements commencant par 1 et postgroup 9, j'ai une seule valeur : a
Pour les departements commencant par 2 et postgroup 1, j'ai deux valeurs : B et C
rien pour les departments commencant par 3.


En executant le code suivant, on voit bien le nombre distinct de valeur non nulles par SUBSTR(DEPT_CODE).

Code :

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

1
2
3
4
5
6
7
8
9
 
 select SUBSTR(DEPT_CODE,1,1), POST_GROUP, COUNT(DISTINCT N1_N1_DIV_PROMO_DEBITS)
from mytest
group by SUBSTR(DEPT_CODE,1,1), POST_GROUP
 
1	1	2
1	9	1
2	1	2
3	1	0

Mais je voudrais que ce resultat soit une colonne dans une requite compant deja le nombre de valeurs distinctes par dept-code dans la table
or en appliquant la requite suivante, le comptage par substr(dept_code,1,1) est faux (voit la derniere colonne)

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
 
select SUBSTR(DEPT_CODE,1,1), POST_GROUP, DEPT_CODE,
       COUNT(DISTINCT N1_N1_DIV_PROMO_DEBITS), 
       count (count( distinct N1_N1_DIV_PROMO_DEBITS
           ))
       OVER (partition by SUBSTR(DEPT_CODE,1,1), POST_GROUP)     
from mytest
group by SUBSTR(DEPT_CODE,1,1), POST_GROUP, DEPT_CODE;
 
1	1	12	2	3
1	1	13	1	3
1	1	14	0	3
1	9	14	1	1
2	1	21	1	4
2	1	22	1	4
2	1	23	1	4
2	1	24	0	4
3	1	33	0	1

Normalement je devrais avoir cela :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
1	1	12	2	2
1	1	13	1	2
1	1	14	0	2
1	9	14	1	1
2	1	21	1	2
2	1	22	1	2
2	1	23	1	2
2	1	24	0	2
3	1	33	0	0

Je precise bien sur que dans la requite de base, mytest est en faite une sous requete complexe. Il n'est donc pas question de re-faire des sous select de cette sous requite, d'ou pour moi l'interet d'utiliser over partitioning.

Merci d'avance

Cdlt

[jejesh26]

et desole pour les fautes e frappe, mais avec un clavier anglais/chinois c pas super simple

[aieeeuuuuu]

Bonjour,

comme ceci ?

Code :

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

1
2
3
4
5
6
7
 
select DISTINCT SUBSTR(DEPT_CODE,1,1), POST_GROUP, DEPT_CODE,
        COUNT(DISTINCT N1_N1_DIV_PROMO_DEBITS)
         OVER (partition by DEPT_CODE,POST_GROUP ),     
       count( distinct N1_N1_DIV_PROMO_DEBITS)
         OVER (partition by SUBSTR(DEPT_CODE,1,1), POST_GROUP)     
from mytest

[jejesh26]

Magnifique, merci.

Pour comprendre, en gros il vaut mieux replacer le GROUP BY du niveau le plus bas, par un OVER PARTITION BY, afin que les deux niveaux puissent "cohabiter " ?

En tout cas merci, je vais tester cela sur le programme principal.

Cdlt