如何查询美国各州最受欢迎的新年决心类别（取每个州Top1）

你的原SQL语句会返回每个州下所有新年决心类别的统计数据，要筛选出每个州中计数最高的Top1类别，可以使用窗口函数来实现，以下是两种常用方案：

方案1：仅保留单个Top1（遇并列随机选其一） #

使用ROW_NUMBER()窗口函数按州分组，对类别计数降序排名，筛选出排名为1的行：

WITH category_counts AS (
    SELECT 
        COUNT(tweet_category) AS category_count,
        tweet_category,
        tweet_state,
        ROW_NUMBER() OVER (PARTITION BY tweet_state ORDER BY COUNT(tweet_category) DESC) AS rank_num
    FROM New_years_resolutions_2020
    GROUP BY tweet_state, tweet_category
)
SELECT category_count, tweet_category, tweet_state
FROM category_counts
WHERE rank_num = 1;

方案2：保留并列的Top1 #

如果某个州存在多个类别计数相同且均为最高，使用RANK()可以保留所有并列的第一：

WITH category_counts AS (
    SELECT 
        COUNT(tweet_category) AS category_count,
        tweet_category,
        tweet_state,
        RANK() OVER (PARTITION BY tweet_state ORDER BY COUNT(tweet_category) DESC) AS rank_num
    FROM New_years_resolutions_2020
    GROUP BY tweet_state, tweet_category
)
SELECT category_count, tweet_category, tweet_state
FROM category_counts
WHERE rank_num = 1;

示例输出 #

内容的提问来源于stack exchange，提问作者Andrea Wupuy