Presto使用sum over partition by窗口函数计算周期消费报错如何解决

问题原因 #

原SQL存在3处错误：

• 语法错误：CASE判断分支存在拼写错误，when as spend_over_period = 'one_time'属于非法语法
• 冗余GROUP BY：SQL中使用窗口函数而非普通聚合函数，窗口函数会逐行返回计算结果，无需添加GROUP BY分组，多余的分组规则会触发非聚合字段的校验报错
• 窗口函数逻辑缺失：未在SUM()窗口函数中添加ORDER BY子句，会返回分区内所有消费的总求和，无法得到按日期滚动的累计结果

正确SQL #

select 
    s.day, 
    s.client_id, 
    b.budget_id, 
    b.budget_period, 
    b.budget_amount,
    s.spend,
    case 
        when b.budget_period = 'daily' then s.spend
        when b.budget_period = 'monthly' then sum(s.spend) over (partition by b.budget_id, date_trunc('month', date(s.day)) order by s.day)
        when b.budget_period = 'one_time' then sum(s.spend) over (partition by b.budget_id order by s.day)
    end as spend_over_period
from spend_table as s
join budget_table as b
    on s.day = b.day
    and s.client_id = b.client_id 
order by s.client_id, s.day

逻辑说明 #

• 按budget_id+月份分区计算月度预算的累计消费，保证不同月份的同预算ID数据不会被错误合并计算
• 窗口函数增加order by s.day规则，实现按日期从小到大的滚动累计求和，和期望输出的累计逻辑一致
• 去掉冗余的GROUP BY子句，避免非聚合字段的校验错误

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