SQL查询需求：按机器、类型及日期小时分组取TOP2分钟总和

现有如下结构的表（包含示例数据）：

需要编写SQL查询实现以下要求：

• 按START_DATE的日期小时、MACHINE、TYPE分组，计算MINUTES的总和
• 仅显示每个分组维度下总和排名前2的记录
• 生成TYPE_MINUTES列，格式为TYPE (总和 minutes)

预期结果如下：

这里以MySQL为例，使用窗口函数实现分组排名筛选：

WITH grouped_data AS (
    SELECT
        -- 提取日期+小时，格式可根据需求调整为空格分隔
        DATE_FORMAT(START_DATE, '%d/%m/%Y_%H') AS DATE_HOUR,
        MACHINE,
        TYPE,
        SUM(MINUTES) AS total_minutes,
        -- 按日期小时+机器分组，按总分钟数降序生成排名
        ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(START_DATE, '%d/%m/%Y_%H'), MACHINE ORDER BY SUM(MINUTES) DESC) AS rn
    FROM
        your_table_name
    GROUP BY
        DATE_FORMAT(START_DATE, '%d/%m/%Y_%H'),
        MACHINE,
        TYPE
)
SELECT
    DATE_HOUR,
    total_minutes AS MINUTES,
    MACHINE,
    CONCAT(TYPE, ' (', total_minutes, ' minutes)') AS TYPE_MINUTES
FROM
    grouped_data
WHERE
    rn <= 2
ORDER BY
    DATE_HOUR,
    MACHINE,
    total_minutes DESC;

关键说明 #

• 用CTE先完成分组聚合和排名计算：
  • 日期格式化部分，若需要空格分隔的格式，可将%d/%m/%Y_%H改为%d/%m/%Y %H
  • ROW_NUMBER()窗口函数按日期小时+机器分组，确保每个机器在对应小时内的类型按分钟总和排序
• 外层查询筛选排名前2的记录，通过CONCAT()拼接生成TYPE_MINUTES列
• 若使用PostgreSQL等其他数据库，只需调整日期格式化函数（如PostgreSQL用TO_CHAR(START_DATE, 'DD/MM/YYYY_HH24')），窗口函数逻辑保持一致

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