如何在Snowflake中为每个ID生成对应长度的日期序列列？

需求说明 #

现有一张表，每个id对应的行数不同，且同一id的date_column值完全一致。需要新增一列generated_column，规则为：

• 以每个id对应的date_column值作为起始日期
• 该id下的后续行日期依次递减1个月、2个月……直至处理完所有行
• 新的id重新生成对应的日期序列

原始表数据 #

id     date_column
A      2020-05-20
A      2020-05-20
A      2020-05-20
B      2020-07-23    
B      2020-07-23
C      2021-08-27
C      2021-08-27

期望输出 #

id     date_column      generated_column    
A      2020-05-20        2020-05-20
A      2020-05-20        2020-04-20
A      2020-05-20        2020-03-20
B      2020-07-23        2020-07-23
B      2020-07-23        2020-06-23
C      2021-08-27        2021-08-27
C      2021-08-27        2021-07-27

解决方案 #

完全可以通过Snowflake的SQL实现，核心是结合窗口函数生成分组内的行序号，再通过日期偏移函数计算目标日期：

SELECT
    id,
    date_column,
    DATEADD(MONTH, -(ROW_NUMBER() OVER (PARTITION BY id ORDER BY date_column) - 1), date_column) AS generated_column
FROM
    your_table_name;

代码说明 #

• PARTITION BY id：按id分组，确保每个id内部单独生成行序号，不同id的序号从头开始计数
• ROW_NUMBER() OVER (...)：为每个id下的行生成从1开始的连续序号
• DATEADD(MONTH, -(序号-1), date_column)：以原日期为基准，第一行偏移量为0（保持原日期），第二行偏移-1个月（递减1个月），以此类推，实现逐月递减的效果

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