SQL查询：如何选取每小时平均数量最高的对应类别

你现有查询已经完成了「按小时+类别统计平均数量」的第一步，接下来只需要基于这个结果，筛选出每个小时平均数值最高的记录即可。

解法1：支持窗口函数的数据库（MySQL8.0+、PostgreSQL、SQL Server等，推荐使用） #

用窗口函数给同小时内的记录按平均数量降序排名，取排名第一的即可：

WITH hourly_category_avg AS (
    SELECT 
        hour_key,
        category,
        AVG(Quantity) AS avg_qty
    FROM Step2Time
    GROUP BY hour_key, category
)
SELECT 
    hour_key AS Hour,
    category AS Category,
    avg_qty AS Quantity
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER(PARTITION BY hour_key ORDER BY avg_qty DESC) AS rank_num
    FROM hourly_category_avg
) ranked_data
WHERE rank_num = 1;

如果需要返回同小时平均数量并列第一的所有类别，把ROW_NUMBER()替换为RANK()即可。

解法2：不支持窗口函数的低版本数据库 #

用子查询先拿到每小时的最大平均数值，再关联回原统计结果匹配对应类别：

SELECT 
    t1.hour_key AS Hour,
    t1.category AS Category,
    t1.avg_qty AS Quantity
FROM (
    SELECT 
        hour_key,
        category,
        AVG(Quantity) AS avg_qty
    FROM Step2Time
    GROUP BY hour_key, category
) t1
INNER JOIN (
    SELECT 
        hour_key,
        MAX(avg_qty) AS max_avg_qty
    FROM (
        SELECT 
            hour_key,
            AVG(Quantity) AS avg_qty
        FROM Step2Time
        GROUP BY hour_key, category
    ) tmp
    GROUP BY hour_key
) t2 ON t1.hour_key = t2.hour_key AND t1.avg_qty = t2.max_avg_qty;

这个写法默认会返回同小时所有并列第一的记录，如果只需要保留一条，可自行增加额外过滤规则。

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