如何为SQL统计结果添加最值对应的timestamp

1. 先新增所需字段 #

首先给tReportAvgMinMax表添加存储最值对应时间的字段：

ALTER TABLE tReportAvgMinMax 
ADD Timestamp_min DATETIME,
ADD Timestamp_max DATETIME;

2. 高效的插入语句（单表扫描版） #

使用窗口函数ROW_NUMBER()一次性计算出每个point_id的平均值、最值及对应时间，避免多次扫描tData表导致的性能损耗：

INSERT INTO tReportAvgMinMax (Date, point_id, average, minimum, maximum, Timestamp_min, Timestamp_max)
SELECT 
    @Date,
    Def.point_id,
    agg.average,
    agg.minimum,
    agg.maximum,
    agg.Timestamp_min,
    agg.Timestamp_max
FROM tDefAvgMinMax Def
LEFT JOIN (
    SELECT 
        point_id,
        AVG(Value) AS average,
        MIN(Value) AS minimum,
        MAX(Value) AS maximum,
        -- 取最小值对应的最早时间，要最晚时间可改ORDER BY Timestamp DESC
        FIRST_VALUE(Timestamp) OVER (PARTITION BY point_id ORDER BY Value ASC, Timestamp ASC) AS Timestamp_min,
        -- 取最大值对应的最早时间，要最晚时间可改ORDER BY Timestamp DESC
        FIRST_VALUE(Timestamp) OVER (PARTITION BY point_id ORDER BY Value DESC, Timestamp ASC) AS Timestamp_max
    FROM tData
    WHERE Timestamp BETWEEN CAST(@Date AS DATETIME) + '00:00:00' AND CAST(@Date AS DATETIME) + '23:59:59'
    GROUP BY point_id, Timestamp, Value
) agg ON Def.point_id = agg.point_id;

大数据量优化版（分步聚合） #

如果tData表数据量极大，可通过CTE分步聚合，减少单步计算的数据量：

WITH daily_agg AS (
    SELECT 
        point_id,
        AVG(Value) AS average,
        MIN(Value) AS minimum,
        MAX(Value) AS maximum
    FROM tData
    WHERE Timestamp BETWEEN CAST(@Date AS DATETIME) + '00:00:00' AND CAST(@Date AS DATETIME) + '23:59:59'
    GROUP BY point_id
),
min_time AS (
    SELECT 
        point_id,
        MIN(Timestamp) AS Timestamp_min -- 多值取最早，要最晚则用MAX
    FROM tData
    WHERE 
        Timestamp BETWEEN CAST(@Date AS DATETIME) + '00:00:00' AND CAST(@Date AS DATETIME) + '23:59:59'
        AND (point_id, Value) IN (SELECT point_id, minimum FROM daily_agg)
    GROUP BY point_id
),
max_time AS (
    SELECT 
        point_id,
        MIN(Timestamp) AS Timestamp_max -- 多值取最早，要最晚则用MAX
    FROM tData
    WHERE 
        Timestamp BETWEEN CAST(@Date AS DATETIME) + '00:00:00' AND CAST(@Date AS DATETIME) + '23:59:59'
        AND (point_id, Value) IN (SELECT point_id, maximum FROM daily_agg)
    GROUP BY point_id
)
INSERT INTO tReportAvgMinMax (Date, point_id, average, minimum, maximum, Timestamp_min, Timestamp_max)
SELECT 
    @Date,
    Def.point_id,
    da.average,
    da.minimum,
    da.maximum,
    mt.Timestamp_min,
    xt.Timestamp_max
FROM tDefAvgMinMax Def
LEFT JOIN daily_agg da ON Def.point_id = da.point_id
LEFT JOIN min_time mt ON Def.point_id = mt.point_id
LEFT JOIN max_time xt ON Def.point_id = xt.point_id;

关键说明 #

• 两种方案都保留了原逻辑中对tDefAvgMinMax全量point_id的统计，即使当天无数据，对应字段会以NULL填充。
• 窗口函数方案只需扫描tData一次，性能更优；分步聚合方案逻辑更直观，适合需要灵活调整时间规则的场景。
• 若存在多个相同最值的记录，可通过调整排序规则或MIN/MAX(Timestamp)来选择取最早或最晚的时间戳。

内容的提问来源于stack exchange，提问作者Jean-Charles Trottier