牛客面试题解析笔记——大数据方向

牛客面试题解析笔记——sql【19】

题目信息：红袖APP付费记录中间表u_wsd.t_od_qidian_pay_hx，该表仅有3个字段，包含日期字段ds(格式：YYYY-MM-DD，如：2020-02-28)，用户登录账号qq_no，用户当天的总付费金额hx_amt(单位：分)，请问在2020年2月份里：

Q1:累计总付费金额大于等于100元的用户及其付费总金额并按照付费金额降序排列（只取top100）；

select qq_no, sum(hx_amt) as hx_amt
from u_wsd.t_od_qidian_pay_hx
where ds>='2020-02-01' and ds<'2020-03-01'
group by qq_no
having sum(hx_amt)>=10000
order by hx_amt desc
limit 100

group by:根据用户登录账号qq_no进项分组

sum(hx_amt):根据付费金额进行求和

where：条件是日期字段ds在2020-02-01至2020-03-01之间，也就是题目要求的2020年2月份

order by:根据hx_amt进行排序，这里的hx_amt是sum(hx_amt)了，desc表示降序排序

limit：将前100条记录查询出来，但因为order by那里已经实现降序排序了（也就是从大到小排），所以limit 100后就能取到top100了

 

Q2:连续付费4天及以上的用户，同时输出该连续付费的起止日期。输出字段： qq_no, 起始日期，结束日期

--获取2020-02的消费记录，并按照qq_no分组，按照日期升序排序得到row_num
with base as ( 
select qq_no, ds,row_number() over(partition by qq_no order by ds) as row_num
from u_wsd.t_od_qidian_pay_hx
where ds>='2020-02-01'
and ds<'2020-03-01')
, 

--用日期减去其所在的排序值row_num，获取一个"差值"日期，同一个qq_no下该日期相同的则为连续。
aa as ( 
select qq_no,ds,row_num,date_sub(ds, row_num) as date_rank
from base),

--若连续则"差值"日期的前一天为起始日期，再根据"差值"日期分组按照排序值row_num降序排序为获取结束日期做准备。
res_pre as ( 
select qq_no,ds,date_add(date_rank,1) as start_date,row_number() over(partition by date_rank order by row_num desc) as rn 
from aa)
select qq_no as qq_no,start_date as start_date,ds as end_date
from res_pre
where rn=1 and datediff(end_date, start_date)>= 3 --连续4天及以上

解析：

with....as....用法:

在本例子中相当于建了3个名字为base,aa,res_pre的临时表，放在with ... as的括号()里面的sql语句执行后的结果就是临时表的数据，with...as()中as前的那一个就是别名，

with...as()，...as(),...as()多个临时表用逗号连接，后面的查询就可以用它，这样对于大批量的sql语句起到一个优化的作用，而且清楚明了。详细解释和用法可以自行去了解一下

row_number() over(partition by qq_no order by ds) as row_num:

按照qq_no分组，按照日期组内分别升序排序得到row_num

date_sub:函数从日期减去指定的时间间隔,例如

--假如ds=2020-02-04 row_num=3
date_sub(ds,row_num) as date_rank
--最终结果就是date_rank=2020-02-01

date_add():函数在日期中添加或减去指定的时间间隔，第一个参数是合法的日期表达式。第二个参数是您希望添加的间隔数；对于未来的时间，此数是正数，对于过去的时间，此数是负数。例如：

--假如date_rank=2020-02-25
date_add(date_rank,1) as start_date
--最终的结果就是start_date=2020-02-26

datediff() 函数:返回两个日期之间的时间，例如：

--假如end_date=2020-02-07,start_date=2020-02-01
datediff(end_date, start_date)
--那最终的结果就是 6

--假如end_date=2020-02-07,start_date=2020-02-09
datediff(end_date, start_date)
--那最终的结果就是 -2

 为了更直观理解答案的sql执行，模拟一下base,aa,res_pre每个的临时表大致会变成什么样子

step01:

with base as ( 
select qq_no, ds,row_number() over(partition by qq_no order by ds) as row_num
from u_wsd.t_od_qidian_pay_hx
where ds>='2020-02-01'
and ds<'2020-03-01'),

base表

qq_no	ds	row_num
QQ号01	2020-02-01	1
QQ号01	2020-02-02	2
QQ号01	2020-02-03	3
QQ号01	2020-02-04	4
QQ号01	2020-02-05	5
QQ号02	2020-02-01	1
QQ号02	2020-02-05	2
QQ号02	2020-02-06	3
QQ号02	2020-02-07	4
QQ号03	2020-02-15	1
QQ号03	2020-02-16	2
QQ号03	2020-02-17	3
QQ号03	2020-02-18	4
QQ号04	2020-02-13	1

 

step02:

--用日期减去其所在的排序值row_num，获取一个"差值"日期，同一个qq_no下该日期相同的则为连续。
aa as ( 
select qq_no,ds,row_num,date_sub(ds, row_num) as date_rank
from base),

aa表

qq_no	ds	row_num	date_rank（ds - row_num）
QQ号01	2020-02-01	1	2020-01-31  (2020-02-01 减 1天)
QQ号01	2020-02-02	2	2020-01-31
QQ号01	2020-02-03	3	2020-01-31
QQ号01	2020-02-04	4	2020-01-31
QQ号01	2020-02-05	5	2020-01-31
QQ号02	2020-02-01	1	2020-01-31
QQ号02	2020-02-05	2	2020-02-03
QQ号02	2020-02-06	3	2020-02-03
QQ号02	2020-02-07	4	2020-02-03
QQ号03	2020-02-15	1	2020-02-14
QQ号03	2020-02-16	2	2020-02-14
QQ号03	2020-02-17	3	2020-02-14
QQ号03	2020-02-18	4	2020-02-14
QQ号04	2020-02-13	1	2020-02-12

同一个QQ号下date_rank相同才表示连续，QQ号02的第一个date_rank跟其余三个不一样，所以只有三个连续

QQ号02

2020-02-01

1

2020-01-31

 

step03:

--若连续则"差值"日期的前一天为起始日期，再根据"差值"日期分组按照排序值row_num降序排序为获取结束日期做准备。
res_pre as ( 
select qq_no,ds,date_add(date_rank,1) as start_date,row_number() over(partition by date_rank order by row_num desc) as rn 
from aa)

 res_pre表

qq_no	ds	start_date(date_rank + 1)	rn
QQ号01	2020-02-05	2020-02-01(2020-01-31 + 1)	1
QQ号01	2020-02-04	2020-02-01	2
QQ号01	2020-02-03	2020-02-01	3
QQ号01	2020-02-02	2020-02-01	4
QQ号01	2020-02-01	2020-02-01	5
QQ号02	2020-02-07	2020-02-04 (2020-02-03 + 1)	1
QQ号02	2020-02-06	2020-02-04	2
QQ号02	2020-02-05	2020-02-04	3
QQ号02	2020-02-01	2020-02-01 (2020-01-31 + 1)	4
QQ号03	2020-02-18	2020-02-15 (2020-02-14 + 1)	1
QQ号03	2020-02-17	2020-02-15	2
QQ号03	2020-02-16	2020-02-15	3
QQ号03	2020-02-15	2020-02-15	4
QQ号04	2020-02-13	2020-02-13	1

 

step04:

select qq_no as qq_no,start_date as start_date,ds as end_date
from res_pre
where rn=1 and datediff(end_date, start_date)>= 3 --连续4天及以上

最终结果

qq_no	start_date	end_date
QQ号01	2020-02-01	2020-02-05
QQ号03	2020-02-15	2020-02-18