前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >图解面试题:双11用户如何分析?

图解面试题:双11用户如何分析?

原创
作者头像
猴子聊数据分析
修改2020-06-02 10:27:38
1.7K0
修改2020-06-02 10:27:38
举报
文章被收录于专栏:猴子聊数据分析

【题目】

现有用户登录时间表,记录每个用户的id,姓名,邮箱地址和用户最后登录时间。表如下:

问题:生成一张临时表(表名:用户登录表),表中呈现四列数据分别为:姓名,最后登录时间,登录时间排名,登录天数排名

要求

1.“登录时间排名”这一列:按时间给出每个人的登录次数,登录时间最早的为1,之后的分别是2,3,4等。

2.“登录天数排名”这一列:按天给出每个人的登录次数,同一天多次登录认为是同一次,最早标记为1,之后以此类推。

【解题思路】

题目没看懂,对吧?没事,使用逻辑树分析方法,将复杂问题拆解为简单问题。

看看我们已有的字段有:姓名,最后登录时间,未知的字段 :登录时间排名,登录天数排名两列,要求的表格如下图:

接下来看如何得到这个表里的每一列。

1.“登录时间排名”这一列:按时间给出每个人的登录次数,登录时间最早的为1,之后的分别是2,3,4等。

 (1)当“每个”出现的时候你能想到什么?

就是《猴子 从零学会sql》里讲过的:每个出现的时候,就要想到是分组汇总。

表里能区分“每个人”的是“姓名”,所以按“姓名”来分组(group by或者窗口函数的partiotion  by)。

汇总的要求是“登录次数”(登录时间最早的为1,之后的分别是2,3,4等),这句话翻译成大白话就是用登录时间来排序,最早登录记1,第二次登录记2 ,依次排序。也就是排名。

因为是排名问题,所以使用分组(窗口函数partiotion by 姓名),并按最后登录时间升序排列(order by最后登录时间 asc),套入窗口函数的语法,得出下面的sql语句:

代码语言:javascript
复制
select 姓名,最后登录时间,       row_number() over (partition by 姓名                          order by  最后登录时间 asc) as 登录时间排名from  用户登录表 ;

查询结果如下:

2.“登录天数排名”这一列:按天给出每个人的登录次数,同一天多次登录认为是同一次,最早标记为1,之后以此类推。

同一天多次登录认为是同一次,也就是同一天排名相同,我们用test1登录次数举例,如下图:

排名问题,要使用窗口函数。因为同一天登录的记录排名相同,不占用下一名次排名,所以用dense_rank函数,

按姓名分组(partiotion by,并按最后登录天数升序排列(order by,升序asc)。套入窗口函数的语法,得出下面的sql语句:

代码语言:javascript
复制
select 姓名,最后登录时间,       dense_rank() over (partition by 姓名                          order by 最后登录时间 asc) as 登录天数排名from  用户登录表 ;

得出查询结果如下:

发现上面的查询结果里“登录天数排名”里是登录时间,而题目要求是登录天数,所以需要使用date_format(date,format)来转化为时间格式:

date_format(最新登录时间,’%Y%m%d’)。

套入上面的sql语句中就是:

代码语言:javascript
复制
select 姓名,最后登录时间,       dense_rank() over (partition by 姓名                          order by date_format( 最后登录时间,'%Y%m&d' ) asc) as 登录天数排名from  用户登录表 ;

查询结果如下:

3.将得到的2列合并到题目要求的表中

查询代码如下:

代码语言:javascript
复制
select 姓名,最后登录时间,       row_number() over (partition by 姓名                          order by 最后登录时间 asc) as 登录时间排名,       dense_rank() over (partition by 姓名                          order by date_format( 最后登录时间,'%Y%m&d' ) asc) as 登录天数排名from  用户登录表 ;

查询结果如下:

【本题考点】

1.考察逻辑思维能力,如何将复杂问题转化为可以解决的子问题能力。这时候可以使用逻辑树分析方法。

2.本题本质上是“分组排名”问题,就要想到用窗口函数。

3.考察窗口函数的 dense_rank, row_number的区别和使用。

dense_rank函数,如果有并列名次的行,不占用下一名次的位置(即1,1,2,3);row_number函数:不考虑并列名次的情况(即1,2,3,4)。

4.考察时间格式的数据如何灵活转换,用date_format(date,format)根据format的指定的格式显示date值。

【举一反三】

下表是双十一客户购买清单表。双十一当天,商家为每位客户购买的第一个商品免单,请找出商品名称,客户姓名及购买金额。

参考答案:

1. 双十一当天每个客户(分组)第一个下单的商品(按购买时间来排名),是“分组排名”问题,使用窗口函数来解决。

代码语言:javascript
复制
select 客户姓名 ,商品名称 ,购买金额,购买时间,       rank() over (partition by 客户姓名                     order by 购买时间 asc ) as 下单顺序from 双十一客户购买清单表;

查询结果如下:

2.题目要求是商家为每位客户购买的第一个商品免单,接着上面的查询结果写,可以加入一个where子句把“下单顺序”值为1,把第1个商品筛选出来。

查询结果如下:

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

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

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

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

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档