BigQuery SQL实现酒店预订数据按月份拆分统计跨月入住房晚及营收

核心思路是将每笔订单按实际入住的晚数拆分为单行记录，每一行对应一个入住日，再按酒店、入住月份维度聚合即可自动处理跨月订单的拆分需求，完整SQL如下：

WITH
-- 基础数据预处理，计算单晚营收
base_data AS (
  SELECT
    hotel_long_nm,
    arrival_dt,
    depart_dt,
    DATE_DIFF(depart_dt, arrival_dt, day) AS total_nights,
    rez_rate_total,
    -- 计算单晚营收，SAFE_DIVIDE避免除零报错
    SAFE_DIVIDE(rez_rate_total, DATE_DIFF(depart_dt, arrival_dt, day)) AS daily_revenue
  FROM
    `DATABASE.analytics.bookings`
  -- 过滤入住晚数为0的异常订单
  WHERE DATE_DIFF(depart_dt, arrival_dt, day) > 0
),
-- 拆分订单为单晚记录
split_nights AS (
  SELECT
    hotel_long_nm,
    daily_revenue,
    -- 提取入住日对应统计月份，格式为年-月
    FORMAT_DATE('%Y-%m', stay_date) AS report_month
  FROM base_data,
  -- 生成所有有效入住日期（离店当天不计入入住晚数）
  UNNEST(GENERATE_DATE_ARRAY(arrival_dt, DATE_SUB(depart_dt, INTERVAL 1 DAY), INTERVAL 1 DAY)) AS stay_date
)
-- 按酒店、月份聚合得到最终统计结果
SELECT
  hotel_long_nm AS 酒店名称,
  report_month AS 统计月份,
  COUNT(1) AS 预订房间数,
  ROUND(SUM(daily_revenue), 2) AS 总营收
FROM split_nights
GROUP BY hotel_long_nm, report_month
ORDER BY hotel_long_nm, report_month

逻辑说明 #

• 跨月订单会自动按实际入住日期归属到对应月份，不需要额外做边界判断，符合你提到的拆分规则
• 最终输出的字段可以直接匹配你需要的表格样式，可根据实际需求调整统计月份的格式、营收的保留小数位数

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