编写SQL查询：基于已知Total与Sales计算缺失的Total值

现有一张SALES表，仅2019-01-02的Sales和Total值已知，其余日期仅Sales值已知、Total值缺失。需要根据Sales字段计算出已知日期前后的缺失Total值。

原始表结构及数据 #

期望输出 #

建表及插入数据SQL #

CREATE TABLE SALES (DATE DATE, SALES INT, TOTAL INT);
INSERT INTO SALES VALUES
('2018-12-31', -200, NULL),
('2019-01-01', 100, NULL),
('2019-01-02', 200, 1000),
('2019-01-03', -150, NULL),
('2019-01-04', 300, NULL);

（注：修正了原插入语句的语法错误，补充了必要的逗号和分号）

解决方案SQL #

我们可以通过窗口函数分别计算已知日期前后的累积值，推导缺失的Total：

WITH base_info AS (
    SELECT 
        DATE,
        SALES,
        TOTAL,
        -- 获取已知的基准Total值（仅2019-01-02有值）
        MAX(TOTAL) OVER () AS base_total,
        -- 标记已知Total的日期
        (SELECT DATE FROM SALES WHERE TOTAL IS NOT NULL) AS known_date
    FROM SALES
),
calc_sums AS (
    SELECT 
        DATE,
        SALES,
        TOTAL,
        base_total,
        known_date,
        -- 计算已知日期之后的Sales累积和（从已知日期下一行到当前行）
        SUM(CASE WHEN DATE > known_date THEN SALES ELSE 0 END) 
            OVER (ORDER BY DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS post_sales_sum,
        -- 计算已知日期之前的Sales累积和（从当前行到已知日期上一行）
        SUM(CASE WHEN DATE < known_date THEN SALES ELSE 0 END) 
            OVER (ORDER BY DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pre_sales_sum
    FROM base_info
)
SELECT 
    DATE,
    SALES,
    CASE
        WHEN TOTAL IS NOT NULL THEN CONCAT(TOTAL, ' : This total is known')
        WHEN DATE < known_date THEN 
            CONCAT(base_total - pre_sales_sum, ' : ', base_total, ' - ', pre_sales_sum)
        WHEN DATE > known_date THEN 
            CONCAT(base_total + post_sales_sum, ' : ', base_total, ' + ', post_sales_sum)
    END AS Total
FROM calc_sums
ORDER BY DATE;

逻辑说明 #

1. base_info CTE：提取全局基准Total值和已知Total的日期，为后续计算提供基础。
2. calc_sums CTE：
   • 对已知日期之后的行，累加从已知日期下一行到当前行的Sales，用基准值加上该累加和得到当前行Total。
   • 对已知日期之前的行，累加从当前行到已知日期上一行的Sales，用基准值减去该累加和得到当前行Total。
3. 最终通过CASE语句拼接成包含计算逻辑的输出格式，按日期排序返回结果。

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