Agrégation MIN et COUNT ensemble

Version imprimable

Bonjour,

Je cherche a obtenir dans le même tableau le MIN(condition), le MAX(condition) puis ensuite d'obtenir le nombre de fois qu'une valeur est inférieur au min avant:

Exemple:
Code:

1 2 3 4 5 6 SELECT article, MIN(CASE WHEN jugement= 1 THEN mesure END) AS min_mesure, MAX(CASE WHEN jugement= 1 THEN mesure END) AS max_mesure, COUNT(CASE WHEN mesure1<? THEN 1 END) AS nb_mesure FROM mesure_table GROUP BY article
Dans ce code, je souhaite reprendre la valeur min_mesure du tableau à la place de ?

J'ai donc essayé ceci:
Code:

1 2 3 4 5 6 SELECT article, MIN(CASE WHEN jugement= 1 THEN mesure END) AS min_mesure, MAX(CASE WHEN jugement= 1 THEN mesure END) AS max_mesure, COUNT(CASE WHEN mesure1<min_mesure THEN 1 END) AS nb_mesure FROM mesure GROUP BY article
J'ai une erreur car min_mesure n'existe pas

J'ai donc écris ceci:
Code:

1 2 3 4 5 6 SELECT article, MIN(CASE WHEN jugement= 1 THEN mesure END) AS min_mesure, MAX(CASE WHEN jugement= 1 THEN mesure END) AS max_mesure, COUNT(CASE WHEN mesure1<MIN(CASE WHEN jugement= 1 THEN mesure END) THEN 1 END) AS nb_mesure FROM mesure GROUP BY article
Et j'ai une nouvelle erreur lié à la fonction d'agrégation.

J'aimerais compter le nombre de mesures en fonction de l'agrégation min(condition)
Pour le moment, n'ayant pas de solution, j'ai écris un seconde requete avec count(condition) conditionner dans la boucle de la première. Le problème est que le résultat est très très long.

Je pense qu'il y a une solution pour tout inclure dans le même tableau
Merci pour votre aide

19/05/2021, 13h49
tatayo

Bonjour,
Le "problème" ici est que le "min d'avant" n'a pas de sens en SQL.
Est-ce que tu peux nous donner un jeu de test et le résultat attendu ?
Et aussi le SGBD utilisé, certaines fonctions risquent de ne pas être disponible en fonction du moteur et de sa version.

Tatayo.

Bonjour et merci pour ta réponse

Justement l'expression me donne une valeur dans le tableau qui doit me permettre de de faire une comparaison.
EXEMPLE:
mesure_table

id article jugement mesure

1 article1 1 20

2 article2 0 15

3 article1 0 8

4 article1 0 7

5 article2 1 22

6 article2 0 15

7 article1 0 16

8 article2 1 21

9 article1 1 23

10 article1 1 13

11 article1 1 12

12 article2 1 15

13 article1 1 16

14 article2 0 18

Résultat attendu:

article min max compteur min

article1 12 23 count(mesure<12) -->2

article2 15 22 count(mesure<15) -->0

J'utilise H2 Database

19/05/2021, 14h21
escartefigue

pour utiliser le résultat de l'agrégat min(x) ou max(y) on ne peut pas utiliser son alias dans la même requête, il faut donc faite une requête imbriquée dans laquelle on calcule ces agrégats et utiliser les alias dans la requête principale
19/05/2021, 14h28
peofofo

Une requête imbriquée? C'est une requête dans une boucle? Comme j'utilise actuellement. Sauf que la requête imbriqué met 500ms environ donc 1 seconde pour 2 lignes, pour 100 lignes 50seconde!
Ou est ce possible d'utiliser une sous-requête ?
19/05/2021, 14h36
escartefigue
Non pas de boucle !
SQL (ou plutôt les bases de données relationnelles) est fait pour être ensembliste, une requête imbriquée dans une autre comme ceci :
Code:

1 2 3 4 5 6 7 8 9 SELECT subq.truc , subq.machin , subq.bidule from (SELECT... from where ) as subq where order by
19/05/2021, 14h50
peofofo

Je ne vois pas comment interpréter ton exemple.

J'ai testé ceci:

Code:

1
2
3
4
5
6
7
SELECT article,
MIN(CASE WHEN jugement= 1 THEN mesure END) AS min_mesure,
MAX(CASE WHEN jugement= 1 THEN mesure END) AS max_mesure,
(SELECT COUNT(*) AS mesure  FROM mesure m WHERE m.mesure>=min_mesure) AS test,
COUNT(CASE WHEN mesure1<MIN(CASE WHEN jugement= 1 THEN mesure END) THEN 1 END) AS nb_mesure
FROM mesure
GROUP BY article

Je retombe toujours sur le même problème de min_mesure

Les colonnes calculées dans la clause SELECT ne peuvent pas être utilisées dans la clause WHERE.

