Search code examples
sqlpostgresqlvertica

PostgreSQL -Assign Unique ID to each groups of records, based on a condition

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!

Solution

  • 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;

    Demo