SQL COUNT et DISTINCT

**soshin** · 16/12/2018, 10h12

Bonjour tout le monde,
Heureux de vous retrouver :-)

Je me permets de vous soumettre mon problème :

- j'associe et je stocke dans une table les ID d'un patient, d'un prestataire et d'une prestation au format AAAAMMDDXXX (avec AAAA année en 4 chiffres, MM mois en 2 chiffres, DD jour en 2 chiffres, et XXX un numéro d'ordre). En somme, chaque prestation est représentée par ces 3 informations :

PRESTATAIRE 004
PATIENT 034
PRESTATION 20181215003

- il peut y avoir plusieurs prestations par jour, et parfois pour une même personne. Par exemple :

PRESTATAIRE 004
PATIENT 034
PRESTATION 20181215003

et

PRESTATAIRE 004
PATIENT 034
PRESTATION 20181215004

et

PRESTATAIRE 004
PATIENT 040
PRESTATION 20181215005

etc...

- j'aimerais simplement savoir combien de patients un prestataire a vus sur un mois par exemple. C'est à dire, éviter les doublons si un patient a eu 2 séances certains jours...

Je sais que ceci n'est pas correct :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT COUNT(DISTINCT prestation) FROM base WHERE prestation LIKE "201812%" AND prestataire LIKE "004"

--> le DISTINCT ne sert à rien vu que chaque code de prestation est unique... il ne filtre pas les doublons si une personne a eu 2 séances le même jour...

mais je ne vois pas comment sortir de là :-)
Il faudrait comparer les 8 premiers chiffres de mon identifiant de prestation...

Quelqu'un pourrait-il m'éclairer svp ?
D'avance, merci

Cédric

**SQLpro** · 16/12/2018, 11h09

Envoyé par soshin

Je sais que ceci n'est pas correct :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT COUNT(DISTINCT prestation) FROM base WHERE prestation LIKE "201812%" AND prestataire LIKE "004"

Beaucoup d'erreur dans votre requête :
1) les chaines de caractères sont délimités par des apostrophes ( ' ) et non des guillemets
2) une date est un type DATE et non une chaine de caractères. On ne peut pas lui appliquer l'opérateur LIKE… Les résultats seraient hasardeux !
3) utiliser le LIKE pour une égalité est stupide car cela bouffe plus de ressources et donc diminue les performances

Pour résoudre votre problème, il suffit d'utiliser une fonction de fenêtrage :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT SUM(COUNT(prestation)) OVER() AS NOMBRE
FROM   base 
WHERE  prestation BETWEEN '2018-12-01' AND '2018-12-31'
  AND  prestataire = '004'
GROUP  BY prestation

Lisez l'article que j'ai écrit au sujet des fonctions de fenêtrage :
https://sqlpro.developpez.com/articl...clause-window/

Lisez mon livre pour apprendre le langage SQL :
Nom : SQL.jpg
Affichages : 1721
Taille : 47,4 Ko

Nom : SQL.jpg
Affichages : 1721
Taille : 47,4 Ko

A +

**soshin** · 16/12/2018, 17h29

Merci pour votre réponse, qui m'aide déjà à avancer

Envoyé par SQLpro

1) les chaines de caractères sont délimités par des apostrophes ( ' ) et non des guillemets

Ce que je ne vous ai pas dit, c'est que j'appelle ma requête SQL dans une variable php ($req = 'SELECT...'; ) et que du coup dans mon cas cela générait des erreurs.
Cependant mes autres requêtes semblent bien fonctionner avec des guillemets. Pourriez-vous me dire quels problèmes cela peut-il amener, peut-être dans d'autres circonstances ?

Envoyé par SQLpro

2) une date est un type DATE et non une chaine de caractères. On ne peut pas lui appliquer l'opérateur LIKE… Les résultats seraient hasardeux !

