SQL实现：按间隔天数阈值重置TimesCalled累加值

需求是对TimesCalled字段进行累加，但当累计的DaysBtwnCalls达到或超过DaysBtwnCallsLimit阈值时，需重置累加并重新开始计算。

示例场景：

• 前3条记录的累计DaysBtwnCalls为28（小于阈值30），TimesCalled累加结果为37；
• 第4条记录加入后，累计间隔天数超过阈值，此时TimesCalled需从当前行的15重新开始累加。

当前使用的SQL仅能累加当前行与前一行的DaysBtwnCalls，无法实现跨多行累加至阈值后重置的逻辑：

SELECT Name, Loc, DateCalled, TimesCalled, DaysBtwnCalls,DaysBtwnCallsLimit,
Sum(DaysBtwnCalls) Over (Partition by Name, Loc Order By DateCalled ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) as CallAccum
from WRK_ACCUM A left join wrk_accum2 b on a.row2 = b.ROW#  
ORDER BY NAME, LOC

解决方案 #

要实现该逻辑，核心是先为每一行标记所属的累加分组：累计DaysBtwnCalls未超阈值时归为同一分组，一旦超阈值则开启新分组；之后按分组对TimesCalled进行累加。以下提供两种适配不同数据库的实现方式：

方法1：窗口函数生成分组ID（适用于PostgreSQL、SQL Server、Oracle等） #

通过嵌套窗口函数计算累计天数，并以此生成分组标识，再按分组累加TimesCalled：

WITH grouped_data AS (
    SELECT 
        a.Name, 
        a.Loc, 
        a.DateCalled, 
        a.TimesCalled, 
        b.DaysBtwnCalls, 
        b.DaysBtwnCallsLimit,
        -- 计算当前行及之前的累计DaysBtwnCalls
        SUM(b.DaysBtwnCalls) OVER (PARTITION BY a.Name, a.Loc ORDER BY a.DateCalled) AS running_days,
        -- 生成分组ID：每次累计天数（不含当前行）超阈值时，分组ID+1
        SUM(CASE 
                WHEN SUM(b.DaysBtwnCalls) OVER (PARTITION BY a.Name, a.Loc ORDER BY a.DateCalled) - b.DaysBtwnCalls >= b.DaysBtwnCallsLimit 
                THEN 1 
                ELSE 0 
            END) OVER (PARTITION BY a.Name, a.Loc ORDER BY a.DateCalled) AS group_id
    FROM WRK_ACCUM a
    LEFT JOIN wrk_accum2 b ON a.row2 = b.ROW#
),
final_result AS (
    SELECT 
        Name, 
        Loc, 
        DateCalled, 
        TimesCalled, 
        DaysBtwnCalls, 
        DaysBtwnCallsLimit,
        -- 按分组ID累加TimesCalled
        SUM(TimesCalled) OVER (PARTITION BY Name, Loc, group_id ORDER BY DateCalled) AS CallAccum
    FROM grouped_data
)
SELECT * FROM final_result ORDER BY Name, Loc, DateCalled;

方法2：递归CTE（兼容性更强） #

如果数据库支持递归CTE（多数主流数据库均支持），可逐行计算累计值并判断是否重置：

WITH ordered_data AS (
    SELECT 
        a.Name, 
        a.Loc, 
        a.DateCalled, 
        a.TimesCalled, 
        b.DaysBtwnCalls, 
        b.DaysBtwnCallsLimit,
        -- 为每个(Name, Loc)组内的记录按日期排序
        ROW_NUMBER() OVER (PARTITION BY a.Name, a.Loc ORDER BY a.DateCalled) AS rn
    FROM WRK_ACCUM a
    LEFT JOIN wrk_accum2 b ON a.row2 = b.ROW#
),
recursive_accum AS (
    -- 初始化：每组的第一条记录
    SELECT 
        Name, 
        Loc, 
        DateCalled, 
        TimesCalled, 
        DaysBtwnCalls, 
        DaysBtwnCallsLimit,
        rn,
        TimesCalled AS CallAccum,
        DaysBtwnCalls AS running_days
    FROM ordered_data
    WHERE rn = 1
    UNION ALL
    -- 递归处理后续行
    SELECT 
        od.Name, 
        od.Loc, 
        od.DateCalled, 
        od.TimesCalled, 
        od.DaysBtwnCalls, 
        od.DaysBtwnCallsLimit,
        od.rn,
        -- 判断是否重置累加：若累计天数+当前天数超阈值，从当前TimesCalled开始；否则累加
        CASE WHEN ra.running_days + od.DaysBtwnCalls >= od.DaysBtwnCallsLimit 
             THEN od.TimesCalled 
             ELSE ra.CallAccum + od.TimesCalled 
        END AS CallAccum,
        -- 更新累计天数：超阈值则重置为当前天数，否则继续累加
        CASE WHEN ra.running_days + od.DaysBtwnCalls >= od.DaysBtwnCallsLimit 
             THEN od.DaysBtwnCalls 
             ELSE ra.running_days + od.DaysBtwnCalls 
        END AS running_days
    FROM ordered_data od
    JOIN recursive_accum ra ON od.Name = ra.Name AND od.Loc = ra.Loc AND od.rn = ra.rn + 1
)
SELECT Name, Loc, DateCalled, TimesCalled, DaysBtwnCalls, DaysBtwnCallsLimit, CallAccum
FROM recursive_accum
ORDER BY Name, Loc, DateCalled;

关键说明 #

• 两种方法均以Name和Loc作为分组维度，确保不同维度的累加独立计算；
• 方法1代码简洁，依赖数据库对嵌套窗口函数的支持；
• 方法2逻辑直观，兼容性更强，适合窗口函数支持有限的数据库。

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