I need to run:

select arrayagg(o_clerk) 
  within group (order by o_orderkey desc) 
  OVER (PARTITION BY o_orderkey order by o_orderkey 
     ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS RESULT
from sample_data

But Snowflake returns the error Sliding window frame unsupported for function ARRAYAGG. If I try to accumulate all without a sliding window, I get the error Cumulative window frame unsupported for function ARRAY_AGG.

How can I achieve this?

Sample data:

create or replace table sample_data as (
    with data as (
        select 1 a, [1,3,2,4,7,8,10] b
        union all select 2, [1,3,2,4,7,8,10]
    )

    select 'Ord'||a o_orderkey, 'c'||value o_clerk, index
    from data, table(flatten(b))
)
;

Desired result:

(

CodePudding user response：

Using the ARRAY_AGG and ARRAY_SLICE. Inspired by

The start_index and num_elem computation could be further simplified/generalized to emulate window sliding frame ROWS BETWEEN PRECEDING prec AND FOLLOWING foll.

SELECT *
   ,ROW_NUMBER() OVER(PARTITION BY o_orderkey ORDER BY INDEX) AS rn
   ,3 AS prec
   ,0 AS foll
   ,ARRAY_SLICE(
         ARRAY_AGG(o_clerk) WITHIN GROUP (ORDER BY INDEX) 
                            OVER(PARTITION BY o_orderkey)
         ,IFF(rn <= prec 1, 0, rn-(prec 1))
         ,IFF(rn <= prec 1, 0, rn-(prec 1))   IFF(rn <= prec 1, rn foll,prec 1 foll)
   ) 
FROM sample_data
ORDER BY O_ORDERKEY, INDEX;

Displaying each argument of ARRAY_SLICE for debug: