sql - PostgreSQL - 选择重复连续序列的计数

Question

我有下表/数据:

| 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         |

在 postgresql 中有没有一种方法可以计算重复连续的“飞行”事件？在这个例子中，结果应该是:

counts
------
  1  
  3
  1
  2

Answer 1

是的，这是可能的，使用lag 窗口函数和累积和:

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