[Sql]Having Count ( Distinct )

Version imprimable

Bonjour,

j'ai besoin d'aide et de conseil concernant cette aimable clause having tout à fait spéciale. Pour partir sur une base commune :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE TABLE_TEST(ID NUMBER, GROUPEMENT VARCHAR2(30), INDICATEUR NUMBER, INDICE NUMBER);
 
INSERT INTO TABLE_TEST VALUES(1,'A',10,1);
INSERT INTO TABLE_TEST VALUES(2,'A',20,1);
INSERT INTO TABLE_TEST VALUES(3,'A',30,1);
INSERT INTO TABLE_TEST VALUES(4,'A',40,1);
INSERT INTO TABLE_TEST VALUES(5,'B',100,1);
INSERT INTO TABLE_TEST VALUES(6,'B',200,2);
INSERT INTO TABLE_TEST VALUES(7,'B',300,1);
INSERT INTO TABLE_TEST VALUES(8,'B',400,2);
INSERT INTO TABLE_TEST VALUES(9,'C',1000,1);
INSERT INTO TABLE_TEST VALUES(10,'C',2000,2);
INSERT INTO TABLE_TEST VALUES(11,'C',3000,3);
INSERT INTO TABLE_TEST VALUES(12,'C',4000,4);

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> select * from TABLE_TEST;
 
        ID GROUPEMENT                     INDICATEUR     INDICE
---------- ------------------------------ ---------- ----------
         1 A                                      10          1
         2 A                                      20          1
         3 A                                      30          1
         4 A                                      40          1
         5 B                                     100          1
         6 B                                     200          2
         7 B                                     300          1
         8 B                                     400          2
         9 C                                    1000          1
        10 C                                    2000          2
        11 C                                    3000          3
        12 C                                    4000          4

L'idée de base est de calculer des agregats de INDICATEUR en fonction des valeurs de GROUPEMENT.
La requête de base serait :

Code:

1
2
3
4
5
6
7
SQL> select GROUPEMENT, sum(INDICATEUR) from TABLE_TEST group by GROUPEMENT;
 
GROUPEMENT                     SUM(INDICATEUR)
------------------------------ ---------------
A                                          100
B                                         1000
C                                        10000

Cependant les lignes sont marquées par un indicateur de cohérence, le champ INDICE. Ce champ permet de savoir à quel "lot" appartiennent les différentes lignes et donc à ne pas mélanger les lignes entre des lots. L'idée est donc de ne calculer la valeur d'INDICATEUR que pour les groupes qu'on peut qualifier de fiables, donc qui n'ont qu'UNE SEULE valeur d'indice.

Evidemment certains me diront qu'il suffit de faire ça :

Code:

1
2
3
4
5
6
7
8
9
10
11
SQL> select GROUPEMENT,INDICE, sum(INDICATEUR) from TABLE_TEST group by GROUPEMENT, INDICE;
 
GROUPEMENT                         INDICE SUM(INDICATEUR)
------------------------------ ---------- ---------------
A                                       1             100
B                                       1             400
B                                       2             600
C                                       1            1000
C                                       2            2000
C                                       3            3000
C                                       4            4000

et qu'on a toutes les sommes par GROUPEMENT et par INDICE, mais je ne vois pas comment, sans rajouter de sur-requête on peut arriver à ce résultat là:

Code:

1
2
3
4
5
SQL> select GROUPEMENT, sum(INDICATEUR) from TABLE_TEST group by GROUPEMENT having count(distinct INDICE)=1;
 
GROUPEMENT                     SUM(INDICATEUR)
------------------------------ ---------------
A                                          100

C'est à dire, ne retourner la somme de INDICATEUR que pour les groupes qui ne possèdent qu'UNE valeur UNIQUE d'INDICE. En effet en regardant les données on s'apperçoit que le GROUPEMENT A est le seul à possèder une valeur unique d'INDICE qui est 1. Les autres REGROUPEMENT possèdent 2 ou 3 valeurs différentes pour INDICE (2 valeurs distinctes d'INDICE pour B et 4 valeurs distinctes d'INDICE pour C).

Alors mon problème maintenant, j'aimerais réaliser la même chose mais sans sur ou sous requêtes, sans having count (distinct) mais avec une sur-couche PL/SQL.

Des idées ? Merci.

20/02/2006, 19h38
bpprive

Premièrement, bravo pour ton exemple, très facile à lire, très facile à reproduire, chapeau!

Juste une question : Pourquoi tu ne désires pas avoir de sous-requête SQL afin de faire le tout dans du PL/SQL? Où est le gain de performance dans ce besoin?

Autrement, les fonctions analytiques pourraient t'aider dans ta quête de performance et répondraient à ton besoin. Cependant, comme il est impossible de spécifier une fonction analytique dans une clause WHERE, tu dois englober le tout dans un sous-select...

Bonne chance!
20/02/2006, 20h33
nuke_y

Euh merci.

Mais les fonctions analytiques non plus je ne peux pas les utiliser en fait :?

Pourquoi ? He bien parce que cette requête doit être réalisée sous GENIO, un ETL qui ne possède pas toutes les finesses du SQL. Par contre il possède un solide bagage de scripting, donc je peux rajouter des couches de script pour faire ce que je veux. Je parlais de PL/SQL pour avoir une idée de l'algo, mais cet algo sera implémenté en script GENIO en fait.

Merci de ton aide, je posterais ici si je trouve une solution.

Et au fait, y a que moi qui savais pas que ça existait ce genre de choses en SQL ? Je veux dire pouvoir filtrer post agregat sur des données qu'on a pas ramené dans le select...
20/02/2006, 21h16
bpprive

Citation:

Et au fait, y a que moi qui savais pas que ça existait ce genre de choses en SQL ? Je veux dire pouvoir filtrer post agregat sur des données qu'on a pas ramené dans le select...

Avec de l'agrégat standard, pas de problème, avec de l'analytique, impossible. Si ce n'est pas la réponse que tu attendais, je ne suis pas certain de comprendre ce que tu voulais dire!

Tu peux toujours faire un order by sur ton

Code:

select GROUPEMENT,INDICE, sum(INDICATEUR) from TABLE_TEST group by GROUPEMENT, INDICE;

et une bonne vieille loop afin de vérifier si ton groupement s'y retrouve une seule fois!

Voici une solution qui si base sur le fait que si dans une liste min=max alors tous les éléments de la liste ont la même et unique valeur:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 begin for cg in ( select GROUPEMENT, min(INDICE) as gmin, max(INDICE) as gmax, sum(INDICATEUR) from TABLE_TEST group by GROUPEMENT ) loop if cg.gmin = cg.gmax then dbms_output.put_line('Groupement ' || cg.GROUPEMENT || ' OK'); end if; end loop; end; /

20/02/2006, 21h36
nuke_y

Euh certes mais la bonne vieille loop là, comment faire pour la lire sans avoir à rembobinner la série de données ?

Je veux dire : je lis mes premières données et tant que mon indice en varie pas, je sais que je peux intégrer mes chiffres. Mais si l'indice varie (dans le cas du B, à la 2e ligne) comment je fais pour savoir ? La seule solution serait soit de stocker les lignes en mémoire soit de pouvoir lire plusieurs fois ma boucle, ce qui n'est pas vraiment envisageable.