Finding consecutive patterns (with SQL)

Question

A table consecutive in PostgreSQL: Each se_id has an idx from 0 up to 100 - here 0 to 9.

The search pattern:

SELECT *
FROM consecutive
WHERE val_3_bool = 1
AND val_1_dur > 4100 AND val_1_dur < 5900

Now I'm looking for the longest consecutive appearance of this pattern for each p_id - and the AVG of the counted val_1_dur.

Is it possible to calculate this in pure SQL?

table as txt "Result" as txt

Post the tables and data as text [READ THIS](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) to understand why — Juan Carlos Oropeza, Nov 06 '17 at 14:36
https://stackoverflow.com/questions/tagged/gaps-and-islands+postgresql — , Nov 06 '17 at 14:41

score 3 · Answer 1 · answered Nov 06 '17 at 14:41

One method is the difference of row numbers approach to get the sequences for each:

select pid, count(*) as in_a_row, sum(val1_dur) as dur
from (select t.*,
             row_number() over (partition by pid order by idx) as seqnum,
             row_number() over (partition by pid, val3_bool order by idx) as seqnum_d
      from consecutive t
     ) t
group by (seqnun - seqnum_d), pid, val3_bool;

If you are looking specifically for "1" values, then add where val3_bool = 1 to the outer query. To understand why this works, I would suggest that you stare at the results of the subquery, so you can understand why the difference defines the consecutive values.

You can then get the max using distinct on:

select distinct on (pid) t.*
from (select pid, count(*) as in_a_row, sum(val1_dur) as dur
      from (select t.*,
                   row_number() over (partition by pid order by idx) as seqnum,
                   row_number() over (partition by pid, val3_bool order by idx) as seqnum_d
            from consecutive t
           ) t
      group by (seqnun - seqnum_d), pid, val3_bool;
     ) t
order by pid, in_a_row desc;

The distinct on does not require an additional level of subquery, but I think that makes the logic clearer.

Sounds interesing. ;) But I’m looking only for the consecutive matching of a specific pattern. — Teletubbi-OS X, Nov 06 '17 at 15:24
@Teletubbi-OSX . . . This does what you want. Just add the `where` clause to the query before the `group by`, as explained in the answer. — Gordon Linoff, Nov 06 '17 at 23:21
Yes, it works! Thanks. But this procedure is a bit slow. It takes ~30sec. (table size 750MB). — Teletubbi-OS X, Nov 07 '17 at 17:39

score 0 · Answer 2 · edited Nov 06 '17 at 14:40

There are Window Functions, that enable you to compare one line with the previous and next one.

https://community.modeanalytics.com/sql/tutorial/sql-window-functions/ https://www.postgresql.org/docs/current/static/tutorial-window.html

As seen on How to compare the current row with next and previous row in PostgreSQL? and Filtering by window function result in Postgresql

Finding consecutive patterns (with SQL)

2 Answers2