如何计算重叠日期区间内酒店各日的总营业时长

需求说明 #

需要计算每家酒店d1-d7各日的总营业时长（小时），核心规则：

• 每条记录对应一个日期适用区间（from至till），以及该区间内各日的营业时段
• 当多条记录的日期区间重叠时，对应日期的营业时长需累加所有重叠记录的时段
• 日期区间不重叠的记录，不计入彼此的累计时长

比如示例中第6条记录（2020-12-01至2020-12-03），需累加第1-3条（日期区间与它重叠）的d2时长，而第4-5条的区间和它不重叠，因此不计入。

数据示例 #

hotel   d1_from     d1_to       d2_from      d2_to         from        till
    1   00:00:00    00:00:00    09:00:00    10:00:00    2020-05-15  2020-12-31
    1   00:00:00    00:00:00    13:00:00    14:00:00    2020-08-27  2020-12-31
    1   00:00:00    00:00:00    15:00:00    16:00:00    2020-09-11  2020-12-31
    1   09:30:00    10:15:00    18:00:00    19:00:00    2020-11-24  2020-11-25
    1   00:00:00    00:00:00    20:00:00    21:00:00    2020-11-25  2020-11-25
    1   09:30:00    10:15:00    22:00:00    23:00:00    2020-12-01  2020-12-03

现有SQL代码（逻辑缺陷） #

select 
  d1_from,
  d1_to,
  d2_from,
  d2_to,
  timediff('minute',d1_from,d1_to) /60 as d1_total,
  timediff('minute',d2_from,d2_to)/60  as d2_total,
  case 
    when `from` >=  lead(`from`)over(partition by hotel ORDER by `from`) 
    then lead(`from`)over(partition by hotel ORDER by `from`) 
  else null end as date_adjustment,

  sum(d2_total) over (partition by hotel order by `from`) as cumulative_d2,
  sum(d1_total) over (partition by hotel order by `from`) as cumulative_d1


from `table`

预期结果（d2_hours列） #

1 --仅第1行：9:00 - 10:00
2 --前2行：9:00-10:00 和13:00 -14:00
3 --前3行：9:00-10:00、13:00 -14:00、15:00 -16:00
4 --前4行累加（1+1+1+1）
4 --前3行+当前行（1+1+1+1），第4行区间与当前行部分重叠，计入
4 --前3行+当前行（1+1+1+1），第4-5行区间与当前行不重叠，不计入

解决方案 #

核心逻辑：对每条记录，找出同酒店下所有日期区间与当前记录重叠的记录，累加对应日的营业时长。

通用SQL实现 #

WITH base_data AS (
    SELECT 
        hotel,
        d1_from,
        d1_to,
        d2_from,
        d2_to,
        `from` AS record_from,
        `till` AS record_till,
        TIMEDIFF('minute', d1_from, d1_to)/60 AS d1_total,
        TIMEDIFF('minute', d2_from, d2_to)/60 AS d2_total
    FROM `table`
)
SELECT 
    bd.*,
    -- 计算d1累计时长：累加同酒店且区间重叠的所有d1_total
    SUM(CASE 
        WHEN bd.record_from <= bd2.record_till AND bd.record_till >= bd2.record_from 
        THEN bd2.d1_total 
        ELSE 0 
    END) AS d1_cumulative,
    -- 计算d2累计时长，逻辑同d1
    SUM(CASE 
        WHEN bd.record_from <= bd2.record_till AND bd.record_till >= bd2.record_from 
        THEN bd2.d2_total 
        ELSE 0 
    END) AS d2_cumulative
FROM base_data bd
JOIN base_data bd2 ON bd.hotel = bd2.hotel
GROUP BY bd.hotel, bd.record_from, bd.record_till, bd.d1_from, bd.d1_to, bd.d2_from, bd.d2_to, bd.d1_total, bd.d2_total
ORDER BY bd.record_from;

关键细节说明 #

1. 区间重叠判断条件：A.record_from <= B.record_till AND A.record_till >= B.record_from
2. 多日期列扩展：对于d3-d7，只需复制d1/d2的SUM(CASE...)逻辑，替换对应的dX_total字段即可
3. 方言优化：如果使用PostgreSQL等支持FILTER子句的SQL方言，可简化累加逻辑：

SUM(bd2.d2_total) FILTER (
    WHERE bd.record_from <= bd2.record_till AND bd.record_till >= bd2.record_from
) AS d2_cumulative

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