sql - PostgreSQL 数据的分组

Question

我有一个 postgresql 表，其中包含按日期/时间记录的事件。该表包含列 id、event 和 timestamp。

我的输出必须是这样的:

'Day', '1st Timers', '2nd Timers', '3rd Timers', '3+ Timers'

1st timers 是所有第一次完成事件的 id。第二次计时器是所有第二次完成该事件的 ID。等等等等

这可以使用单个 SQL 查询吗？

编辑:根据请求的样本数据和输出

user_id date                event
1       09/03/15 14:08      opened
2      10/03/15 14:08       opened
1      11/03/15 14:08       opened
4      14/03/15 14:08       opened
1      15/03/15 14:08       opened
5      16/03/15 14:08       opened
1      17/03/15 14:08       opened
4      17/03/15 14:08       opened
6      18/03/15 14:08       opened
1      18/03/15 14:08       opened
6      18/03/15 14:08       other


Output (for event=opened)
date        1time   2times  3times  4times  5times
09/03/15    1       0       0       0       0
10/03/15    1       0       0       0       0
11/03/15    0       1       0       0       0
14/03/15    1       0       0       0       0
15/03/15    0       0       1       0       0
16/03/15    1       0       0       0       0
17/03/15    0       1       0       1       0
18/03/15    1       0       0       0       1

Answer 1

对于每个日期，您似乎都想统计点击 1 次、2 次等的用户数。我将其视为 row_number() 后跟条件聚合:

select thedate,
       sum(case when seqnum = 1 then 1 else 0 end) as time_1,
       sum(case when seqnum = 2 then 1 else 0 end) as time_2,
       sum(case when seqnum = 3 then 1 else 0 end) as time_3,
       sum(case when seqnum = 4 then 1 else 0 end) as time_4,
       sum(case when seqnum = 5 then 1 else 0 end) as time_5
from (select t.*, date_trunc('day', date) as thedate
             row_number() over (partition by user_id order by date_trunc('day', date)) as seqnum
      from table t
      where event = 'opened'
     ) t
group by thedate
order by thedate;