基于delivery_data表按小时统计配送半径持续时长的SQL需求

现有数据表结构及数据 #

CREATE TABLE delivery_data (
  Delivery_Area_ID INT,
  current_Delivery_Radius_Meters INT,
  Event_Started_Timestamp TIMESTAMP,
  event_started_date DATE,
  event_started_hour INT,
  event_started_mins INT,
  event_ended_time TIMESTAMP,
  prev_delivery_radius INT
);

INSERT INTO delivery_data (
  Delivery_Area_ID,
  current_Delivery_Radius_Meters,
  Event_Started_Timestamp,
  event_started_date,
  event_started_hour,
  event_started_mins,
  event_ended_time,
  prev_delivery_radius
)
VALUES
  (1, 3500, '2022-01-15 19:46:37.995951 UTC', '2022-01-15', 19, 46, '2022-01-15 20:05:29.049375 UTC', NULL),
  (1, 6500, '2022-01-15 20:05:29.049375 UTC', '2022-01-15', 20, 5, '2022-01-16 12:31:22.778229 UTC', 3500),
  (1, 3500, '2022-01-16 12:31:22.778229 UTC', '2022-01-16', 12, 31, '2022-01-16 12:50:12.562042 UTC', 6500),
  (1, 6500, '2022-01-16 12:50:12.562042 UTC', '2022-01-16', 12, 50, '2022-01-18 20:46:41.937279 UTC', 3500),
  (1, 3500, '2022-01-18 20:46:41.937279 UTC', '2022-01-18', 20, 46, '2022-01-18 20:58:55.794286 UTC', 6500);

需求说明 #

统计每日0-23点每个小时内各配送半径的持续时长，例如：

• 2022-01-15 19:46生效的3500米半径，在19点时段持续14分钟；
• 20:05变更为6500米后，3500米半径在20点时段持续5分钟，6500米半径在20点时段持续55分钟。

要求输出为每日24条记录（对应0-23点），但现有SQL查询结果不准确，需优化。

现有问题SQL #

with  dim_date AS(
SELECT 
 *
FROM 
 `dim_date` 
WHERE 
 DATE BETWEEN '2022-01-01' AND '2022-12-31'
)
, delivery_radius_log_data AS (
 SELECT 
   Delivery_Area_ID,
   Delivery_Radius_Meters as current_Delivery_Radius_Meters,
   Event_Started_Timestamp,
   --提取事件开始日期和小时，用于查看某一小时内事件发生次数
   EXTRACT(DATE FROM Event_Started_Timestamp) AS event_started_date,
   EXTRACT(HOUR FROM Event_Started_Timestamp) AS event_started_hour,
   EXTRACT(MINUTE FROM Event_Started_Timestamp) AS event_started_mins,
  -- 获取下一个事件开始时间，作为当前事件的结束时间
   LEAD(Event_Started_Timestamp) OVER (
     PARTITION BY Delivery_Area_ID ORDER BY Event_Started_Timestamp ASC
   ) AS event_ended_time,
   -- 获取上一个配送半径，用于验证执行正确性
   LAG(Delivery_Radius_Meters) OVER (
     PARTITION BY Delivery_Area_ID ORDER BY Event_Started_Timestamp ASC
   ) AS prev_delivery_radius
 FROM 
   `radius_data` 
 WHERE 
   DATE(Event_Started_Timestamp) BETWEEN '2022-01-01' AND '2022-12-31'
   AND delivery_area_id ='1'
)
,temp_table as(
select
   dr.delivery_area_id AS delivery_area_id,
   dd.datetime,
   dd.date,
   dd.hour_of_day,
   LEAD(dd.hour_of_day) OVER (PARTITION BY dd.date ORDER BY dd.datetime ASC) AS next_hour_of_day,
   --dr.prev_delivery_radius,
   dr.current_delivery_radius_meters,
   dr.event_started_timestamp,
   event_started_date,
   event_started_hour,
   event_started_mins,
   dr.event_ended_time,
   EXTRACT(DATE FROM dr.event_ended_time) AS event_ended_date,
   EXTRACT(HOUR FROM dr.event_ended_time) AS event_ended_hour,
   EXTRACT(MINUTE FROM dr.event_ended_time) AS event_ended_mins,
   --计算事件时间差
   ROUND(TIMESTAMP_DIFF(dr.event_ended_time, dr.event_started_timestamp, second)/3600,2) AS event_duration_hours,
   CASE 
     WHEN 
       TIMESTAMP_DIFF(dr.event_ended_time, dr.event_started_timestamp, second)/3600 >= 24 THEN 'Default'
     ELSE 'Not Default'
   END AS is_default
FROM
  dim_date dd 
  LEFT JOIN delivery_radius_log_data dr 
   ON DATE(dd.date) = DATE(dr.event_started_date) 
  AND TRIM(CAST(dd.hour_of_day AS STRING))  = TRIM(CAST(dr.event_started_hour AS STRING)) 
ORDER BY
 2
)
SELECT
 *,
 CASE
   WHEN event_started_date = event_ended_date and event_started_hour = event_ended_hour
     THEN event_ended_mins - event_started_mins
   WHEN Event_started_date = event_ended_date and event_started_hour < event_ended_hour 
     THEN  60 - event_started_mins --TIMESTAMP_DIFF(event_ended_time, event_started_timestamp, MINUTE)
   WHEN Event_started_date = event_ended_date and event_started_hour < event_ended_hour 
     THEN  60 - event_started_mins
   WHEN event_started_date < event_ended_date
     THEN 60 - event_started_mins
   ELSE 60
 END AS radius_life_of_that_hour
