La clause QUALIFY permet de filtrer les résultats des fonctions analytiques dans une instruction SELECT. Sa relation avec les fonctions analytiques est comparable à celle entre les clauses HAVING et GROUP BY.
Sans la clause QUALIFY, le seul moyen de filtrer les résultats d’une fonction analytique consiste à imbriquer la requête et à appliquer le filtre dans une requête parente, ce qui alourdit le code, augmente la charge de maintenance et peut rendre l’intention de la requête moins claire pour le lecteur.
La clause QUALIFY permet de contourner toutes ces limitations.
Nous allons créer deux tables et y insérer des données pour illustrer l’utilisation de cette fonction magique :
SQL>create table dept (
deptnum number(2) constraint pk_dept primary key,
deptname varchar2(20),
etage varchar2(20)
);
SQL>create table emp (
empnum number(4) constraint pk_emp primary key,
empname varchar2(20),
job varchar2(20),
sal number(8),
deptnum number(2) constraint fk_deptno references dept
);
create index emp_dept_fk_i on emp(deptnum);
INSERT INTO dept (deptnum, deptname, etage) VALUES (10, 'COMPTABILITE', '1ER ETAGE');
INSERT INTO dept (deptnum, deptname, etage) VALUES (20, 'JURIDIQUE', '2EME ETAGE');
INSERT INTO dept (deptnum, deptname, etage) VALUES (30, 'VENTES', '3EME ETAGE');
INSERT INTO dept (deptnum, deptname, etage) VALUES (40, 'INFORMATIQUE', '4EME ETAGE');
INSERT INTO dept (deptnum, deptname, etage) VALUES (50, 'RESSOURCES HUMAINES', '5EME ETAGE');
Commit;
-- Département 10 : COMPTABILITE
INSERT INTO emp (empnum, empname, job, sal, deptnum) VALUES (1001, 'JEAN DURAND', 'DIRECTEUR', 39000, 10);
INSERT INTO emp (empnum, empname, job, sal, deptnum) VALUES (1007, 'CLAIRE ROUX', 'COMPTABLE', 7200, 10);
INSERT INTO emp (empnum, empname, job, sal, deptnum) VALUES (1008, 'PAUL BENOIT', 'ASSISTANT', 6500, 10);
INSERT INTO emp (empnum, empname, job, sal, deptnum) VALUES (1002, 'MARC MARTIN', 'ANALYSTE', 15000, 20);
INSERT INTO emp (empnum, empname, job, sal, deptnum) VALUES (1009, 'ANNE GIRAUD', 'CHERCHEUR', 23600, 20);
INSERT INTO emp (empnum, empname, job, sal, deptnum) VALUES (1003, 'SOPHIE LEBRUN', 'COMMERCIAL', 15000, 30);
INSERT INTO emp (empnum, empname, job, sal, deptnum) VALUES (1005, 'EMILIE PETIT', 'COMMERCIAL', 13000, 30);
INSERT INTO emp (empnum, empname, job, sal, deptnum) VALUES (1011, 'ALAIN NOIR', 'CHEF VENTE', 17000, 30);
INSERT INTO emp (empnum, empname, job, sal, deptnum) VALUES (1004, 'LUCAS BERTRAND', 'TECHNICIEN', 7000, 40);
INSERT INTO emp (empnum, empname, job, sal, deptnum) VALUES (1012, 'JULIE MASSON', 'DEVELOPPEUR', 10000, 40);
INSERT INTO emp (empnum, empname, job, sal, deptnum) VALUES (1006, 'NATHALIE MOREAU', 'DRH', 30000, 50);
INSERT INTO emp (empnum, empname, job, sal, deptnum) VALUES (1014, 'SANDRA MICHEL', 'RECRUTEUR', 15000, 50);
INSERT INTO emp (empnum, empname, job, sal, deptnum) VALUES (1015, 'DIDIER ROLLAND', 'ASSISTANT RH', 13000, 50);
Commit;
Langage du code : JavaScript (javascript)Besoin :
Passons maintenant au vif du sujet, pour calculer le salaire moyen par département tout en conservant l’affichage des données détaillées de chaque employé, il faut utiliser la fonction analytique AVG() OVER (PARTITION BY)
SQL>select empnum, deptnum, sal,
avg(sal) over (partition by deptnum) as avg_dept_sal
from emp;
EMPNUM DEPTNUM SAL AVG_DEPT_SAL
---------- ---------- ---------- ------------
1001 10 39000 17566,6667
1007 10 7200 17566,6667
1008 10 6500 17566,6667
1002 20 15000 19300
1009 20 23600 19300
1003 30 15000 15000
1005 30 13000 15000
1011 30 17000 15000
1004 40 7000 8500
1012 40 10000 8500
1006 50 30000 19333,3333
1014 50 15000 19333,3333
1015 50 13000 19333,3333
13 lignes selectionnees.Langage du code : JavaScript (javascript)Allons plus loin : si l’on souhaite exclure les lignes pour lesquelles la moyenne des salaires du département est inférieure ou égale à 15 000, le premier réflexe est généralement de penser à l’utilisation d’une requête imbriquée avec une clause WHERE.
SQL>select * from
(select empnum, deptnum, sal,avg(sal) over (partition by deptnum) as avg_dept_sal from emp)
where avg_dept_sal > 15000;
EMPNUM DEPTNUM SAL AVG_DEPT_SAL
---------- ---------- ---------- ------------
1001 10 39000 17566,6667
1007 10 7200 17566,6667
1008 10 6500 17566,6667
1002 20 15000 19300
1009 20 23600 19300
1006 50 30000 19333,3333
1014 50 15000 19333,3333
1015 50 13000 19333,3333
8 lignes selectionnees.Langage du code : JavaScript (javascript)Solution:
Nouvelle clause SQL introduite dans Oracle AI Database 26ai, QUALIFY permet de sélectionner uniquement les lignes qui répondent à une condition basée sur des calculs effectués dans la requête, notamment via des fonctions analytiques. Elle s’utilise pour filtrer les résultats après l’exécution de ces calculs, ce qui la rend particulièrement pratique dans les requêtes avancées.
SQL>select empnum, deptnum, sal,avg(sal) over (partition by deptnum) as avg_dept_sal
from emp
qualify avg_dept_sal > 15000;
EMPNUM DEPTNUM SAL AVG_DEPT_SAL
---------- ---------- ---------- ------------
1001 10 39000 17566,6667
1007 10 7200 17566,6667
1008 10 6500 17566,6667
1002 20 15000 19300
1009 20 23600 19300
1006 50 30000 19333,3333
1014 50 15000 19333,3333
1015 50 13000 19333,3333
8 lignes selectionnees.Langage du code : JavaScript (javascript)
Poster un Commentaire