SQL视频这么火，你会这么分析吗？（分组汇总、相邻判断、偏移函数）

【题目】

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

其中表内各字段含义如下

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

操作记录：表示用户在该短视频平台点击的按钮名称。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.考察遇到复杂问题，如何用逻辑树分析方法来拆解解决成简单问题