Let's say that I have this table:

I want to construct an array in Snowflake where the dimension of the array is always 50, the default value is zero, and I wanna populate the array with certain values at certain positions according to Index and Value columns. The desired output is

CodePudding user response：

Maybe something like this.

with my_data as (
    -- data table as cte
    select
        1 as Key,
        3 as Index,
        10 as Value
    union all
    select
        1,
        5,
        18
    union all
    select
        1,
        14,
        4
    union all
    select
        2,
        2,
        11
    union all
    select
        2,
        13,
        24
    union all
    select
        2,
        29,
        40
),
index_cte as (
    -- generate 1-50 index values
    select
        row_number() over (
            order by
                seq4()
        ) as fixed_index
    from
        table(generator(rowcount => 50))
),
keys_fixed_index as(
    -- cross join distinct keys with fixed_index values
    select
        distinct key,
        fixed_index
    from
        my_data
        cross join index_cte
)
select
    distinct
    t1.key,
    ARRAY_AGG(ifnull(value, 0)) WITHIN GROUP (
        order by
            t1.fixed_index asc
    ) OVER (PARTITION BY t1.key) as array
from
    keys_fixed_index t1
    left join my_data t2 on t1.key = t2.key
    and t1.fixed_index = t2.index
order by
    key

CodePudding user response：

UPDATED TO USE A SET BASED APPRAOCH

Instead of using string manipulations and aggregations, this can be done in a much simpler way using a set based approach. I don't know why it didn't occur to me sooner.

with cte as

(select key,
        row_number() over (partition by key order by 1)-1 as index, 
        0 as value
 from table(generator(rowcount=>50)) cross join (select distinct key from t)

 union
 select key,index,value 
 from t

 minus
 select key,index,0
 from t)

select key,array_agg(value) within group (order by index) as value_array
from cte
group by key