How to create multiple rows from a single row in snowflake using sql-CodePudding

I have a table and I want to create 5 rows with respect to id and week. for example

input table

id  week   value
1  2022-W1 200
2  2022-W3 500
2  2022-W6 600

output

id  week   value
1  2022-W1 200
1  2022-W2 200
1  2022-W3 200
1  2022-W4 200
1  2022-W5 200
2  2022-W3 500
2  2022-W4 500
2  2022-W5 500
2  2022-W6 600
2  2022-W7 600
2  2022-W8 600
2  2022-W9 600
2  2022-W10 600

Explanation -
for id = 1, w1 -> add 5 rows incremental to week

1  2022-W1 200
1  2022-W2 200
1  2022-W3 200
1  2022-W4 200
1  2022-W5 200

There are 2 rows for id = 2, (w3, and w6). for w3, there will be only 3 rows because 4 rows should be w6 and w6 is already present in the table.

2  2022-W3 500
2  2022-W4 500
2  2022-W5 500

for id=2, w6 -> add 5 rows incremental to week

2  2022-W6 600
2  2022-W7 600
2  2022-W8 600
2  2022-W8 600
2  2022-W8 600

CodePudding user response：

Operating on string (YYYY-Wx) is not recommended, and it will be much easier if input is provided as first day of week (YYYY-MM-DD) and only transformed to YYYY-Wx for presentation.

Key ideas of script below:

transform week to date - column yw (DATEADD)
find next date (LEAD)
compute week difference (DATEDIFF)
create tally table (VALUES (0), (1),(2),(3),(4))
compute new dates
transform back to YYYY-Wx: (CONCAT)

Sample:

CREATE OR REPLACE TABLE tab(id INT, week TEXT, value INT )
AS
SELECT 1,  '2022-W1', 200 UNION 
SELECT 2,  '2022-W3', 500 UNION
SELECT 2,  '2022-W6', 600;

Query:

WITH cte AS (
  SELECT *, 
    DATEADD('week',REPLACE(week, LEFT(week,6)),
            DATE_FROM_PARTS(LEFT(week,4),1,1)) AS yw,
    DATEDIFF('week', YW, LEAD(yw) OVER(PARTITION BY id ORDER BY yw)) AS diff
  FROM tab
) 
SELECT cte.ID, 
   CONCAT(YEAR(DATEADD('week', s.num, cte.YW)), '-W',
          WEEK(DATEADD('week', s.num, cte.YW))) AS new_week, cte.value
FROM cte
JOIN (VALUES (0), (1),(2),(3),(4)) AS s(num)
  ON  s.num < cte.diff
  OR cte.diff IS NULL
ORDER BY cte.ID, cte.YW;

Output:

Warning: The computation YYYY-WW -> date -> back to week may not work across multiple years i.e, 2022-W52 -> 2023-W1. Testing such scenario is recommended. Ideally storing week as date.