Snowflake中PLC变更数据的加权时间序列聚合查询方案

针对你的需求，我们可以通过时间区间处理+窗口重叠计算替代逐行扩展的方式，大幅提升查询性能。以下是完整的Snowflake SQL查询语句及关键说明：

1. 定义查询参数 #

先设置时间范围和图表最大点数，可根据Seeq的用户选择动态替换：

SET @START_TIME = '2023-01-01 00:00:00'::TIMESTAMP_NTZ;
SET @END_TIME = '2023-01-02 00:00:00'::TIMESTAMP_NTZ;
SET @MAX_POINTS = 2000; -- Seeq图表允许的最大点数

2. 完整查询语句 #

WITH tag_value_intervals AS (
    -- 为每个TAG的每条记录计算值的有效时间区间
    SELECT
        TAG,
        TIMESTAMP AS start_ts,
        -- 用LEAD获取下一条记录的时间，无后续记录则用查询结束时间
        COALESCE(LEAD(TIMESTAMP) OVER (PARTITION BY TAG ORDER BY TIMESTAMP), @END_TIME) AS end_ts,
        VALUE
    FROM plc_data
    WHERE TIMESTAMP BETWEEN @START_TIME AND @END_TIME

    -- 补充：如果TAG在查询开始前已有值，补上该值从@START_TIME到第一条记录的区间
    UNION ALL
    SELECT
        TAG,
        @START_TIME AS start_ts,
        MIN(TIMESTAMP) AS end_ts,
        VALUE
    FROM plc_data
    WHERE TIMESTAMP < @START_TIME
    GROUP BY TAG, VALUE
    HAVING MIN(TIMESTAMP) = MAX(TIMESTAMP) -- 确保取查询开始前最后一个有效值
),
window_params AS (
    -- 计算窗口大小：总时长/最大点数，向上取整保证不超过2000个点
    SELECT
        DATEDIFF(SECOND, @START_TIME, @END_TIME) AS total_seconds,
        CEIL(DATEDIFF(SECOND, @START_TIME, @END_TIME) / @MAX_POINTS) AS window_size_seconds
),
time_windows AS (
    -- 生成指定数量的时间窗口
    SELECT
        DATEADD(SECOND, (seq4() * window_size_seconds), @START_TIME) AS window_start,
        DATEADD(SECOND, ((seq4() + 1) * window_size_seconds), @START_TIME) AS window_end
    FROM window_params,
         TABLE(GENERATOR(ROWCOUNT => @MAX_POINTS))
    WHERE window_start < @END_TIME

    -- 调整最后一个窗口，确保覆盖到@END_TIME
    UNION ALL
    SELECT
        @END_TIME - INTERVAL '1 SECOND' * window_size_seconds AS window_start,
        @END_TIME AS window_end
    FROM window_params
    WHERE MOD(DATEDIFF(SECOND, @START_TIME, @END_TIME), window_size_seconds) != 0
    QUALIFY ROW_NUMBER() OVER () = 1
),
overlap_calculations AS (
    -- 计算每个TAG的数值区间与时间窗口的重叠部分
    SELECT
        tvi.TAG,
        tw.window_start,
        tw.window_end,
        GREATEST(tvi.start_ts, tw.window_start) AS overlap_start,
        LEAST(tvi.end_ts, tw.window_end) AS overlap_end,
        tvi.VALUE,
        DATEDIFF(SECOND, overlap_start, overlap_end) AS duration_seconds -- 重叠时长（秒）
    FROM tag_value_intervals tvi
    JOIN time_windows tw
        ON tvi.start_ts < tw.window_end AND tvi.end_ts > tw.window_start -- 只保留有重叠的区间
    WHERE duration_seconds > 0
)
-- 最终计算每个窗口的加权平均值，窗口中点作为图表时间点
SELECT
    TAG,
    DATEADD(SECOND, DATEDIFF(SECOND, window_start, window_end)/2, window_start) AS chart_timestamp,
    SUM(VALUE * duration_seconds) / SUM(duration_seconds) AS weighted_average_value
FROM overlap_calculations
GROUP BY TAG, window_start, window_end
ORDER BY TAG, chart_timestamp;

3. 性能优化说明 #

• 避免逐行扩展：不再生成每秒一行的冗余数据，直接处理时间区间，数据量大幅减少。
• 高效窗口生成：用GENERATOR只生成最多2000个窗口，而非全量时间维度。
• 精准重叠计算：通过区间交集判断，只处理有实际数据的窗口，避免无效连接。

关键逻辑匹配 #

以你提到的cheese标签例子为例：
原始数据会被转换为对应时间区间（如[0,6)值100、[6,100)值500），查询会自动计算每个区间与目标窗口的重叠时长，最终得出(5*100+95*500)/100的加权平均值，窗口中点作为图表展示的时间点。

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