Snowflake中Array_Agg不支持窗口框架的替代实现方案

需要在Snowflake中实现滑动窗口内的数组聚合，原SQL语句如下：

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

执行后Snowflake返回错误：Sliding window frame unsupported for function ARRAYAGG；尝试累积聚合时也报错：Cumulative window frame unsupported for function ARRAY_AGG。

样本数据创建语句：

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))
);

需求为：针对每个o_orderkey分区，每行取当前行及往前3行的o_clerk组成数组（对应BigQuery中滑动窗口ARRAY_AGG的效果）。

Snowflake目前不支持在滑动/累积窗口中直接使用ARRAY_AGG，可通过**LAG函数结合数组构造**的方式实现需求，具体代码如下：

select 
    o_orderkey,
    o_clerk,
    index,
    array_compact(
        array_construct(
            lag(o_clerk, 3) over (partition by o_orderkey order by index),
            lag(o_clerk, 2) over (partition by o_orderkey order by index),
            lag(o_clerk, 1) over (partition by o_orderkey order by index),
            o_clerk
        )
    ) as result
from sample_data
order by o_orderkey, index;

代码说明 #

1. 按o_orderkey分区、index排序：index是原数据中行的顺序标识，替代原SQL中无意义的o_orderkey排序逻辑。
2. LAG函数获取前N行值：分别获取当前行的前1、2、3行o_clerk值。
3. 构造并清理数组：用array_construct组合当前行与前N行的值，再通过array_compact移除数组中的NULL值（处理窗口起始阶段不足3行的场景）。

若需要调整数组内元素的排序（比如从当前行到前3行排列），可修改数组构造的顺序：

select 
    o_orderkey,
    o_clerk,
    index,
    array_compact(
        array_construct(
            o_clerk,
            lag(o_clerk, 1) over (partition by o_orderkey order by index),
            lag(o_clerk, 2) over (partition by o_orderkey order by index),
            lag(o_clerk, 3) over (partition by o_orderkey order by index)
        )
    ) as result
from sample_data
order by o_orderkey, index;

内容的提问来源于stack exchange，提问作者Felipe Hoffa