sql - 如何将 ORDER BY 与由不同聚合函数创建的 2 列一起使用
问题描述
我正在尝试使用 SALESPERSON_CUSTOMER_REVENUE(每个客户的销售人员收入)和 ORDER BY 中每个销售人员的总收入。目前,我只能在 ORDER BY 语句中使用 SALESPERSONPERSONID 和 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:我想按总收入对销售人员进行排序,对于每个销售人员,我想按每个客户的收入进行排序。
请让我知道你的想法。谢谢!
解决方案
好吧,您得到了相同的结果,因为您在每个聚合列中执行完全相同的操作。我认为这将是窗口函数的一个很好的用例。
由于您想获得按不同列分组的结果,因此 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)
每个销售人员/客户组合的操作。
推荐阅读
- apache-flink - Apache flink - 阅读后将文件移动到不同的文件夹
- vue.js - vue.js pass function to component as prop
- javascript - Display several images with vuejs
- typescript - Typescript Promise 文件上传
- php - How to show multiple images by ajax success
- node.js - Issue connecting to Mongo Atlas from Apostrophe CMS application on Elastic Beanstalk
- node.js - Mongoose populate not working as expected
- date - 比较日期范围内的注册表字符串值
- php - 如何在 Wordpress 中使用 wp_link_pages() 在页面 url 中附加文本?
- python - python virtualenv 实例已损坏