sql - 在 Oracle SQL 的一个查询中显示 3 个不同的查询

Question

目前我正在研究包含的数据库

顾客

->
CUSTOMER_ID
NAME
ADDRESS
WEBSITE
CREDIT_LIMIT

订单

->
ORDER_ID
CUSTOMER_ID
STATUS
SALESMAN_ID
ORDER_DATE

order_items

->
ORDER_ID
ITEM_ID
PRODUCT_ID
QUANTITY
UNIT_PRICE

我想创建一个 SQL 查询来显示

1. 总订单金额超过所有订单平均金额的客户数量，
2. 总订单金额低于所有订单平均金额的客户数量，
3. 没有订单的客户数和客户总数。
4. 客户总数

这是我迄今为止的尝试。（我知道这还不够）

SELECT 'Number of customers with total purchase amount over average: ' || COUNT(DISTINCT c.customer_id) AS "Report"
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
WHERE 
UNION
SELECT 'Number of customers with total purchase amount below average: ' || COUNT(DISTINCT c.customer_id) AS "Report"
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
UNION
SELECT 'Number of customers with no orders: ' || COUNT(DISTINCT c.customer_id) AS "Report"
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE      o.customer_id IS NULL
UNION
SELECT 'Total number of customers: ' || COUNT(DISTINCT customer_id) AS "Report"
FROM customers
ORDER BY 1;

我现在只是尝试获取更容易查询的输出，所以我得到了 3、4 的正确输出。但是 1、2 ......我尝试了一些查询但没有工作......

如果您能在 1、2 方面帮助我，我将不胜感激。

如果有人能回答我的问题，我将不胜感激。

期望的输出

Report
------------------------------------------
Number of customers with total purchase amount over average: (number)
Number of customers with total purchase amount below average: (number)
Number of customers with no orders: (number)
Total number of customers: (number)

标签： sqloracleoracle-sqldeveloper

Answer 1

您需要按如下方式使用windows功能：

SELECT COUNT(DISTINCT CUSTOMER_ID) FROM
(SELECT CUSTOMER_ID,
       SUM(ORDER_TOTAL) OVER (PARTITION BY ORDER_ID) AS CUST_TOTAL,
       AVG(ORDER_TOTAL) OVER () AS AVG_ALL
FROM
(SELECT C.CUSTOMER_ID,
       O.ORDER_ID,
       SUM(OI.UNIT_PRICE*OI.QUANTITY) AS ORDER_TOTAL
  FROM CUSTOMERS C
  JOIN ORDERS O ON O.CUSTOMER_ID = C.CUSTOMER_ID
  JOIN ORDER_ITEMS OI ON OI.ORDER_ID = O.ORDER_ID
GROUP BY C.CUSTOMER_ID,
         O.ORDER_ID))
WHERE CUST_TOTAL > AVG_ALL -- point 1
--WHERE CUST_TOTAL < AVG_ALL -- point 2