图解面试题：双 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？