如何用单行Trino SQL实现sum的last_value功能替代嵌套查询

需求 #

实现以下数据计算逻辑：

• 当flag=1时，获取该行之前7行中flag=0的input_value求和值
• flag=1的行需沿用最近一次计算出的求和结果

当前已通过CTE结合两个窗口函数实现该逻辑，但需改为单行Trino SQL，无需CTE或嵌套子查询。

示例表创建语句 #

CREATE TABLE <database>.<table> AS
SELECT * FROM (
    VALUES
('2025-03-01', 1.1, 0, 1.1),
('2025-03-02', 2.1, 0, 3.2),
('2025-03-03', 3.1, 0, 6.3),
('2025-03-04', 4.1, 0, 10.4),
('2025-03-05', 5.1, 0, 15.5),
('2025-03-06', 6.1, 0, 21.6),
('2025-03-07', 7.1, 0, 28.7),
('2025-03-08', 8.1, 0, 35.7),
('2025-03-09', 9.1, 0, 42.7),
('2025-03-10', 10.1, 1, 42.7),
('2025-03-11', 11.1, 0, 50.7),
('2025-03-12', 12.1, 0, 58.7),
('2025-03-13', 13.1, 0, 66.7),
('2025-03-14', 14.1, 0, 74.7),
('2025-03-15', 15.1, 0, 82.7),
('2025-03-16', 16.1, 0, 90.7),
('2025-03-17', 17.1, 1, 90.7),
('2025-03-18', 18.1, 1, 90.7),
('2025-03-19', 19.1, 1, 90.7),
('2025-03-20', 20.1, 0, 101.7),
('2025-03-21', 21.1, 0, 111.7),
('2025-03-22', 22.1, 0, 121.7),
('2025-03-23', 23.1, 0, 131.7),
('2025-03-24', 24.1, 0, 141.7),
('2025-03-25', 25.1, 0, 151.7),
('2025-03-26', 26.1, 0, 161.7),
('2025-03-27', 27.1, 0, 168.7),
('2025-03-28', 28.1, 0, 175.7),
('2025-03-29', 29.1, 0, 182.7),
('2025-03-30', 30.1, 0, 189.7),
('2025-03-31', 31.1, 0, 196.7)
) AS t (input_date, input_value, flag, expected_sum)
;

原CTE实现代码 #

with calculate_sum as (
SELECT input_date
, input_value
, flag
, expected_sum
, SUM(CASE WHEN flag=0 then input_value END)
    OVER(PARTITION BY flag ORDER BY input_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as intermediate_7day_sum
FROM <database>.<table>
)
SELECT input_date
    , input_value
    , flag
    , expected_sum
    , intermediate_7day_sum
    , LAST_VALUE(intermediate_7day_sum) IGNORE NULLS 
        OVER(ORDER BY input_date ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) as final_7day_sum
FROM calculate_sum
ORDER BY input_date
;

单行Trino SQL解决方案 #

SELECT 
    input_date,
    input_value,
    flag,
    expected_sum,
    SUM(CASE WHEN flag=0 THEN input_value END) 
        OVER(ORDER BY input_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS intermediate_7day_sum,
    LAST_VALUE(
        SUM(CASE WHEN flag=0 THEN input_value END) 
            OVER(ORDER BY input_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
    ) IGNORE NULLS 
        OVER(ORDER BY input_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS final_7day_sum
FROM <database>.<table>
ORDER BY input_date;

逻辑说明 #

Trino支持窗口函数嵌套调用，因此可以直接将内层的7日求和窗口函数作为LAST_VALUE的参数，省去CTE中间表：

1. 内层窗口函数：计算当前行及之前6行（共7行）中flag=0的input_value之和，flag=1的行此结果为NULL
2. 外层LAST_VALUE函数：通过IGNORE NULLS忽略空值，取到当前行为止最近一次有效的7日求和结果，满足flag=1行沿用最近结果的需求

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