图解面试题：双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个商品筛选出来。

查询结果如下：