求Oracle查询：计算排除闭店时段的有效配送耗时（分钟）

需求说明 #

我们需要计算配送的有效耗时（单位：分钟），需排除店铺的闭店时段。已知信息包括：

• 门店的每日开闭店时间（假设存储在store_hours表，包含store_id、open_time（时分格式，如'08:00:00'）、close_time（时分格式，如'22:00:00'））
• 配送的起止时间（假设存储在deliveries表，包含delivery_id、store_id、start_time（日期时间类型）、end_time（日期时间类型））

当前已能通过起止时间差得到calculatable elapsed time in minutes，但需要获取排除闭店时段后的required elapsed time in minutes（即配送过程中处于门店营业时段的总时长）。

核心思路 #

1. 拆分配送时间跨度为涉及的每一天，计算每天内配送时段与门店营业时段的重叠时长
2. 累加所有天数的重叠时长，得到最终有效耗时

Oracle查询语句 #

假设表结构如下：

• store_hours：store_id(VARCHAR2), open_time(VARCHAR2), close_time(VARCHAR2)
• deliveries：delivery_id(NUMBER), store_id(VARCHAR2), start_time(TIMESTAMP), end_time(TIMESTAMP)

WITH delivery_dates AS (
    -- 生成配送起止时间覆盖的所有日期
    SELECT 
        d.delivery_id,
        d.store_id,
        d.start_time,
        d.end_time,
        s.open_time,
        s.close_time,
        TRUNC(d.start_time) + LEVEL - 1 AS delivery_date
    FROM deliveries d
    JOIN store_hours s ON d.store_id = s.store_id
    CONNECT BY LEVEL <= TRUNC(d.end_time) - TRUNC(d.start_time) + 1
        AND PRIOR d.delivery_id = d.delivery_id
        AND PRIOR SYS_GUID() IS NOT NULL
),
daily_overlaps AS (
    -- 计算每天配送时段与营业时段的重叠时长（分钟）
    SELECT 
        delivery_id,
        store_id,
        TO_TIMESTAMP(delivery_date || ' ' || open_time, 'YYYY-MM-DD HH24:MI:SS') AS daily_open,
        TO_TIMESTAMP(delivery_date || ' ' || close_time, 'YYYY-MM-DD HH24:MI:SS') AS daily_close,
        GREATEST(start_time, TO_TIMESTAMP(delivery_date || ' ' || open_time, 'YYYY-MM-DD HH24:MI:SS')) AS daily_delivery_start,
        LEAST(end_time, TO_TIMESTAMP(delivery_date || ' ' || close_time, 'YYYY-MM-DD HH24:MI:SS')) AS daily_delivery_end
    FROM delivery_dates
)
SELECT 
    delivery_id,
    store_id,
    -- 累加每天有效重叠时长，无重叠时计0
    SUM(CASE 
        WHEN daily_delivery_start <= daily_delivery_end 
        THEN ROUND((CAST(daily_delivery_end AS DATE) - CAST(daily_delivery_start AS DATE)) * 1440, 2)
        ELSE 0 
    END) AS required_elapsed_time_in_minutes,
    -- 原始总耗时（用于对比）
    ROUND((CAST(end_time AS DATE) - CAST(start_time AS DATE)) * 1440, 2) AS calculatable_elapsed_time_in_minutes
FROM daily_overlaps
GROUP BY delivery_id, store_id, start_time, end_time
ORDER BY delivery_id;

关键说明 #

• delivery_dates CTE：通过递归生成配送时间段覆盖的所有日期，确保每一天的营业时段都被纳入计算
• daily_overlaps CTE：将每天的营业时段转换为时间戳，同时确定配送在当天的实际起止范围（取配送起止与营业时段的交集）
• 最终聚合时，通过CASE语句过滤掉无重叠的情况，累加有效时长

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