PostgreSQL嵌套子查询优化：提升酒店年度预订统计运行效率

你的判断没错——原查询慢的核心原因是分组时每一行都会重复执行一次全表COUNT查询，相当于扫了N次全表（N是分组后的行数），数据量越大，耗时越夸张。下面是两种高效的优化方案，结果和原查询完全一致，但性能提升明显：

方案1：预计算总预订量再关联 #

先单独计算一次全表总预订量，再和分组结果做交叉关联，这样全表COUNT只执行一次：

WITH total_bookings AS (
    -- 只执行一次全表计数
    SELECT COUNT(*) AS total_booking FROM "Full_Data"
),
hotel_year_stats AS (
    -- 按酒店+年份分组统计
    SELECT 
        hotel, 
        arrival_date_year,
        COUNT(*) AS booking_by_hotel
    FROM "Full_Data"
    GROUP BY hotel, arrival_date_year
)
-- 关联两个结果集计算占比
SELECT 
    h.hotel,
    h.arrival_date_year,
    h.booking_by_hotel,
    t.total_booking,
    ROUND(h.booking_by_hotel * 100.0 / t.total_booking, 2) AS percent
FROM hotel_year_stats h
CROSS JOIN total_bookings t;

方案2：使用窗口函数（更简洁） #

利用PostgreSQL的窗口函数OVER ()直接计算全局总数，无需额外CTE，数据库会自动优化为一次全表计数：

SELECT 
    hotel,
    arrival_date_year,
    COUNT(*) AS booking_by_hotel,
    -- 窗口函数计算全局总预订量
    COUNT(*) OVER () AS total_booking,
    ROUND(COUNT(*) * 100.0 / COUNT(*) OVER (), 2) AS percent
FROM "Full_Data"
GROUP BY hotel, arrival_date_year;

额外优化建议 #

如果"Full_Data"表数据量很大，建议给分组字段建立联合索引，进一步加快分组查询速度：

CREATE INDEX idx_full_data_hotel_year ON "Full_Data" (hotel, arrival_date_year);

执行结果验证 #

优化后的查询会输出和原查询完全一致的结果：

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