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

查询结果如下：