SQL：返回每个人最常见的值

Question

编辑：我正在使用MySQL，我找到了另一篇帖子与相同的问题，但它是关于Postgres的；我需要MySQL。

在SQL中为每个值获取另一列中最常见的值

经过对该网站和其他网站进行广泛搜索后，我提出了这个问题，但没有找到符合我的意图的结果。

我有一个人员表（recordid，personid，transactionid）和一个交易表（transactionid，rating）。 我需要一个单一的SQL语句，可以返回每个人拥有的最常见评级。

我目前有这个SQL语句，它返回指定人员ID的最常见评级。 它有效，也许它可以帮助其他人。

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

然而，我需要一个与上述语句对于personTable中的每个personid执行相同操作的语句。

我的尝试如下;然而，它会使我的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

如果您能给我任何帮助，我将不胜感激。谢谢。

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

我要搜索的SQL语句的输出结果应该是：

输出：

PersonID,   MostCommonRating
Adam        Good
Ben         Good
Caitlin     Average

- Rusty Robot

---

请标记您正在使用的数据库引擎。 - Mat

我即将离开几个小时，所以只能在大约5个小时后再次检查，但是有几件事情浮现在脑海中。您是否尝试在这些列中放置索引？这很可能是您的SQL服务器超时的原因。http://msdn.microsoft.com/en-us/library/ms190806.aspx - user1590499

我觉得你在TRANSACTIONTABLE的第一行中有一个拼写错误，1 GOOD不应该是1非常差，因为你有3 GOOD吗？ - Mahmoud Gamal

拼写错误不是原因，这些示例只是为了模拟我遇到的问题，它们并不是我正在处理的实际数据。 - Rusty Robot

这篇帖子与我的相同，但答案是针对POSTGRES的，而我需要MYSQL。https://dev59.com/OHRC5IYBdhLWcg3wS_EF - Rusty Robot

可能是重复的问题 MySQL SELECT most frequent by group。 - Ciro Santilli OurBigBook.com

Answer 1

初步评论

请学会使用明确的 JOIN 符号，而不是旧式（1992 年之前）的隐式 JOIN 符号。

旧式写法：

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

优选风格：

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

每个JOIN语句都需要有一个ON条件。

此外，数据中的personID值是字符串，而不是数字，因此您需要编写：

 WHERE personTable.personid = "Ben"

例如，要使查询在显示的表格上运行。

---

主要回答

您正在寻找对聚合进行聚合的方法：在本例中，计数的最大值。因此，任何通用解决方案都将涉及MAX和COUNT。您不能直接将MAX应用于COUNT，但是可以将MAX应用于子查询中的列，其中该列恰好是COUNT。

使用测试驱动的查询设计（TDQD）逐步构建查询。

选择人员和交易评级

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

选择人员、评级以及评级出现的次数

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

现在我们知道每个人的最大计数是多少。

所需结果

为了获得结果，我们需要从子查询中选择具有最大计数的行。请注意，如果某人拥有2个好评和2个差评（并且2是该人物的同一类型评分的最大数量），则该人物将显示两条记录。

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

如果您也想知道实际的评分数量，那也很容易选择。

这是一个相当复杂的SQL语句。我不想从头开始编写它，因为那很麻烦。事实上，我可能会像示例一样逐步开发它。但由于我们已经在更大的表达式中使用了子查询之前进行了调试，所以我们可以对答案有信心。

WITH子句

请注意，标准SQL提供了一个WITH子句，它为SELECT语句添加前缀，并命名子查询。(它也可用于递归查询，但我们这里不需要)

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

这种写法更简单。不幸的是，MySQL目前还不支持WITH子句。

---

上述SQL已在运行Mac OS X 10.7.4上的IBM Informix Dynamic Server 11.70.FC2进行过测试。该测试揭示了初步评论中诊断出的问题。主答案的SQL正确执行而无需更改。

- Jonathan Leffler

---

+1，非常好的回答。我已经尝试了一个多小时了。这里有一个实时演示。但是这种问题有没有通用名称？它是聚合的聚合吗？还是其他什么？谢谢。 - Mahmoud Gamal

太棒了！非常感谢，这对我帮助很大。由于您的查询方式和逐步开发的方式，使得它易于理解。A+ - Rusty Robot

作为 WITH 子句的替代，您可以使用 CREATE TEMPORARY TABLE RatingList AS _select-statement_。 - Barmar

叹气，不起作用了 - MySQL 不允许在查询中多次引用临时表。你需要创建一个真正的表。 - Barmar

@Barmar：真不走运。这种限制——既不明显又不必要——在DBMS中是令人恼火的。但在单个查询中不能多次使用临时表名似乎确实是一种奇怪的限制。 - Jonathan Leffler

Answer 2

以下是 MySQL 中 max 聚合函数的一种不太正规使用方式：它会对 varchar 类型进行字典排序（对整数/浮点数进行数值排序，这是其预期的行为）：

SELECT 
  PersonID, 
  substring(max(concat(lpad(c, 20, '0'), Rating)), 21) AS MostFrequentRating 
FROM (
    SELECT PersonID, Rating, count(*) c 
    FROM PERSONTABLE INNER JOIN TRANSACTIONTABLE USING(TransactionID) 
    GROUP BY PersonID, Rating
) AS grouped_ratings 
GROUP BY PersonID;

这将产生所需的结果：

+----------+--------------------+
| PersonID | MostFrequentRating |
+----------+--------------------+
| Adam     | Good               |
| Ben      | Good               |
| Caitlin  | Average            |
+----------+--------------------+

（注意，如果每个人有多种模式，则会选择字母表顺序最高的那个，因此——几乎是随机的——好优于坏，坏优于平均水平）

您应该能够通过检查以下内容来确定 max 操作的对象：

SELECT PersonID, Rating, count(*) c, concat(lpad(count(*), 20, '0'), Rating) as LexicalMaxMe 
FROM PERSONTABLE INNER JOIN TRANSACTIONTABLE USING(TransactionID) 
GROUP BY PersonID, Rating
ORDER BY PersonID, c DESC;

这将输出：

+----------+---------+---+-----------------------------+
| PersonID | Rating  | c | LexicalMaxMe                |
+----------+---------+---+-----------------------------+
| Adam     | Good    | 2 | 00000000000000000002Good    |
| Adam     | Bad     | 1 | 00000000000000000001Bad     |
| Ben      | Good    | 2 | 00000000000000000002Good    |
| Ben      | Average | 1 | 00000000000000000001Average |
| Caitlin  | Average | 2 | 00000000000000000002Average |
| Caitlin  | Good    | 1 | 00000000000000000001Good    |    
+----------+---------+---+-----------------------------+

- EoghanM

Answer 3

对于使用 Microsoft SQL Server 的任何人：您可以创建自定义聚合函数来获取最常见的值。Ahmed Tarek Hasan 的博客文章示例2描述了如何执行此操作：

http://developmentsimplyput.blogspot.nl/2013/03/creating-sql-custom-user-defined.html

- Jorr.it