Incrementing row numbers by condition in postgres

Question

I have a postgres table with timestamps and the rounded difference in hours between current and previous (lagged) timestamp in difftime

 timestamp               type    difftime
 2013-09-14 14:19:46     JPR03   2 
 2013-09-14 15:11:48     JPR03   1 
 2013-09-14 16:11:49     JPR03   1 
 2013-09-14 17:13:45     JPR03   1 
 2013-09-22 00:08:38     JPR03   175 
 2013-09-22 00:10:11     JPR03   0 
 2013-09-22 01:11:36     JPR03   1 
 2013-09-22 02:16:11     JPR03   1 
 2013-09-22 03:13:16     JPR03   1 
 2013-09-22 04:05:38     JPR03   1 
 2013-09-22 06:10:11     JPR03   2 
 2013-09-22 07:26:43     JPR03   1 
 2013-09-22 08:17:35     JPR03   1 
 2013-09-22 09:16:08     JPR03   1 
 2013-09-22 10:16:08     JPR03   1 
 2013-10-01 06:15:07     JPR03   212 
 2013-10-01 06:15:12     JPR03   0 
 2013-10-02 07:15:15     JPR03   25 
 2013-10-02 08:05:09     JPR03   1

My objective is to create an incremental row number sequence that increases by 1 when and only when the value in difftime is above a certain threshold x (ordered by time). If x = 5, then the output would look like this:

 timestamp               type    difftime  rownum
 2013-09-14 14:19:46     JPR03   2         0
 2013-09-14 15:11:48     JPR03   1         0
 2013-09-14 16:11:49     JPR03   1         0
 2013-09-14 17:13:45     JPR03   1         0
 2013-09-22 00:08:38     JPR03   175       1
 2013-09-22 00:10:11     JPR03   0         1
 2013-09-22 01:11:36     JPR03   1         1
 2013-09-22 02:16:11     JPR03   1         1
 2013-09-22 03:13:16     JPR03   1         1
 2013-09-22 04:05:38     JPR03   1         1
 2013-09-22 06:10:11     JPR03   2         1
 2013-09-22 07:26:43     JPR03   1         1
 2013-09-22 08:17:35     JPR03   1         1
 2013-09-22 09:16:08     JPR03   1         1
 2013-09-22 10:16:08     JPR03   1         1
 2013-10-01 06:15:07     JPR03   212       2
 2013-10-01 06:15:12     JPR03   0         2
 2013-10-02 07:15:15     JPR03   25        3
 2013-10-02 08:05:09     JPR03   1         3

I am familiar with the RANK(), DENSE_RANK(), ROW_NUMBER(), and COALESCE() functions, but none of these would achieve the objective of incrementing a row number by condition (beginning with 0). Any suggestions on how to implement this kind of variable assignment or what functions might be applied here to partition based on a condition?

is the condition from the previous row or from the first row since the condition was last met? — MVB76
– MVB76, Commented Feb 3, 2021 at 6:40
from the first since the condition was last met, so a new value would "start" on the same record where the condition is met — iskandarblue
– iskandarblue, Commented Feb 3, 2021 at 6:42

S-Man · Accepted Answer · 2021-02-03 07:01:03Z

5

demo:db<>fiddle

You can use the cumulative SUM() function with a conditional value: Add 1 if the condition is met, 0 otherwise:

SELECT
    *,
    SUM(
        CASE 
            WHEN diff >= 5 THEN 1
            ELSE 0
        END
    ) OVER (ORDER BY ts)
FROM --<your query>

edited Feb 3, 2021 at 7:01

answered Feb 3, 2021 at 6:55

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2021-02-03 13:38:15Z

2

In Postgres, I would recommend using filter:

select q.*,
       count(*) filter (where diff > ?) over (order by ts) as rownum
from <your query> q;

The ? is a placeholder for whatever value you have in mind.

answered Feb 3, 2021 at 13:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Incrementing row numbers by condition in postgres

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related