mysql - 如何检索连续几天发生某些事件的不同行的计数

Question

我有一个包含 4 列的表:日期、事件名称、视频 ID 和用户 ID。

我需要编写一个查询，该查询将返回在给定日期内观看至少一个视频的用户数量以及第二天返回观看至少一个视频的用户数量。 video_play 事件表示用户播放了视频。

以下是我的查询需要基于的一些示例数据:

date            event_name      video_id    user_id
2018-01-01      video_play      51651561651 989189198
2018-01-01      video_play      98121651656 561884864
2018-01-01      video_play      78918918918 561884864
2018-01-01      create_video    32156541355 153215651
2018-01-01      create_video    87351531311 232135135
2018-01-02      create_video    51651561651 489846581
2018-01-02      video_play      12315315352 561884864
2018-01-02      create_video    32156541355 321351351
2018-01-02      create_video    87351531311 231513515
2018-01-02      video_play      98191891894 615616516

这个查询完成了查询任务的前半部分，但我无法计算出后半部分:

SELECT date
    , COUNT(DISTINCT user_id) AS users_view_videos
    FROM clickstream_video
    WHERE event_name = 'video_play'
    GROUP BY date;

输出应如下所示:

date        users_view_videos   users_view_next_day
2018-01-01  2                   1
2018-01-02  2                   0

Answer 1

提示:

SELECT x.date
     , x.user_id
     , y.event_name
  FROM clickstream_video x
  LEFT
  JOIN clickstream_video y
    ON y.date = x.date + INTERVAL 1 DAY
   AND y.user_id = x.user_id
WHERE x.event_name = 'video_play';