如何选择每个id组中某一列中最频繁出现的值？

Question

我在SQL中有一个表，长这个样子：

user_id | data1
0       | 6
0       | 6
0       | 6
0       | 1
0       | 1
0       | 2
1       | 5
1       | 5
1       | 3
1       | 3
1       | 3
1       | 7

我想编写一条查询语句，返回两列数据：一个用于用户ID，另一个用于每个ID最常出现的值。在我的示例中，对于用户ID 0，最常见的值是6，而对于用户ID 1，最常见的值是3。 我希望结果如下所示：

user_id | most_frequent_value
0       | 6
1       | 3

我正在使用以下查询来获取最常见的值，但它针对整个表运行并返回整个表的最常见值，而不是每个id的最常见值。我需要在查询中添加什么才能使其返回每个id的最常见值？我想我需要使用子查询，但不确定如何结构化。

SELECT user_id, data1 AS most_frequent_value
FROM my_table
GROUP BY user_id, data1
ORDER BY COUNT(*) DESC LIMIT 1

- cjh193

---

那么对于平局呢？（例如，您在示例中添加了一个（0,1）行；然后6和1都是最常见的值，因为它们都出现了3次）。 - pozs

Answer 1

您可以使用窗口函数根据数据1的计数对用户ID进行排名。

WITH cte AS (
SELECT 
    user_id 
  , data1
  , ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY COUNT(data1) DESC) rn
FROM dbo.YourTable
GROUP BY
  user_id,
  data1)

SELECT
    user_id,
    data1
FROM cte WHERE rn = 1 

- SQLChao

Answer 2

如果您使用正确的"order by"，那么distinct on (user_id)可以完成相同的工作，因为它从按"user_id"分区的数据中取出第一行。 DISTINCT ON是PostgreSQL的特色。

select distinct on (user_id) user_id, most_frequent_value from (
SELECT user_id, data1 AS most_frequent_value, count(*) as _count
FROM my_table
GROUP BY user_id, data1) a
ORDER BY user_id, _count DESC 

- JosMac

---

我可以知道为什么是-1吗？ 因为我在这个例子上测试过，并且我经常使用相同的查询.... - JosMac

这很好用 - 谢谢！我也想知道为什么它有一个-1的投票（那不是我）。 - cjh193

请用简单的英语解释一下你的评论是什么意思？正如我所写的，DISTINCT ON 是 PostgreSQL 的特色功能 - https://www.postgresql.org/docs/current/static/sql-select.html - 它与下面提到的窗口函数执行相同的操作。 - JosMac

Answer 3

使用postgres9.4或更高版本，是可以实现的。你可以像这样使用：

SELECT 
    user_id, MODE() WITHIN GROUP (ORDER BY value) 
FROM  
    (VALUES (0,6), (0,6), (0, 6), (0,1),(0,1), (1,5), (1,5), (1,3), (1,3), (1,7)) 
    users (user_id, value)
GROUP BY user_id

- Gabriel Furstenheim