CODEWITHSUNDEEP
snowflake-cloud-data-platform
John
John

Reputation: 71

create a new column that contains a list of values from subsequent rows of partition by ID

I have a table like below,

enter image description here

and want to create a new column that contains a list of values from another column subsequent rows like below,

enter image description here

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

Answers (2)

Lukasz Szozda
Lukasz Szozda

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:

enter image description here

Upvotes: 1

Gokhan Atil
Gokhan Atil

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

Related Questions