AWS Athena SQL查询：按name分组获取多列最频繁出现的值

针对按name分组，提取hobby和food列中出现最频繁值的需求，可以利用Presto SQL（Athena基于Presto）的窗口函数实现，具体SQL如下：

WITH hobby_freq AS (
    SELECT 
        name,
        hobby,
        COUNT(*) AS occurrence,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY COUNT(*) DESC) AS rank
    FROM your_table_name
    GROUP BY name, hobby
),
food_freq AS (
    SELECT 
        name,
        food,
        COUNT(*) AS occurrence,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY COUNT(*) DESC) AS rank
    FROM your_table_name
    GROUP BY name, food
)
SELECT 
    h.name,
    h.hobby,
    f.food
FROM hobby_freq h
INNER JOIN food_freq f 
    ON h.name = f.name
WHERE h.rank = 1 AND f.rank = 1;

代码说明： #

• 两个CTE（hobby_freq、food_freq）分别统计每个name下hobby和food的出现次数，并用ROW_NUMBER()函数按频次降序为每个分组内的条目排名，排名第一的即为该列的众数。
• 若存在多个值出现频次相同的情况（并列众数），ROW_NUMBER()会随机选取一个；若需保留所有并列结果，可将ROW_NUMBER()替换为RANK()。
• 最后通过name关联两个CTE，筛选出排名第一的记录，得到目标结果。

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