Count equal, consecutive values ​​in an ordered set of rows

Question

I have a table with two columns like:

CREATE TABLE actions (
  action_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "action" text NOT NULL
);

and the following data:

        action_time         | action 
----------------------------+--------
 2016-12-30 14:12:33.353269 | a
 2016-12-30 14:12:38.536818 | b
 2016-12-30 14:12:43.305001 | a
 2016-12-30 14:12:49.432981 | a
 2016-12-30 14:12:53.536397 | b
 2016-12-30 14:12:57.449101 | b
 2016-12-30 14:13:01.592785 | a
 2016-12-30 14:13:06.192907 | b
 2016-12-30 14:13:11.249181 | b
 2016-12-30 14:13:13.690897 | b
(10 rows)

It can be assumed that there are no duplicate values ​​in the action_time column.

How to count the number of the same actions in a row that have been done since the last action?

There are no restrictions on the number of identical actions per line, and any action can be the last. In addition, there are no restrictions on many different actions: I used only two to simplify the example data.

In this example, I expect the result to be 3. This is because the last action was "b", and it happened 3 times in a row.

I think a solution can be reached by combining window functions and suggestions WITH RECURSIVE, but I have no idea how to do this.

Answer 1

.

SELECT COUNT(*)
FROM actions
WHERE action_time > (
SELECT action_time
  FROM actions 
  WHERE action <> (SELECT action FROM actions ORDER BY action_time DESC LIMIT 1) 
ORDER BY action_time DESC LIMIT 1);

SELECT action FROM actions ORDER BY action_time DESC LIMIT 1

.

SELECT action_time
  FROM actions 
  WHERE action <> (SELECT action FROM actions ORDER BY action_time DESC LIMIT 1) 
ORDER BY action_time DESC LIMIT 1

.

.

Answer 2

.
, ROW_NUMBER ORDER BY.

select  count(*)

from   (select  

            action
           ,row_number() over (                    order by action_time desc) as rn
           ,row_number() over (partition by action order by action_time desc) as rn_action

        from    mytab
        ) t

group by action
        ,rn - rn_action

having   min(rn) = 1

Answer 3

:

select count(*)
from t cross join
     (select t2.action
      from t t2
      order by action_time desc
      limit 1
     ) last
where t.action_time >= (select max(t2.action_time)
                        from t t2
                        where t2.action <> last.action
                       );

(action_time, action).

Answer 4

select  count(*)

from   (select  

            action
           ,row_number() over (                    order by action_time desc) as rn
           ,row_number() over (partition by action order by action_time desc) as rn_action

        from    mytab
        ) t

where   rn = rn_action