Search code examples
sqloracle-database

How to batch in SQL for set of records from table

Hi The table details looks like : Table name: TBL_TXN enter image description here

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. enter image description here

Thanks for the help !!

Solution

  • 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