如何编写SQL查询获取分组平均Count及Top5最高Count与对应时间

嘿，这就帮你把需求、数据和解决方案整理成清晰的Markdown格式，方便你查看和使用～

你原本已经有了按「姓名+日期」分组计算平均Count的SQL查询，现在需要扩展功能：在每个（姓名、日期）组合的结果里，额外包含该分组内五个最高Count值及对应的datetime，同时保留平均Count值。

原始示例数据 #

原查询语句 #

你原本用来获取按日期分组的每人平均Count的SQL：

SELECT firstname, lastname, date(datetime), avg(count) 
FROM table 
WHERE date(datetime) between '2019-07-08' and '2019-07-08' 
GROUP BY firstname, lastname, date(datetime)

目标输出格式 #

期望的结果表格样式如下：

实现查询方案 #

下面是适配需求的SQL查询（支持窗口函数的数据库如MySQL 8.0+、PostgreSQL、SQL Server等都可以用）：

WITH ranked_data AS (
    SELECT 
        firstname,
        lastname,
        DATE(datetime) AS record_date,
        count,
        datetime,
        -- 计算当前分组的平均Count
        AVG(count) OVER (PARTITION BY firstname, lastname, DATE(datetime)) AS avg_count,
        -- 给每个分组内的记录按Count降序排名
        ROW_NUMBER() OVER (PARTITION BY firstname, lastname, DATE(datetime) ORDER BY count DESC) AS rn
    FROM table
    -- 注意：原查询日期是2019-07-08，但示例数据全是07-09，按需调整
    WHERE DATE(datetime) BETWEEN '2019-07-09' AND '2019-07-09'
)
SELECT 
    firstname AS `First Name`,
    lastname AS `Last Name`,
    avg_count AS Avg_count,
    record_date AS date,
    MAX(CASE WHEN rn = 1 THEN count END) AS max1,
    MAX(CASE WHEN rn = 1 THEN datetime END) AS max1_datetime,
    MAX(CASE WHEN rn = 2 THEN count END) AS max2,
    MAX(CASE WHEN rn = 2 THEN datetime END) AS max2_datetime,
    MAX(CASE WHEN rn = 3 THEN count END) AS max3,
    MAX(CASE WHEN rn = 3 THEN datetime END) AS max3_datetime,
    MAX(CASE WHEN rn = 4 THEN count END) AS max4,
    MAX(CASE WHEN rn = 4 THEN datetime END) AS max4_datetime,
    MAX(CASE WHEN rn = 5 THEN count END) AS max5,
    MAX(CASE WHEN rn = 5 THEN datetime END) AS max5_datetime
FROM ranked_data
-- 只保留前5条最高Count的记录
WHERE rn <=5
GROUP BY firstname, lastname, record_date, avg_count
ORDER BY firstname, lastname;

小提示： #

• 如果你的数据库不支持CTE（比如MySQL 5.x），可以把ranked_data换成子查询形式。
• 原查询的日期范围和示例数据不匹配，实际使用时记得根据业务需求调整WHERE里的日期条件。

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