与窗口子句等效的LISTAGG [英] LISTAGG equivalent with windowing clause
问题描述
在oracle中，LISTAGG函数允许我将其与OVER (PARTITION BY column..)子句一起分析使用.但是，它不支持通过ROWS或RANGE关键字使用窗口.
In oracle, the LISTAGG function allows me to use it analytically with a OVER (PARTITION BY column..) clause. However, it does not support use of windowing with the ROWS or RANGE keywords.
我有一个来自商店寄存器的数据集(针对问题进行了简化).请注意，收银台的数量始终为1-一件，一条交易行.
I have a data set from a store register (simplified for the question). Note that the register table's quantity is always 1 - one item, one transaction line.
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(即使物料相同，订购的组件也可能不同).
I have to "match" this data to a table in an special order system where items are grouped by quantity. Note that the system can have the same item ID on multiple lines (components ordered may be different even if the item is the same).
ItemId OrderID Order Line Dollars Quantity
------ ------- ---------- ------- --------
23845 23 1 8.97 3
23845 23 2 5.98 2
我只能通过订单ID，商品ID和美元金额匹配该数据的 .
The only way I can match this data is by order id, item id and dollar amount.
基本上，我需要得到以下结果.
Essentially I need to get to the following result.
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
我并不特别在意tran行是否以任何方式排序，我所关心的只是美元金额匹配，并且在计算总和时我不重用"寄存器中的一行.特殊订单.我不需要将tran行分解成一个表-这是出于报告目的，并且粒度永远不会下降到寄存器事务行级别.
I don't specifically care if the tran lines are ordered in any way, all I care is that the dollar amounts match and that I don't "re-use" a line from the register in computing the total on the special order. I don't need the tran lines broken out into a table - this is for reporting purposes and the granularity never goes back down to the register transaction line level.
我最初的想法是，我可以使用分析函数来做到这一点，以最佳匹配"来识别与订购系统中美元数量和数量相匹配的第一行，从而得到如下结果集:
My initial thinking was that I can do this with analytic functions to do a "best match" to identify the the first set of rows that match to the dollar amount and quantity in the ordering system, giving me a result set like:
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
到目前为止，一切都很好.但是我然后尝试将LISTAGG添加到我的查询中:
So far so good. But I then try to add LISTAGG to my query:
SELECT tranid, tranline, itemid, orderid, dollars, quantity,
SUM(dollars) OVER (partition by tranid, itemid, orderid order by tranline) cumdollar,
SUM(quantity) OVER (partition by tranid, itemid, orderid order by tranline) cumqty
LISTAGG (tranline) within group (order by tranid, itemid, orderid, tranline) OVER (partition by tranid, itemid, orderid)
FROM table
我发现它总是返回完整的agg而不是累积的agg:
I discover that it always returns a full agg instead of a cumulative 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中执行此操作.我知道我可以使用游标&程序逻辑.
I would much prefer to do this in SQL if at all possible. I am aware that I can do this with cursors & procedural logic.
是否可以使用LISTAGG分析功能进行窗口化，或者是否可以使用其他支持此功能的分析功能?
Is there any way to do windowing with the LISTAGG analytic function, or perhaps another analytic function which would support this?
我正在使用11gR2.
I'm on 11gR2.
推荐答案
我想到的唯一方法是使用相关子查询:
The only way I can think of to achieve this is with a correlated subquery:
WITH CTE AS
( SELECT TranID,
TranLine,
ItemID,
OrderID,
Dollars,
Quantity,
SUM(dollars) OVER (PARTITION BY TranID, ItemID, OrderID ORDER BY TranLine) AS CumDollar,
SUM(Quantity) OVER (PARTITION BY TranID, ItemID, OrderID ORDER BY TranLine) AS CumQuantity
FROM T
)
SELECT TranID,
TranLine,
ItemID,
OrderID,
Dollars,
Quantity,
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, itemid, orderid
) AS ListAgg
FROM CTE T1;
我意识到这并不能提供您所需要的确切输出，但是希望它足以克服累积LISTAGG的问题，并助您一臂之力.
I realise this doesn't give the exact output you were asking for, but hopefully it is enough to overcome the problem of the cumulative LISTAGG and get you on your way.
我已经设置了 SQL小提琴来演示解决方案.
I've set up an SQL Fiddle to demonstrate the solution.
这篇关于与窗口子句等效的LISTAGG的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
