使用SQL累计求和与分区计算流程累计暂停时长的问题求助

问题根源 #

你的原查询逻辑存在重复计算问题：窗口函数按DATA_BASE排序累加时，会把前序基准日的单条暂停时长也叠加到当前基准日的结果中。比如同一次暂停在基准日1计算的时长是3天，在基准日2计算的时长是8天，原写法会把3+8都计入基准日2的累计值，而实际只需要取8作为该次暂停在基准日2的贡献值。

实现方案 #

方案1：仅查询每个基准日对应的累计暂停总时长 #

SELECT 
    s1.ID_PROCESS,
    s1.DATA_BASE,
    SUM(JULIANDAY(s1.DATA_BASE) - JULIANDAY(s2.DATA_SUSPENSION)) AS TIME_AUX
FROM (
    -- 取每个流程的所有唯一基准日
    SELECT DISTINCT ID_PROCESS, DATA_BASE 
    FROM SUSPENSIONS
) s1
-- 关联同一流程下所有发生在当前基准日之前的暂停记录
LEFT JOIN SUSPENSIONS s2 
    ON s1.ID_PROCESS = s2.ID_PROCESS 
    AND s2.DATA_SUSPENSION <= s1.DATA_BASE
GROUP BY s1.ID_PROCESS, s1.DATA_BASE
ORDER BY s1.ID_PROCESS, s1.DATA_BASE;

如果只要查询指定流程的结果，在子查询s1中加过滤条件WHERE ID_PROCESS = 2301194即可。

方案2：保留原表所有明细字段，同时带出累计时长 #

如果需要保留原表的DATA_SUSPENSION等所有字段，可以用上述统计结果关联回原表：

SELECT 
    s.*,
    t.TIME_AUX
FROM SUSPENSIONS s
LEFT JOIN (
    SELECT 
        s1.ID_PROCESS,
        s1.DATA_BASE,
        SUM(JULIANDAY(s1.DATA_BASE) - JULIANDAY(s2.DATA_SUSPENSION)) AS TIME_AUX
    FROM (SELECT DISTINCT ID_PROCESS, DATA_BASE FROM SUSPENSIONS) s1
    LEFT JOIN SUSPENSIONS s2 
        ON s1.ID_PROCESS = s2.ID_PROCESS 
        AND s2.DATA_SUSPENSION <= s1.DATA_BASE
    GROUP BY s1.ID_PROCESS, s1.DATA_BASE
) t ON s.ID_PROCESS = t.ID_PROCESS AND s.DATA_BASE = t.DATA_BASE
WHERE s.ID_PROCESS = 2301194
ORDER BY s.DATA_BASE, s.DATA_SUSPENSION;

该方案输出的同一基准日的所有行的TIME_AUX值一致，就是该基准日对应的累计暂停总天数，和你期望的结果一致。

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