Par contre suivant le SGBD utilisé (non précisé ici), tu peux utiliser des CTE.
Exemple:
Pour calculer les min,max, la requête ressemble à ceci:
Code:

1 2 3 4 select article,min(mesure),max(mesure) from mesure_table group by article
En la plaçant dans une CTE, tu peux alors l'utiliser comme une table:
Code:

1 2 3 4 5 6 7 8 9 10 11 with stat(article,minmesure,maxmesure) as ( select article,min(mesure),max(mesure) from mesure_table group by article ) select x from table_mesure inner join stat on stat.article = table_mesure.article where table_mesure.mesure < stat.minmesure
Je te laisse adapter la requête pour y ajouter les filtres/agrégations nécessaires.

Tatayo

Avec votre jeu d'essai enrichi d'un troisième article comme suit :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
with T1(id, article, jugement, mesure) as
    (select 01, 'article1', 1, 20 union all
     select 02, 'article2', 0, 15 union all
     select 03, 'article1', 0, 08 union all
     select 04, 'article1', 0, 07 union all
     select 05, 'article2', 1, 22 union all
     select 06, 'article2', 0, 15 union all
     select 07,	'article1', 0, 16 union all
     select 08, 'article2', 1, 21 union all
     select 09, 'article1', 1, 23 union all
     select 10, 'article1', 1, 13 union all
     select 11, 'article1', 1, 12 union all
     select 12, 'article2', 1, 15 union all
     select 13, 'article1', 1, 16 union all
     select 14, 'article2', 0, 18 union all
     select 15, 'truc    ', 1, 16 union all
     select 16, 'truc    ', 0, 27
    )

Qu'on utilise ensuite une requête imbriquée corrélée comme ici:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select T2.art
     , T2.mini
     , T2.maxi
     , count(*)
from (select article      art 
          , min(mesure)   mini
          , max(mesure)   maxi
     from T1 
     where jugement=1
     group by article
    ) as T2 
left join T1
   on T1.mesure<T2.mini
group by T2.art
       , T2.mini
       , T2.maxi

Ou qu'on passe par une CTE comme là (T2 est déclarée à la suite de T1 grâce au "with") :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
   , T2 (art, mini, maxi) as 
    (select article     
          , min(mesure) 
          , max(mesure) 
     from T1 
     where jugement=1
     group by article
    )
select T2.art
     , T2.mini
     , T2.maxi
     , count(*)
from T2 
left join T1
   on T1.mesure<T2.mini
group by T2.art
       , T2.mini
       , T2.maxi

On obtient dans les deux cas :

art	mini	maxi	(No column name)
article1	12	23	2
article2	15	22	4
truc	16	16	7

à noter que pour article2, le résultat est 4 et non pas zéro contrairement à ce que vous avez indiqué dans le résultat attendu du post n°3, ou alors il faut expliquer la règle de calcul :weird:

Merci à tous les deux,

J'ai compris désormais comment fonctionne le CTE.
Ma base de donnée est une base embarqué H2 Database.
Cette base accepte CTE.

@escartefigue, il n'y a pas de mesure inférieure à 15 dans article2, donc sa valeur est de 0.

Voici ce que j'ai écris (j'ai l'impression que ça fonctionne)
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 WITH stat(article,min_mesure,max_mesure) as ( SELECT article, MIN(CASE WHEN jugement= 1 THEN mesure END) AS min_mesure, MAX(CASE WHEN jugement= 1 THEN mesure END) AS max_mesure, from mesure_table group by article ) select stat.article, stat.min_mesure, stat.max_mesure, COUNT(CASE WHEN mesure< stat.min_mesure THEN 1 END) AS nb_min from mesure_table inner join stat on stat.article = mesure_table.article GROUP BY mesure_table.article
J'ai bien 3 champs regroupés par articles.

Qu'en pensez-vous?

Je croyais que le comptage du nombre de cas < au mini était sans condition sur le code article.

En ce cas il faut une équijointure sur le code article

Votre requête peut fonctionner mais en ajoutant un regroupement sur les colonnes non agrégées, ce qui donne :
Code:

1 2 3 4 5 6 7 8 9 10 select stat.article , stat.min_mesure , stat.max_mesure , COUNT(CASE WHEN mesure< stat.min_mesure THEN 1 END) AS nb_min from mesure_table inner join stat on stat.article = mesure_table.article GROUP BY stat.article , stat.min_mesure , stat.max_mesure

id	article	jugement	mesure
1	article1	1	20
2	article2	0	15
3	article1	0	8
4	article1	0	7
5	article2	1	22
6	article2	0	15
7	article1	0	16
8	article2	1	21
9	article1	1	23
10	article1	1	13
11	article1	1	12
12	article2	1	15
13	article1	1	16
14	article2	0	18

article	min	max	compteur min
article1	12	23	count(mesure<12) -->2
article2	15	22	count(mesure<15) -->0