SQL查询编写请求：统计各城市客户数并筛选州内超平均的城市

实现思路 #

要完成需求需要分两步推进：

1. 先统计出每个城市的客户数量
2. 计算每个州内所有城市客户数的平均值，再筛选出客户数高于所在州平均值的城市

完整SQL语句 #

SELECT 
    city_stats.state,
    city_stats.city,
    city_stats.customer_count
FROM (
    -- 统计每个城市的客户数量
    SELECT 
        "Customer State" AS state,
        "Customer City" AS city,
        COUNT("Customer id") AS customer_count
    FROM 
        customers
    GROUP BY 
        "Customer State", "Customer City"
) AS city_stats
JOIN (
    -- 统计每个州内城市客户数的平均值
    SELECT 
        "Customer State" AS state,
        AVG(city_count) AS avg_city_customer_count
    FROM (
        -- 先获取每个城市的客户数，作为计算州平均值的基础
        SELECT 
            "Customer State",
            COUNT("Customer id") AS city_count
        FROM 
            customers
        GROUP BY 
            "Customer State", "Customer City"
    ) AS state_city_counts
    GROUP BY 
        "Customer State"
) AS state_avg_stats
ON city_stats.state = state_avg_stats.state
-- 筛选出客户数高于所在州平均值的城市
WHERE city_stats.customer_count > state_avg_stats.avg_city_customer_count
-- 按客户数从高到低排序
ORDER BY city_stats.customer_count DESC;

语句说明 #

• 内层子查询state_city_counts：按州和城市分组，得到每个城市的客户数，这是计算州级平均值的基础数据。
• 中间子查询state_avg_stats：基于城市客户数的结果按州分组，用AVG()函数算出每个州内所有城市客户数的平均值。
• 外层查询：将城市客户数统计结果与州平均值结果通过州字段关联，用WHERE条件过滤出客户数大于所在州平均值的记录，最后按客户数降序排列结果。

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