基于条件的SQL累计计算需求：含节假日特殊处理规则

需求规则 #

• 节假日（IsHoliday = 1）不参与累计计算；
• 按日期排序后的首行若为节假日，直接取该行的DaywisePlan值作为结果；
• 后续行：
  • 非节假日（IsHoliday = 0）：将该行的DaywisePlan累加到之前的有效累计值中；
  • 节假日：沿用最近一次的有效累计值（即上一个非节假日的累计结果）。

示例数据 #

解决方案（SQL） #

简洁版（支持IGNORE NULLS的数据库：MySQL 8.0+、PostgreSQL、SQL Server、Oracle） #

WITH non_holiday_cum AS (
    SELECT 
        *,
        -- 计算非节假日的累计和，节假日位置填充NULL
        SUM(CASE WHEN IsHoliday = 0 THEN DaywisePlan ELSE 0 END) OVER(ORDER BY Date) AS cum_sum,
        -- 仅保留非节假日的累计值，节假日设为NULL
        CASE WHEN IsHoliday = 0 THEN cum_sum ELSE NULL END AS valid_cum
    FROM your_table
    ORDER BY Date
)
SELECT 
    Date,
    DaywisePlan,
    IsHoliday,
    -- 取最近的非空有效累计值，首行假日则取自身DaywisePlan
    COALESCE(
        LAST_VALUE(valid_cum IGNORE NULLS) OVER(ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
        DaywisePlan
    ) AS ExpectedOutput
FROM non_holiday_cum
ORDER BY Date;

兼容低版本数据库（如MySQL < 8.0） #

WITH ranked_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER(ORDER BY Date) AS rn,
        -- 生成分组ID：每遇到非节假日则递增，连续假日归为同一组
        SUM(CASE WHEN IsHoliday = 0 THEN 1 ELSE 0 END) OVER(ORDER BY Date) AS group_id
    FROM your_table
),
cumulative_plans AS (
    SELECT 
        rn,
        group_id,
        -- 分组内计算非节假日的累计和
        SUM(CASE WHEN IsHoliday = 0 THEN DaywisePlan ELSE 0 END) OVER(PARTITION BY group_id ORDER BY rn) AS cum_plan,
        IsHoliday
    FROM ranked_data
)
SELECT 
    rd.Date,
    rd.DaywisePlan,
    rd.IsHoliday,
    -- 首行假日取自身值，否则取当前组的累计值
    CASE 
        WHEN rd.rn = 1 AND rd.IsHoliday = 1 THEN rd.DaywisePlan
        ELSE cp.cum_plan
    END AS ExpectedOutput
FROM ranked_data rd
JOIN cumulative_plans cp ON rd.group_id = cp.group_id AND cp.IsHoliday = 0
ORDER BY rd.Date;

逻辑说明 #

1. 简洁版：

   • 先计算所有非节假日的累计和，节假日对应的累计值设为NULL；
   • 用LAST_VALUE(IGNORE NULLS)获取截至当前行最近的非空累计值，首行如果是假日则用COALESCE兜底取自身的DaywisePlan。

2. 兼容版：

   • 给每行按日期排序并生成行号，同时用累加标记生成分组ID，连续假日会和下一个非节假日归为同一组；
   • 在每个分组内计算非节假日的累计和；
   • 最后将分组的累计值关联到组内所有行，首行特殊处理直接取自身值。

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