如何为分组时间序列数据的缺失间隙填充0值？

思路概述 #

要让每个thing覆盖指定时间段内的所有小时节点，缺失数据的v字段填充为0，核心是先构建完整的时间-维度组合，再关联原数据补全缺失值：

• 生成目标时间段内的所有小时时间点
• 提取所有唯一的thing值
• 交叉连接两者得到无间隙的基础数据集
• 左连接原数据，用NVL将缺失的v替换为0

具体SQL代码 #

WITH time_range AS (
    -- 生成目标时间段内的每小时时间点（2024-12-19 08:00 至 2024-12-20 23:00）
    SELECT 
        timestamp '2024-12-19 08:00:00' + interval (level - 1) hour AS t
    FROM dual
    CONNECT BY level <= (timestamp '2024-12-20 23:00:00' - timestamp '2024-12-19 08:00:00') / interval '1' hour + 1
),
unique_things AS (
    -- 提取所有唯一的thing值
    SELECT DISTINCT thing
    FROM (
        select 'a' as thing, timestamp '2024-12-19 11:00:00' as t, DBMS_RANDOM.value() as v from dual union all
        select 'a' as thing, timestamp '2024-12-19 12:00:00' as t, DBMS_RANDOM.value() as v from dual union all
        select 'a' as thing, timestamp '2024-12-19 13:00:00' as t, DBMS_RANDOM.value() as v from dual union all
        select 'a' as thing, timestamp '2024-12-19 14:00:00' as t, DBMS_RANDOM.value() as v from dual union all
        select 'a' as thing, timestamp '2024-12-19 15:00:00' as t, DBMS_RANDOM.value() as v from dual union all
        select 'a' as thing, timestamp '2024-12-19 16:00:00' as t, DBMS_RANDOM.value() as v from dual union all
        select 'a' as thing, timestamp '2024-12-19 17:00:00' as t, DBMS_RANDOM.value() as v from dual union all
        select 'a' as thing, timestamp '2024-12-19 18:00:00' as t, DBMS_RANDOM.value() as v from dual union all
        select 'a' as thing, timestamp '2024-12-19 19:00:00' as t, DBMS_RANDOM.value() as v from dual union all
        select 'a' as thing, timestamp '2024-12-19 20:00:00' as t, DBMS_RANDOM.value() as v from dual union all
        select 'a' as thing, timestamp '2024-12-19 21:00:00' as t, DBMS_RANDOM.value() as v from dual union all
        select 'a' as thing, timestamp '2024-12-19 22:00:00' as t, DBMS_RANDOM.value() as v from dual union all
        select 'a' as thing, timestamp '2024-12-19 23:00:00' as t, DBMS_RANDOM.value() as v from dual union all
        select 'b' as thing, timestamp '2024-12-19 15:00:00' as t, DBMS_RANDOM.value() as v from dual union all
        select 'b' as thing, timestamp '2024-12-19 16:00:00' as t, DBMS_RANDOM.value() as v from dual union all
        select 'b' as thing, timestamp '2024-12-19 17:00:00' as t, DBMS_RANDOM.value() as v from dual union all
        select 'b' as thing, timestamp '2024-12-19 18:00:00' as t, DBMS_RANDOM.value() as v from dual union all
        select 'b' as thing, timestamp '2024-12-19 19:00:00' as t, DBMS_RANDOM.value() as v from dual union all
        select 'b' as thing, timestamp '2024-12-19 20:00:00' as t, DBMS_RANDOM.value() as v from dual union all
        select 'c' as thing, timestamp '2024-12-19 12:00:00' as t, DBMS_RANDOM.value() as v from dual
    )
),
full_dataset AS (
    -- 交叉连接得到每个thing对应所有时间点的完整组合
    SELECT ut.thing, tr.t
    FROM unique_things ut
    CROSS JOIN time_range tr
)
-- 左连接原数据，缺失值用0填充
SELECT 
    fd.thing,
    fd.t,
    NVL(original.v, 0) AS v
FROM full_dataset fd
LEFT JOIN (
    -- 原数据集
    select 'a' as thing, timestamp '2024-12-19 11:00:00' as t, DBMS_RANDOM.value() as v from dual union all
    select 'a' as thing, timestamp '2024-12-19 12:00:00' as t, DBMS_RANDOM.value() as v from dual union all
    select 'a' as thing, timestamp '2024-12-19 13:00:00' as t, DBMS_RANDOM.value() as v from dual union all
    select 'a' as thing, timestamp '2024-12-19 14:00:00' as t, DBMS_RANDOM.value() as v from dual union all
    select 'a' as thing, timestamp '2024-12-19 15:00:00' as t, DBMS_RANDOM.value() as v from dual union all
    select 'a' as thing, timestamp '2024-12-19 16:00:00' as t, DBMS_RANDOM.value() as v from dual union all
    select 'a' as thing, timestamp '2024-12-19 17:00:00' as t, DBMS_RANDOM.value() as v from dual union all
    select 'a' as thing, timestamp '2024-12-19 18:00:00' as t, DBMS_RANDOM.value() as v from dual union all
    select 'a' as thing, timestamp '2024-12-19 19:00:00' as t, DBMS_RANDOM.value() as v from dual union all
    select 'a' as thing, timestamp '2024-12-19 20:00:00' as t, DBMS_RANDOM.value() as v from dual union all
    select 'a' as thing, timestamp '2024-12-19 21:00:00' as t, DBMS_RANDOM.value() as v from dual union all
    select 'a' as thing, timestamp '2024-12-19 22:00:00' as t, DBMS_RANDOM.value() as v from dual union all
    select 'a' as thing, timestamp '2024-12-19 23:00:00' as t, DBMS_RANDOM.value() as v from dual union all
    select 'b' as thing, timestamp '2024-12-19 15:00:00' as t, DBMS_RANDOM.value() as v from dual union all
    select 'b' as thing, timestamp '2024-12-19 16:00:00' as t, DBMS_RANDOM.value() as v from dual union all
    select 'b' as thing, timestamp '2024-12-19 17:00:00' as t, DBMS_RANDOM.value() as v from dual union all
    select 'b' as thing, timestamp '2024-12-19 18:00:00' as t, DBMS_RANDOM.value() as v from dual union all
    select 'b' as thing, timestamp '2024-12-19 19:00:00' as t, DBMS_RANDOM.value() as v from dual union all
    select 'b' as thing, timestamp '2024-12-19 20:00:00' as t, DBMS_RANDOM.value() as v from dual union all
    select 'c' as thing, timestamp '2024-12-19 12:00:00' as t, DBMS_RANDOM.value() as v from dual
) original ON fd.thing = original.thing AND fd.t = original.t
ORDER BY fd.thing, fd.t;

关键说明 #

• time_range CTE：用Oracle的CONNECT BY生成连续小时时间点，通过计算时间段内的总小时数确定生成的记录数，避免硬编码时间点
• unique_things CTE：自动提取所有需要处理的thing维度值，后续新增thing时无需修改代码
• full_dataset CTE：交叉连接维度和时间，构建出每个thing对应所有时间点的完整框架
• NVL(original.v, 0)：左连接后，原数据中不存在的记录会返回NULL，用NVL将其替换为0

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