无法正确使用SQL Lead函数

我有一个表,有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

这就是输出结果的样子

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

这个查询完成了查询任务的前半部分 但我无法理解后半部分的内容

with t1 as
(select date,
count(distinct user_id) as users_view_next_day
from clickstream_videos
WHERE event_name = 'video_play'
group by 1)

select clickstream_videos.date, 
count(distinct user_id) as users_view_videos,
lead(users_view_next_day, 1) over(order by clickstream_videos.date) 
from clickstream_videos
join t1
on clickstream_videos.date = t1.date 
WHERE event_name = 'video_play'
group by 1, t1.users_view_next_day

谢谢,我有一个有四列的表

解决方案:

如果你做一个 CTE 它列出了所有在某一天观看过视频的不同用户,可以用来统计在某一天观看视频的用户。然后,你可以将它连接到自己,条件是第二张表中的日期是第二天,而第二张表中的 user_id 是一样的,得到当天和下一天观看视频的人数。

WITH viewers AS (
  SELECT DISTINCT date, user_id
  FROM clickstream_videos
  WHERE event_name = 'video_play'
)
SELECT v1.date, 
       COUNT(v1.user_id) AS user_view_videos,
       COUNT(v2.user_id) AS users_view_next_day
FROM viewers v1
LEFT JOIN viewers v2 ON v2.date = v1.date + interval '1 day'
                    AND v2.user_id = v1.user_id
GROUP BY v1.date

输出:

date                        user_view_videos    users_view_next_day
2018-01-01T00:00:00.000Z    2                   1
2018-01-02T00:00:00.000Z    2                   0

dbfiddle上的演示

给TA打赏
共{{data.count}}人
人已打赏
未分类

如何防止数据在Widget Flutter内重新整理?

2022-9-10 17:03:36

未分类

如何将CommandBar的更多按钮与二级命令中的项目绑定?

2022-9-10 17:03:38

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索