SQL视频这么火,你会这么分析吗?(分组汇总、相邻判断、偏移函数)

发布于:2023-01-24 ⋅ 阅读:(651) ⋅ 点赞:(0)

【题目】

"用户操作记录表"里记录着每天某短视频平台的用户点击访问情况,以便帮助公司内部分析师了解用户对于当前页面的点击偏好。
表包字段有:用户名、操作记录、操作时间。

其中表内各字段含义如下

用户名:表示用户在该短视频平台注册的唯一用户名。

操作记录:表示用户在该短视频平台点击的按钮名称。A表示用户点击“短视频”播放入口,B表示用户点击“长视频”播放入口。

操作时间:表示用户点击时候的时间,精确到秒。

现在运营人员找到作为数据分析师的你,想让你帮忙看看用SQL取两个数据,具体如下:

1.分析每天的访客数和他们的平均操作次数

2.统计每天符合以下条件的用户数:A操作之后是B操作,AB操作必须相邻。

举个例子,比如小明在2020年1月2日当天先点了短视频入口(操作ID为A),其后又点了长视频入口(操作ID为B),那么小明就属于当天我们的目标用户。(如下图黄色字段所示)

1.分析每天的访客数和他们的平均操作次数

假如短视频平台2020年1月2号正式上线,第一天,只有小明和小红两个人浏览短视频平台。

其中小明对短视频和长视频都感兴趣,上午九点点击了A按钮进入短视频界面观看。然后下午3点钟,小明点了B按钮,进入长视频频道观看。晚上时候,小明觉着还想再看看短视频,就在晚上7点40时候,又点击了A按钮去观看短视频。

而小红是重度短视频爱好者,当天下午2点小红第一次点击A按钮进入短视频界面,觉着内容不错,于是晚上6点时候,又点击了A按钮,又开心的看短视频。

那么,如何统计当天访客数以及平均操作次数呢?

短视频平台上线第一天,也就是2020年1月2日,当天的访客数和平均操作次数我们可以很轻而易举的看出来:当天访客数是2(小明和小红两人),当天的平均操作次数是2.5次(当天一共有5次点击行为,2个人操作,所以5除以2得2.5)

然而,随着短视频平台人数的增多,如果让分析师天天进行手动数数,那工作量可想而知,有什么方式可以帮我们快速统计每天的访客数和平均操作次数呢?

题目中想要知道每天的访客数和平均操作次数,遇到这类“每个”的问题,要想到《猴子 从零学会SQL》里讲过的用“分组汇总”。

按日期分组(group by日期),汇总(人数用count用户名,操作数用count操作时间)。这里因为表中代表日期的字段“操作时间”是精确到分秒的,所以需要利用date函数将操作时间转为日期形式。具体形式如下:

这里你可以会存在一个疑问,既然group by代表分组意思,我直接在结尾时候增添group by分组命令即可,但为什么在开头的select 后面,也需要加上分组标准date(操作时间)呢?

这就涉及到聚合函数另一个特点,“首尾呼应”。你可以简单理解为,select后面为最后呈现在餐桌上的美味佳肴,而group by后面的内容为厨房幕后烹饪的食材加工过程。

理解清楚后,代入具体代码字段如下:


select date(操作时间) as 操作日期,
       count(distinct 用户名) as 人数,
       avg(操作时间) as 操作数
from 用户操作记录表
group by date(操作时间);

这里有两个注意点,其一是需要去重,count(distinct 用户名)其中的distinct就是去重的意思。

比如小明在2020-02-01早上点了一次,下午点了一次,晚上点了一次,那么小明其实在表里面当天是有三条记录的,但是人头数的话,小明当天只贡献了一个人头数,所以这里需要去重。

其二是日期需要改为date形式,date函数可以帮我们将长时间段变为简介的年-月-日的日期形式。

2.统计每天符合以下条件的用户数:A操作之后是B操作,AB操作必须相邻

其实这个是在日常工作中业务经常要用到的SQL取数场景。例如,某电商公司主管在做针对功能优化的决策,主管非常想知道,咱们的用户在点击“下单”按钮后,都流向到哪里去了?

这时候数据分析师小甲淡定的汇报到:主管,咱们用户呀,在点击“下单”按钮后,有80%的用户立马点击了“领优惠券”按钮,有15%的用户立马点击了“评价”按钮,有5%的用户立马点击了“随便看看”按钮。

主管一听,便立马就知道了用户的点击行为偏好。

其实这里,数据分析师小甲用了SQL中的lead()窗口函数,实现了这种“相邻”操作的数据统计。

 

如上图可知,用户(花花)的行为路径是,先下单,其后他领取了优惠券,领完优惠券之后再去评价。而小白则是下单后直接就去评价了。

数据分析师小甲先以用户进行分组,找出用户当前操作以及最近一次操作的行为,而后进行统计记人头数,从而给了主管一个非常满意的答案。

lead()函数的模板如上,基本上只要改变ABC三个方格里面的值,就可以实现任何相邻元素之间的取数。具体表达含义如下:

lead()表示找出XXX的下一次相邻行为。

partition表示分组

order by表示排序;

比如说,想找出每天用户相邻两次的点击。

按照什么分组呢?按照用户分组,所以partition by后面填上用户id。

那如何区别每个用户各个行为的先后顺序呢?按照每个用户点击的时间来区分,所以order by后面填入操作时间。

那最后,我们究竟想看的行为是什么呢?操作记录(比如上述提到的优惠券行为、评价行为等)。

思路整理完后,SQL如下:


lead(操作ID,1) over(partition by 用户名 
                    order by 操作时间);

回过头来继续看当前的这个案例。

假如短视频平台2020年1月2号正式上线,第一天,只有小明和小红两个人浏览短视频平台。

其中小明对短视频和长视频都感兴趣,上午九点点了A按钮进入短视频界面观看。其后下午3点钟,小明点了B按钮,进入长视频频道观看。

晚上时候,小明觉着还想再看看短视频,就在晚上7点40时候,又点击了A按钮去观看短视频。

小红则是重度短视频爱好者,当天下午2点小红第一次点击A按钮进入短视频界面,觉着内容不错,于是晚上6点时候,又点击了A按钮,又开心的看短视频。

这里找出A操作后,立马是B操作后的用户数,具体思路如下。

首先按照用户名进行分组,其次按照每个用户名下用户的点击时间进行排序,最后以操作记录作为我们想要定位的行为。

如下,黄色部分表示,当小明点击A按钮观看短视频后,其后下一次最近的行为是点击B按钮观看了长视频。白色部分表示,当小明点击B按钮观看长视频后,其后下一次最近的行为是又在晚上时候点击A按钮观看了短视频。

红色部分则代表了小红的行为路径,小红最初是点击A按钮观看短视频,其后又再次点击了A按钮观看了短视频。

从而可以写出第一条SQL语句:

 最终SQL如下:


select 操作时间,count(distinct 用户名) as 用户数
from
(select date(操作时间) as 操作时间,用户名,
操作记录,lead(操作记录,1) over(partition by 用户名order by 操作时间) as 最近的下一次操作记录
from tracking_log) t
where 操作记录=‘A’ and 最近的下一次操作记录='B'
group by 操作时间

 

 

【本题考点】

1.考查对分组汇总的应用,以及灵活使用来解决业务问题

2.当遇到相邻问题时,要想到用窗口函数lead来解决

3.考察遇到复杂问题,如何用逻辑树分析方法来拆解解决成简单问题

 

 

本文含有隐藏内容,请 开通VIP 后查看

网站公告

今日签到

点亮在社区的每一天
去签到