如何编写SQL实现impressions值在连续null行与非null行间均分？

原始数据集 #

其中HOUR字段为无间隔的小时递增序列。

需求说明 #

当impressions字段值为null时，将后续第一个非null的impressions值均分到该段连续null行及此非null行本身：

• 4个连续null行后是值为40的行，共5行，每行分配8；
• 1个null行后是值为10的行，共2行，每行分配5。

已编写的部分SQL语句 #

select *,
      case when impressions is null then row_number() over(partition by media_id,ACCOUNT_ID ORDER BY HOUR) else 0 end as rn1,
from table_name order by 1 ;

预期输出 #

解决方案SQL #

完整代码 #

WITH step1 AS (
    SELECT 
        *,
        -- 标记每个待分配区间的分组ID
        SUM(CASE WHEN impressions IS NOT NULL THEN 1 ELSE 0 END) OVER (
            PARTITION BY media_id, account_id 
            ORDER BY hour 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS group_id
    FROM table_name
),
step2 AS (
    SELECT 
        *,
        -- 获取当前区间要分配的总impressions值
        LAST_VALUE(impressions IGNORE NULLS) OVER (
            PARTITION BY media_id, account_id, group_id 
            ORDER BY hour 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS total_impressions,
        -- 统计当前区间的总行数
        COUNT(*) OVER (
            PARTITION BY media_id, account_id, group_id
        ) AS group_row_count
    FROM step1
)
SELECT 
    hour,
    account_id,
    media_id,
    impressions,
    -- 计算均分后的值
    total_impressions / group_row_count AS distributed_impressions
FROM step2
ORDER BY hour;

逻辑解释 #

1. step1：分组标记
   通过累加窗口函数，每遇到非null的impressions就累加1，把连续的null行和后续第一个非null行归为同一个group_id，实现区间划分。

2. step2：计算区间参数

   • LAST_VALUE(impressions IGNORE NULLS)：提取当前区间内最后一个非null的impressions值，也就是要均分的总数值。
   • COUNT(*) OVER (...)：统计当前区间的总行数，作为均分的分母。

3. 最终计算
   用区间总数值除以总行数，得到每行的分配值distributed_impressions。

这个方案无需依赖你之前编写的rn1字段，逻辑简洁且能覆盖所有符合需求的场景。

内容的提问来源于stack exchange，提问作者Teja Goud Kandula