Netezza中按ID为30天窗口内的日期分配连续递增分桶编号的方法
Netezza 30天窗口相邻日期分桶实现
核心逻辑
不需要写自定义循环逻辑,通过标准窗口函数即可完成分桶:
- 按ID对数据分区,将每个ID下的日期按时间正序排列
- 用
LAG()函数取同ID下与当前日期相邻的上一条记录的日期,计算两个日期的间隔天数 - 将相邻间隔超过30天的位置标记为分桶断点,对断点值在同ID分区内做累积求和,得到的累积值就是对应日期的分桶编号
- 每个ID下的第一条日期默认计入第一个分桶,保证分桶号从1开始,匹配预期输出规则
可直接运行的Netezza SQL
WITH adjacent_date_gap AS ( SELECT ID, DATE, -- 取同ID内排在当前日期之前的上一个日期,分区首条记录的前置值为NULL LAG(DATE, 1) OVER (PARTITION BY ID ORDER BY DATE) AS prev_date FROM dates_bucket ), bucket_break_flag AS ( SELECT ID, DATE, -- 标记断点:首条记录、与上一个日期间隔超过30天的位置标记为1,其余位置标记为0 CASE WHEN prev_date IS NULL THEN 1 WHEN (DATE - prev_date) > 30 THEN 1 ELSE 0 END AS break_flag FROM adjacent_date_gap ) -- 对断点值做从分区首行到当前行的累积求和,得到最终分桶号 SELECT ID, DATE, SUM(break_flag) OVER ( PARTITION BY ID ORDER BY DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS ROW_NUM FROM bucket_break_flag ORDER BY ID, DATE;
语法适配说明
- Netezza 中两个date类型字段直接做减法运算,返回值为两个日期间隔的整数天数,无需额外调用日期差函数,计算结果可直接用于30天间隔判断
- 累积求和时显式指定窗口范围为“从分区起始行到当前行”,是为了规避Netezza不同小版本间窗口帧默认行为的差异,确保累积计算仅在当前ID分区内生效,不会出现跨ID累加的问题
- 该SQL执行后的返回结果与需求给出的预期输出完全匹配,可直接在对应业务场景中复用
内容的提问来源于stack exchange,提问作者Kellan Baker
相关产品推荐
相关产品推荐

