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.

GMB · Accepted Answer · 2020-09-09 23:38:58Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related