Sql – LISTAGG equivalent with windowing clause

oracleoracle11gsql

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.

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

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

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

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

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

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

So this isn't useful.

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.

Is there any way to do windowing with the LISTAGG analytic function, or perhaps another analytic function which would support this?

I'm on 11gR2.

Best Answer

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;

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.

I've set up an SQL Fiddle to demonstrate the solution.

Related Topic