Snowflake中按分钟拆分START_TIME与END_TIME区间生成逐分钟记录

该需求在Snowflake中可直接通过原生函数实现，无需开发自定义函数或额外ETL流程。

• 先对每条记录的起止时间做分钟级对齐，计算两个时间点之间跨越的总分钟数，确定单条记录需要拆分出的行数
• 借助Snowflake内置的序列生成表函数，按分钟偏移量逐行生成对应记录
• 保留原表所有原始字段，基于偏移量计算逐分钟递增的MINUTE_SLOT字段值输出

假设你的原始业务表名为user_session，可直接使用以下SQL，若表名不同替换为实际表名即可：

SELECT 
    s.USER_ID,
    s.START_TIME,
    s.END_TIME,
    DATEADD(
        'minute', 
        g.VALUE, 
        DATE_TRUNC('minute', s.START_TIME)
    ) AS MINUTE_SLOT
FROM user_session s
CROSS JOIN TABLE(
    GENERATE_SERIES(
        0, 
        DATEDIFF('minute', DATE_TRUNC('minute', s.START_TIME), DATE_TRUNC('minute', s.END_TIME))
    )
) g;

如果你需要验证逻辑，可使用以下带示例测试数据的SQL直接运行查看效果：

WITH user_session AS (
    SELECT 'AAA001' AS USER_ID,
           '2020-04-04 09:04:27.000'::TIMESTAMP AS START_TIME,
           '2020-04-04 09:08:34.000'::TIMESTAMP AS END_TIME
)
SELECT 
    s.USER_ID,
    s.START_TIME,
    s.END_TIME,
    DATEADD(
        'minute', 
        g.VALUE, 
        DATE_TRUNC('minute', s.START_TIME)
    ) AS MINUTE_SLOT
FROM user_session s
CROSS JOIN TABLE(
    GENERATE_SERIES(
        0, 
        DATEDIFF('minute', DATE_TRUNC('minute', s.START_TIME), DATE_TRUNC('minute', s.END_TIME))
    )
) g;

针对给出的AAA001测试数据，执行后返回结果如下：

• 如果你不需要将MINUTE_SLOT对齐到整分钟，而是需要从原始START_TIME开始每60秒生成一个时间点，只需要把SQL中两处DATE_TRUNC('minute', s.START_TIME)替换为s.START_TIME即可
• 该逻辑自动兼容起止时间在同一分钟的记录，此类记录只会生成1行对应数据，不会出现重复或缺失
• 若需要过滤掉时长小于1分钟的无效记录，可在WHERE子句中添加DATEDIFF('second', s.START_TIME, s.END_TIME) >= 60条件

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