Redshift SQL：基于work_day偏移volume值并指定日期求和的实现

数据准备 #

CREATE TABLE test_work_day
(
  work_day  integer ,
  send_date DATE ,
  volume  integer,
  week_day varchar,
  hols varchar
);
insert into test_work_day values
(0, '2022-05-21', 0, 'Saturday',null),
(0, '2022-05-22', 0, 'Sunday',null),
(1, '2022-05-23', 0, 'Monday',null),
(1, '2022-05-24', 99, 'Tuesday',null),
(1, '2022-05-25', 111,'Wednesday',null),
(0, '2022-05-26', 154, 'Thursday', 'Public_holiday' ),
(1, '2022-05-27', 200, 'Friday',null),
(0, '2022-05-28', 0, 'Saturday',null),
(0, '2022-05-29', 0, 'Sunday',null),
(0, '2022-05-30', 0, 'Monday', 'Public_holiday'),
(1, '2022-05-31', 164, 'Tuesday',null),
(1, '2022-06-01', 123, 'Wednesday',null),
(1, '2022-06-02', 189, 'Thursday',null),
(1, '2022-06-03', 150, 'Friday',null),
(0, '2022-06-04', 0, 'Saturday',null),
(0, '2022-06-05', 0, 'Sunday',null),
(1, '2022-06-06', 100, 'Monday',null),
(1, '2022-06-07', 200, 'Tuesday',null);

select * from test_work_day;

当前查询输出：

业务需求 #

• 当work_day列值为0时，需将该行及后续行的volume值向下偏移至最近的work_day=1的行；若连续多行work_day=0，则统一将这些行的volume值偏移至第一个work_day=1的行，仅让volume值与work_day=1的行对齐。
• 从第一个work_day=0的日期（2022-05-26）起第8个日历日为补算节点，需将此前偏移的volume值与当日的volume值求和。

解决方案 #

核心思路 #

1. 用窗口函数LEAD IGNORE NULLS定位每个work_day=0行对应的后续第一个work_day=1的日期，作为偏移目标行。
2. 计算补算节点日期：第一个work_day=0日期加8天，得到2022-06-03。
3. 聚合所有work_day=0行的volume到对应目标行；在补算节点行，额外累加之前所有偏移过来的volume总和。

Redshift SQL 代码 #

WITH cte_target_dates AS (
    SELECT
        send_date,
        work_day,
        volume,
        week_day,
        hols,
        -- 定位当前行之后第一个work_day=1的日期
        LEAD(CASE WHEN work_day = 1 THEN send_date END) IGNORE NULLS OVER (ORDER BY send_date) AS target_work_day_date,
        -- 获取第一个work_day=0的日期
        MIN(CASE WHEN work_day = 0 THEN send_date END) OVER () AS first_zero_date
    FROM test_work_day
),
cte_calc_node AS (
    SELECT
        DATEADD(day, 8, first_zero_date) AS calc_node_date
    FROM cte_target_dates
    LIMIT 1
),
cte_shifted_volume AS (
    SELECT
        target_work_day_date AS send_date,
        SUM(volume) AS shifted_volume
    FROM cte_target_dates
    WHERE work_day = 0
    GROUP BY target_work_day_date
)
SELECT
    t.work_day,
    t.send_date,
    CASE
        -- 补算节点：累加当日volume+之前所有偏移的volume总和
        WHEN t.send_date = cn.calc_node_date 
        THEN t.volume + COALESCE(SUM(sv.shifted_volume) OVER (ORDER BY t.send_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0)
        -- 普通工作日：原volume+对应偏移过来的volume
        ELSE t.volume + COALESCE(sv.shifted_volume, 0)
    END AS volume,
    t.week_day,
    t.hols
FROM test_work_day t
LEFT JOIN cte_shifted_volume sv ON t.send_date = sv.send_date
CROSS JOIN cte_calc_node cn
WHERE t.work_day = 1  -- 仅保留工作日行，符合volume与work_day=1对齐的要求
ORDER BY t.send_date;

期望输出 #

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