PostgreSQL - select count of repeated continuous sequences

Question

I have the following table/data:

| user_id | action_id | data        |
------------------------------------- 
| 10      |    1      | fly         |
| 10      |    2      | train       |
| 10      |    3      | fly         |
| 10      |    4      | fly         |
| 10      |    5      | fly         |
| 10      |    6      | train       |
| 10      |    7      | fly         |
| 10      |    8      | train       |
| 10      |    9      | fly         |
| 10      |   10      | fly         |

Is there a way in postgresql to count repeated continuous 'fly' occurrences? In this example, the results should be:

counts
------
  1  
  3
  1
  2

Check this out: [Row Number Over Partition](http://stackoverflow.com/questions/25562542/postgresql-window-function-row-number-over-partition-col-order-by-col2) — Daniel Corzo, Dec 09 '15 at 17:21
Yes, i know about window function but partition by not applicable here. I've try some variants with partition by with no success. I've try also some variants with window function with order by and achieve results near to needed with absolutely ugly SQL. But @sstan's answer much simple, it's really cool! — Alexey Sviridov, Dec 09 '15 at 18:52

score 5 · Accepted Answer · answered Dec 09 '15 at 18:25

Yes, it's possible, using the lag window function and a cumulative sum:

with FlagCTE as (
  select t.action_id, t.data,
         case when t.data = 'fly' and t.data = lag(t.data) over (order by t.action_id) then 0 else 1 end as Flag
    from some_table t),
GroupCTE as (
  select t.action_id,
         t.data,
         sum(t.Flag) over (order by t.action_id) as GroupId
    from FlagCTE t
   where t.data = 'fly')
select count(*) as counts
  from GroupCTE t
 group by t.GroupId
 order by t.GroupId

SQLFiddle Demo

PostgreSQL - select count of repeated continuous sequences

1 Answers1