与窗口子句等效的LISTAGG

Question

在oracle中，该LISTAGG函数允许我将其与OVER (PARTITION BY column..)子句一起使用。但是，它不支持使用带有ROWS或RANGE关键字的窗口。

我有一个来自商店寄存器的数据集（针对该问题进行了简化）。请注意，寄存器表的数量始终为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

我并不特别在意tran行是否以任何方式排序，我只关心美元金额是否匹配，并且我在计算特殊订单的总数时不“重用”寄存器中的一行。我不需要将tran行分解成一个表-
这是出于报告目的，并且粒度永远不会下降到寄存器事务行级别。

我最初的想法是，我可以使用解析函数来做到“最佳匹配”，以识别与订购系统中的美元数量和数量相匹配的第一行，从而得到如下结果集：

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添加到我的查询中：

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：

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。

Answer 1

我能想到的唯一方法是使用相关子查询：

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的问题并助您一臂之力。

我已经设置了一个SQL Fiddle来演示该解决方案。