SQL:HAVING 与 WHERE 的计数高于平均水平

Question

我有一个相当简单的面试问题:返回客户数量多于所有城市平均客户数量的所有国家/地区。

国家/地区表:

id country_name
1  Austria
2  Germany
3  United Kingdom

城市表:

id city_name country_id
1  Wien      1
2  Berlin    2
3  Hamburg   2
4  London    3

客户表:

id customer_name city_id
1  cust1         1
2  cust2         4
3  cust3         3
4  cust4         1
5  cust5         2
6  cust6         1
7  cust7         4
8  cust8         2

下面是我的解决方案。我的答案很接近，但没有通过测试用例，因为我返回的国家/地区比预期输出更多。您知道为什么，或者您将如何解决这个问题吗？

SELECT 
    co.country_name, COUNT(customer_name)
FROM
    country co
        JOIN
    city ci ON co.id = ci.country_id
        JOIN
    customer cu ON ci.id = cu.city_id
GROUP BY co.country_name
HAVING COUNT(customer_name) > (SELECT 
        AVG(temp.cnt)
    FROM
        (SELECT 
            ci.id, COUNT(customer_name) AS cnt
        FROM
            city ci
        JOIN customer cu ON ci.id = cu.city_id
        GROUP BY ci.id) temp)
ORDER BY co.country_name ASC;

此线程中的用户 SQL: Count values higher than average for a group使用 WHERE 而不是 HAVING 来选择大于平均值的值。我想知道 HAVING 是否不适合此类问题。

Answer 1

您当前的查询是解决此问题的一种有效方法，但您的答案的接收者可能期望不同的解决方案。更现代的方法可能涉及分析函数:

WITH cte AS (
    SELECT co.country_name, COUNT(customer_name) cnt,
           AVG(COUNT(customer_name)) OVER () avg_cnt
    FROM country co
    INNER JOIN city ci ON co.id = ci.country_id
    INNER JOIN customer cu ON ci.id = cu.city_id
    GROUP BY co.country_name
)

SELECT country_name, cnt
FROM cte
WHERE cnt > avg_cnt;

在上面的 CTE 中，当我们计算出每个国家/地区名称的计数时，我们还会计算整个原始结果集的平均计数。然后，我们所要做的就是子查询并将每个计数与平均值进行比较。