如何使用SQL窗口函数实现连续同类型数据的起止日期分组

核心思路是生成连续分组的标识ID：通过对比当前行和上一行的WTYPE，只要类型发生变化就生成一个新的分组ID，之后基于这个ID做聚合统计就行，全程不用自连接，效率比自连接高很多。

完整SQL代码 #

WITH GroupedData AS (
    SELECT 
        WTIME,
        VAL,
        WTYPE,
        -- 生成分组ID：当前行WTYPE和上一行不同时，累加1
        SUM(CASE WHEN LAG(WTYPE) OVER(ORDER BY WTIME) = WTYPE THEN 0 ELSE 1 END) 
            OVER(ORDER BY WTIME) AS GroupID
    FROM @Table
)
SELECT
    MIN(WTIME) AS StartTime,
    MAX(WTIME) AS EndTime,
    AVG(VAL) AS AvgVAL,
    WTYPE,
    COUNT(*) AS RecordCount
FROM GroupedData
GROUP BY GroupID, WTYPE
ORDER BY StartTime;

代码说明 #

1. LAG()窗口函数：LAG(WTYPE) OVER(ORDER BY WTIME)按时间戳排序后，获取当前行的上一行WTYPE值，保证顺序和时间线一致。
2. 生成分组ID：用SUM() OVER()累加判断结果——当前行和上一行WTYPE相同则加0，不同则加1，这样连续相同的WTYPE会被分到同一个GroupID下。
3. 聚合统计：最后按GroupID和WTYPE分组，计算每组的起止时间、VAL平均值和记录总数。

示例输出 #

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