sql技巧之开窗函数rank()的使用

今天分享一篇交流群里群友的问题和某群友的解答！

题目

当前数据库的”testsql”日志中，存在日期字段”gl_dt”(varchar)和玩具id字段”loan_amt”(varchar)，现需求找出每个月相较于上个月新增玩具名，和每个月相较于下个月失去的玩具名。以下为数据样例展示：

gl_dt loan_amt
varchar varchar
2021/2/28 5001273101
2021/2/28 5001192158
2021/2/28 5001174891
2021/2/28 5001066042
2021/2/28 5001243311
2021/3/31 5001193986
2021/3/31 5001273101
2021/3/31 5001066042
2021/3/31 5001243311
2021/4/30 5001193986
2021/4/30 5001273101
2021/4/30 5001192158
2021/4/30 5001174891
2021/5/31 5001193986
2021/5/31 5001273101
2021/5/31 5001192158
2021/5/31 5001174891
2021/5/31 5001066043
2021/5/31 5001243312

思路分析/踩坑总结

清洗数据

日期是varchar格式，根据个人实际代码思路考虑是否能转为date格式，后清洗至“每月”的格式。这里我用的是 substr(GL_DT,1,7)，不转日期格式，只保留日期的前7位。

如何解题

• “上月新增”和“下月失去”的概念，可以抽象并统称为“回流”或“流失”。
• 回流概念简述：用户在某日登录时，前x日没有登录记录（题里不设计新增排除问题）。根据题目可理解为，玩具于上月未记录，本月出现“回流”玩具。
• 流失概念简述：用户在某日登录后，在x日内不再登录即为流失。根据题目可理解为，玩具在本月记录后，下月不记录即为流失

使用sql处理回流/流失的方法简述如下：

a）使用left join

b）Left join的左右表完全一致。示例中使用group by主要怕实际代码里可能有重复值，如果没有可以忽略，dense_rank()先跳过，下面会讲。示例代码：

(select LOAN_AMT
       ,substr(GL_DT,1,7) as date1
       ,dense_rank()over(order by substr(GL_DT,1,7))as rn
       from testsql
       group by LOAN_AMT,substr(GL_DT,1,7)
       )as a
left join(select LOAN_AMT
       ,substr(GL_DT,1,7)as date2
       ,dense_rank()over(order by substr(GL_DT,1,7))as rn
       from testsql
       group by LOAN_AMT,substr(GL_DT,1,7)
       )as b

c）on后匹配条件保证”玩具id”一致,匹配周期相差一个月 示例代码：

on a.LOAN_AMT = b.LOAN_AMT and date_diff(a.date1,b.date2) = 1

d）最后取匹配的不相交部分，即为“回流”或“流失”值 实际代码：where b.LOAN_AMT is null

为什么要用rank()over()开窗函数

Left join使用方法中，date_diff()的使用方法是错的，GL_DT不是date格式，不能使用这个函数，需要使用rank()over()达到替代效果并满足“回流”或“流失”中的减法匹配定义（下月-上月=1）。

具体的分级效果为，从最小的月份开始排序，rank级别为1，每增加一个月，rank+1，同月的所有数据处于同一rank下。

示例代码：dense_rank()over(order by substr(GL_DT,1,7))as rn

dense_rank()，rank()，row_number()的区别

a）row_number():不考虑数据的重复性 按照顺序一次打上标号 如：1 2 3 4

b）rank()是跳跃排序, 如：1 2 2 4会跳过3

c）dense_rank()是连续排序, 如：1 2 2 3 序号连续 这里使用dense_rank()作为排序函数，如果使用rank()，假设现在一共有10w条数据，rank的排序结尾值为10w，会失去月份排序的效果

多次left join需要注意的问题

如题所示，“回流”和“流失”给数据使用者展示时，其实最好分列展示，分列需要做两次left join，每次left join时，都要注意主表是否会产生变化，比如出现一对多的情况导致数据翻倍，写了where xxx is null导致主表变化。

完整代码展示

select a.LOAN_AMT as "玩具id"
       ,a.date1  as "日期"
       ,b.date2   as "下月未失去日期"
       ,c.date3   as "上月新增日期"
      from
(select LOAN_AMT
       ,substr(GL_DT,1,7) as date1
       ,dense_rank()over(order by substr(GL_DT,1,7))as rn
       from testsql
       group by LOAN_AMT,substr(GL_DT,1,7)
       )as a
left join(select LOAN_AMT
       ,substr(GL_DT,1,7)as date2
       ,dense_rank()over(order by substr(GL_DT,1,7))as rn
       from testsql
       group by LOAN_AMT,substr(GL_DT,1,7)
       )as b
on a.LOAN_AMT = b.LOAN_AMT
 and b.rn-a.rn = 1
left join(select LOAN_AMT
       ,substr(GL_DT,1,7)as date3
       ,dense_rank()over(order by substr(GL_DT,1,7))as rn
       from testsql
       group by LOAN_AMT,substr(GL_DT,1,7)
       )as c
on a.LOAN_AMT = c.LOAN_AMT
 and a.rn-c.rn = 1
where c.LOAN_AMT is  null
 or b.LOAN_AMT is  null