Excel公式转Snowflake查询结果异常，请求技术支持

Excel原公式逻辑 #

E列的递推计算逻辑如下：当当前行PARTS列（B列）与上一行相同时，用上一行的E列值减去当前行Non_Restricted_Empty_Bins列（D列）；否则用Open_Repair_Orders列（C列）减去当前行D列。

=If(B2=B1,E1-D2,C2-D2)

现有Snowflake查询 #

用户编写的查询语句：

SELECT 
 THEATER, PARTS, Open_Repair_Orders,Non_Restricted_Empty_Bins,
 case when 
 parts = lag(parts) over (order by parts) 
 then (lag(non_restricted_empty_bins,1,0) over (partition by parts order by theater) - non_restricted_empty_bins)
 else Open_Repair_Orders - Non_Restricted_Empty_Bins end as Rpr_Order_Consumption
 FROM CX_DB.CX_GSLOBAC_STG.XXBAC_SPM_FILL_RATE_TEST;

测试数据与期望输出 #

问题现象 #

现有查询运行后，Rpr_Order_Consumption列在出现11后的结果为0，与期望输出不符。

问题排查与修正 #

问题根源 #

1. 排序逻辑错误：原查询仅按parts排序，导致同parts的行顺序混乱，无法匹配Excel按行递推的逻辑。
2. 递推逻辑偏离：Excel使用上一行的计算结果（E1）减去当前D列值，而原查询用的是上一行的Non_Restricted_Empty_Bins减去当前值，完全不符合递推逻辑。
3. 缺少明确行顺序：必须指定能确定行顺序的字段（如主键、时间戳），否则Snowflake无法保证行的固定顺序，递推结果不可靠。

修正后的查询 #

使用累积求和模拟Excel的递推逻辑：

WITH ordered_data AS (
    SELECT 
        THEATER, 
        PARTS, 
        Open_Repair_Orders,
        Non_Restricted_Empty_Bins,
        -- 标记每个PARTS分组的起始行
        CASE WHEN PARTS != LAG(PARTS) OVER (ORDER BY PARTS, THEATER) THEN 1 ELSE 0 END AS group_start,
        -- 计算每行的增量：起始行用C-D，后续行用 -D
        CASE WHEN PARTS != LAG(PARTS) OVER (ORDER BY PARTS, THEATER) 
             THEN Open_Repair_Orders - Non_Restricted_Empty_Bins
             ELSE -Non_Restricted_Empty_Bins END AS delta
    FROM CX_DB.CX_GSLOBAC_STG.XXBAC_SPM_FILL_RATE_TEST
    -- 替换为数据真实的行顺序字段（如主键、加载时间），确保和Excel行顺序一致
    ORDER BY PARTS, THEATER
),
cumulative_calc AS (
    SELECT 
        *,
        -- 按PARTS分组累积求和，模拟递推计算
        SUM(delta) OVER (PARTITION BY PARTS ORDER BY PARTS, THEATER ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Rpr_Order_Consumption
    FROM ordered_data
)
SELECT 
    THEATER, 
    PARTS, 
    Open_Repair_Orders,
    Non_Restricted_Empty_Bins,
    Rpr_Order_Consumption
FROM cumulative_calc
ORDER BY PARTS, THEATER;

关键说明 #

• ordered_data CTE：先标记每个PARTS分组的起始行，同时计算每行的增量值（起始行取Open_Repair_Orders - Non_Restricted_Empty_Bins，后续行取-Non_Restricted_Empty_Bins）。
• cumulative_calc CTE：对每个PARTS分组内的增量值进行累积求和，得到和Excel完全一致的递推结果。
• 必须将ORDER BY PARTS, THEATER替换为数据真实的行顺序字段，否则结果可能不符合预期。

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