LISTAGG 윈도잉 절과 동등함
오라클에서,LISTAGG함수를 사용하면 분석적으로 사용할 수 있습니다.OVER (PARTITION BY column..)절그러나 다음과 같이 윈도우를 사용하는 것은 지원하지 않습니다.ROWS아니면RANGE키워드.
스토어 레지스터에서 설정한 데이터가 있습니다(질문은 단순화).레지스터 테이블의 수량은 항상 1개 - 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
지금까지 좋아요.그런 다음 쿼리에 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입니다.
이를 달성할 수 있는 유일한 방법은 상호 연관된 하위 쿼리를 사용하는 것입니다.
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;
저는 이것이 당신이 요구했던 정확한 결과를 주지 못한다는 것을 알고 있지만, 그것이 LISTAG 누적의 문제를 극복하고 당신이 나아갈 수 있도록 하기에 충분하기를 바랍니다.
솔루션을 시연하기 위해 SQL Fiddle을 설치했습니다.
예제에서 스토어 레지스터 테이블은 5개의 행을 포함하고 특수 주문 시스템 테이블은 2개의 행을 포함합니다.예상 결과 세트에는 특별 주문 시스템 테이블의 두 행이 포함되어 있으며, 스토어 레지스터 테이블의 모든 "트란 라인"이 "트랜 라인" 열에 표시되어야 합니다.
즉, 이 5개 행에서 2개 행으로 집계해야 합니다.즉, LISTAGG 분석 기능이 필요한 것이 아니라 LISTAGG Aggregate 기능이 필요합니다.
스토어 레지스터 테이블의 행을 특수 주문 시스템 테이블의 오른쪽 행에 결합하는 것이 여러분의 과제입니다.당신은 달러와 수량의 합계를 계산해서 잘 가고 있었습니다.누락된 유일한 단계는 각 상점 레지스터 행을 각 특수 주문 시스템 행에 할당할 수 있는 달러 및 수량 범위를 정의하는 것입니다.
여기 예가 있습니다.먼저 테이블을 정의합니다.
SQL> create table store_register_table (tranid,tranline,itemid,orderid,dollars,quantity)
2 as
3 select 1, 101, 23845, 23, 2.99, 1 from dual union all
4 select 1, 102, 23845, 23, 2.99, 1 from dual union all
5 select 1, 103, 23845, 23, 2.99, 1 from dual union all
6 select 1, 104, 23845, 23, 2.99, 1 from dual union all
7 select 1, 105, 23845, 23, 2.99, 1 from dual
8 /
Table created.
SQL> create table special_order_system_table (itemid,orderid,order_line,dollars,quantity)
2 as
3 select 23845, 23, 1, 8.97, 3 from dual union all
4 select 23845, 23, 2, 5.98, 2 from dual
5 /
Table created.
그리고 질문은:
SQL> with t as
2 ( select tranid
3 , tranline
4 , itemid
5 , orderid
6 , sum(dollars) over (partition by itemid,orderid order by tranline) running_sum_dollars
7 , sum(quantity) over (partition by itemid,orderid order by tranline) running_sum_quantity
8 from store_register_table srt
9 )
10 , t2 as
11 ( select itemid
12 , orderid
13 , order_line
14 , dollars
15 , quantity
16 , sum(dollars) over (partition by itemid,orderid order by order_line) running_sum_dollars
17 , sum(quantity) over (partition by itemid,orderid order by order_line) running_sum_quantity
18 from special_order_system_table
19 )
20 , t3 as
21 ( select itemid
22 , orderid
23 , order_line
24 , dollars
25 , quantity
26 , 1 + lag(running_sum_dollars,1,0) over (partition by itemid,orderid order by order_line) begin_sum_dollars
27 , running_sum_dollars end_sum_dollars
28 , 1 + lag(running_sum_quantity,1,0) over (partition by itemid,orderid order by order_line) begin_sum_quantity
29 , running_sum_quantity end_sum_quantity
30 from t2
31 )
32 select t3.itemid "ItemID"
33 , t3.orderid "OrderID"
34 , t3.order_line "Order Line"
35 , t3.dollars "Dollars"
36 , t3.quantity "Quantity"
37 , t.tranid "Tran ID"
38 , listagg(t.tranline,';') within group (order by t3.itemid,t3.orderid) "Tran Lines"
39 from t3
40 inner join t
41 on ( t.itemid = t3.itemid
42 and t.orderid = t3.orderid
43 and t.running_sum_dollars between t3.begin_sum_dollars and t3.end_sum_dollars
44 and t.running_sum_quantity between t3.begin_sum_quantity and t3.end_sum_quantity
45 )
46 group by t3.itemid
47 , t3.orderid
48 , t3.order_line
49 , t3.dollars
50 , t3.quantity
51 , t.tranid
52 /
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
2 rows selected.
안부 전해요,
롭.
언급URL : https://stackoverflow.com/questions/10952950/listagg-equivalent-with-windowing-clause
'programing' 카테고리의 다른 글
| Spring Security에서 보안 주석 구성에서 URL 패턴 제외 (0) | 2023.11.01 |
|---|---|
| 우커머스는 가격을 계산하고 절약합니다. (0) | 2023.11.01 |
| 한 사용자가 수정한 모든 파일을 알려줄 수 있습니까? (0) | 2023.11.01 |
| libfoo.so .1과 같은 라이브러리 이름이 지정된 전체 파일 경로는 어떻게 찾을 수 있습니까? (0) | 2023.11.01 |
| 날짜 시간을 지원하는 파이썬 JSON 인코더? (0) | 2023.11.01 |