L'architecture, peut-être inadéquate j'en conviens, ne travaille pas avec des dates directement mais bien avec une chaine de 11 caractères (les 8 premiers évoquant la date effectivement). Je recherche donc lorsque je parcours ma base, s'il existe pour un même patient un autre identifiant commençant par les 8 mêmes caractères. Dans l'affirmative je ne n'en tiens pas compte dans le COUNT.

Envoyé par SQLpro

3) utiliser le LIKE pour une égalité est stupide car cela bouffe plus de ressources et donc diminue les performances

Je vois effectivement que ce n'est pas l'idéal, je vais revoir ce point vous avez raison.

Envoyé par SQLpro

Pour résoudre votre problème, il suffit d'utiliser une fonction de fenêtrage :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT SUM(COUNT(prestation)) OVER() AS NOMBRE
FROM   base 
WHERE  prestation BETWEEN '2018-12-01' AND '2018-12-31'
  AND  prestataire = '004'
GROUP  BY prestation

Je vais donc de ce pas consulter votre article pour voir et comprendre votre requête.

J'avance, j'avance...

Merci !

**aieeeuuuuu** · 20/12/2018, 17h53

Bonjour,

je ne suis pas sur d'avoir bien compris, mais si vous voulez le nombre distinct de patient, pourquoi pas simplement un COUNT(DISTINCT PATIENT) ?

**Artemus24** · 26/12/2018, 13h42

Salut à tous.

J'aimerai connaitre le descriptif de votre table afin de savoir si vous avez correctement déclaré vos colonnes et identifiant ?

Ensuite, il serait intéressant de connaitre aussi toutes vous requêtes qui viennent interroger votre table.
Par exemple, est-il nécessaire d'avoir la colonne prestation où vous concaténez l'année, le mois, le jour et un numéro séquentiel ?
Éclater cette colonne en quatre colonnes n'est pas si absurde à la condition que vos requêtes utilise séparément l'année, et le mois.

@+

**soshin** · 29/12/2018, 21h01

Déjà un grand merci à vous pour vous être intéressés à mon problème ;-)

Envoyé par aieeeuuuuu

Je ne suis pas sur d'avoir bien compris, mais si vous voulez le nombre distinct de patient, pourquoi pas simplement un COUNT(DISTINCT PATIENT) ?

Envoyé par Artemus24

J'aimerai connaitre le descriptif de votre table afin de savoir si vous avez correctement déclaré vos colonnes et identifiant ?

Ensuite, il serait intéressant de connaitre aussi toutes vous requêtes qui viennent interroger votre table.
Par exemple, est-il nécessaire d'avoir la colonne prestation où vous concaténez l'année, le mois, le jour et un numéro séquentiel ?
Éclater cette colonne en quatre colonnes n'est pas si absurde à la condition que vos requêtes utilise séparément l'année, et le mois.

@+

Pour vous répondre à tous les deux, voici la structure de ma table :

TABLE ENREGISTREMENTS :

