能否用SQL窗口函数基于前一行计算值生成当前行数据？（Databricks SQL）

需要实现基于前一行计算后的值推导当前行的预测销量字段，具体生成CURR_DATE_EXTRAP_NUM_SOLD（当日预测销量）和LTD_EXTRAP_NUM_SOLD（累计预测销量），使用Databricks SQL完成。

背景数据 #

CREATE OR REPLACE TABLE example_data (
    SALES_DATE DATE,
    CURR_DATE_UNITS_PURCHASED NUMBER(10,1),
    LTD_UNITS_PURCHASED NUMBER(10,1),
    CURR_DATE_EXTRAP_NUM_SOLD NUMBER(10,1),
    LTD_EXTRAP_NUM_SOLD NUMBER(10,1)
);
INSERT INTO example_data VALUES
('2023-11-01', 1000, 1000, 0, 0),
('2023-11-02', 0,    1000, 0, 0),
('2023-11-03', 0,    1000, 0, 0),
('2023-11-04', 200,  1200, 0, 0),
('2023-11-05', 0,    1200, 0, 0),
('2023-11-06', 0,    1200, 0, 0),
('2023-11-07', 50,   1250, 0, 0),
('2023-11-08', 0,    1250, 0, 0);

原始数据展示：

说明：LTD代表Live-to-date，即截至当前日期的累计值。

计算规则 #

• CURR_DATE_EXTRAP_NUM_SOLD：当日预测销量 = 剩余库存 × 10%
• LTD_EXTRAP_NUM_SOLD：截至当日的预测销量累计值
• 剩余库存 = 当日LTD_UNITS_PURCHASED - 前一日LTD_EXTRAP_NUM_SOLD

期望结果 #

CREATE OR REPLACE TABLE example_data_expected (
    SALES_DATE DATE,
    CURR_DATE_UNITS_PURCHASED NUMBER(10,1),
    LTD_UNITS_PURCHASED NUMBER(10,1),
    CURR_DATE_EXTRAP_NUM_SOLD NUMBER(10,1),
    LTD_EXTRAP_NUM_SOLD NUMBER(10,1)
);
INSERT INTO example_data_expected VALUES
('2023-11-01', 1000, 1000, 100,  100),   -- 剩余库存1000
('2023-11-02', 0,    1000, 90,   190),   -- 剩余库存=1000-100=900
('2023-11-03', 0,    1000, 81,   271),   -- 剩余库存=1000-190=810
('2023-11-04', 200,  1200, 92.9, 363.9), -- 剩余库存=1200-271=929
('2023-11-05', 0,    1200, 83.6, 447.5), -- 剩余库存=1200-363.9=836.1
('2023-11-06', 0,    1200, 75.3, 522.8), -- 剩余库存=1200-447.5=752.5
('2023-11-07', 50,   1250, 72.7, 595.5), -- 剩余库存=1250-522.8=727.2
('2023-11-08', 0,    1250, 65.5, 661.0); -- 剩余库存=1250-595.5=654.5

期望结果展示：

之前的尝试及问题 #

使用LAG窗口函数无法实现需求，因为LAG读取的是原始表中LTD_EXTRAP_NUM_SOLD的初始0值，而非前一行计算后的结果。尝试代码如下：

SELECT
    sales_date,
    curr_date_units_purchased,
    ltd_units_purchased,
    (ltd_units_purchased - LAG(ltd_extrap_num_sold, 1, 0) OVER (ORDER BY sales_date)) AS remaining_units,
    (remaining_units * 0.10) as curr_date_extrap_num_sold,
    SUM(curr_date_extrap_num_sold) OVER (ORDER BY sales_date) AS ltd_extrap_num_sold
FROM example_data;

错误结果展示：

解决方案：递归CTE #

Databricks SQL支持ANSI标准的递归CTE，可通过递归方式逐行计算，每一行基于前一行的计算结果推导。

实现代码：

WITH recursive_sales AS (
    -- 基础部分：取第一行数据，计算初始值
    SELECT
        sales_date,
        curr_date_units_purchased,
        ltd_units_purchased,
        -- 第一天剩余库存是ltd_units_purchased，取10%作为当日销量
        ROUND(ltd_units_purchased * 0.1, 1) AS curr_date_extrap_num_sold,
        ROUND(ltd_units_purchased * 0.1, 1) AS ltd_extrap_num_sold
    FROM example_data
    WHERE sales_date = (SELECT MIN(sales_date) FROM example_data)
    
    UNION ALL
    
    -- 递归部分：关联前一行的计算结果，计算当前行的值
    SELECT
        ed.sales_date,
        ed.curr_date_units_purchased,
        ed.ltd_units_purchased,
        -- 当日销量 = (当日累计采购 - 前一日累计预测销量) × 10%
        ROUND((ed.ltd_units_purchased - rs.ltd_extrap_num_sold) * 0.1, 1) AS curr_date_extrap_num_sold,
        -- 累计预测销量 = 前一日累计 + 当日销量
        ROUND(rs.ltd_extrap_num_sold + ROUND((ed.ltd_units_purchased - rs.ltd_extrap_num_sold) * 0.1, 1), 1) AS ltd_extrap_num_sold
    FROM example_data ed
    JOIN recursive_sales rs ON ed.sales_date = DATEADD(day, 1, rs.sales_date)
)
SELECT * FROM recursive_sales ORDER BY sales_date;

代码说明 #

1. 基础CTE：筛选最早日期的数据，直接计算第一天的预测销量和累计值。
2. 递归CTE：通过DATEADD关联当前行与前一行，利用前一行的LTD_EXTRAP_NUM_SOLD计算当前行的剩余库存、当日销量及累计销量，用ROUND保证小数精度与期望结果一致。

运行该代码将生成与期望结果完全一致的输出，每一行计算均依赖前一行的最终计算值。

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