Count equal, consecutive values in an ordered rowset

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 in it:

        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)

You can assume that there are no duplicate values in the action_time column.

How can I count the number of same actions in a row that were made starting from the last action?

There is no limit on the number of same actions in a row, and any action can be the last one. Also, there is no limit on the variety of different actions: I used just two to simplify the example data.

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

I think the solution can be achieved combining window functions and the WITH RECURSIVE clause, but I have no idea how to do it.

Answer 1

This should do it.

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

The inner most query

SELECT action FROM actions ORDER BY action_time DESC LIMIT 1

determines the last action.

The query

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

finds the last row with a different action.

The outermost query finds all rows after that row.

Answer 2

I added a little twist to the classic gaps-and-islands solution.
Notice how the ROW_NUMBER functions use descending 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

This comes to mind:

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

This should be able to take advantage of indexes on (action_time, action).

Answer 4

Improved solution

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