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腾讯云开发者
