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

大数据笔试题 专栏收录该内容
1 篇文章 0 订阅

牛客面试题解析笔记——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_nodsrow_num
QQ号012020-02-011
QQ号012020-02-022
QQ号012020-02-033
QQ号012020-02-044
QQ号012020-02-055
QQ号022020-02-011
QQ号022020-02-052
QQ号022020-02-063
QQ号022020-02-074
QQ号032020-02-151
QQ号032020-02-162
QQ号032020-02-173
QQ号032020-02-184
QQ号042020-02-131

 

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_nodsrow_numdate_rank(ds - row_num)
QQ号012020-02-0112020-01-31  (2020-02-01 减 1天)
QQ号012020-02-0222020-01-31
QQ号012020-02-0332020-01-31
QQ号012020-02-0442020-01-31
QQ号012020-02-0552020-01-31
QQ号022020-02-0112020-01-31
QQ号022020-02-0522020-02-03
QQ号022020-02-0632020-02-03
QQ号022020-02-0742020-02-03
QQ号032020-02-1512020-02-14
QQ号032020-02-1622020-02-14
QQ号032020-02-1732020-02-14
QQ号032020-02-1842020-02-14
QQ号042020-02-1312020-02-12

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

QQ号022020-02-0112020-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_nodsstart_date(date_rank + 1)rn
QQ号012020-02-052020-02-01(2020-01-31 + 1)1
QQ号012020-02-042020-02-012
QQ号012020-02-032020-02-013
QQ号012020-02-022020-02-014
QQ号012020-02-012020-02-015
QQ号022020-02-072020-02-04 (2020-02-03 + 1)1
QQ号022020-02-062020-02-04 2
QQ号022020-02-052020-02-04 3
QQ号022020-02-012020-02-01 (2020-01-31 + 1)4
QQ号032020-02-182020-02-15 (2020-02-14 + 1)1
QQ号032020-02-172020-02-152
QQ号032020-02-162020-02-153
QQ号032020-02-152020-02-154
QQ号042020-02-132020-02-131

 

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_nostart_dateend_date
QQ号012020-02-012020-02-05
QQ号032020-02-152020-02-18

 

  • 0
    点赞
  • 2
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值