Utilisation de coutn avec over partition by
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:
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:
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:
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:
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
