Reputation: 71
I have a table like below,
and want to create a new column that contains a list of values from another column subsequent rows like below,
for copy paste: timestamp ID Value
date_time ID s_val
2021-12-03 04:03:45 ID1 O
2021-12-03 04:03:46 ID1 P
2021-12-03 04:03:47 ID1 Q
2021-12-03 04:03:48 ID1 R
2021-12-03 04:03:49 ID1 NULL
2021-12-03 04:03:50 ID1 S
2021-12-03 04:03:51 ID1 T
2021-12-04 11:09:03 ID2 A
2021-12-04 11:09:04 ID2 B
2021-12-04 11:09:05 ID2 C
Upvotes: 2
Views: 114
Reputation: 176324
As a time of writing cumulative window frame in not supported to LISTAGG
, but it is supported for ARRAY_AGG
and
ARRAY_TO_STRING(ARRAY_AGG(),...) <~> LISTAGG(...)
:
SELECT *,
ARRAY_TO_STRING(ARRAY_AGG(s_val) OVER(PARTITION BY ID ORDER BY date_time), ',') AS new_val
FROM tab
ORDER BY ID, date_time;
For data:
CREATE OR REPLACE TABLE tab(date_time DATETIME, ID TEXT, s_val TEXT)
AS SELECT '2021-12-03 04:03:45', 'ID1','O'
UNION ALL SELECT '2021-12-03 04:03:46', 'ID1','P'
UNION ALL SELECT '2021-12-03 04:03:47', 'ID1','Q'
UNION ALL SELECT '2021-12-03 04:03:48', 'ID1','R'
UNION ALL SELECT '2021-12-03 04:03:49', 'ID1', NULL
UNION ALL SELECT '2021-12-03 04:03:50', 'ID1','S'
UNION ALL SELECT '2021-12-03 04:03:51', 'ID1','T'
UNION ALL SELECT '2021-12-04 11:09:03', 'ID2','A'
UNION ALL SELECT '2021-12-04 11:09:04', 'ID2','B'
UNION ALL SELECT '2021-12-04 11:09:05', 'ID2','C';
Output:
Upvotes: 1
Reputation: 10199
Because Snowflake does not support cumulative window frames for LISTAGG, I wrote this one:
SELECT m.date_time, m.ID, m.s_val,
( SELECT LISTAGG( s.s_val) FROM mydata s WHERE s.ID= m.ID and s.date_time <= m.date_time ) new_val
FROM mydata m
order by m.ID, m.date_Time;
+---------------------+-----+-------+---------+
| DATE_TIME | ID | S_VAL | NEW_VAL |
+---------------------+-----+-------+---------+
| 2021-12-03 40:03:45 | ID1 | O | O |
| 2021-12-03 40:03:46 | ID1 | P | OP |
| 2021-12-03 40:03:47 | ID1 | Q | OPQ |
| 2021-12-03 40:03:48 | ID1 | R | OPQR |
| 2021-12-03 40:03:49 | ID1 | NULL | OPQR |
| 2021-12-03 40:03:50 | ID1 | S | OPQRS |
| 2021-12-03 40:03:51 | ID1 | T | OPQRST |
| 2021-12-04 11:09:03 | ID2 | A | A |
| 2021-12-04 11:09:04 | ID2 | B | AB |
| 2021-12-04 11:09:05 | ID2 | C | ABC |
+---------------------+-----+-------+---------+
Upvotes: 3