Requête agrégation deux fois sur la même table

Bonjour,

je pose la question ici car la base est FB mais je pense que c'est une question SQL générale

soit 3 tables

Praticiens
Utilisateurs
Appels

un appel est fait pour un praticien et c'est un utilisateur qui décroche (ou pas => 0)

un appel a une date d'appel, date de décrochage (éventuelle) et une date de fin d'appel

je voudrais avoir un tableau avec

Praticien | Utilisateur | Nombre d'appels | Temps de communication

ça c'est facile, mais je dois distinguer les appels de plus et moins 10 secondes DATEDIFF(SECOND FROM Decrochage TO FinAppel)il me faut alors

Praticien | Utilisateur | Nombre d'appels de +10s | Temps de comm | Nombre d'appels de -10s | Temps de comm

j'ai une requête qui marche presque, mais elle ne me retourne pas les lignes quand aucun appel pour un couple praticien, utilisateur ne dépasse 10s

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

SELECT p.Nom AS Praticien, u.Nom AS Utilisateur, d1.Appels AS Appels1, d1.Duree AS Duree1, d2.Appels AS Appels2, d2.Duree AS Duree2   FROM Praticiens p   JOIN ( SELECT Praticien, Utilisateur, COUNT(*) AS Appels, SUM(DATEDIFF(SECOND FROM Decrochage TO FinAppel)) AS Duree FROM Appels WHERE DATEDIFF(SECOND FROM Decrochage TO FinAppel) >= 10 GROUP BY Praticien, Utilisateur ) d1 ON d1.Praticien = p.id   JOIN Utilisateurs u ON u.ID = d1.Utilisateur   LEFT JOIN ( SELECT Praticien, Utilisateur, COUNT(*) AS Appels, SUM(DATEDIFF(SECOND FROM Decrochage TO FinAppel)) AS Duree FROM Appels WHERE DATEDIFF(SECOND FROM Decrochage TO FinAppel) < 10 GROUP BY Praticien, Utilisateur ) d2 ON d2.Praticien = p.id AND d2.Utilisateur = u.ID

---

je vois bien le pourquoi de cette limite mais je ne vois pas comment faire le lien entre p, d1, d2 et u autrement.

Merci

Bonjour,

Quand je tombe sur des requêtes comme celle-ci je commence par utiliser des CTE

Code:

---

1 2 3 4 5 6 7 8 9 10

WITH d1 AS (SELECT Praticien, Utilisateur, COUNT(*) AS Appels, SUM(DATEDIFF(SECOND FROM Decrochage TO FinAppel)) AS Duree FROM Appels WHERE DATEDIFF(SECOND FROM Decrochage TO FinAppel) >= 10 GROUP BY Praticien, Utilisateur), d2 AS (SELECT Praticien, Utilisateur, COUNT(*) AS Appels, SUM(DATEDIFF(SECOND FROM Decrochage TO FinAppel)) AS Duree FROM Appels WHERE DATEDIFF(SECOND FROM Decrochage TO FinAppel) < 10 GROUP BY Praticien, Utilisateur) // à suivre

---

Histoire de simplifier la suite
ensuite, il me semble qu'il en faut encore une (CTE) qui va obtenir les couples Praticien,Utilisateur (il serait certainement possible de faire sans mais "diviser pour mieux régner" (C pour Couples)

Code:

---

1 2 3 4

, C AS (SELECT Praticien,Utilisateur,count(*) AS TotalAppels FROM Appels GROUP BY Praticien,Utilisateur)   // à suivre

---

Maintenant que les 'tables temporaires' sont faites il suffit de les utiliser

Code:

---

1 2 3

SELECT C.Praticien,C.Utilisateur,C.TotalAppels,COALESCE(d1.Appels,0) Appels1 ,COALESCE(d1.Duree,0) Duree1,COALESCE(d2.Appels,0) Appels2 ,COALESCE(d2.Duree,0) Duree2 FROM C LEFT JOIN d1 ON d1.Praticien=c.Praticien AND d1.Utilisateur=c.Utilisateur LEFT JOIN d2 ON d2.Praticien=c.Praticien AND d2.Utilisateur=c.Utilisateur

---

Voilà, il n'y a qu'à ;) mettre tous les morceaux ensemble et je pense que la table résultat devrait te satisfaire (si j'ai bien saisi le problème)
Il y a certainement mieux avec FB3 et les fonctions de fenêtrage mais 1) tu n'indiques pas quelle version de Firebird 2) je ne maitrise absolument pas ces fonctions :(

génial !

je ne connaissais pas cette syntaxe :)

