SQL : Renvoyer la valeur la plus courante pour chaque personne

EDIT : j'utilise MySQL, j'ai trouvé un autre message avec la même question, mais c'est dans Postgres ; J'ai besoin de MySQL.

Obtenir la valeur la plus courante pour chaque valeur d'une autre colonne en SQL

Je pose cette question après une recherche approfondie sur ce site et d'autres, mais je n'ai pas trouvé de résultat qui fonctionne comme je le souhaite.

J'ai une table de personnes (recordid, personid, transactionid) et une table de transaction (transactionid, rating). J'ai besoin d'une seule instruction SQL pouvant renvoyer la note la plus courante de chaque personne.

J'ai actuellement cette instruction SQL qui renvoie la note la plus courante pour un identifiant de personne spécifié. Cela fonctionne et peut-être que cela peut aider les autres.

SELECT transactionTable.rating as MostCommonRating 
FROM personTable, transactionTable 
WHERE personTable.transactionid = transactionTable.transactionid 
AND personTable.personid = 1
GROUP BY transactionTable.rating 
ORDER BY COUNT(transactionTable.rating) desc 
LIMIT 1

Cependant, j'ai besoin d'une déclaration qui fait ce que la déclaration ci-dessus fait pour chaque personid dans personTable.

Ma tentative est ci-dessous; cependant, il expire mon serveur MySQL.

SELECT personid AS pid, 
(SELECT transactionTable.rating as MostCommonRating 
FROM personTable, transactionTable 
WHERE personTable.transactionid = transactionTable.transactionid 
AND personTable.personid = pid
GROUP BY transactionTable.rating 
ORDER BY COUNT(transactionTable.rating) desc 
LIMIT 1)
FROM persontable
GROUP BY personid

Toute aide que vous pouvez me donner serait très obligée. Merci.

PERSONTABLE:

RecordID, PersonID, TransactionID
1, Adam, 1
2, Adam, 2
3, Adam, 3
4, Ben, 1
5, Ben, 3
6, Ben, 4
7, Caitlin, 4
8, Caitlin, 5
9, Caitlin, 1

TRANSACTIONTABLE:

TransactionID, Rating
1 Good
2 Bad
3 Good
4 Average
5 Average

La sortie de l'instruction SQL que je recherche serait :

SORTIE :

PersonID, MostCommonRating
Adam Good
Ben Good
Caitlin Average

Solution du problème

Commentaire préliminaire

Veuillez apprendre à utiliser la notation JOIN explicite, et non l'ancienne notation de jointure implicite (avant 1992).

Style ancien :

SELECT transactionTable.rating as MostCommonRating 
FROM personTable, transactionTable 
WHERE personTable.transactionid = transactionTable.transactionid 
AND personTable.personid = 1
GROUP BY transactionTable.rating 
ORDER BY COUNT(transactionTable.rating) desc 
LIMIT 1

Style préféré :

SELECT transactionTable.rating AS MostCommonRating 
 FROM personTable
 JOIN transactionTable 
 ON personTable.transactionid = transactionTable.transactionid 
 WHERE personTable.personid = 1
 GROUP BY transactionTable.rating 
 ORDER BY COUNT(transactionTable.rating) desc 
 LIMIT 1

Vous avez besoin d'une condition ON pour chaque JOIN.

De plus, les personIDvaleurs dans les données sont des chaînes, pas des nombres, vous devez donc écrire

 WHERE personTable.personid = "Ben"

par exemple, pour que la requête fonctionne sur les tables affichées.

Réponse principale

Vous cherchez à trouver un agrégat d'un agrégat : dans ce cas, le maximum d'un décompte. Ainsi, toute solution générale impliquera à la fois MAX et COUNT. Vous ne pouvez pas appliquer MAX directement à COUNT, mais vous pouvez appliquer MAX à une colonne à partir d'une sous-requête où la colonne se trouve être un COUNT.

Créez la requête à l'aide de Test-Driven Query Design — TDQD.

Sélectionnez la notation de la personne et de la transaction

