How to partition a table based on a pattern(in this case an increasing sub sequence) that is found in a column in presto sql

I am encountering the following problem. I have a table T, in which there is column named offset which will always be a collection of increasing sub sequences of integers beginning with zero.

Table T:

**row_num, offset ,col_1, col_2, col_3 ....**
     1       0
     2      10
     3      22
     4       32
     5       0
     6       0
     7       12
     8       32 
     9       44
             

I want to partition the table based on offset column such that the rows belonging to each strictly increasing subsequence are put into one partition. In the above example rows [1,2,3,4] , [0], [0,12,32,44] are the partitions that i am looking for. How do i proceed or is this even possible in sql?

I tried using lead and lag partition functions, the idea was to come up with an auxiliary column which stores partition numbers and then group by the resulting table based on this column. But i believe such an effort would require the loop functionality which i don't think exists in sql.

You can use MATCH_RECOGNIZE to identify rows that satisfy a pattern. In your case, you can define a pattern of increasing values of the column offset:

WITH t(row_num, offset) AS (
    VALUES
             (1, 0),
             (2, 10),
             (3, 22),
             (4, 32),
             (5, 0),
             (6, 0),
             (7, 12),
             (8, 32),
             (9, 44)
)
SELECT row_num, offset, partition FROM t
    MATCH_RECOGNIZE (
        ORDER BY row_num
        MEASURES MATCH_NUMBER() AS partition
        ALL ROWS PER MATCH
        AFTER MATCH SKIP PAST LAST ROW
        PATTERN (START UP*)
        DEFINE UP AS offset > PREV(offset)
    )

What the MATCH_RECOGNIZE clause says in the query above is:

• Find a sequence of rows (ordered by row_num) that matches the pattern START UP*. The START element should appear once and the UP element should appear zero or more times following it. START and UP are arbitrary names to represent rows in the sequence. START is defined implicitly and it matches any row, while UP is defined as a row whose offset column has a value larger than the offset column of the previous row in the sequence (DEFINE UP AS offset > PREV(offset))
• Compute an output column named partition whose value is a synthetic number associated with each match. (MEASURES MATCH_NUMBER() AS partition)
• Output all the input rows with the partition column annotation. (ALL ROWS PER MATCH)

The query produces:

 row_num | offset | partition
---------+--------+-----------
       1 |      0 |         1
       2 |     10 |         1
       3 |     22 |         1
       4 |     32 |         1
       5 |      0 |         2
       6 |      0 |         3
       7 |     12 |         3
       8 |     32 |         3
       9 |     44 |         3
(9 rows)

For more details, please see:

• https://trino.io/docs/current/sql/match-recognize.html
• https://trino.io/blog/2021/05/19/row_pattern_matching.html

Note that this functionality is only available in Trino and Athena version 3, which is based on Trino.

You could use a running sum function that increased by 1 whenever a zero offset value is found over the increasing of row_num:

select row_num, offset,
  sum(case when offset = 0 then 1 else 0 end) over (order by row_num) prt
from table_name

The output of this query:

row_num	  offset	prt
1	      0	        1
2	      10	    1
3	      22	    1
4	      32	    1
5	      0	        2
6	      0	        3
7	      12    	3
8	      32	    3
9	      44	    3