Presto窗口函数按周聚合日期统计SQL执行报错排查

你写的SQL存在以下语法、逻辑问题，直接导致执行失败、结果不准：

• 语法错误
  • CTE块结尾多余逗号：第一个cte右括号后多了逗号，Presto不支持CTE定义末尾留悬空逗号
  • select子句末尾多余逗号：窗口函数那行结尾多打了逗号，后续无其他字段，直接触发语法报错
  • 窗口函数语法错误：over()内不能写group by，分组逻辑要写在partition by后；同时拼写错误，将preceding错写为preceeding
  • 字段不存在：date_trunc('week', yr)里的yr字段在CTE中没有定义，CTE内存储出生日期的字段是dob
• 逻辑错误
  • 分组维度缺失：原正确逻辑是按c_id、出生日期、税号后4位三个维度分组，你写的CTE漏掉了c_id维度，统计出的cnt值和正确结果不符
  • 窗口逻辑不符合需求：你写的rows between 5 preceding and current row只取当前行+前5行共6行数据，既不是自然周聚合，也不是7天滚动累计，完全不符合统计要求
  • 原初始SQL里的distinct是冗余写法：已经写了group by的前提下，返回结果天然是去重的，加distinct只会增加不必要的计算开销
  • 缺少全量总计数计算逻辑：没有提前统计全量cnt总和，无法完成占比计算

with base_group as (
    -- 先复用你原SQL的正确分组逻辑，去掉冗余的distinct
    select 
        c_id,
        date_of_birth as dob,
        substr(trim(tax_id), -4) as last_4,
        count(*) as cnt 
    from schema.table
    where tax_id not in ('', ' ')
      and length(cast(date_of_birth as varchar)) > 0
    group by c_id, date_of_birth, substr(trim(tax_id), -4)
),
week_stat as (
    select
        -- Presto中date_trunc('week', 日期)返回对应周的周一日期，作为周度维度的标识
        date_trunc('week', dob) as 周度日期,
        sum(cnt) as 周度cnt总和,
        -- 嵌套sum+全局窗口，直接拿到全量总cnt，不需要额外join
        sum(sum(cnt)) over () as 全量总cnt
    from base_group
    group by 1
)
select
    周度日期,
    周度cnt总和,
    -- 转成浮点运算避免整数除法精度丢失，保留2位小数输出百分比
    round(周度cnt总和 * 100.0 / 全量总cnt, 2) as 周度总和占全量cnt总计数的百分比
from week_stat
order by 周度日期;

扩展说明：

• 如果需要月度统计，仅需把date_trunc('week', dob)的'week'替换为'month'，其余逻辑无需修改
• 如果需要的是滚动7天累计（非自然周，每个日期对应近7天的累计值），可以使用如下窗口逻辑替换聚合部分：

select
    dob as 统计日期,
    sum(cnt) over (order by dob rows between 6 preceding and current row) as 滚动7天cnt总和,
    round(sum(cnt) over (order by dob rows between 6 preceding and current row) * 100.0 / sum(cnt) over (), 2) as 滚动7天占比
from base_group
order by dob

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