PRESTATION : numéro unique inspiré de la date de prestation, au format AAAAmmddXXX (avec XXX le numéro d'ordre pour le jour donné) (exemple : 20181231034 pour la 34ème prestation du 31 décembre 2018)
PRESTATAIRE : format XXX (exemple : 012)
PATIENT : numéro unique au format XXX (exemple : 354)
CODE : code spécifique à la prestation format XXX (exemple : 009)

IMPORTANT : un patient peut se voir facturer 2 codes pour une même séance !
C'est la source de mon "problème".

Exemples d'enregistrements :

prestation "20181229001" --> prestataire "004" --> patient "012" --> code "008"
prestation "20181230001" --> prestataire "004" --> patient "012" --> code "001"
prestation "20181230002" --> prestataire "004" --> patient "012" --> code "003"
prestation "20181230003" --> prestataire "006" --> patient "010" --> code "001"
prestation "20181230004" --> prestataire "010" --> patient "123" --> code "001"
prestation "20181231001" --> prestataire "010" --> patient "123" --> code "001"
prestation "20181231002" --> prestataire "004" --> patient "012" --> code "001"
prestation "20181231003" --> prestataire "004" --> patient "076" --> code "008"
prestation "20181231004" --> prestataire "004" --> patient "076" --> code "012"

L'objectif est de connaître combien de séances ont été réalisées par le prestataire "XXX" sur le mois de décembre par exemple (et non le nombre de codes prestés).
==> Avec mon exemple ci-dessus, l'agent "004" a fait 3 séances en décembre 2018, même si 2 séances ont donné lieu à 2 codes de prestation (30 et 31/12).

Si je fais la requête suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT COUNT(DISTINCT prestation) FROM enregistrements WHERE prestation LIKE "201812%" AND prestataire LIKE "004"

Je ne filtre pas les "doublons" pour les séances avec plusieurs codes de prestations et j'obtiens le résultat : 5.

Si je fais la requête suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT COUNT(DISTINCT patient) FROM enregistrements WHERE prestation LIKE "201812%" AND prestataire LIKE "004"

Je n'obtiendrais que le nombre de patients distincts vu par le prestataire 004 sur le mois de décembre 2018 et j'obtiens le résultat : 2.

Je suis quasi sûr que ce n'est pas grand chose, mais j'ai l'esprit trop embué pour trouver ma solution

**Artemus24** · 30/12/2018, 01h19

salut soshin.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`          integer  unsigned NOT NULL auto_increment primary key,
  `date`        date              NOT NULL,
  `rang`        smallint unsigned NOT NULL,
  `prestataire` smallint unsigned NOT NULL,
  `patient`     smallint unsigned NOT NULL,
  `code`        smallint unsigned NOT NULL,
  index `idx` (`date`,`rang`,`prestataire`,`patient`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TRIGGER IF EXISTS `rang`
--------------
 
--------------
CREATE TRIGGER `rang`
BEFORE insert ON `test`
FOR EACH ROW
BEGIN
  SET NEW.rang=ifnull((select max(rang)+1 from `test` where date=NEW.date),1);
END
--------------
 
--------------
insert into `test` (`date`,`prestataire`,`patient`,`code`) values
  ('2018-12-29',  4,  12,  8),
  ('2018-12-30',  4,  12,  1),
  ('2018-12-30',  4,  12,  3),
  ('2018-12-30',  6,  10,  1),
  ('2018-12-30', 10, 123,  1),
  ('2018-12-31', 10, 123,  1),
  ('2018-12-31',  4,  12,  1),
  ('2018-12-31',  4,  76,  8),
  ('2018-12-31',  4,  76, 12)
--------------
 
--------------
select * from `test`
--------------
 
+----+------------+------+-------------+---------+------+
| id | date       | rang | prestataire | patient | code |
+----+------------+------+-------------+---------+------+
|  1 | 2018-12-29 |    1 |           4 |      12 |    8 |
|  2 | 2018-12-30 |    1 |           4 |      12 |    1 |
|  3 | 2018-12-30 |    2 |           4 |      12 |    3 |
|  4 | 2018-12-30 |    3 |           6 |      10 |    1 |
|  5 | 2018-12-30 |    4 |          10 |     123 |    1 |
|  6 | 2018-12-31 |    1 |          10 |     123 |    1 |
|  7 | 2018-12-31 |    2 |           4 |      12 |    1 |
|  8 | 2018-12-31 |    3 |           4 |      76 |    8 |
|  9 | 2018-12-31 |    4 |           4 |      76 |   12 |
+----+------------+------+-------------+---------+------+
--------------
select    prestataire,
          COUNT(distinct date) as Nbre
    from  `test`
   where  date between  '2018-12-01' and '2018-12-31'
group by  prestataire
--------------
 
+-------------+------+
| prestataire | Nbre |
+-------------+------+
|           4 |    3 |
|           6 |    1 |
|          10 |    2 |
+-------------+------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

@+

**escartefigue** · 30/12/2018, 10h23

Il y a bien plus simple, avec la table suivante

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
---PSTTN-------PSTTR--PTIEN
'20181229001', '004', '012' 
'20181230001', '004', '012' 
'20181230002', '004', '012' 
'20181230003', '006', '010' 
'20181230004', '010', '123' 
'20181231001', '010', '123' 
'20181231002', '004', '012' 
'20181231003', '004', '076' 
'20181231004', '004', '076'

Et la requête suivante

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
select prestataire
     , count(distinct seance)
from (select PSTTR                            as prestataire
           , substr(PSTTN, 01, 08) !! ptien   as seance
      from Ma_Table)
group by prestataire
order by prestataire

On obtient

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Et ce sont bien 4 prestations que le prestataire "004" a faites en décembre et non pas 3, dans l'énoncé de Soshin hier à 21h01, la ligne prestation "20181231002" --> prestataire "004" --> patient "012" --> code "001" devrait également être en rouge, car elle concerne bien le prestataire n°4

**soshin** · 30/12/2018, 20h46

Envoyé par escartefigue

Et ce sont bien 4 prestations que le prestataire "004" a faites en décembre et non pas 3, dans l'énoncé de Soshin hier à 21h01, la ligne prestation "20181231002" --> prestataire "004" --> patient "012" --> code "001" devrait également être en rouge, car elle concerne bien le prestataire n°4

Juste !!!!

Je vais décortiquer ton code...

Je vais regarder aussi le tiens Artemus24

mais je vois que tu proposes de changer le format de la base.

Déjà Merci !

**soshin** · 30/12/2018, 21h06

Envoyé par escartefigue

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
select prestataire
     , count(distinct seance)
from (select PSTTR                            as prestataire
           , substr(PSTTN, 01, 08) !! ptien   as seance
      from Ma_Table)
group by prestataire
order by prestataire

et si je ne veux sortir que le nombre de patients vus par le prestataire "004" (et non lister tous les patients par prestataire) ?

J'appelle le "004" par une variable php...

**escartefigue** · 31/12/2018, 11h34

il suffit d'ajouter un filtre (where) de préférence dans la requête corrélée ce sera plus performant

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
select prestataire
     , count(distinct seance)
from (select PSTTR                            as prestataire
           , substr(PSTTN, 01, 08) !! ptien   as seance
      from Ma_Table
      where PSTTR = ma_variable_PHP)
group by prestataire
order by prestataire

Mon code n'est pas compliqué : je concatène la sous-chaine de caractères qui permet de connaitre une séance (en s'affranchissant donc des 3 derniers caractères) avec les caractères qui identifient le patient, je compte ensuite combien de valeurs distinctes il y a pour cette concaténation, that's all folks

La solution proposée par Artemus24 n'est pas ensembliste (boucle for each) et elle utilise un trigger qui nécessite une opération de mise à jour ; deux facteurs qui pénaliseront les performances, à éviter si vos tables sont volumineuses.
De plus, comme expliqué précédemment, le résultat obtenu est faux puisqu'on doit bel et bien obtenir 4 pour les prestataire "004" et non pas 3.

Pour en revenir aux critiques concernant votre modèle de données, il est vrai que l'erreur est d'avoir créé une colonne qui contient deux informations : la date d'une part, et un chrono d'autre part.
Il s'agit d'un viol de la 1 forme normale, qui stipule que tout attribut contient une valeur scalaire ("atomique" est souvent utilisé dans la littérature à ce sujet)
Si vous en avez la possibilité faites modifier la BDD, dans la négative, vous pouvez directement appliquer ma requête.

**Artemus24** · 31/12/2018, 12h40

Salut à tous.

Envoyé par Escartefigue

Et ce sont bien 4 prestations que le prestataire "004" a faites en décembre et non pas 3,

Tout dépend de ce que vous avez compris.

J'ai compris que soshin recherche le nombre de jours travaillés par le prestataire "004". De ce fait, on trouve bien 3.

Si maintenant, on cherche le nombre de patients sachant qu'un même patient qui revient deux fois dans la journée sera comptabilité 1 alors on trouve 4.

Envoyé par soshin

mais je vois que tu proposes de changer le format de la base.

A vrai dire, Escartefigue isole la date en faisant un "substr(PSTTN, 01, 08)", ce qui revient à créer une colonne contenant seulement la date.
D'ailleurs, il le confirme :

Envoyé par Escartefigue

Pour en revenir aux critiques concernant votre modèle de données, il est vrai que l'erreur est d'avoir créé une colonne qui contient deux informations : la date d'une part, et un chrono d'autre part.

Envoyé par Escartefigue

La solution proposée par Artemus24 n'est pas ensembliste (boucle for each)

Le déclencheur que j'ai rajouté est seulement pour indiquer que l'on peut remplir la colonne "rang" d'une manière automatique.

A vrai dire, le déclencheur ne sert à rien ainsi que la colonne "rang", que ce soit dans mon modèle ou dans celui de soshin !
J'ai repris mon exemple que voici :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`          integer  unsigned NOT NULL auto_increment primary key,
  `date`        date              NOT NULL,
  `prestataire` smallint unsigned NOT NULL,
  `patient`     smallint unsigned NOT NULL,
  `code`        smallint unsigned NOT NULL,
  index `idx` (`date`,`prestataire`,`patient`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`date`,`prestataire`,`patient`,`code`) values
  ('2018-12-29',  4,  12,  8),
  ('2018-12-30',  4,  12,  1),
  ('2018-12-30',  4,  12,  3),
  ('2018-12-30',  6,  10,  1),
  ('2018-12-30', 10, 123,  1),
  ('2018-12-31', 10, 123,  1),
  ('2018-12-31',  4,  12,  1),
  ('2018-12-31',  4,  76,  8),
  ('2018-12-31',  4,  76, 12)
--------------
 
--------------
select * from `test`
--------------
 
+----+------------+-------------+---------+------+
| id | date       | prestataire | patient | code |
+----+------------+-------------+---------+------+
|  1 | 2018-12-29 |           4 |      12 |    8 |
|  2 | 2018-12-30 |           4 |      12 |    1 |
|  3 | 2018-12-30 |           4 |      12 |    3 |
|  4 | 2018-12-30 |           6 |      10 |    1 |
|  5 | 2018-12-30 |          10 |     123 |    1 |
|  6 | 2018-12-31 |          10 |     123 |    1 |
|  7 | 2018-12-31 |           4 |      12 |    1 |
|  8 | 2018-12-31 |           4 |      76 |    8 |
|  9 | 2018-12-31 |           4 |      76 |   12 |
+----+------------+-------------+---------+------+
--------------
select    prestataire,
          COUNT(distinct `date`) as Nbre
    from  `test`
   where  `date` between  '2018-12-01' and '2018-12-31'
group by  prestataire
--------------
 
+-------------+------+
| prestataire | Nbre |
+-------------+------+
|           4 |    3 |
|           6 |    1 |
|          10 |    2 |
+-------------+------+
--------------
select    prestataire,
          COUNT(distinct `date`, patient) as Nbre
    from  `test`
   where  `date` between  '2018-12-01' and '2018-12-31'
group by  prestataire
--------------
 
+-------------+------+
| prestataire | Nbre |
+-------------+------+
|           4 |    4 |
|           6 |    1 |
|          10 |    2 |
+-------------+------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Et j'ai mis deux les deux requêtes donnant les résultats attendus (3 ou 4).

@+

**soshin** · 01/01/2019, 13h36

Super heureux de vos réponses et explications, à l'un comme à l'autre, qui me font avancer dans ma connaissance MYSQL !
J'ai matière à tester et triturer le code

Merci, merci, merci !

Je reviendrai pour mettre en résolu une fois validé de mon côté

SQL COUNT et DISTINCT

Requêtes MySQL

Vue hybride

Discussions similaires

Partager

Partager