如何使用SQL GROUP BY查询计算分组统计结果的百分比占比

调整方案 #

你之前的查询没有先按酒店维度聚合周末入住总晚数，直接基于原始预订记录计算占比，才会得到单条记录的占比结果。正确写法如下：

写法1（支持窗口函数的数据库，如MySQL 8.0+、PostgreSQL、SQL Server等）

SELECT 
    hotel,
    SUM(stays_in_weekend_nights) AS total_weekend_nights,
    ROUND(SUM(stays_in_weekend_nights) * 100.0 / SUM(SUM(stays_in_weekend_nights)) OVER (), 2) AS weekend_night_percentage
FROM hotel_bookings
GROUP BY hotel;

这里用100.0是为了避免整数除法导致结果精度丢失，ROUND函数用来保留2位小数，可根据需求调整

写法2（兼容不支持窗口函数的低版本数据库）

SELECT 
    a.hotel,
    a.total_weekend_nights,
    ROUND(a.total_weekend_nights * 100.0 / b.total_all_weekend_nights, 2) AS weekend_night_percentage
FROM 
    (SELECT hotel, SUM(stays_in_weekend_nights) AS total_weekend_nights FROM hotel_bookings GROUP BY hotel) a,
    (SELECT SUM(stays_in_weekend_nights) AS total_all_weekend_nights FROM hotel_bookings) b;

输出效果参考

按你提供的测试数据计算后，结果如下：

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