Hi The table details looks like :
Table name: TBL_TXN
My query output should be like this :
While fetching the data we need to batch based on limits. so the limit is each batch should have max of 2 transaction and max item count should be 4.
Thanks for the help !!
4
From Oracle 12, you can use MATCH_RECOGNIZE for row-by-row pattern matching:
SELECT transaction, participant, item_count, batch_id
FROM (
SELECT transaction, participant, item_count,
TO_NUMBER(SUBSTR(transaction, 4)) AS trans_id
FROM table_name
)
MATCH_RECOGNIZE(
ORDER BY trans_id
MEASURES
MATCH_NUMBER() AS batch_id
ALL ROWS PER MATCH
PATTERN ( first_trans transaction_limit{0,1} )
DEFINE
transaction_limit AS SUM(item_count) <= 4
AND participant = FIRST(participant)
)
Which, for the sample data:
CREATE TABLE table_name (transaction, participant, item_count) AS
SELECT 'TSN1', 1, 2 FROM DUAL UNION ALL
SELECT 'TSN2', 1, 2 FROM DUAL UNION ALL
SELECT 'TSN3', 1, 2 FROM DUAL UNION ALL
SELECT 'TSN4', 1, 3 FROM DUAL UNION ALL
SELECT 'TSN5', 1, 4 FROM DUAL UNION ALL
SELECT 'TSN6', 2, 2 FROM DUAL UNION ALL
SELECT 'TSN7', 2, 4 FROM DUAL UNION ALL
SELECT 'TSN8', 2, 3 FROM DUAL UNION ALL
SELECT 'TSN9', 2, 2 FROM DUAL UNION ALL
SELECT 'TSN10', 2, 2 FROM DUAL UNION ALL
SELECT 'TSN11', 2, 2 FROM DUAL UNION ALL
SELECT 'TSN12', 3, 2 FROM DUAL UNION ALL
SELECT 'TSN13', 3, 1 FROM DUAL UNION ALL
SELECT 'TSN14', 3, 1 FROM DUAL;
Outputs:
| TRANSACTION | PARTICIPANT | ITEM_COUNT | BATCH_ID |
|---|---|---|---|
| TSN1 | 1 | 2 | 1 |
| TSN2 | 1 | 2 | 1 |
| TSN3 | 1 | 2 | 2 |
| TSN4 | 1 | 3 | 3 |
| TSN5 | 1 | 4 | 4 |
| TSN6 | 2 | 2 | 5 |
| TSN7 | 2 | 4 | 6 |
| TSN8 | 2 | 3 | 7 |
| TSN9 | 2 | 2 | 8 |
| TSN10 | 2 | 2 | 8 |
| TSN11 | 2 | 2 | 9 |
| TSN12 | 3 | 2 | 10 |
| TSN13 | 3 | 1 | 10 |
| TSN14 | 3 | 1 | 11 |
fiddle
2
This needs recursive query to get the desired output.
Here is a way to get the output you desire. The way to understand is to run each of the blocks separately to get the intermediate results and corelate with the intension.
WITH data AS (
SELECT 'TSN1' as trans,1 participant,2 item_cnt from dual union all
SELECT 'TSN2',1,2 from dual union all
SELECT 'TSN3',1,2 from dual union all
SELECT 'TSN4',1,3 from dual union all
SELECT 'TSN5',1,4 from dual union all
SELECT 'TSN6',2,2 from dual union all
SELECT 'TSN7',2,4 from dual union all
SELECT 'TSN8',2,3 from dual union all
SELECT 'TSN9',2,2 from dual union all
SELECT 'TSN10',2,2 from dual
)
,ranking_tran
as (select row_number() over(order by cast(replace(trans,'TSN','') as number)) as rnk
,trans
,participant
,item_cnt
from data
)
,cte(rnk,trans,participant,orig_item_cnt,item_cnt,trans_cnt,batchid)
as (select rnk
,trans
,participant
,item_cnt as orig_item_cnt
,item_cnt
,1 as trans_cnt
,1 as batchid
from ranking_tran
where rnk=1
union all
select d.rnk
,d.trans
,d.participant
,d.item_cnt as orig_Item_cnt
,case when c.item_cnt+ d.item_cnt>4 then d.item_cnt else c.item_cnt+d.item_cnt end
,case when c.trans_cnt +1 =3 then 1 else c.trans_cnt + 1 end
,case when c.item_cnt+ d.item_cnt>4
or (c.trans_cnt +1=4) then
c.batchid +1
else c.batchid
end
from cte c
join ranking_tran d
on c.rnk+1=d.rnk
)
select trans,participant,orig_Item_cnt,batchid
from cte
–db fiddle link
https://dbfiddle.uk/82-hVTbg
+-------+-------------+---------------+---------+
| TRANS | PARTICIPANT | ORIG_ITEM_CNT | BATCHID |
+-------+-------------+---------------+---------+
| TSN1 | 1 | 2 | 1 |
| TSN2 | 1 | 2 | 1 |
| TSN3 | 1 | 2 | 2 |
| TSN4 | 1 | 3 | 3 |
| TSN5 | 1 | 4 | 4 |
| TSN6 | 2 | 2 | 5 |
| TSN7 | 2 | 4 | 6 |
| TSN8 | 2 | 3 | 7 |
| TSN9 | 2 | 2 | 8 |
| TSN10 | 2 | 2 | 8 |
+-------+-------------+---------------+---------+
1