sql - 如何在每个 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 最常出现的值。在我的示例中，对于 user_id 0，最常见的值是 6，对于 user_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

Answer 1

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

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