我们如何根据业务的计数输出列。如果显示的业务频率相同,我们将订购库存商品最多的业务 SUM(Inventory) - SUM(Total Sold)
给定下表
Business | Product | Total_Sold | Inventory
---------------------------------------------------
Jane's | Shoes | 10 | 30
Jane's | Top | 20 | 14
Jane's | Top | 20 | 21
Smith's | Bottom | 50 | 30
Kamp's | Shoes | 20 | 40
Kamp's | Top | 40 | 50
Kamp's | Bottom | 50 | 70
输出
Business | Product
------------------------
Kamp's | Shoes
Kamp's | Top
Kamp's | Bottom
Jane's | Shoes
Jane's | Top
Jane's | Top
Smith's | Bottom
Kamp's 将首先显示,因为它出现最多且库存最多 (70)。Jane's 排在第二位,因为它也出现了 3 次,但它只有 15 件商品。
下面的查询返回 Jane's 在 Kamp's 之前,因为 Jane's 在表中出现不同。
SELECT business, product
FROM (
SELECT business, product
, count(*) OVER (PARTITION BY business) AS ct
FROM TABLE
) sub
ORDER BY ct DESC, business, product;
下面的查询计算到库存总量
SELECT business, SUM(total_sold)-SUM(inventory) as diff
FROM TABLE
GROUP by business
ORDER by COUNT(distinct product) DESC, diff ASC
是否可以添加一个新分区来处理 group by 并将这些查询组合在一起?
您可以使用窗口函数:
select *
from(select *, count(*) over (partition by business) as businesscnt,
sum(inventory-total_sold) over(partition by business) as closingqty
from t
) t
order by businesscnt desc, closingqty desc;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句