SQL COUNT et DISTINCT

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:

---

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

Citation:

---

Envoyé par soshin

Je sais que ceci n'est pas correct :

Code:

---

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:

---

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 :
Pièce jointe 434818

A +

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

Citation:

---

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 ?

Citation:

---

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.

Citation:

---

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.

Citation:

---

Envoyé par SQLpro

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

Code:

---

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... :D
Merci !

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) ?

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.

@+

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

Citation:

---

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) ?

---

Citation:

---

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:

---

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:

---

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 :)

salut soshin.

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

-------------- 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...

---

@+

Il y a bien plus simple, avec la table suivante

Code:

---

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:

---

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:

---

1 2 3

004|4 006|1 010|2

---

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 ;)

Citation:

---

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 !!!! :mouarf:
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 !

Citation:

---

Envoyé par escartefigue

Code:

---

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...

;)

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

Code:

---

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.

Salut à tous.

Citation:

---

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.

Citation:

---

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 :

Citation:

---

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.

---

Citation:

---

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:

---

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).

@+

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 :D

Merci, merci, merci !

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