专栏首页猴子数据分析视频这么火,你会这么分析吗?
原创

视频这么火,你会这么分析吗?

【题目】

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

表包字段有:用户名、操作记录、操作时间。

其中表内各字段含义如下

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

操作记录:表示用户在该短视频平台点击的按钮名称。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后面的内容为厨房幕后烹饪的食材加工过程。

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

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

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

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

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

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

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

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

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

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

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

lead窗口函数大致结构如下:

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

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

partition表示分组

order by表示排序;

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

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

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

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

思路整理完后,SQL如下:

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

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

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

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

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

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

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

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

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

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

填空完成后,再在外边嵌套条件:

最终SQL如下:

 查询结果:

【举一反三】

某APP的播放记录表如下(用户操作记录表),记录了每个用户进入(Enter)、离开(Leave)某App的时间。

计算2019年4月19日这天,每个用户的次均停留时间,即每个用户相邻leave时刻-enter 时刻的均值。

思路:当看到求同一用户相邻状态时候,需要马上想到利用lead函数来求,如下:

查询结果:

【本题考点】

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

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

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

推荐:从零学会SQL?

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 微信大战抖音,短视频这么火吗?

    从文字时代到图片时代,再到现在的视频时代,短视频的丰富化、碎片化特点逐渐被大家接受和喜爱。很多人每天晚上睡觉之前,或者是空闲时间,总是要刷刷头条、快速翻翻短视频...

    谭庆波
  • Docker这么火,但是你真的了解吗?

    集装箱,英文名container.集装箱的出现,大大降低了货物运输的成本,实现了货物运输的标准化,以此为基础逐步建立全球范围内的船舶、港口、航线、公路、中转站、...

    Rainbond开源
  • Docker这么火,但是你真的了解吗?

    Rainbond开源
  • 看完这几点,你就会知道微服务为什么这么火爆了

    微服务体系的发展并不是一蹴而就的,经过了2014年前后的低潮期,微服务概念顶层的泡沫逐渐褪去,那些真正能够在企业落地的实践在一轮又一轮的大浪淘沙后被甄别、沉淀。...

    技术zhai
  • 推荐系统这么火,但你真的需要吗

    当下,推荐系统已然成了一门显学,也是现在热门的 AI 分支之一。最近十年尤其最近五年,借助推荐系统的技术和名头,异军突起的互联网产品越来越多,个性化推荐系统成了...

    abs_zero
  • 那些年这么火的MyCat你还不知道吗?

    今天,终于安排到Mycat系列文章了。在Mycat系列文章中,我们一起从一个利用Mycat实现分库分表的案例作为入门程序。后续会持续更新Mycat原理、架构和底...

    狼王编程
  • 【趣味】这么简单的代码这样用,你会玩吗?

    编程太累了吧?Bug太多了吧?腰太酸了吧?快来看看吧!!老九君精心为各位喜欢挑战和刺激的小伙伴们准备了一些有趣而不乏烧脑的代码,让你换换脑,活动活动逻辑思维,换...

    老九君
  • 磁盘这么分区你知道了吗?

    首先选择要分区的磁盘,主要操作的时候要两外加一个磁盘哦,sda为系统盘,千万不要随意操作。

    DataScience
  • 人工智能这么火,可你真的会用 TensorFlow?

    关键时刻,第一时间送达! ? 启 看到最近很多在讲 TensorFlow 的文章,忽然想写一些关于机器学习和算法基础类的文章,当一个东西每个人都在谈论的时候就少...

    企鹅号小编
  • 批处理框架 Spring Batch 这么强,你会用吗?

    spring batch是spring提供的一个数据处理框架。企业域中的许多应用程序需要批量处理才能在关键任务环境中执行业务操作。这些业务运营包括:

    良月柒
  • 批处理框架 Spring Batch 这么强,你会用吗?

    spring batch是spring提供的一个数据处理框架。企业域中的许多应用程序需要批量处理才能在关键任务环境中执行业务操作。这些业务运营包括:

    芋道源码
  • 批处理框架 Spring Batch 这么强,你会用吗?

    spring batch是spring提供的一个数据处理框架。企业域中的许多应用程序需要批量处理才能在关键任务环境中执行业务操作。这些业务运营包括:

    程序猿DD
  • 批处理框架 Spring Batch 这么强,你会用吗?

    spring batch是spring提供的一个数据处理框架。企业域中的许多应用程序需要批量处理才能在关键任务环境中执行业务操作。这些业务运营包括:

    Java小咖秀
  • 这么好的视频不看吗?深度学习和线代,微积分

    大家盼望的中秋节和十一已经基本都要过去了,大家是不是都玩的挺开心呀?(哎,我可没0.0,基本没离开过实验室,别认为我在学习

    zenRRan
  • Python"大"数据分析,《庆余年》为啥这么火

    今年末尾上映的古装剧《庆余年》可谓是赚足了眼球,号称投资了7个亿,集齐陈道明吴刚袁泉等一票老戏骨,实力演员张若昀李沁主演,又有新顶流肖战做配,在播出后没多久便圈...

    用户6888863
  • 敲了这么久的代码,@Transactional你真会用了吗

    首先请大家看一下下面的代码,大家觉得有问题吗?代码的期望是子事务遇到异常进行回滚,而父事务不受其影响,在外部调用addParent()后,大家觉这段代码能做到吗...

    IT大咖说
  • 这些分析你都会,为什么不能像别人一样发5+分?

    Effect of tobacco smoking on the epigenetic age of human respiratory organs吸烟对人体...

    科研菌
  • QQ可以注销你会注销吗?他们是这么说的.

    在当时要是有个诺基亚,你可就非常厉害了,天天想着挂等级,加好友,不管认不认识的都加一遍,偷菜抢车那是非常火的了!

    IT小侠公社
  • 分析B站100万+视频,发现竟然有这么多干货资源。

    共产生了41亿次的观看,2千万的弹幕,1.3亿的点赞,近7千万的投币,1.1亿的收藏,1.5千万的分享,以及1.8千万的评论。

    小F

扫码关注云+社区

领取腾讯云代金券