I am trying to group records of a table, based on a condition, and then asing in a new column a simple integer ID for each group obtained from where the condition is met.
ID | TMSTP | CAT_TYPE | TELEGRAMMZAEHLER |
---|---|---|---|
1 | 2022-05-03 20:52:02 | 32 | 5004 |
2 | 2022-05-03 20:51:34 | 32 | 5002 |
3 | 2022-05-03 20:51:34 | 32 | 5001 |
4 | 2022-05-03 20:51:33 | 32 | 5000 |
5 | 2022-05-03 20:41:22 | 32 | 4996 |
6 | 2022-05-03 20:41:21 | 32 | 4995 |
I need to assign the same ID to those rows whose TELEGRAMMZAEHLER number is consecutive to the next one (for example, rows 2 and 3 have TZ 5002 and 5001, therefore they are consecutive and should belong to a same Group ID.)
The GRUPPE column would be my desired outcome. Rows 2 to 4 belong together in the same group ID, bur then rows 5 and 6 should have another ID, because the TZ in row 5 is not consecutive from the TZ in row 4.
ID | TMSTP | CAT_TYPE | TELEGRAMMZAEHLER | GRUPPE |
---|---|---|---|---|
1 | 2022-05-03 20:52:02 | 32 | 5004 | 1 |
2 | 2022-05-03 20:51:34 | 32 | 5002 | 2 |
3 | 2022-05-03 20:51:34 | 32 | 5001 | 2 |
4 | 2022-05-03 20:51:33 | 32 | 5000 | 2 |
5 | 2022-05-03 20:41:22 | 32 | 4996 | 3 |
6 | 2022-05-03 20:41:21 | 32 | 4995 | 3 |
Any ideas on how can that be achieved on postgreSQL?
Thank you very much!
Advertisement
Answer
We can use LAG
here along with SUM
as an analytic function:
WITH cte AS ( SELECT *, CASE WHEN TELEGRAMMZAEHLER = LAG(TELEGRAMMZAEHLER) OVER (ORDER BY TMSTP DESC) - 1 THEN 0 ELSE 1 END AS idx FROM yourTable ) SELECT ID, TMSTP, CAT_TYPE, TELEGRAMMZAEHLER, SUM(idx) OVER (ORDER BY TMSTP DESC) AS GRUPPE FROM cte ORDER BY TMSTP DESC;