SQL按报告日期统计在住酒店游客数的高效优化方案

现有两张业务表：

• 表apples_consumption：存储苹果消费数据，字段为report_date（报告日期）、apples_consumed（苹果消费量），样例数据为2022-01-01消费5个、2022-02-01消费7个、2022-03-01消费2个。
• 表hotel_visitors：存储酒店住客入住数据，字段为visitor_id（游客ID）、check_in_date（入住日期）、check_out_date（退房日期，值为NULL表示游客仍在住未退房），共4条样例游客记录。

按每个报告日期统计当日在住游客数量、对应苹果消费量，用于计算二者比值。
在住游客判定规则：游客check_in_date小于等于当前报告日期，且（check_out_date为NULL 或 check_out_date大于等于当前报告日期）。
期望输出包含report_date、visitors_count、apples_consumed三个字段，对应样例结果：

• 2022-01-01：在住游客2人（ID1、2）
• 2022-02-01：在住3人（ID1、2、3）
• 2022-03-01：在住3人（ID2、3、4）

当前通过相关子查询实现需求，代码如下（已修正原代码中表名拼写错误、缺少括号的语法问题）：

select
    ac.report_date,
    ac.apples_consumed,
    (
        select count(*) 
        from hotel_visitors hv 
        where 
            hv.check_in_date <= ac.report_date and 
            (hv.check_out_date is null or hv.check_out_date >= ac.report_date)
    ) as visitors_count
from
    apples_consumption ac
order by
    ac.report_date

该写法属于相关子查询，会针对外层apples_consumption表的每一行单独执行内层count(*)统计，大数据量下执行耗时较长、效率低下。

方案1：左连接+聚合（通用场景首选，兼容所有SQL引擎） #

通过一次跨表范围匹配+分组聚合完成统计，避免逐行触发子查询，配合索引性能可提升数倍至数十倍：

select
    ac.report_date,
    ac.apples_consumed,
    count(hv.visitor_id) as visitors_count
from apples_consumption ac
left join hotel_visitors hv
    on hv.check_in_date <= ac.report_date
    and (hv.check_out_date is null or hv.check_out_date >= ac.report_date)
group by ac.report_date, ac.apples_consumed
order by ac.report_date

优化提示：执行前给hotel_visitors表创建(check_in_date, check_out_date)联合索引，给apples_consumption表的report_date创建主键索引，可进一步加速匹配过程。

方案2：事件增量计数（千万级以上超大数据量场景最优） #

当两张表数据量达到百万、千万级时，范围join仍存在一定计算开销，可将入住、退房转化为人数增减事件，通过窗口函数累计计算每日在住人数，时间复杂度降到O(n+m)，数据量越大性能优势越明显（适用于支持CTE、窗口函数的SQL引擎，如MySQL 8.0+、PostgreSQL、Hive、Spark SQL等）：

with date_events as (
    -- 锚定所有需要统计的报告日期
    select report_date as stat_date, 0 as visitor_delta
    from apples_consumption
    union all
    -- 入住事件：入住当日在住人数+1
    select check_in_date as stat_date, 1 as visitor_delta
    from hotel_visitors
    union all
    -- 退房事件：退房次日在住人数-1（退房当日仍计入在住）
    select date_add(check_out_date, interval 1 day) as stat_date, -1 as visitor_delta
    from hotel_visitors
    where check_out_date is not null
),
daily_running_count as (
    select
        stat_date,
        sum(visitor_delta) over (order by stat_date) as total_visitors
    from date_events
)
select
    ac.report_date,
    ac.apples_consumed,
    dr.total_visitors as visitors_count
from apples_consumption ac
join daily_running_count dr
    on dr.stat_date = (select max(stat_date) from daily_running_count where stat_date <= ac.report_date)
order by ac.report_date

该方案完全避免了跨表笛卡尔积式的范围匹配，仅需对事件做一次排序和窗口累计计算，超大数据量下查询耗时可从小时级降到秒级。

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