在oracle中,LISTAGG函数允许我用OVER(PARTITION BY column ..)子句分析地使用它.但是,它不支持使用ROWS或RANGE关键字使用窗口.
我有一个来自商店寄存器的数据集(简化了问题).请注意,寄存器表的数量始终为1 – 1项,一个交易行.
TranID TranLine ItemId OrderID Dollars Quantity ------ -------- ------ ------- ------- -------- 1 101 23845 23 2.99 1 1 102 23845 23 2.99 1 1 103 23845 23 2.99 1 1 104 23845 23 2.99 1 1 105 23845 23 2.99 1
我必须将该数据“匹配”到特殊订单系统中的表格,其中项目按数量分组.请注意,系统可以在多行上具有相同的项目ID(即使项目相同,订购的组件也可能不同).
ItemId OrderID Order Line Dollars Quantity ------ ------- ---------- ------- -------- 23845 23 1 8.97 3 23845 23 2 5.98 2
我可以匹配这个数据的唯一方法是订单ID,项目ID和美元金额.
基本上我需要得到以下结果.
ItemId OrderID Order Line Dollars Quantity Tran ID Tran Lines ------ ------- ---------- ------- -------- ------- ---------- 23845 23 1 8.97 3 1 101;102;103 23845 23 2 5.98 2 1 104;105
我不是特别关心转换线是否以任何方式进行订购,所有我关心的是美元数量相匹配,而且我不会在注册表中“重新使用”一行来计算特定订单的总额.我不需要将转换分解成一个表 – 这是为了报告的目的,粒度永远不会回到注册事务线级别.
我的初步想法是,我可以通过分析功能来做到这一点,做一个“最佳匹配”,以确定与订单系统中的美元数量和数量相匹配的第一组行,给出如下结果：
TranID TranLine ItemId OrderID Dollars Quantity CumDollar CumQty ------ -------- ------ ------- ------- -------- -------- ------ 1 101 23845 23 2.99 1 2.99 1 1 102 23845 23 2.99 1 5.98 2 1 103 23845 23 2.99 1 8.97 3 1 104 23845 23 2.99 1 11.96 4 1 105 23845 23 2.99 1 14.95 5
SELECT tranid,tranline,itemid,orderid,dollars,quantity,SUM(dollars) OVER (partition by tranid,orderid order by tranline) cumdollar,SUM(quantity) OVER (partition by tranid,orderid order by tranline) cumqty
LISTAGG (tranline) within group (order by tranid,tranline) OVER (partition by tranid,orderid)
FROM table
我发现它总是返回一个完整的,而不是一个累积的agg：
TranID TranLine ItemId OrderID Dollars Quantity CumDollar CumQty ListAgg ------ -------- ------ ------- ------- -------- -------- ------ ------- 1 101 23845 23 2.99 1 2.99 1 101;102;103;104;105 1 102 23845 23 2.99 1 5.98 2 101;102;103;104;105 1 103 23845 23 2.99 1 8.97 3 101;102;103;104;105 1 104 23845 23 2.99 1 11.96 4 101;102;103;104;105 1 105 23845 23 2.99 1 14.95 5 101;102;103;104;105
所以这没有用.
如果可能,我更喜欢在sql中执行此操作.我知道我可以用光标和程序逻辑
有什么办法可以使用LISTAGG分析函数进行窗口化,还是可以支持这一点的另一个分析函数？
我在11gR2.
解决方法
我可以想到的唯一方法就是使用相关的子查询：
WITH CTE AS
( SELECT TranID,TranLine,ItemID,OrderID,Dollars,Quantity,SUM(dollars) OVER (PARTITION BY TranID,OrderID ORDER BY TranLine) AS CumDollar,SUM(Quantity) OVER (PARTITION BY TranID,OrderID ORDER BY TranLine) AS CumQuantity
FROM T
)
SELECT TranID,CumDollar,CumQuantity,( SELECT LISTAGG(Tranline,';') WITHIN GROUP(ORDER BY CumQuantity)
FROM CTE T2
WHERE T1.CumQuantity >= T2.CumQuantity
AND T1.ItemID = T2.ItemID
AND T1.OrderID = T2.OrderID
AND T1.TranID = T2.TranID
GROUP BY tranid,orderid
) AS ListAgg
FROM CTE T1;
我意识到这并没有给出您要求的确切输出,但希望足以克服累积LISTAGG的问题并让您顺利进行.
我已经设置了一个SQL Fiddle来演示解决方案.
