计算postgres中列更改为特定值的位置 [英] Count where column changes to specific value in postgres

问题描述

我需要对action = 1和action与上面的行不同的行进行计数。

I need a count of rows where action=1 and action is different from the above row. The first row should count if action=1.

     action_date     | action 
---------------------+--------
 2017-01-01 00:00:00 |      1
 2017-01-02 00:00:00 |      1
 2017-01-03 00:00:00 |      0
 2017-01-04 00:00:00 |      0
 2017-01-05 00:00:00 |      1
 2017-01-06 00:00:00 |      0
 2017-01-07 00:00:00 |      1

在此示例中，第1，第5和第7行计数，结果应为3。任何

In this example the 1st, 5th, and 7th rows count and the result should be 3. Any help is much appreciated.

推荐答案

使用 lag 获取值

select count(*)
from (select action_date,action,lag(action) over(order by action_date) as prev_action
      from t
     ) t
where (action<>prev_action and action=1) or (action=1 and prev_action is null)

或者可以简化为

select 
count(case when lag(action) over(order by action_date) is null then and action = 1 then 1
           when lag(action) over(order by action_date) is not null and lag(action) over(order by action_date) <> action and action = 1 then 1 
      end) as cnt
from t

这篇关于计算postgres中列更改为特定值的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！