Merci !

Et pourquoi pas plus simple en une seule requête ?

Code:

---

1 2 3 4 5 6 7 8 9 10

SELECT Praticien, Utilisateur, COUNT(*) AS Appels, SUM(DATEDIFF(SECOND FROM Decrochage TO FinAppel)) AS Duree, CASE WHEN DATEDIFF(SECOND FROM Decrochage TO FinAppel) >= 10 THEN 'RETARD' ELSE 'OK' END AS LIMITE FROM Appels WHERE GROUP BY Praticien, Utilisateur, CASE WHEN DATEDIFF(SECOND FROM Decrochage TO FinAppel) >= 10 THEN 'RETARD' ELSE 'OK' END

---

A +

Bonjour,

il est vrai que ce serait plus simple.
Il y a plusieurs raisons qui m'ont fait proposer les CTE
1 - je voulais reprendre ce que Paul avait déjà écrit
2 - je n'étais pas sûr qu'il ne veuille pas ajouter les noms et autres informations des praticiens et utilisateurs
3 - je me laissais la possibilité de proposer une CTE récursive pour obtenir tous les couples Praticien/utilisateur

Salut à tous.

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 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226

CREATE DATABASE '..\Data\Base.fdb' page_size 4096 DEFAULT CHARACTER SET WIN1252;   -- ================================ -- Création de la table 'praticien' -- ================================   create table praticien ( id smallint generated by default as identity not null, nom varchar(20) not null, prenom varchar(25) not null, primary key (id) );   -- ========================== -- Insertion dans 'praticien' -- ==========================   insert into praticien (nom,prenom) values ('Nom praticien 1','Prénom praticien 1'); insert into praticien (nom,prenom) values ('Nom praticien 2','Prénom praticien 2');   -- ===================== -- Vidage de 'praticien' -- =====================   select * from praticien;   ID NOM PRENOM ======= ==================== ========================= 1 Nom praticien 1 Prénom praticien 1 2 Nom praticien 2 Prénom praticien 2     -- ================================== -- Création de la table 'utilisateur' -- ==================================   create table utilisateur ( id smallint generated by default as identity not null, nom varchar(20) not null, prenom varchar(25) not null, primary key (id) );   -- ============================ -- Insertion dans 'utilisateur' -- ============================   insert into utilisateur (nom,prenom) values ('Nom utilisateur 1','Prénom utilisateur 1'); insert into utilisateur (nom,prenom) values ('Nom utilisateur 2','Prénom utilisateur 2'); insert into utilisateur (nom,prenom) values ('Nom utilisateur 3','Prénom utilisateur 3');   -- ======================= -- Vidage de 'utilisateur' -- =======================   select * from utilisateur;   ID NOM PRENOM ======= ==================== ========================= 1 Nom utilisateur 1 Prénom utilisateur 1 2 Nom utilisateur 2 Prénom utilisateur 2 3 Nom utilisateur 3 Prénom utilisateur 3     commit;   -- ============================ -- Création de la table 'appel' -- ============================   create table appel ( id smallint generated by default as identity not null, praticien smallint not null REFERENCES praticien (id) ON DELETE CASCADE ON UPDATE CASCADE, utilisateur smallint not null REFERENCES utilisateur (id) ON DELETE CASCADE ON UPDATE CASCADE, decrochage timestamp not null, finappel timestamp not null, primary key (id) );   CREATE INDEX IDX_PRATICIEN ON appel (praticien); CREATE INDEX IDX_UTILISATEUR ON appel (utilisateur);   -- ====================== -- Insertion dans 'appel' -- ======================   insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 1, '2019-06-01 11:37:45', '2019-06-01 11:37:48'); insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 2, '2019-06-01 11:39:45', '2019-06-01 11:42:28'); insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 3, '2019-06-01 11:43:17', '2019-06-01 11:43:22'); insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 1, '2019-06-01 11:45:29', '2019-06-01 11:50:01'); insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 2, '2019-06-01 11:47:33', '2019-06-01 11:47:38'); insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 3, '2019-06-01 11:49:12', '2019-06-01 11:51:37');   insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 1, '2019-06-01 11:50:24', '2019-06-01 11:50:25'); insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 2, '2019-06-01 11:53:18', '2019-06-01 11:58:24'); insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 3, '2019-06-01 11:55:47', '2019-06-01 12:05:12'); insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 1, '2019-06-01 12:15:33', '2019-06-01 12:15:38'); insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 2, '2019-06-01 12:17:27', '2019-06-01 12:20:57'); insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 3, '2019-06-01 12:23:23', '2019-06-01 12:23:45');   insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 1, '2019-06-01 14:37:45', '2019-06-01 14:37:48'); insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 2, '2019-06-01 14:39:45', '2019-06-01 14:42:28'); insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 3, '2019-06-01 14:43:17', '2019-06-01 14:43:22'); insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 1, '2019-06-01 14:45:29', '2019-06-01 14:50:01'); insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 2, '2019-06-01 14:47:33', '2019-06-01 14:47:38'); insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 3, '2019-06-01 14:49:12', '2019-06-01 14:51:37');   insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 1, '2019-06-01 14:50:24', '2019-06-01 14:50:25'); insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 2, '2019-06-01 14:53:18', '2019-06-01 14:58:24'); insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 3, '2019-06-01 14:55:47', '2019-06-01 15:05:12'); insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 1, '2019-06-01 15:15:33', '2019-06-01 15:15:38'); insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 2, '2019-06-01 15:17:27', '2019-06-01 15:20:57'); insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 3, '2019-06-01 15:23:23', '2019-06-01 15:23:45');   insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 1, '2019-06-01 16:37:45', '2019-06-01 16:37:48'); insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 2, '2019-06-01 16:39:45', '2019-06-01 16:42:28'); insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 3, '2019-06-01 16:43:17', '2019-06-01 16:43:22'); insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 1, '2019-06-01 16:45:29', '2019-06-01 16:50:01'); insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 2, '2019-06-01 16:47:33', '2019-06-01 16:47:38'); insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 3, '2019-06-01 16:49:12', '2019-06-01 16:51:37');   insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 1, '2019-06-01 16:50:24', '2019-06-01 16:50:25'); insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 2, '2019-06-01 16:53:18', '2019-06-01 16:58:24'); insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 3, '2019-06-01 16:55:47', '2019-06-01 17:05:12'); insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 1, '2019-06-01 17:15:33', '2019-06-01 17:15:38'); insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 2, '2019-06-01 17:17:27', '2019-06-01 17:20:57'); insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 3, '2019-06-01 17:23:23', '2019-06-01 17:23:45');   -- ================= -- Vidage de 'appel' -- =================   select * from appel;   ID PRATICIEN UTILISATEUR DECROCHAGE FINAPPEL ======= ========= =========== ========================= ========================= 1 1 1 2019-06-01 11:37:45.0000 2019-06-01 11:37:48.0000 2 2 2 2019-06-01 11:39:45.0000 2019-06-01 11:42:28.0000 3 1 3 2019-06-01 11:43:17.0000 2019-06-01 11:43:22.0000 4 2 1 2019-06-01 11:45:29.0000 2019-06-01 11:50:01.0000 5 1 2 2019-06-01 11:47:33.0000 2019-06-01 11:47:38.0000 6 2 3 2019-06-01 11:49:12.0000 2019-06-01 11:51:37.0000 7 1 1 2019-06-01 11:50:24.0000 2019-06-01 11:50:25.0000 8 2 2 2019-06-01 11:53:18.0000 2019-06-01 11:58:24.0000 9 1 3 2019-06-01 11:55:47.0000 2019-06-01 12:05:12.0000 10 2 1 2019-06-01 12:15:33.0000 2019-06-01 12:15:38.0000 11 1 2 2019-06-01 12:17:27.0000 2019-06-01 12:20:57.0000 12 2 3 2019-06-01 12:23:23.0000 2019-06-01 12:23:45.0000 13 1 1 2019-06-01 14:37:45.0000 2019-06-01 14:37:48.0000 14 2 2 2019-06-01 14:39:45.0000 2019-06-01 14:42:28.0000 15 1 3 2019-06-01 14:43:17.0000 2019-06-01 14:43:22.0000 16 2 1 2019-06-01 14:45:29.0000 2019-06-01 14:50:01.0000 17 1 2 2019-06-01 14:47:33.0000 2019-06-01 14:47:38.0000 18 2 3 2019-06-01 14:49:12.0000 2019-06-01 14:51:37.0000 19 1 1 2019-06-01 14:50:24.0000 2019-06-01 14:50:25.0000 20 2 2 2019-06-01 14:53:18.0000 2019-06-01 14:58:24.0000   ID PRATICIEN UTILISATEUR DECROCHAGE FINAPPEL ======= ========= =========== ========================= ========================= 21 1 3 2019-06-01 14:55:47.0000 2019-06-01 15:05:12.0000 22 2 1 2019-06-01 15:15:33.0000 2019-06-01 15:15:38.0000 23 1 2 2019-06-01 15:17:27.0000 2019-06-01 15:20:57.0000 24 2 3 2019-06-01 15:23:23.0000 2019-06-01 15:23:45.0000 25 1 1 2019-06-01 16:37:45.0000 2019-06-01 16:37:48.0000 26 2 2 2019-06-01 16:39:45.0000 2019-06-01 16:42:28.0000 27 1 3 2019-06-01 16:43:17.0000 2019-06-01 16:43:22.0000 28 2 1 2019-06-01 16:45:29.0000 2019-06-01 16:50:01.0000 29 1 2 2019-06-01 16:47:33.0000 2019-06-01 16:47:38.0000 30 2 3 2019-06-01 16:49:12.0000 2019-06-01 16:51:37.0000 31 1 1 2019-06-01 16:50:24.0000 2019-06-01 16:50:25.0000 32 2 2 2019-06-01 16:53:18.0000 2019-06-01 16:58:24.0000 33 1 3 2019-06-01 16:55:47.0000 2019-06-01 17:05:12.0000 34 2 1 2019-06-01 17:15:33.0000 2019-06-01 17:15:38.0000 35 1 2 2019-06-01 17:17:27.0000 2019-06-01 17:20:57.0000 36 2 3 2019-06-01 17:23:23.0000 2019-06-01 17:23:45.0000     commit;   -- =========== -- Requête N°1 -- ===========   with CTE_1 as ( SELECT p.nom as Praticien, u.nom as Utilisateur, datediff(SECOND FROM a.decrochage TO a.finAppel) as duree   FROM appel as a   INNER JOIN praticien as p ON p.id = a.praticien   INNER JOIN utilisateur as u ON u.id = a.utilisateur ), CTE_2 as ( SELECT Praticien, Utilisateur, case when duree <=10 then duree else null end as moins, case when duree > 10 then duree else null end as plus FROM CTE_1 ), CTE_3 as ( SELECT Praticien, utilisateur, cast( sum(moins) as smallint) as moins_de_10, cast( sum(plus) as smallint) as plus_de_10, cast(count(moins) as smallint) as nbre_moins_de_10, cast(count(plus) as smallint) as nbre_plus_de_10   FROM CTE_2 GROUP BY Praticien, Utilisateur )   select * from CTE_3;   PRATICIEN UTILISATEUR MOINS_DE_10 PLUS_DE_10 NBRE_MOINS_DE_10 NBRE_PLUS_DE_10 ==================== ==================== =========== ========== ================ =============== Nom praticien 1 Nom utilisateur 1 12 <null> 6 0 Nom praticien 1 Nom utilisateur 2 15 630 3 3 Nom praticien 1 Nom utilisateur 3 15 1695 3 3 Nom praticien 2 Nom utilisateur 1 15 816 3 3 Nom praticien 2 Nom utilisateur 2 <null> 1407 0 6 Nom praticien 2 Nom utilisateur 3 <null> 501 0 6   exit;   Appuyez sur une touche pour continuer...

