sql - 计算连续天数

Question

请我想计算一个事件记录的连续天数，并按此记录分组按 actor id 排序。例如我们有。对于 sqlite

event_id| created_at |actor_id
1       |  2018-07-01|  40     /* this is a consecutive days
1       |  2018-07-02|  40     */
1       |  2018-07-04|  40
1       |  2018-07-05|  40
1       |  2018-07-09|  40
2       |  2018-07-11|  40
2       |  2018-07-12|  40
1       |  2018-07-13| 41

应该给我类似的东西

actor_id|streak
40     | 3 
41     | 0

Answer 1

您可以group by actor_id如果存在连续的一天，则有条件地求和:

select 
  t.actor_id,
  sum(case when exists (
      select 1 from tablename
      where
        actor_id = t.actor_id and
        julianday(created_at) - julianday(t.created_at) = 1 
  ) then 1 else 0 end) streak
from tablename t
group by t.actor_id

见 demo .
或自加入:

select 
  t.actor_id,
  sum(tt.created_at is not null) streak
from tablename t left join tablename tt
on tt.actor_id = t.actor_id and julianday(tt.created_at) - julianday(t.created_at) = 1
group by t.actor_id

见 demo .

结果:

| actor_id | streak |
| -------- | ------ |
| 40       | 3      |
| 41       | 0      |