How to perform aggregation based on fixed number of chunk of rows in Snowflake-CodePudding

Output Table expecting Output Table

As per above example chunk of row are 3. Output of aggression function taking group by 3 rows.

All key are unique. Any lead how to achieve this in snowflake?

CodePudding user response：

First I assigned row numbers, and then I group them using CONDITIONAL_TRUE_EVENT. Can you try this one?

with sorted_keys as (
select InputKey,
row_number() over (order by 1) - 1 order_no
from test1 -- source table
),
groupped_keys as (
select
InputKey,
CONDITIONAL_TRUE_EVENT( MOD(order_no, 3 ) = 0 ) OVER (order by order_no) gr_no
from sorted_keys
)
select listagg( InputKey, ',' ) OutputKeys
from groupped_keys
group by gr_no
order by gr_no;

 ------------ 
| OUTPUTKEYS |
 ------------ 
| K1,K2,K3   |
| K4,K5,K6   |
| K7,K8,K9   |
| K10,K11    |
 ------------

CodePudding user response：

Alternatively, you can just divide the row number by the chunk size and truncate the decimal places, this way you will get unique group numbers:

WITH sample_data AS (
    
    SELECT CONCAT('K', SEQ4() 1) AS InputKey
      FROM TABLE(GENERATOR(ROWCOUNT => 10))
    
), sample_data_with_row_number AS (
    
    SELECT InputKey, ROW_NUMBER() OVER(ORDER BY 1)-1 AS rn
      FROM sample_data
)  
SELECT LISTAGG(InputKey, ',') AS OutputKeys
  FROM sample_data_with_row_number
 GROUP BY TRUNC(rn/3)
 ORDER BY TRUNC(rn/3);