DEV Community

Judy
Judy

Posted on

SQL, mark continuous rows with sequence numbers #eg30

In PostgreSQL database, tmp table has two grouping fields – source_id and event_user. Group the table by source_id and sort each group by event_date, rows having same event_user value will form sub-groups in order, as shown below:

Image description
We want to add a computed column named SERIES_ID to number sub-groups under each source_id. The expected result is as follows:

Image description
Write the following SPL code:

Image description

A1: Retrieve data with a SQL statement and sort rows by source_id and event_date; SERIES_ID is eimpty.

A2: Group rows by comparing neighboring source_id values without sorting, and then group rows in each group by comparing neighboring event_user values.

A3: Mark each sub-group of each group with a sequence number and concatenate members of each groups as records.

SPL open source address

Top comments (0)