我想创建一个SQL查询，以列出所有订单数量高于平均值的客户

Question

sql查询以列出所有订单数量高于平均值的客户。

订单详细信息在nw_orders表中存在，并且客户信息在nw_customers表中存在。

首先，我计算了所有客户下达的订单数量。然后，我只想拉出大于AVG订单数量的客户。

我的查询：

SELECT 
    C.customerid, C.companyname, COUNT(O.orderid) AS cnt
FROM 
    NW_customers C
LEFT JOIN 
    NW_orders O ON O.customerID = C.Customerid 
GROUP BY 
    C.customerid 
HAVING 
   cnt > (SELECT COUNT(O.OrderID) / COUNT(DISTINCT(c.customerid)) AS Avg
          FROM NW_orders O
          LEFT JOIN NW_customers C ON O.customerID = C.Customerid)

我有错误

ora-00904：" cnt"：无效标识符

任何人都可以帮助纠正错误吗？

Answer 1

使用公共表表达式：

WITH cte AS (
    SELECT o.customerid, COUNT(o.orderid) AS cnt
    FROM NW_orders o
    GROUP BY o.customerid
)
SELECT t.customerid
FROM cte t
WHERE t.cnt > (SELECT AVG(cnt) FROM cte)

如果您想引入实际的客户信息，则可以在上述查询中添加加入：

SELECT t1.*, t2.*
FROM cte t1
INNER JOIN NW_customers t2
    ON t1.customerid = t2.customerid
WHERE t1.cnt > (SELECT AVG(cnt) FROM cte)

Answer 2

这是分析功能的经典。

cutomerid

select      customerID
from       (select      customerID
                       ,count(*)                as customer_orders
                       ,avg  (count(*)) over () as avg_customer_orders
            from        NW_orders
            group by    customerID
            )
where       customer_orders > avg_customer_orders
;

完整的客户信息

select      *
from        NW_customers
where       customerID in
            (
                select      customerID
                from       (select      customerID
                                       ,count(*)                as customer_orders
                                       ,avg  (count(*)) over () as avg_customer_orders
                            from        NW_orders
                            group by    customerID
                            )
                where       customer_orders > avg_customer_orders
            )
;

完整的客户信息 订单信息

select      o.customer_orders
           ,o.avg_customer_orders
           ,c.*
from                    NW_customers    c
            join        (select     customerID
                                   ,count(*)                as customer_orders
                                   ,avg  (count(*)) over () as avg_customer_orders
                        from        NW_orders
                        group by    customerID
                        ) o
            on          o.customerID    =
                        c.customerID

where       o.customer_orders > o.avg_customer_orders
;