如何根据记录的日期间隔补全表格中的缺失行？

问题描述 #

我有一份按记录日期存储的包裹行程历史表，但该表的记录日期存在间隔。当当前记录日期与下一条记录日期的间隔超过1天时，需要补全这些间隔。补全规则为：在间隔的每一天的00:00:00添加一条记录，状态沿用当前记录的状态。

以首个ID_SHP为例，原始数据如下：

原始数据 #

需要补充3条记录：1条在10/31和11/01之间，另外2条在11/01和11/03之间，补全后的数据如下：

补全后的数据 #

测试数据的SQL如下：

WITH table AS
 (SELECT '10000' as SHP_ID,
  '2022-10-31T15:26:02' as Event_date,
  'a_date_created' as Status
  UNION ALL SELECT '10000', DATETIME '2022-10-31T15:29:40', 'b_date_buff'
  UNION ALL SELECT '10000', DATETIME '2022-11-01T03:10:03', 'c_date_hand'
  UNION ALL SELECT '10000', DATETIME '2022-11-01T03:10:03', 'd_date_inv'
  UNION ALL SELECT '10000', DATETIME '2022-11-01T06:18:28', 'e_date_rea_print'
  UNION ALL SELECT '10000', DATETIME '2022-11-01T06:18:54', 'f_date_pri'
  UNION ALL SELECT '10000', DATETIME '2022-11-01T11:49:56', 'h_date_dro'
  UNION ALL SELECT '10000', DATETIME '2022-11-03T13:15:01', 'i_date_pic'
  UNION ALL SELECT '20000', DATETIME '2022-12-16T14:52:55', 'a_date_created'
  UNION ALL SELECT '20000', DATETIME '2022-12-19T11:10:23', 'g_date_rea_pickup'
  UNION ALL SELECT '20000', DATETIME '2022-12-20T10:58:25', 'i_date_pic'
  )

SELECT *
FROM table

我尝试用LAG函数获取下一条记录的日期并计算间隔，但不知道如何生成示例中所需的重复行。

解决方案 #

可以通过以下步骤实现：

1. 使用LEAD函数获取每条记录的下一条记录的日期（按SHP_ID分组，Event_date排序）。
2. 生成日期序列表，用来填补间隔的日期。
3. 将原始数据与生成的填补记录合并，最后排序得到结果。

完整SQL代码 #

WITH original_data AS (
  SELECT '10000' as SHP_ID,
         DATETIME '2022-10-31T15:26:02' as Event_date,
         'a_date_created' as Status
  UNION ALL SELECT '10000', DATETIME '2022-10-31T15:29:40', 'b_date_buff'
  UNION ALL SELECT '10000', DATETIME '2022-11-01T03:10:03', 'c_date_hand'
  UNION ALL SELECT '10000', DATETIME '2022-11-01T03:10:03', 'd_date_inv'
  UNION ALL SELECT '10000', DATETIME '2022-11-01T06:18:28', 'e_date_rea_print'
  UNION ALL SELECT '10000', DATETIME '2022-11-01T06:18:54', 'f_date_pri'
  UNION ALL SELECT '10000', DATETIME '2022-11-01T11:49:56', 'h_date_dro'
  UNION ALL SELECT '10000', DATETIME '2022-11-03T13:15:01', 'i_date_pic'
  UNION ALL SELECT '20000', DATETIME '2022-12-16T14:52:55', 'a_date_created'
  UNION ALL SELECT '20000', DATETIME '2022-12-19T11:10:23', 'g_date_rea_pickup'
  UNION ALL SELECT '20000', DATETIME '2022-12-20T10:58:25', 'i_date_pic'
),
-- 添加下一条记录的日期
data_with_next_date AS (
  SELECT 
    SHP_ID,
    Event_date,
    Status,
    LEAD(Event_date) OVER (PARTITION BY SHP_ID ORDER BY Event_date) AS next_event_date
  FROM original_data
),
-- 生成需要填补的日期记录
gap_records AS (
  SELECT 
    SHP_ID,
    -- 生成间隔期间每天的00:00:00
    DATETIME_ADD(DATE(Event_date), INTERVAL day_offset DAY) AS Event_date,
    Status
  FROM data_with_next_date
  -- 生成从1到间隔天数的序列
  CROSS JOIN UNNEST(GENERATE_ARRAY(1, DATE_DIFF(DATE(next_event_date), DATE(Event_date), DAY) - 1)) AS day_offset
  -- 仅处理间隔超过1天的情况
  WHERE DATE_DIFF(DATE(next_event_date), DATE(Event_date), DAY) > 1
)
-- 合并原始数据和填补记录，按ID和日期排序
SELECT SHP_ID, Event_date, Status FROM original_data
UNION ALL
SELECT SHP_ID, Event_date, Status FROM gap_records
ORDER BY SHP_ID, Event_date;

代码说明 #

• original_data：定义测试数据，替换为你的实际表即可。
• data_with_next_date：使用LEAD函数获取同个包裹下的下一条记录日期，用来判断间隔天数。
• gap_records：通过GENERATE_ARRAY生成间隔天数的序列，再结合DATETIME_ADD生成每天的00:00:00记录，状态沿用当前记录的状态。
• 最后将原始数据和填补记录合并，排序得到完整的行程表。

内容的提问来源于stack exchange，提问作者GF.Otaviano