图解面试题:双11用户如何分析?-阿里云开发者社区

开发者社区> 数据库> 正文

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

简介:

【题目】

现有用户登录时间表,记录每个用户的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语句:

select 姓名,最后登录时间,

   row_number() over (partition by 姓名
                      order by  最后登录时间 asc) as 登录时间排名

from 用户登录表 ;

查询结果如下:

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

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

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

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

select 姓名,最后登录时间,

   dense_rank() over (partition by 姓名
                      order by 最后登录时间 asc) as 登录天数排名

from 用户登录表 ;

得出查询结果如下:

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

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

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

select 姓名,最后登录时间,

   dense_rank() over (partition by 姓名
                      order by date_format( 最后登录时间,'%Y%m&d' ) asc) as 登录天数排名

from 用户登录表 ;

查询结果如下:

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

查询代码如下:

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

select 客户姓名 ,商品名称 ,购买金额,购买时间,

   rank() over (partition by 客户姓名 
                order by 购买时间 asc ) as 下单顺序

from 双十一客户购买清单表;

查询结果如下:

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

查询结果如下:

推荐:如何从零学会sql?

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章