Extract records from table on dynamic interval based on column value spilling over-CodePudding

I have a table as below

id	key	address
11	0	newYork
12	0	Washington
13	1	Washington
14	1	newYork
15	2	Washington
16	3	Washington
17	3	Washington
18	4	Luxemberg

wish to extract information based on key so that min records per page would be 3 and if key at record 3 is still there for next few records need to consider subsequent records so results sets would appear like

Page 1

id	key	address
11	0	newYork
12	0	Washington
13	1	Washington
14	1	newYork

Page 2

id	key	address
15	2	Washington
16	3	Washington
17	3	Washington

Page 3

id	key	address
18	4	Luxemberg

CodePudding user response：

From Oracle 12, you can use MATCH_RECOGNIZE for row-by-row processing:

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  ORDER BY id
  MEASURES
    MATCH_NUMBER() AS page
  ALL ROWS PER MATCH
  PATTERN ( any_row{1,3} same_key* )
  DEFINE same_key AS PREV(key) = key
);

Which, for the sample data:

CREATE TABLE table_name (id, key, address) AS
SELECT 11, 0, 'newYork' FROM DUAL UNION ALL
SELECT 12, 0, 'Washington' FROM DUAL UNION ALL
SELECT 13, 1, 'Washington' FROM DUAL UNION ALL
SELECT 14, 1, 'newYork' FROM DUAL UNION ALL
SELECT 15, 2, 'Washington' FROM DUAL UNION ALL
SELECT 16, 3, 'Washington' FROM DUAL UNION ALL
SELECT 17, 3, 'Washington' FROM DUAL UNION ALL
SELECT 18, 4, 'Luxemberg' FROM DUAL;

Outputs:

ID	PAGE	KEY	ADDRESS
11	1	0	newYork
12	1	0	Washington
13	1	1	Washington
14	1	1	newYork
15	2	2	Washington
16	2	3	Washington
17	2	3	Washington
18	3	4	Luxemberg

If you only want a certain page then add a WHERE filter for that at the end of the query.

fiddle