PostgreSQL: Select rows greater than a value by a number of consecutive times

Question

I have the following table:

id  updated_on           ch1
1   2020-03-23 08:30:25  90.577
2   2020-03-23 08:30:55  99.213
3   2020-03-23 08:31:05  101.426
4   2020-03-23 08:31:15  103.457
5   2020-03-23 08:31:25  103.982
6   2020-03-23 08:31:35  101.742
7   2020-03-23 08:31:45  97.983
8   2020-03-23 08:32:15  90.091
9   2020-03-23 08:41:35  96.985
10  2020-03-23 08:41:45  99.468
11  2020-03-23 08:41:55  101.714
12  2020-03-23 08:42:05  103.66
13  2020-03-23 08:42:15  104.388
14  2020-03-23 08:42:25  105.12
15  2020-03-23 08:42:35  106.737
16  2020-03-23 08:42:45  108.19
17  2020-03-23 08:42:55  109.626
18  2020-03-23 08:43:05  110.91

I need to select the 1st row where ch1 greater than 100 and more than 5 consecutive times. In the above table:

id 1 and 2 are below 100
id 3,4,5,6 are greater than 100 but not 5 consecutive times
id 7,8,9,10 are below 100
id 11,12,13,14,15 are greater than 100 and 5 consecutive times
return row id 15 from select

I started code with:

SELECT id, updated_on, ch1, CASE WHEN ch1>100 THEN 1 ELSE 0 END greater FROM table order by updated_on

but I don't know how to continue from here.

score 1 · Accepted Answer · answered Sep 09 '20 at 23:38

You could use window functions to solve this gaps-and-islands problem.

You would start by building groups of consecutive records having a value greater than 100 with a window count. Then, enumerate the rows in each group, and finally filter on the fifth record per group.

select id, updated_on, ch1
from (
    select
        t.*,
        row_number() over(partition by grp order by updated_on) rn
    from (
        select 
            t.*,
            count(*) filter(where ch1 <= 100) over(
                order by updated_on 
                rows between unbounded preceding and 1 preceding
            ) grp
        from mytable t
    ) t
) t
where ch1 > 100 and rn = 5

Demo on DB Fiddlde:

id | updated_on          |     ch1
-: | :------------------ | ------:
15 | 2020-03-23 08:42:35 | 106.737

PostgreSQL: Select rows greater than a value by a number of consecutive times

1 Answers1