如何基于门店营业时间计算date_diff？缺货时长计算需求

直接使用date_diff会计算两个时间点的绝对时长差，无法过滤非营业时段，所以会得到不符合预期的结果。要统计营业时段内的缺货时长，需要拆分缺货周期内的每一天，计算当日营业时段与缺货时段的交集时长，再累加总和。

通用SQL实现示例（以BigQuery为例） #

针对你提供的缺货时段（2022-11-01 18:00 至 2022-11-02 11:00）和营业时间（每日8:00-22:00），可通过以下查询计算有效缺货时长：

WITH dates AS (
  SELECT date_day
  FROM UNNEST(GENERATE_DATE_ARRAY(
    DATE('2022-11-01 18:00:00'),
    DATE('2022-11-02 11:00:00')
  )) AS date_day
),
daily_intervals AS (
  SELECT
    date_day,
    -- 当日营业时段的起止时间戳
    TIMESTAMP(DATE(date_day) || ' 08:00:00') AS daily_open,
    TIMESTAMP(DATE(date_day) || ' 22:00:00') AS daily_close,
    -- 当日缺货时段的实际覆盖范围（与当日时间取交集）
    GREATEST(TIMESTAMP('2022-11-01 18:00:00'), TIMESTAMP(DATE(date_day))) AS daily_oos_start,
    LEAST(TIMESTAMP('2022-11-02 11:00:00'), TIMESTAMP(DATE_ADD(date_day, INTERVAL 1 DAY))) AS daily_oos_end
  FROM dates
),
daily_duration AS (
  SELECT
    date_day,
    -- 计算当日营业时段与缺货时段的交集时长（单位：小时）
    TIMESTAMP_DIFF(
      LEAST(daily_close, daily_oos_end),
      GREATEST(daily_open, daily_oos_start),
      HOUR
    ) AS daily_hours
  FROM daily_intervals
  -- 过滤无交集的日期
  WHERE LEAST(daily_close, daily_oos_end) > GREATEST(daily_open, daily_oos_start)
)
SELECT SUM(daily_hours) AS total_oos_business_hours
FROM daily_duration

逻辑说明 #

1. 生成日期序列：通过dates CTE获取缺货时间段覆盖的所有自然日
2. 拆分每日时段：daily_intervals分别计算当日的营业时段范围，以及当日缺货时段的实际起止（避免跨天溢出）
3. 计算每日交集时长：daily_duration取营业时段与缺货时段的重叠部分，计算有效时长
4. 累加总时长：最后汇总所有日期的有效时长，得到营业时段内的总缺货时长

上述查询会返回7小时（2022-11-01的18:00-22:00共4小时，2022-11-02的8:00-11:00共3小时）。若你预期为8小时，可检查缺货结束时间是否应为2022-11-02 12:00，此时当日交集为4小时，总时长即为8小时。

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