Vertica中如何替代COUNT(DISTINCT) OVER()实现窗口去重计数？

问题背景 #

现有Vertica数据库表my_table，包含id、error_code、timestamp三列，需要检查最近1小时内，同一id的5分钟时间窗口区间中是否出现了3种及以上不同的error_code。

原尝试使用窗口函数COUNT(DISTINCT)实现，但Vertica不支持该语法，报错如下：

原查询代码 #

select * from
(SELECT id, err_code, timestamp,
COUNT(DISTINCT err_code) OVER (PARTITION BY id ORDER BY timestamp
       RANGE BETWEEN INTERVAL '10 minutes'
   PRECEDING AND CURRENT ROW) as count
FROM my_table
WHERE timestamp > CURRENT_TIMESTAMP - INTERVAL '1 hour 5 minutes'
group by id, err_code, timestamp
order by id, timestamp) a
order by id, timestamp

报错信息 #

ERROR: Only MIN/MAX and BOOL_AND/BOOL_OR are allowed to use DISTINCT

解决方案 #

针对Vertica的限制，提供两种可行实现方式：

方案一：窗口函数+条件聚合（高性能推荐） #

利用ROW_NUMBER()标记每个error_code在同一id下的首次出现，再通过窗口内的条件求和统计不同error_code数量：

WITH window_marked AS (
    SELECT 
        id,
        error_code,
        timestamp,
        -- 标记当前error_code在同一id下是否为首次出现
        ROW_NUMBER() OVER (
            PARTITION BY id, error_code 
            ORDER BY timestamp
        ) AS rn
    FROM my_table
    WHERE timestamp > CURRENT_TIMESTAMP - INTERVAL '1 hour' -- 限定最近1小时数据
),
distinct_error_stats AS (
    SELECT 
        id,
        error_code,
        timestamp,
        -- 统计5分钟窗口内不同error_code的数量
        SUM(CASE WHEN rn = 1 THEN 1 ELSE 0 END) OVER (
            PARTITION BY id 
            ORDER BY timestamp
            RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW
        ) AS distinct_error_count
    FROM window_marked
)
-- 筛选出符合条件的记录（不同error_code≥3）
SELECT DISTINCT id, timestamp, distinct_error_count
FROM distinct_error_stats
WHERE distinct_error_count >= 3
ORDER BY id, timestamp;

方案二：自关联查询（逻辑直观） #

通过自关联匹配同一id、时间在当前记录5分钟窗口内的所有数据，直接统计不同error_code的数量：

SELECT DISTINCT
    t1.id,
    t1.timestamp,
    COUNT(DISTINCT t2.error_code) AS distinct_error_count
FROM my_table t1
JOIN my_table t2 
    ON t1.id = t2.id
    AND t2.timestamp BETWEEN t1.timestamp - INTERVAL '5 minutes' AND t1.timestamp
WHERE t1.timestamp > CURRENT_TIMESTAMP - INTERVAL '1 hour'
GROUP BY t1.id, t1.timestamp
HAVING COUNT(DISTINCT t2.error_code) >= 3
ORDER BY t1.id, t1.timestamp;

方案说明 #

• 方案一基于窗口函数实现，避免了自关联的笛卡尔积，数据量大时性能更优。
• 方案二逻辑简单易懂，但当表数据量较大时，自关联会产生较多中间数据，性能可能不如方案一。

内容的提问来源于stack exchange，提问作者Dean Taler