如何对由不同聚合函数创建的2列使用ORDER BY

Question

我试图使用SALESPERSON_CUSTOMER_REVENUE(销售人员对每个客户的收入)和ORDER by中每个销售人员的总收入。目前，我只能在ORDER BY语句中使用salespersonid和SALESPERSON_CUSTOMER_REVENUE。

SALES_ORDERS
-------------------------------------------------------------------------
|   SALESPERSONPERSONID    |      CUSTOMERID        |     ORDERID       |
-------------------------------------------------------------------------
|              3           |            10          |      324371       |
-------------------------------------------------------------------------
SALES_ORDERLINES
--------------------------------------------------------------------
|   ORDERID    |    ORDERLINEID    |   QUANTITY   |   UNITPRICE    |
--------------------------------------------------------------------
|   324371     |      10           |      32      |      100       |
--------------------------------------------------------------------

当前查询

SELECT 
ORD.SALESPERSONPERSONID,  
ORD.CUSTOMERID,
SUM(LINE.QUANTITY * LINE.UNITPRICE) AS SALESPERSON_CUSTOMER_REVENUE 

FROM SALES_ORDERS ORD
INNER JOIN SALES_ORDERLINES LINE
ON ORD.ORDERID = LINE.ORDERID

GROUP BY ORD.SALESPERSONPERSONID, ORD.CUSTOMERID
ORDER BY ORD.SALESPERSONPERSONID, SALESPERSON_CUSTOMER_REVENUE DESC

预期结果

--------------------------------------------------------------------
| SALESPERSONPERSONID | CUSTOMERID | SALESPERSON_CUSTOMER_REVENUE |
--------------------------------------------------------------------
|       3             |     10     |             3200             |
--------------------------------------------------------------------
|       3             |     12     |             2200             |
--------------------------------------------------------------------
|       1             |     2      |             2000             |
--------------------------------------------------------------------
|       1             |     1      |             1200             |
--------------------------------------------------------------------
|       2             |     3      |             3000             |

TLDR:我想按销售人员的总收入对他们进行排序，对于每个销售人员，我想按每个客户的收入对他们进行排序。

请让我知道你的想法。谢谢你！

Answer 1

您得到了相同的结果，因为您在每个聚合列中执行了完全相同的操作。我认为这将是一个很好的窗口函数用例。

由于您希望获得按不同列分组的结果，因此PARTITION bY解决了这个问题:

SELECT DISTINCT
ORD.SALESPERSONID,  
ORD.CUSTOMERID,
SUM(LINE.QUANTITY * LINE.UNITPRICE) OVER (PARTITION BY SALESPERSONID) AS SALESPERSON_REVENUE, 
SUM(LINE.QUANTITY * LINE.UNITPRICE) OVER (PARTITION BY SALESPERSONID, CUSTOMERID) AS SALESPERSON_CUSTOMER_REVENUE 
FROM 
SALES_ORDERS AS ORD
INNER JOIN 
SALES_ORDERLINES AS LINE ON ORD.ORDERID = LINE.ORDERID               
ORDER BY 
SALESPERSON_REVENUE DESC,
SALESPERSON_CUSTOMER_REVENUE DESC

• 您将看到列SALESPERSON_REVENUE将汇总每个销售人员的操作SUM(LINE.QUANTITY * LINE.UNITPRICE)

• 列SALESPERSON_CUSTOMER_REVENUE将对每个销售人员/客户组合的操作SUM(LINE.QUANTITY * LINE.UNITPRICE)进行汇总。

Answer 2

您可以将窗口函数放在ORDER BY子句中:

SELECT ORD.SALESPERSONPERSONID, ORD.CUSTOMERID,
SUM(LINE.QUANTITY * LINE.UNITPRICE) AS SALESPERSON_CUSTOMER_REVENUE     
FROM SALES_ORDERS ORD JOIN
SALES_ORDERLINES LINE
ON ORD.ORDERID = LINE.ORDERID               
GROUP BY ORD.SALESPERSONPERSONID, ORD.CUSTOMERID
ORDER BY SUM(SUM(LINE.QUANTITY * LINE.UNITPRICE)) OVER (PARTITION BY ORD.SALESPERSONPERSONID), 
ORD.SALESPERSONPERSONID,
SALESPERSON_CUSTOMER_REVENUE DESC

注意有三个ORDER BY键。中间的一条很重要。它处理两个销售人员的总收入相同的情况，并确保每个销售人员的行保持在一起。