PostgreSQL 中的 group by 中的窗口函数

Question

每次

操作字段更改为值 1 时，我都需要为每个用户计数。如果第一个条目是 1，则也算数。行顺序不对，但应按action_date顺序计数。

换句话说，我认为需要做的是：按user_id对行进行分组，按时间戳对它们进行排序，然后计算操作=1和操作！=前一行的频率。

例

create table t (
user_id int,
action_date timestamp,
action int
);
Insert into t(user_id, action_date, action)
values
(1, '2017-01-01 00:00:00', 1),
(2, '2017-01-01 00:00:00', 0),
(1, '2017-01-03 00:00:00', 1),
(2, '2017-01-03 00:00:00', 0),
(1, '2017-01-02 00:00:00', 1),
(2, '2017-01-02 00:00:00', 1),
(1, '2017-01-04 00:00:00', 1),
(2, '2017-01-04 00:00:00', 1);

结果应该是

 user_id | count 
---------+-------
       1 |     1
       2 |     2

在此答案的帮助下，我可以通过这种方式获得单个帐户的结果，

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

但是我试图将其扩展到所有用户。

Answer 1

将 lag() 函数与 partition by 一起使用：

select user_id, count(*)
from (select t.*,
             lag(action) over (partition by user_id order by action_date) as prev_action
      from t
     ) t
where (action = 1) and (prev_action is distinct from 1)
group by user_id;