【题目】
现有用户登录时间表，记录每个用户的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值。
【举一反三】
下表是双十一客户购买清单表。双十一当天，商家为每位客户购买的第一个商品免单，请找出商品名称，客户姓名及购买金额。
参考答案：
- 双十一当天每个客户（分组）第一个下单的商品（按购买时间来排名），是“分组排名”问题，使用窗口函数来解决。
select 客户姓名 ,商品名称 ,购买金额,购买时间,
rank() over (partition by 客户姓名
order by 购买时间 asc ) as 下单顺序from 双十一客户购买清单表;
查询结果如下：
2.题目要求是商家为每位客户购买的第一个商品免单，接着上面的查询结果写，可以加入一个where子句把“下单顺序”值为1，把第1个商品筛选出来。
查询结果如下：
推荐：如何从零学会sql？