SQL 分组-按最频繁出现的进行分组

Question

假设我有以下表格：

+-----------------------+----------------+
| PERSON                | ATE            |
+-----------------------+----------------+
| Joe                   | Chicken        |
| Joe                   | Chicken        |
| Joe                   | Chicken        |
| Joe                   | Tuna           |
| Joe                   | Tuna           |
| Joe                   | Chicken        |
| Joe                   | Tuna           |
| Joe                   | Chicken        |
| Joe                   | Tuna           |
| Joe                   | Tuna           |
| Joe                   | Tuna           |
| Joe                   | Chicken        |
| Joe                   | Tuna           |
| Joe                   | Tuna           |
| Joe                   | Chicken        |
| Joe                   | Tuna           |
| Joe                   | Tuna           |
| Joe                   | Tuna           |
| Rob                   | Chicken        |
| Rob                   | Tuna           |
| Rob                   | Chicken        |
| Rob                   | Chicken        |
| Rob                   | Chicken        |
| Rob                   | Chicken        |
| Jonathan              | Tuna           |
| Jonathan              | Tuna           |
| Jonathan              | Chicken        |
| Jonathan              | Tuna           |
| Jonathan              | Chicken        |
+-----------------------+----------------+

我希望找到每个人最常吃的食物。因此，期望的结果如下：

+-----------------------+----------------+
| PERSON                | ATE            |
+-----------------------+----------------+
| Joe                   | Tuna           |
| Rob                   | Chicken        |
| Jonathan              | Tuna           |
+-----------------------+----------------+

自然地，我会使用GROUP BY（人名）进行分组。之后，我需要在每个组中找到最常吃的物品。如果它们是数字，那么很容易，因为我可以使用MAX函数，但是我不知道如何从一堆字符串中挑选出最受欢迎的字符串。

你会如何在SQL中完成我想做的事情？

- Naldhelaan

---

计算并找出计数的最大值。 - PM 77-1

Max of count 会返回一个数字 (count)。那我是不是还要找到计数与 max_count 相匹配的食物呢？对于这样一个简单的任务来说，似乎有点复杂。 - Naldhelaan

@Naldhelaan，使用row_number很简单，这只在MySQL版本8及以上版本中支持-https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number。 - FuzzyTree

所以这并不是一个真正的表格。 - Strawberry

@GordonLinoff 你是不是看错了问题？它并没有涉及到最近的事情。 - Barmar

Answer 1

您可以使用相关子查询：

select distinct t1.PERSON, (
    select ATE
    from myTable t2
    where t2.PERSON = t1.PERSON
    group by ATE
    order by count(*) desc
    limit 1
) as ATE
from myTable t1

如果您有关联，这个查询将会“随机”挑选其中一个最常用的项目。

使用MySQL 8或MariaDB 10.2（都还不稳定），您将能够使用CTE（公共表达式）。

with t1 as (
    select PERSON, ATE, count(*) as cnt
    from myTable
    group by PERSON, ATE
), t2 as (
    select PERSON, max(cnt) as cnt
    from t1
    group by PERSON
)
    select *
    from t1
    natural join t2

在这种情况下，此查询可能会返回每个组（PERSON）多行结果。

- Paul Spiegel

---

哇，太容易了。我觉得难为情，因为我发了一个如此复杂的答案。 - Thorsten Kettner

非常感谢您提供的优美解决方案。编辑：我没有看到更新的答案。 - Naldhelaan

Answer 2

通常情况下，您需要多次聚合数据以获得每个人和食物的计数，然后按人员再次计数，以便进行比较...

但有一个诀窍：将计数与食物粘合在一起：

Joe Chicken 00000000000007Chicken Joe Tuna 00000000000011Tuna Jonathan Chicken 00000000000002Chicken Jonathan Tuna 00000000000003Tuna Rob Chicken 00000000000005Chicken Rob Tuna 00000000000001Tuna

然后获取每个人的最大条目（例如 Joe 的 00000000000011Tuna ），并剥离数字：

select person, substr(max(combined), 15) as ate_most
from
(
  select person, concat(lpad(count(*), 14, '0'), ate) as combined
  from mytable
  group by person, ate
) trick
group by person
order by person;

http://rextester.com/GVO98572

- Thorsten Kettner

---

谢谢你的回答。不幸的是，这种方法在生产系统中不太可扩展，所以我不敢使用它。 - Naldhelaan

@Naldhelaan：你不使用它是正确的。我想得太复杂了。（这可能是因为我习惯于Oracle，那里整个查询要简单得多得多：select person, stats_mode(ate) from mytable group by person。所以我不得不考虑比平常更复杂的模式，并且做得过头了 :-) Paul的答案比我的好多了。 - Thorsten Kettner