How to batch in SQL for set of records from table

Question

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 !!

Answer 1

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