如何按UTCTimestamp小时分组获取每组数值最值及对应时间戳？

我来帮你完善这个查询，实现按小时分组获取NumericValue的最大/最小值及其对应UTCTimestamp的需求，这里提供两种适配不同场景的方案：

方案1：单条记录对应最大/最小值（优先取指定时间戳） #

如果每个小时的最大/最小值唯一，或者你只需要其中一条对应记录，用窗口函数ROW_NUMBER()标记每个小时内的极值记录，再聚合提取：

WITH HourlyValues AS (
    SELECT 
        HOUR(UTCTimestamp) AS `Hour`,
        NumericValue,
        UTCTimestamp,
        -- 按数值降序排名，每个小时第1条就是最大值记录；加UTCTimestamp ASC确保数值相同时取最早时间
        ROW_NUMBER() OVER (PARTITION BY HOUR(UTCTimestamp) ORDER BY NumericValue DESC, UTCTimestamp ASC) AS rn_max,
        -- 按数值升序排名，每个小时第1条就是最小值记录；同理可调整时间排序规则
        ROW_NUMBER() OVER (PARTITION BY HOUR(UTCTimestamp) ORDER BY NumericValue ASC, UTCTimestamp ASC) AS rn_min
    FROM MyTable
)
SELECT 
    `Hour`,
    -- 提取最大值及对应时间戳
    MAX(CASE WHEN rn_max = 1 THEN NumericValue END) AS MaximumValue,
    MAX(CASE WHEN rn_max = 1 THEN UTCTimestamp END) AS MaxValueTimestamp,
    -- 提取最小值及对应时间戳
    MAX(CASE WHEN rn_min = 1 THEN NumericValue END) AS MinimumValue,
    MAX(CASE WHEN rn_min = 1 THEN UTCTimestamp END) AS MinValueTimestamp
FROM HourlyValues
GROUP BY `Hour`
ORDER BY `Hour`;

说明： #

• PARTITION BY HOUR(UTCTimestamp) 按时间戳的小时维度分组
• 排序规则里的UTCTimestamp ASC是可选的，用来处理同一小时内多个记录数值相同的情况，你可以改成DESC来取最晚的时间戳
• 外层用CASE筛选排名为1的记录，MAX聚合只是用来提取唯一的极值记录值

方案2：处理多个并列极值的情况 #

如果同一小时内有多个记录的NumericValue等于最大值/最小值，需要返回所有对应时间戳，用RANK()替代ROW_NUMBER()，再用字符串拼接函数汇总时间戳：

WITH HourlyValues AS (
    SELECT 
        HOUR(UTCTimestamp) AS `Hour`,
        NumericValue,
        UTCTimestamp,
        -- 并列极值会获得相同排名，不会被过滤
        RANK() OVER (PARTITION BY HOUR(UTCTimestamp) ORDER BY NumericValue DESC) AS rnk_max,
        RANK() OVER (PARTITION BY HOUR(UTCTimestamp) ORDER BY NumericValue ASC) AS rnk_min
    FROM MyTable
)
SELECT 
    `Hour`,
    MAX(NumericValue) AS MaximumValue,
    -- 拼接所有最大值对应的时间戳（MySQL语法）
    GROUP_CONCAT(DISTINCT UTCTimestamp ORDER BY UTCTimestamp SEPARATOR ', ') AS MaxValueTimestamps,
    MIN(NumericValue) AS MinimumValue,
    GROUP_CONCAT(DISTINCT UTCTimestamp ORDER BY UTCTimestamp SEPARATOR ', ') AS MinValueTimestamps
FROM HourlyValues
WHERE rnk_max = 1 OR rnk_min = 1
GROUP BY `Hour`
ORDER BY `Hour`;

跨数据库适配提示： #

• SQL Server：替换HOUR()为DATEPART(HOUR, UTCTimestamp)，GROUP_CONCAT替换为STRING_AGG(UTCTimestamp, ', ') WITHIN GROUP (ORDER BY UTCTimestamp)
• PostgreSQL：替换HOUR()为EXTRACT(HOUR FROM UTCTimestamp)，GROUP_CONCAT替换为STRING_AGG(UTCTimestamp::TEXT, ', ' ORDER BY UTCTimestamp)

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