Эквивалент LISTAGG с предложением windowing
В 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
Я должен «сопоставить» эти данные с таблицей в системе специального заказа, где элементы сгруппированы по количеству. Обратите внимание, что система может иметь один и тот же идентификатор элемента в нескольких строках (заказанные компоненты могут отличаться, даже если элемент один и тот же).
ItemId OrderID Order Line Dollars Quantity
------ ------- ---------- ------- --------
23845 23 1 8.97 3
23845 23 2 5.98 2
только способ, которым я могу сопоставить эти данные, — это идентификатор заказа,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.