SQL聚合DVD租赁数据 求解美国各州G级影片人均租赁数问题

需求为基于DVD租赁示例数据库完成统计：针对美国范围内的每个州，按降序返回该州每位客户的G级影片平均租赁量，每个州仅返回一条统计结果。
涉及的数据库表核心字段如下：

• Customer表：customer_id、store_id、first_name、last_name、email、address_id、activebool、create_date、last_update、active
• Address表：address_id、address、address2、district（对应州字段）、city_id、postal_code、phone、last_update
• Rental表：rental_id、rental_date、inventory_id、customer_id、return_date、staff_id、last_update
• Inventory表：inventory_id、film_id、store_id、last_update
• Film表：film_id、title、description、release_year、language_id、original_language_id、rental_duration、rental_rate、length、replacement_cost、rating、last_update、special_features、fulltext
• City表：city_id、city、country_id、last_update

原有SQL存在4个核心逻辑问题，导致统计结果失准：

1. CTE逻辑无效：CTE中直接统计全租赁表的rental_id总数，和后续客户、州维度无任何关联，属于冗余无效代码
2. 隐式笛卡尔积：FROM cte, customer 写法为隐式交叉连接，会将CTE返回的单值结果与customer表所有行做笛卡尔积，直接污染统计基数
3. 聚合层级错误：直接在最外层按district分组求平均，未先统计单个客户维度的G级片租赁量，多表一对多关联会导致行膨胀，计数结果偏大
4. 连接逻辑错误：过滤条件全部放在WHERE子句中，会将LEFT JOIN强制转换为INNER JOIN，漏掉无G级片租赁记录的客户，导致平均值计算失真；同时原写法中未正确处理表关联顺序，容易出现关联字段为空的异常。

统计逻辑分两层聚合：第一层先统计每位美国客户的已归还G级影片租赁总量，第二层按州分组计算州内客户的平均租赁量，最后按平均值降序排列。

WITH customer_g_rental AS (
    -- 第一层：逐客户统计G级已归还影片租赁量
    SELECT
        c.customer_id,
        a.district,
        COUNT(DISTINCT r.rental_id) AS g_rental_count
    FROM customer c
    INNER JOIN address a
        ON c.address_id = a.address_id
    INNER JOIN city ci
        ON a.city_id = ci.city_id
        AND ci.country_id = 103 -- DVD租赁库中103为美国country_id
    LEFT JOIN rental r
        ON c.customer_id = r.customer_id
        AND r.return_date IS NOT NULL -- 仅统计已归还租赁
    LEFT JOIN inventory i
        ON r.inventory_id = i.inventory_id
    LEFT JOIN film f
        ON i.film_id = f.film_id
        AND f.rating = 'G' -- 仅统计G级影片
    GROUP BY c.customer_id, a.district
)
-- 第二层：按州聚合求平均值，降序返回
SELECT
    district,
    ROUND(AVG(g_rental_count), 2) AS avg_g_rental_per_customer
FROM customer_g_rental
GROUP BY district
ORDER BY avg_g_rental_per_customer DESC;

统计口径说明：以上写法会将州内没有任何G级片租赁记录的客户也计入平均分母（租赁量记为0），符合全客户维度的平均统计要求。如果仅需要统计至少租赁过1次G级片的客户平均，将所有LEFT JOIN替换为INNER JOIN即可。

• 分组场景下无需额外加DISTINCT：GROUP BY本身会对分组字段去重，额外加DISTINCT只会增加查询开销，无实际作用
• 禁止使用逗号隐式连接：所有表关联显式声明JOIN类型和关联条件，避免意外生成笛卡尔积
• 分层聚合避免计数错误：涉及多维度聚合时，从最细粒度（此处为客户维度）开始逐层向上聚合，避免多表一对多关联带来的行数重复问题
• 左连接的过滤条件放置在ON子句中：针对右表的过滤条件如果放在WHERE子句，会将左连接转为内连接，丢失左表中不满足右表条件的记录，导致统计偏差。

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