Snowflake中使用滑动窗口计算MEDIAN的纯SQL方案问询

想要用MEDIAN窗口函数实现固定大小（此处为4）的滑动窗口计算，执行以下SQL时出现报错：

SELECT *,
      MEDIAN(n) OVER(ORDER BY id ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
FROM test_data
ORDER BY id;

报错信息：Sliding window frame unsupported for function MEDIAN

示例数据创建语句 #

CREATE OR REPLACE TEMP TABLE test_data
AS
SELECT index AS id, ABS(RANDOM(5))%100 AS n
FROM TABLE(FLATTEN(ARRAY_GENERATE_RANGE(1,10)));

窗口大小为4时的预期输出 #

纯SQL解决方案 #

通过收集滑动窗口内的数据到数组并排序，手动计算中位数：

SELECT 
    id,
    n,
    CASE 
        WHEN ARRAY_SIZE(window_n) % 2 = 1 THEN ARRAY_SLICE(window_n, (ARRAY_SIZE(window_n) + 1) / 2, 1)[0]
        ELSE (ARRAY_SLICE(window_n, ARRAY_SIZE(window_n) / 2, 1)[0] + ARRAY_SLICE(window_n, ARRAY_SIZE(window_n) / 2 + 1, 1)[0]) / 2.0
    END AS median
FROM (
    SELECT 
        id,
        n,
        ARRAY_SORT(ARRAY_AGG(n) OVER(ORDER BY id ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)) AS window_n
    FROM test_data
)
ORDER BY id;

方案说明

1. 用ARRAY_AGG(n) OVER(...)收集当前行及前3行的所有n值，形成滑动窗口数组
2. 用ARRAY_SORT对窗口数组进行排序，为计算中位数做准备
3. 根据数组长度的奇偶性计算中位数：
   • 奇数长度：取数组中间位置的元素
   • 偶数长度：取中间两个元素的平均值

内容的提问来源于stack exchange，提问作者Lukasz Szozda