SQL实现带Transition过滤的累计时间总和计算

现有如下结构的表：

需要编写SQL查询，返回Date列的累计时间总和，但需忽略Transition从0到1或0到0的过渡时段（即ID77到39的时间不计入），仅统计1到0、1到1的过渡时段。期望结果如下：

已知普通累计计算方法，但不清楚如何结合窗口函数实现该过渡过滤逻辑，寻求技术解决方案。

核心思路是用LAG()窗口函数获取上一行的状态和日期，通过条件判断过滤不符合要求的时段，再用累计窗口函数计算总和。

步骤说明 #

1. 获取上一行数据：用LAG()按日期排序，提取上一行的Transition状态和Date值，用于判断过渡类型。
2. 过滤有效时段：仅当上一行Transition为1时（即1→当前行的1或0），计算当前行与上一行的时间差；其他情况（上一行Transition为0）时间差记为0。
3. 累计求和：用SUM()窗口函数对过滤后的时间差做累计，得到每行的累计时间总和。

不同数据库的实现代码 #

MySQL 版本

SELECT 
    ID,
    Date,
    SUM(
        CASE 
            WHEN LAG(Transition) OVER (ORDER BY STR_TO_DATE(Date, '%d/%b/%y')) = 1 THEN 
                TIMESTAMPDIFF(DAY, 
                              LAG(STR_TO_DATE(Date, '%d/%b/%y')) OVER (ORDER BY STR_TO_DATE(Date, '%d/%b/%y')), 
                              STR_TO_DATE(Date, '%d/%b/%y')
                )
            ELSE 0
        END
    ) OVER (ORDER BY STR_TO_DATE(Date, '%d/%b/%y')) AS Running_Total
FROM your_table_name
ORDER BY STR_TO_DATE(Date, '%d/%b/%y');

注：需用STR_TO_DATE()将字符串格式的日期转为日期类型，确保排序和时间差计算准确。

PostgreSQL 版本

SELECT 
    ID,
    Date,
    SUM(
        CASE 
            WHEN LAG(Transition) OVER (ORDER BY TO_DATE(Date, 'DD/Mon/YY')) = 1 THEN 
                EXTRACT(DAY FROM (TO_DATE(Date, 'DD/Mon/YY') - LAG(TO_DATE(Date, 'DD/Mon/YY')) OVER (ORDER BY TO_DATE(Date, 'DD/Mon/YY'))))
            ELSE 0
        END
    ) OVER (ORDER BY TO_DATE(Date, 'DD/Mon/YY')) AS Running_Total
FROM your_table_name
ORDER BY TO_DATE(Date, 'DD/Mon/YY');

SQL Server 版本

SELECT 
    ID,
    Date,
    SUM(
        CASE 
            WHEN LAG(Transition) OVER (ORDER BY CONVERT(DATE, Date, 106)) = 1 THEN 
                DATEDIFF(DAY, 
                          LAG(CONVERT(DATE, Date, 106)) OVER (ORDER BY CONVERT(DATE, Date, 106)), 
                          CONVERT(DATE, Date, 106)
                )
            ELSE 0
        END
    ) OVER (ORDER BY CONVERT(DATE, Date, 106)) AS Running_Total
FROM your_table_name
ORDER BY CONVERT(DATE, Date, 106);

注：SQL Server用CONVERT(DATE, Date, 106)转换DD/Mon/YY格式的日期。

逻辑验证 #

以示例数据为例：

• 第1行（ID45）无前置行，累计值为0
• 第2行（ID23）：上一行Transition为1，计算Jan1到Jan8的时间差，累计值为该差值
• 第3行（ID12）：上一行Transition为1，计算Jan8到Feb2的时间差，累计值为前两次差值之和
• 第4行（ID77）：上一行Transition为1，计算Feb2到Feb14的时间差，累计值加上该差值
• 第5行（ID39）：上一行Transition为0，时间差记为0，累计值保持不变
• 第6行（ID33）：上一行Transition为1，计算Feb20到Mar2的时间差，累计值加上该差值

完全符合预期结果要求。

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