返回高于平均值的行

Question

我试图找到订单数量高于平均水平的国家。这是我所得到的。问题是(见图(每个国家的订单数量都不一样，因为每个人的订单数量都应该不同

SELECT avg(NumberOrders) as avg, 
Customers.Country, 
NumberOrders
FROM Customers,
(SELECT COUNT(Orders.OrderId) AS NumberOrders 
FROM Customers JOIN 
Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.Country) nested
GROUP BY Customers.Country
HAVING NumberOrders > avg;

输出

Answer 1

如果你的DBMS支持窗口聚合(除了MySQL和Access之外的几乎所有

(：

select *
from
(
SELECT Customers.Country, 
COUNT(Orders.OrderId) AS NumberOrders,  -- count per country
AVG(COUNT(Orders.OrderId)) OVER () AS avgOrders -- average count
FROM Customers
JOIN Orders 
ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.Country
) nested
WHERE NumberOrders > avgOrders

编辑：

对于不支持窗口聚合的 DBMS，它要复杂得多：

SELECT Customers.Country, 
COUNT(Orders.OrderId) AS NumberOrders  -- count per country
FROM Customers
JOIN Orders 
ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.Country
HAVING COUNT(Orders.OrderId) >
( select avg(NumberOrders)
from
(
SELECT Customers.Country, 
COUNT(Orders.OrderId) AS NumberOrders  -- count per country
FROM Customers
JOIN Orders 
ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.Country
) AS dt
)

如果 DBMS 支持公用表表达式，则可以简化：

with cte as
(
SELECT Customers.Country, 
COUNT(Orders.OrderId) AS NumberOrders  -- count per country
FROM Customers
JOIN Orders 
ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.Country
) 
select *
from cte
WHERE NumberOrders >
(
select avg(NumberOrders) from cte
)