---

J'ai décomposé le CTE en trois phases afin de bien montrer comment le calcul se fait.
J'ai utilisé le case ... end pour justement décomposer le calcul en moins de 10 secondes et en plus de 10 secondes.
Pour le count(), l'astuce est d'utiliser le NULL car il n'est pas comptabilisé.
Pour chaque couple (praticien, utilisateur), il y a toujours six lignes dans mon exemple.

@+

Re-Salut à tous.

La solution proposée par SQLPRO fonctionne parfaitement.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

SELECT Praticien, Utilisateur, COUNT(*) AS Appels, SUM(DATEDIFF(SECOND FROM Decrochage TO FinAppel)) AS Duree, CASE WHEN DATEDIFF(SECOND FROM Decrochage TO FinAppel) >= 10 THEN 'RETARD' ELSE 'OK' END AS LIMITE FROM Appel GROUP BY Praticien, Utilisateur, CASE WHEN DATEDIFF(SECOND FROM Decrochage TO FinAppel) >= 10 THEN 'RETARD' ELSE 'OK' END ;   PRATICIEN UTILISATEUR APPELS DUREE LIMITE ========= =========== ===================== ===================== ====== 1 1 6 12 OK 1 2 3 15 OK 1 2 3 630 RETARD 1 3 3 15 OK 1 3 3 1695 RETARD 2 1 3 15 OK 2 1 3 816 RETARD 2 2 6 1407 RETARD 2 3 6 501 RETARD   exit;

---

J'avais cru comprendre que le résultat recherché devait être sur 1 et 1 seule ligne.

@+