如何使用CTE计算各酒店错误占比（关联酒店名称）

需求说明 #

从errors（错误表）和hotel（酒店表）中计算每个酒店的以下信息：

• 酒店名称
• 错误占比（该酒店错误数占总错误数的百分比）
• 该酒店的错误总数

数据表结构 #

错误表（errors） #

hotel_id    error
1           x
2           y
3           z
1           a
2           b

酒店表（hotel） #

hotel_id      name
1              marriott
2              Westin
3              Luxury

修正后的SQL语句 #

WITH hotel_errors AS (
    -- 统计每个酒店的错误总数
    SELECT 
        h.hotel_id,
        h.name AS hotel_name,
        COUNT(e.error) AS total_errors_for_the_hotel
    FROM hotel h
    LEFT JOIN errors e ON h.hotel_id = e.hotel_id
    GROUP BY h.hotel_id, h.name
),
total_errors AS (
    -- 统计全局错误总数
    SELECT COUNT(error) AS total_errors
    FROM errors
)
SELECT 
    he.hotel_name,
    -- 计算百分比并格式化，处理除零异常
    ROUND((he.total_errors_for_the_hotel * 100.0 / te.total_errors), 0) AS percent_of_errors,
    he.total_errors_for_the_hotel
FROM hotel_errors he
CROSS JOIN total_errors te
ORDER BY he.hotel_name;

原查询的问题分析 #

1. total CTE仅查询errors全表数据，未计算全局错误总数，导致后续count(t.error)统计的是单酒店错误数而非全局总数，百分比逻辑完全错误。
2. 主查询关联时未做正确分组，会产生重复行，无法得到每个酒店的唯一统计结果。
3. 未将计算结果转为百分比格式（缺少乘以100的操作），也未处理总错误数为0时的除零异常。

预期执行结果 #

hotel_name     percent_of_errors  total_errors_for_the_hotel
marriott             40                  2
Westin               40                  2
Luxury               20                  1

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