SQL, mark continuous rows with sequence numbers
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:
id |
source_id |
event_user |
event_date |
1 |
1 |
A |
05-03-2014 |
2 |
1 |
A |
06-03-2014 |
3 |
1 |
B |
07-03-2014 |
4 |
1 |
B |
08-03-2014 |
5 |
1 |
A |
09-03-2014 |
6 |
1 |
A |
10-03-2014 |
7 |
1 |
A |
11-03-2014 |
8 |
2 |
A |
12-03-2014 |
9 |
2 |
B |
13-03-2014 |
10 |
2 |
A |
14-03-2014 |
11 |
2 |
B |
15-03-2014 |
12 |
2 |
B |
16-03-2014 |
We want to add a computed column named SERIES_ID to number sub-groups under each source_id. The expected result is as follows:
id |
source_id |
SERIES_ID |
event_user |
event_date |
1 |
1 |
1 |
A |
05-03-2014 |
2 |
1 |
1 |
A |
06-03-2014 |
3 |
1 |
2 |
B |
07-03-2014 |
4 |
1 |
2 |
B |
08-03-2014 |
5 |
1 |
3 |
A |
09-03-2014 |
6 |
1 |
3 |
A |
10-03-2014 |
7 |
1 |
3 |
A |
11-03-2014 |
8 |
2 |
1 |
A |
12-03-2014 |
9 |
2 |
2 |
B |
13-03-2014 |
10 |
2 |
3 |
A |
14-03-2014 |
11 |
2 |
4 |
B |
15-03-2014 |
12 |
2 |
4 |
B |
16-03-2014 |
Write the following SPL code:
A |
|
1 |
=post1.query("select id, source_id, SERIES_ID, event_user, event_date from data order by source_id,event_date") |
2 |
=A1.group@o(source_id).(~.group@o(event_user)) |
3 |
=A2.conj@r(~.run( ~.run( SERIES_ID=get(1,#)))) |
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.
Source:https://stackoverflow.com/questions/78422651/identify-groups-of-sequential-records
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