FROM
 temp_table

优化后的SQL #

-- 生成日期小时维度表，覆盖目标时间段内的所有小时
WITH date_hour_dim AS (
  SELECT
    date_trunc('hour', dt) AS hour_start,
    DATE(dt) AS event_date,
    EXTRACT(HOUR FROM dt) AS hour_of_day
  FROM
    UNNEST(GENERATE_TIMESTAMP_ARRAY('2022-01-01 00:00:00 UTC', '2022-12-31 23:00:00 UTC', INTERVAL 1 HOUR)) AS dt
),
-- 整理配送半径事件数据，补全开始/结束时间
radius_events AS (
  SELECT
    Delivery_Area_ID,
    current_Delivery_Radius_Meters,
    Event_Started_Timestamp AS event_start,
    COALESCE(event_ended_time, '2022-12-31 23:59:59 UTC') AS event_end -- 处理最后一个事件无结束时间的情况
  FROM
    delivery_data
  WHERE
    Delivery_Area_ID = 1
    AND Event_Started_Timestamp BETWEEN '2022-01-01 00:00:00 UTC' AND '2022-12-31 23:59:59 UTC'
),
-- 关联维度表与事件数据，拆分跨小时的事件
event_hour_overlap AS (
  SELECT
    re.Delivery_Area_ID,
    dhd.event_date,
    dhd.hour_of_day,
    re.current_Delivery_Radius_Meters,
    -- 计算当前小时内该半径的实际持续分钟数
    GREATEST(0,
      TIMESTAMP_DIFF(
        LEAST(re.event_end, dhd.hour_start + INTERVAL 1 HOUR),
        GREATEST(re.event_start, dhd.hour_start),
        MINUTE
      )
    ) AS duration_mins
  FROM
    date_hour_dim dhd
  LEFT JOIN
    radius_events re
  ON
    re.event_start < dhd.hour_start + INTERVAL 1 HOUR
    AND re.event_end > dhd.hour_start
),
-- 聚合同一小时内的半径时长
final_stats AS (
  SELECT
    Delivery_Area_ID,
    event_date,
    hour_of_day,
    current_Delivery_Radius_Meters,
    SUM(duration_mins) AS total_duration_mins
  FROM
    event_hour_overlap
  GROUP BY
    Delivery_Area_ID, event_date, hour_of_day, current_Delivery_Radius_Meters
)
-- 补全日历所有小时，确保每日24条记录
SELECT
  da.Delivery_Area_ID,
  dhd.event_date,
  dhd.hour_of_day,
  COALESCE(fs.current_Delivery_Radius_Meters, '无生效半径') AS current_Delivery_Radius_Meters,
  COALESCE(fs.total_duration_mins, 0) AS total_duration_mins
FROM
  (SELECT DISTINCT Delivery_Area_ID FROM radius_events) da
CROSS JOIN
  date_hour_dim dhd
LEFT JOIN
  final_stats fs
ON
  da.Delivery_Area_ID = fs.Delivery_Area_ID
  AND dhd.event_date = fs.event_date
  AND dhd.hour_of_day = fs.hour_of_day
ORDER BY
  dhd.event_date, dhd.hour_of_day;

优化思路说明 #

1. 生成完整小时维度：用GENERATE_TIMESTAMP_ARRAY直接生成目标时间段内的所有小时节点，确保每日必出24条记录。
2. 补全事件时间边界：对最后一条无结束时间的事件，设置时间段截止时间为2022-12-31 23:59:59，避免数据遗漏。
3. 精准计算重叠时长：通过GREATEST和LEAST函数，计算每个配送半径事件与当前小时的重叠分钟数，完美拆分跨小时的事件。
4. 强制补全所有小时：通过交叉连接维度表与配送区域，确保每个小时都有记录，无生效半径的小时显示0分钟或标注“无生效半径”。

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