Snowflake SQL需求：计算日期区间内每日需处理的Qty配额

原始数据表 #

假设表名为task_data，字段及数据如下：

计算逻辑 #

• 每日配额 = 当日target_date为该日期的qty总和 + 所有实际处理日期晚于该日期的未完成任务qty（即任务的target_date ≤ 当前日期，且actual_date > 当前日期）
• 具体示例：
  1. 2022-01-01的配额：所有target_date为2022-01-01的qty之和（2+1+3=6）
  2. 2022-01-02的配额：当日target_date为2022-01-02的qty（1） + 前一日未处理的任务qty（id2的1、id3的3），总计1+1+3=4
  3. 2022-01-03的配额：当日target_date为2022-01-03的qty（2） + 前两日未处理的任务qty（id3的3） + 前一日未处理的任务qty（id4的1），总计2+3+1=6

期望输出 #

Snowflake SQL 实现方案 #

WITH date_series AS (
    -- 生成所有需要计算的日期范围
    SELECT DISTINCT target_date AS calc_date
    FROM task_data
    ORDER BY calc_date
),
task_backlog AS (
    -- 计算每个日期对应的待处理任务总量
    SELECT
        ds.calc_date,
        SUM(td.qty) AS backlog_qty
    FROM date_series ds
    LEFT JOIN task_data td 
        ON td.target_date <= ds.calc_date 
        AND td.actual_date > ds.calc_date
    GROUP BY ds.calc_date
),
daily_target AS (
    -- 计算每个日期的当日target任务量
    SELECT
        target_date,
        SUM(qty) AS daily_qty
    FROM task_data
    GROUP BY target_date
)
SELECT
    dt.target_date,
    dt.daily_qty + COALESCE(tb.backlog_qty, 0) AS qty_qouta
FROM daily_target dt
JOIN task_backlog tb 
    ON dt.target_date = tb.calc_date
ORDER BY dt.target_date;

代码说明 #

1. date_series：提取数据中所有唯一的target_date，生成需要计算配额的日期集合
2. task_backlog：统计每个日期下，截止日期不晚于当前日期但实际未处理的任务总量
3. daily_target：统计每个日期作为截止日期的任务量总和
4. 最后关联两个统计结果，用COALESCE处理无待处理任务的空值情况，得到最终配额

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