在Redshift中统计当前行之前的连续NULL值数量

需求描述 #

我有一张按date列排序的表，原始数据如下：

需要得到如下结果：仅保留value非NULL的行，新增counts列表示当前行上方紧邻的连续NULL值行数。

实现方案 #

可以通过Redshift的窗口函数实现，具体SQL如下：

WITH numbered_rows AS (
    SELECT
        date,
        value,
        SUM(CASE WHEN value IS NOT NULL THEN 1 ELSE 0 END) OVER (ORDER BY date) AS group_id,
        ROW_NUMBER() OVER (ORDER BY date) AS row_num
    FROM your_table_name -- 替换为你的表名
),
group_summary AS (
    SELECT
        group_id,
        MIN(row_num) AS first_row_in_group
    FROM numbered_rows
    GROUP BY group_id
)
SELECT
    n.date,
    n.value,
    n.row_num - g.first_row_in_group AS counts
FROM numbered_rows n
JOIN group_summary g ON n.group_id = g.group_id
WHERE n.value IS NOT NULL
ORDER BY n.date;

逻辑说明 #

1. numbered_rows 阶段：
   • group_id：通过累加标记非NULL值的分组，所有位于两个非NULL值之间的NULL会被归到前一个非NULL值的分组中。
   • row_num：给所有行按日期生成连续行号，用于后续计算行数差。
2. group_summary 阶段：
   • 提取每个分组的起始行号（即该分组对应的非NULL值所在行的行号）。
3. 最终查询：
   • 计算当前行号与分组起始行号的差值，差值即为当前行上方紧邻的连续NULL行数（因为起始行就是非NULL行，中间的NULL行数等于行号差）。
   • 过滤掉value为NULL的行，得到目标结果。

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