如何在MySQL查询中两次使用GROUP BY获取24小时数据极值

需求概述 #

现有current_data表（存储设备数据）和device_info表（存储设备名称），已实现按15分钟分组的查询逻辑。需要在此基础上，为每一行15分钟分组数据添加指定设备当日24小时内U03字段的最小值（IndexDebut）和最大值（IndexFin），同时保留原分组的U03平均值等字段。

解决方案 #

方法1：使用CTE（公共表表达式）分步计算 #

先计算当日全局极值，再与15分钟分组结果关联，逻辑清晰易维护：

WITH daily_extremes AS (
    -- 计算指定设备当日的U03全局极值
    SELECT
        DATE(UploadTime) AS target_date,
        MIN(IF(ISNULL(U03) OR U03 = '', 0, U03)) AS global_min,
        MAX(IF(ISNULL(U03) OR U03 = '', 0, U03)) AS global_max
    FROM current_data
    WHERE GSM_ID IN ('330010', '330011')
    GROUP BY target_date
),
fifteen_min_groups AS (
    -- 保留原15分钟分组逻辑，仅获取所需字段
    SELECT 
        cd.GSM_ID AS ID,
        di.GSM_NAME AS Site,
        ('1000-01-01 00:00:00' + INTERVAL (CEILING((TIMESTAMPDIFF(MINUTE, '1000-01-01 00:00:00', cd.UploadTime) / 15)) * 15) MINUTE) AS Jour,
        AVG(cd.U03) AS U03
    FROM current_data cd
    JOIN device_info di ON cd.GSM_ID = di.GSM_ID
    WHERE cd.GSM_ID IN ('330010', '330011')
    GROUP BY Jour, di.GSM_NAME, cd.GSM_ID
)
-- 关联两个结果集，添加全局极值字段
SELECT
    fmg.ID AS GSM_ID,
    fmg.Site,
    fmg.Jour,
    de.global_min AS IndexDebut,
    de.global_max AS IndexFin,
    fmg.U03
FROM fifteen_min_groups fmg
JOIN daily_extremes de ON DATE(fmg.Jour) = de.target_date
ORDER BY fmg.Jour DESC;

方法2：使用窗口函数简化查询 #

通过窗口函数直接在原查询中计算全局极值，减少关联操作：

SELECT 
    cd.GSM_ID AS GSM_ID,
    di.GSM_NAME AS Site,
    ('1000-01-01 00:00:00' + INTERVAL (CEILING((TIMESTAMPDIFF(MINUTE, '1000-01-01 00:00:00', cd.UploadTime) / 15)) * 15) MINUTE) AS Jour,
    -- 按日期分区计算当日U03最小值
    MIN(IF(ISNULL(cd.U03) OR cd.U03 = '', 0, cd.U03)) OVER (PARTITION BY DATE(cd.UploadTime)) AS IndexDebut,
    -- 按日期分区计算当日U03最大值
    MAX(IF(ISNULL(cd.U03) OR cd.U03 = '', 0, cd.U03)) OVER (PARTITION BY DATE(cd.UploadTime)) AS IndexFin,
    -- 按15分钟分组+站点分区计算U03平均值
    AVG(cd.U03) OVER (PARTITION BY ('1000-01-01 00:00:00' + INTERVAL (CEILING((TIMESTAMPDIFF(MINUTE, '1000-01-01 00:00:00', cd.UploadTime) / 15)) * 15) MINUTE), di.GSM_NAME) AS U03
FROM current_data cd
JOIN device_info di ON cd.GSM_ID = di.GSM_ID
WHERE cd.GSM_ID IN ('330010', '330011')
GROUP BY cd.GSM_ID, di.GSM_NAME, Jour, cd.UploadTime
ORDER BY cd.UploadTime DESC;

执行结果 #

两种方法均可得到如下期望结果：

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