SparkSQL动态时间窗口计算：统计参与者事件后3天内其他事件数

数据样本 #

期望结果 #

现有代码（统计过去3天事件数） #

以下SparkSQL代码可实现过去3天的统计需求：

-- 这段SparkSQL用于统计过去3天的事件数量
select performer,
    event,
    event_time,
    count(event) 
         over(partition by performer
              order by cast(event_time as timestamp)
              range between interval 3 days preceding and current row
         ) as cnt
from table

解决方案（统计未来3天内的其他事件数） #

要实现统计每位参与者在某一事件后的未来3天内其他事件数量，可以调整窗口函数的范围为当前行到未来3天，并减去当前事件本身的计数：

select 
    performer,
    event,
    event_time,
    -- 统计当前事件及未来3天内的所有事件数，减去1得到其他事件的数量
    (count(event) over(
        partition by performer
        order by cast(event_time as timestamp)
        range between current row and interval 3 days following
    ) - 1) as count_other_event_in_NEXT_3_days
from table

逻辑说明 #

1. 按performer分区，确保只统计同一参与者的事件
2. 按event_time排序，确定事件的时间顺序
3. 窗口范围设为current row到interval 3 days following，覆盖当前事件及未来3天内的所有事件
4. 用总数减去1，排除当前事件本身，得到未来3天内其他事件的数量

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