SELECT p.PersonID, t.Rating, t.TransactionID
 FROM PersonTable AS p
 JOIN TransactionTable AS t
 ON p.TransactionID = t.TransactionID

Sélectionnez la personne, la note et le nombre d'occurrences de la note

SELECT p.PersonID, t.Rating, COUNT(*) AS RatingCount
 FROM PersonTable AS p
 JOIN TransactionTable AS t
 ON p.TransactionID = t.TransactionID
 GROUP BY p.PersonID, t.Rating

Ce résultat deviendra une sous-requête.

Trouver le nombre maximum de fois que la personne obtient une note

SELECT s.PersonID, MAX(s.RatingCount)
 FROM (SELECT p.PersonID, t.Rating, COUNT(*) AS RatingCount
 FROM PersonTable AS p
 JOIN TransactionTable AS t
 ON p.TransactionID = t.TransactionID
 GROUP BY p.PersonID, t.Rating
 ) AS s
 GROUP BY s.PersonID

Nous savons maintenant quel est le nombre maximum pour chaque personne.

Résultat requis

Pour obtenir le résultat, nous devons sélectionner les lignes de la sous-requête qui ont le nombre maximum. Notez que si quelqu'un a 2 bonnes et 2 mauvaises notes (et 2 est le nombre maximum de notes du même type pour cette personne), alors deux enregistrements seront affichés pour cette personne.

SELECT s.PersonID, s.Rating
 FROM (SELECT p.PersonID, t.Rating, COUNT(*) AS RatingCount
 FROM PersonTable AS p
 JOIN TransactionTable AS t
 ON p.TransactionID = t.TransactionID
 GROUP BY p.PersonID, t.Rating
 ) AS s
 JOIN (SELECT s.PersonID, MAX(s.RatingCount) AS MaxRatingCount
 FROM (SELECT p.PersonID, t.Rating, COUNT(*) AS RatingCount
  FROM PersonTable AS p
  JOIN TransactionTable AS t
  ON p.TransactionID = t.TransactionID
  GROUP BY p.PersonID, t.Rating
 ) AS s
 GROUP BY s.PersonID
 ) AS m
 ON s.PersonID = m.PersonID AND s.RatingCount = m.MaxRatingCount

Si vous voulez aussi que la note réelle compte, c'est facile à sélectionner.

C'est un morceau de SQL assez complexe. Je détesterais essayer d'écrire cela à partir de zéro. En effet, je ne m'en soucierais probablement pas; Je le développerais étape par étape, plus ou moins comme indiqué. Mais parce que nous avons débogué les sous-requêtes avant de les utiliser dans des expressions plus grandes, nous pouvons être sûrs de la réponse.

Clause AVEC

Notez que le SQL standard fournit une clause WITH qui préfixe une instruction SELECT, en nommant une sous-requête. (Il peut également être utilisé pour les requêtes récursives, mais nous n'en avons pas besoin ici.)

WITH RatingList AS
 (SELECT p.PersonID, t.Rating, COUNT(*) AS RatingCount
 FROM PersonTable AS p
 JOIN TransactionTable AS t
 ON p.TransactionID = t.TransactionID
 GROUP BY p.PersonID, t.Rating
 )
SELECT s.PersonID, s.Rating
 FROM RatingList AS s
 JOIN (SELECT s.PersonID, MAX(s.RatingCount) AS MaxRatingCount
 FROM RatingList AS s
 GROUP BY s.PersonID
 ) AS m
 ON s.PersonID = m.PersonID AND s.RatingCount = m.MaxRatingCount

C'est plus simple à écrire. Malheureusement, MySQL ne prend pas encore en charge la clause WITH.

Le SQL ci-dessus a maintenant été testé sur IBM Informix Dynamic Server 11.70.FC2 exécuté sur Mac OS X 10.7.4. Ce test a révélé le problème diagnostiqué dans le commentaire préliminaire. Le SQL pour la réponse principale a fonctionné correctement sans avoir besoin d'être modifié.