Snowflake中带ORDER BY的COUNT(DISTINCT...)窗口函数替代方案

在Snowflake中，当窗口函数的OVER子句包含ORDER BY（不管是累积窗口还是滑动窗口）时，直接使用COUNT(DISTINCT col)会触发编译错误——官方文档明确禁止这种用法。

错误示例 #

执行以下代码：

SELECT *, COUNT(DISTINCT val) OVER(ORDER BY id)
FROM VALUES (0, NULL),(1,10),(2,20),(3,20),(4,30),(5, 20),(6, 10) AS s(id, val)
ORDER BY id;

会得到报错：

Error: distinct cannot be used with a window frame or an order.

等效实现方案 #

核心思路是先标记每个值在目标窗口内是否为首次出现，再通过求和标记值来实现去重计数。

完整SQL代码

SELECT 
    id,
    val,
    -- 累积窗口：从第一行到当前行的去重计数
    SUM(CASE WHEN val IS NOT NULL AND rn_cumulative = 1 THEN 1 ELSE 0 END) 
        OVER(ORDER BY id) AS CNT_CUMULATIVE,
    -- 滑动窗口：当前行及前3行的去重计数
    SUM(CASE WHEN val IS NOT NULL AND rn_sliding = 1 THEN 1 ELSE 0 END) 
        OVER(ORDER BY id ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS CNT_SLIDING
FROM (
    SELECT 
        id,
        val,
        -- 标记累积窗口内val的首次出现
        ROW_NUMBER() OVER(PARTITION BY val ORDER BY id) AS rn_cumulative,
        -- 标记滑动窗口内val的首次出现
        ROW_NUMBER() OVER(PARTITION BY val ORDER BY id ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS rn_sliding
    FROM VALUES (0, NULL),(1,10),(2,20),(3,20),(4,30),(5, 20),(6, 10) AS s(id, val)
) t
ORDER BY id;

执行结果 #

+----+-----+----------------+-------------+
| ID | VAL | CNT_CUMULATIVE | CNT_SLIDING |
+----+-----+----------------+-------------+
|  0 |     |              0 |           0 |
|  1 |  10 |              1 |           1 |
|  2 |  20 |              2 |           2 |
|  3 |  20 |              2 |           2 |
|  4 |  30 |              3 |           3 |
|  5 |  20 |              3 |           2 |
|  6 |  10 |              3 |           3 |
+----+-----+----------------+-------------+

逻辑说明 #

1. 内层子查询中，ROW_NUMBER()按val分区、id排序：
   • 对于累积窗口，每个val的首次出现会被标记为1，后续重复出现标记为2、3...
   • 对于滑动窗口，仅在当前滑动范围内首次出现的val会被标记为1
2. 外层通过SUM()对标记为1的行求和，就等价于COUNT(DISTINCT val)的窗口计数效果
3. 加入val IS NOT NULL的判断，让NULL值不被计入计数（符合示例中的期望输出）

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