SQL/Presto 基于ds维度计算全量item累计创建量的窗口函数实现

你当前SQL输出单用户维度累计值，核心原因是窗口函数中的partition by user_id子句的作用：

• 窗口函数的partition by会将全量数据按指定字段切分为互相独立的计算分区
• 每个分区内部单独执行累计逻辑，自然就变成了单个用户维度的item累计值，和全量统计的目标完全偏离
• 另外直接在原始明细上直接套窗口函数，会因为同一个ds对应多条原始记录，出现同一个日期输出多行累计值的问题，和你期望的单行输出结构不匹配

全量累计统计不需要按用户拆分，且要匹配最终「每个ds对应一行累计值」的输出格式，需要分两步处理：

1. 先按ds做分组聚合，统计每个统计日期下的新增item数量，从数据源层面完成日期粒度的收口
2. 在聚合后的结果上，不做任何用户维度的分区拆分，按日期升序执行累计求和，即可得到全量维度的累计item总数

select
  ds,
  sum(daily_item_cnt) over (
    order by ds
    rows between unbounded preceding and current row
  ) as cum_count
from (
  -- 按ds聚合得到当日新增item数
  select
    ds,
    count(item_id) as daily_item_cnt
  from table1
  group by ds
) base
order by ds

用你提供的样例数据执行该SQL，输出结果和你期望的格式完全一致：

cum_count  ds
1          1/1/2021
2          1/3/2021
4          1/4/2021
5          1/6/2021

• 窗口函数中不需要添加任何partition by子句，整个聚合结果作为统一的计算块，才能实现无维度拆分的全量累计
• 补充rows between unbounded preceding and current row是为了兼容不同数仓引擎的窗口框架默认值，避免相同ds值下的累计计算边界异常
• 如果存在同一个item跨日期重复录入的场景，聚合时可以将count(item_id)替换为count(distinct item_id)，实现去重后的新增统计

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