Snowflake中如何用窗口函数实现可变时间窗口求和？

你最初尝试的窗口函数写法在Snowflake中不可行——Snowflake要求窗口边界（rows/range between ...）必须是常量值（比如固定数字、固定时间间隔），不能引用列变量（比如lookback）。但可以通过以下两种方式用窗口函数实现你的需求：

方案一：前缀和差值法（推荐，性能更优） #

利用累计求和（前缀和）的特性，区间[billing_period_start, snapshot_date]的请求总和等于「截止到snapshot_date的累计请求数」减去「截止到billing_period_start前一天的累计请求数」。

步骤1：计算用户每日请求的累计前缀和 #

with user_requests_cumulative as (
    select
        user_id,
        request_date,
        total_requests,
        -- 计算每个用户从最早日期到当前日期的累计请求数
        sum(total_requests) over (
            partition by user_id
            order by request_date
            rows between unbounded preceding and current row
        ) as cumulative_requests
    from user_daily_requests
)

步骤2：关联订阅快照表计算周期内总和 #

select
    s.snapshot_date,
    s.user_id,
    s.billing_period_start,
    s.billing_period_end,
    -- 用当前快照日期的累计值，减去计费周期前一天的累计值，得到周期内总和
    coalesce(rc_current.cumulative_requests, 0)
    - coalesce(rc_prev.cumulative_requests, 0) as num_requests_in_period
from subscription_snapshot s
-- 关联到当前快照日期的累计请求数
left join user_requests_cumulative rc_current
    on s.user_id = rc_current.user_id
    and rc_current.request_date = s.snapshot_date
-- 关联到计费周期开始前一天的累计请求数
left join user_requests_cumulative rc_prev
    on s.user_id = rc_prev.user_id
    and rc_prev.request_date = dateadd(day, -1, s.billing_period_start)

方案二：窗口内条件求和 #

先将订阅快照表和请求日志表按用户关联，再在窗口内通过条件判断筛选出计费周期内的请求并求和：

with combined_data as (
    select
        s.snapshot_date,
        s.user_id,
        s.billing_period_start,
        s.billing_period_end,
        r.request_date,
        coalesce(r.total_requests, 0) as total_requests
    from subscription_snapshot s
    left join user_daily_requests r
        on s.user_id = r.user_id
)
select
    snapshot_date,
    user_id,
    billing_period_start,
    billing_period_end,
    -- 在窗口内仅对计费周期内的请求求和
    sum(case
        when request_date between s.billing_period_start and s.snapshot_date
        then total_requests
        else 0
    end) over (
        partition by user_id, snapshot_date
    ) as num_requests_in_period
from combined_data
group by snapshot_date, user_id, billing_period_start, billing_period_end, request_date, total_requests

补充说明 #

你之前的关联逻辑存在笔误：r.request_data应为r.request_date，且正确的关联条件应该是s.billing_period_start <= r.request_date and r.request_date <= s.snapshot_date，而非你写的r.request_data < s.billing_period_start（这会筛选出计费周期之前的请求，和需求相反）。

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