红袖APP付费记录中间表u_wsd.t_od_qidian_pay_hx,该表仅有3个字段,包含日期字段ds(格式:YYYY-MM-DD,如:2020 -02 -28 ),用户登录账号qq_no,用户当天的总付费金额hx_amt(单位:分),请问在2020年2月份里: 1 、累计总付费金额大于等于100元的用户及其付费总金额并按照付费金额降序排列(只取top100); 2 、连续付费4天及以上的用户,同时输出该连续付费的起止日期。输出字段: qq_no, 起始日期,结束日期 【建议:脚本书写整齐很关键!利用with语法子查询,分步骤写清注释】-笔试面试资料

这是qklbishe.com第6399 篇笔试面试资料
提供答案分析,通过本文《红袖APP付费记录中间表u_wsd.t_od_qidian_pay_hx,该表仅有3个字段,包含日期字段ds(格式:YYYY-MM-DD,如:2020 -02 -28 ),用户登录账号qq_no,用户当天的总付费金额hx_amt(单位:分),请问在2020年2月份里: 1 、累计总付费金额大于等于100元的用户及其付费总金额并按照付费金额降序排列(只取top100); 2 、连续付费4天及以上的用户,同时输出该连续付费的起止日期。输出字段: qq_no, 起始日期,结束日期 【建议:脚本书写整齐很关键!利用with语法子查询,分步骤写清注释】-笔试面试资料》可以理解其中的代码原理,这是一篇很好的求职学习资料
本站提供程序员计算机面试经验学习,笔试经验,包括字节跳动/头条,腾讯,阿里,美团,滴滴出行,网易,百度,京东,小米,华为,微软等互联网大厂真题学习背诵。

答案:

红袖APP付费记录中间表u_wsd.t_od_qidian_pay_hx,该表仅有3个字段,包含日期字段ds(格式:YYYY-MM-DD,如:20200228),用户登录账号qq_no,用户当天的总付费金额hx_amt(单位:分),请问在2020年2月份里:
1、累计总付费金额大于等于100元的用户及其付费总金额并按照付费金额降序排列(只取top100);
2、连续付费4天及以上的用户,同时输出该连续付费的起止日期。输出字段: qq_no, 起始日期,结束日期
【建议:脚本书写整齐很关键!利用with语法子查询,分步骤写清注释】

红袖APP付费记录中间表u_wsd.t_od_qidian_pay_hx,该表仅有3个字段,包含日期字段ds(格式:YYYY-MM-DD,如:2020 -02 -28 ),用户登录账号qq_no,用户当天的总付费金额hx_amt(单位:分),请问在2020年2月份里:          1 、累计总付费金额大于等于100元的用户及其付费总金额并按照付费金额降序排列(只取top100);          2 、连续付费4天及以上的用户,同时输出该连续付费的起止日期。输出字段: qq_no, 起始日期,结束日期          【建议:脚本书写整齐很关键!利用with语法子查询,分步骤写清注释】 江小虾米
第二题我认为最后的那里应该改成这样才是对的:
datediff(end_date,start_date)>= 3 

这样才表示连续4天及4天以上
–假如end_date=2020-02-04,start_date=2020-02-01

datediff(end_date, start_date)

–那最终的结果就是 3,虽然不满足>=4,但是2020-02-01至2020-02-04确实已经是连续四天了,答案似乎有些不严谨

我的解析如下:

with….as….用法:

在本例子中相当于建了个名字为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分组,按照日期ds组内分别升序排序得到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
QQ号01 2020-02-01 1 2020-01-31
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

2021-01-30 23:39:39 回复(0)

文章部分来自互联网,侵权联系删除
www.qklbishe.com

区块链毕设网(www.qklbishe.com)全网最靠谱的原创区块链毕设代做网站
部分资料来自网络,侵权联系删除!
资源收费仅为搬运整理打赏费用,用户自愿支付 !
qklbishe.com区块链毕设代做网专注|以太坊fabric-计算机|java|毕业设计|代做平台 » 红袖APP付费记录中间表u_wsd.t_od_qidian_pay_hx,该表仅有3个字段,包含日期字段ds(格式:YYYY-MM-DD,如:2020 -02 -28 ),用户登录账号qq_no,用户当天的总付费金额hx_amt(单位:分),请问在2020年2月份里: 1 、累计总付费金额大于等于100元的用户及其付费总金额并按照付费金额降序排列(只取top100); 2 、连续付费4天及以上的用户,同时输出该连续付费的起止日期。输出字段: qq_no, 起始日期,结束日期 【建议:脚本书写整齐很关键!利用with语法子查询,分步骤写清注释】-笔试面试资料

提供最优质的资源集合

立即查看 了解详情