将一行复制一定次数，然后每次将 30 分钟添加到时间戳（就像序列一样）

样本数据：

ID      Location Type   Trip End            Number of periods
1298    Residential     02/01/2022 05:30    48
1298    Residential     03/01/2022 05:30    6
1244    Commercial      31/12/2021 09:00    2
1244    Residential     31/12/2021 10:30    1

本质上，我想根据“周期数”列中的数字复制该行。另外，为每个新的重复行添加 30 分钟。 所以新的第二行将是：

1298    Residential     02/01/2022 06:00    48

所以这是使用表格在雪花中解决的发电机 https://docs.snowflake.com/en/sql-reference/functions/generator.html（有固定输入）结合ROW_NUMBER https://docs.snowflake.com/en/sql-reference/functions/row_number.html然后使用DATEADD https://docs.snowflake.com/en/sql-reference/functions/dateadd.html

我更改了 48 -> 8 以使输出不那么难看。

WITH data(ID, LocationType, TripEnd, NumPeriods) as (
    select COLUMN1, COLUMN2, TO_TIMESTAMP_NTZ(COLUMN3, 'dd/mm/yyyy hh:mi'), COLUMN4 from values
        (1298, 'Residential','02/01/2022 05:30',8),
        (1298, 'Residential','03/01/2022 05:30',6),
        (1244, 'Commercial','31/12/2021 09:00',2),
        (1244, 'Residential','31/12/2021 10:30',1) 
), set_of_nums as (
    SELECT row_number() over (order by null)-1 as rn
    FROM table(generator(ROWCOUNT => 1000))
)
select d.*
    ,dateadd(minute, 30 * s.rn, TripEnd) as range_time
FROM DATA as d
JOIN set_of_nums as s ON d.NumPeriods >= s.rn
ORDER BY 1,3,5
;

这使：