如何求和不同值的OVER(PARTITION BY DISTINCT)

Question

我正在寻找一种在SQL Server中使用Partition by Over的巧妙方法。

我在SQL Server中有3个表(下面的所有*_id列都只是伪主键(

• PO(PO_id，PO_no(
• PO_ITEM(PO_ITEM_id、PO_id、PO_ITEM_no、数量(；//存储采购订单项的订购数量
• PO_ITEM_DELY(PO_ITEM_DELY_id、PO_ITEM_id、DELY_no、DELY_qty(；//存储每个交货编号的每个采购订单项目的交货数量

select
po.po_no, pt.po_item_no, pt.qty, pd.dely_no, pd.dely_qty
from 
PO
inner join 
PO_ITEM pt on pt.po_id = po.po_id
inner join 
PO_ITEM_DELY pd on pd.po_item_id = pt.po_item_id
where 
po.po_no = 'PO1'

此SQL查询的结果供参考：

<1><210>100150

po_no	po_item_no	数量
PO1	PoI11	300
PO1	PoI11	300	2	48
PO1	PoI11	300	3	55
PO1	PoI12	100
PO1	PoI13	250
PO1	PoI13	250	2	100

使用多个不同的窗口规范来解决这个问题：

select
x.po_no, 
x.OrdPOQty,
SUM(pd.dely_qty) OVER(PARTITION BY x.po_no) as DelyPOQty,

x.po_item_no,
x.OrdItemQty,
SUM(pd.dely_qty) OVER(PARTITION BY x.po_no, x.po_item_no) as DelyItemQty,

x.qty, 
pd.dely_no, 
pd.dely_qty
from 
( 
SELECT 
po.po_id, po.po_no, pt.po_item_id, pt.po_item_no, pt.qty, 
SUM(pt.qty) OVER(PARTITION BY po.po_no) as OrdPOQty, 
SUM(pt.qty) OVER(PARTITION BY po.po_no, pt.po_item_no) as OrdItemQty
FROM PO inner join PO_ITEM pt on pt.po_id = po.po_id
) x
inner join PO_ITEM_DELY pd on pd.po_item_id = x.po_item_id
where 
x.po_no = 'PO1'

从技术上讲，partition by po_no是不必要的，因为where子句确保只有一个，但我保留了它，以防您想扩展查询以考虑多个po_no

如果您总是只查询一个po_no:

select
x.po_no, 
x.OrdPOQty,
SUM(pd.dely_qty) OVER() as DelyPOQty,

x.po_item_no,
x.OrdItemQty,
SUM(pd.dely_qty) OVER(PARTITION BY x.po_item_no) as DelyItemQty,

x.qty, 
pd.dely_no, 
pd.dely_qty
from 
( 
SELECT 
po.po_id, po.po_no, pt.po_item_id, pt.po_item_no, pt.qty, 
SUM(pt.qty) OVER(PARTITION BY po.po_no) as OrdPOQty, 
SUM(pt.qty) OVER(PARTITION BY po.po_no, pt.po_item_no) as OrdItemQty
FROM PO inner join PO_ITEM pt on pt.po_id = po.po_id
) x
inner join PO_ITEM_DELY pd on pd.po_item_id = x.po_item_id
where 
x.po_no = 'PO1'

想知道是否有一种更简单的方法可以通过更巧妙地应用子句进行过度分区来进行求和

基本上，对于基本形式，你会得到一行的N个重复，你可以计算重复次数，并将组中值的总和除以组中的重复次数，所以你对原始值的三分之一但重复3次的值求和，得到相同的总和。。但我确实觉得这比在没有笛卡尔乘积的水平上进行求和和和计数更糟糕，然后结果就被执行和重复。。

或者我们可以只计算其中一个项目，假设每个项目都至少有一个交付#1:

select
po.po_no, 
SUM(CASE WHEN pd.dely_no = 1 THEN pt.qty ELSE 0 END) OVER(PARTITION BY po.po_no) as OrdPOQty,
SUM(pd.dely_qty) OVER(PARTITION BY po.po_no) as DelyPOQty,

pt.po_item_no,
SUM(CASE WHEN pd.dely_no = 1 THEN pt.qty ELSE 0 END) OVER(PARTITION BY po.po_no, pt.po_item_no) as OrdItemQty,
SUM(pd.dely_qty) OVER(PARTITION BY po.po_no, pt.po_item_no) as DelyItemQty,

pt.qty, 
pd.dely_no, 
pd.dely_qty
from 
PO
inner join PO_ITEM pt on pt.po_id = po.po_id
inner join PO_ITEM_DELY pd on pd.po_item_id = pt.po_item_id
where 
po.po_no = 'PO1'

如果添加另一个表，导致pd.dely_no在每个po/po+item分区中重复值为1，则需要扩展CASE逻辑