摘要：
第 1 题 连续问题 第4题打折日期交叉问题如下为平台商品促销数据：字段为品牌，打折开始日期，打折结束日期brandsttedtoppo2021-06-052021-06-09oppo2021-06-112021-06-21vivo2021-06-052021-06-15vivo2021-06-092021-06-21redmi2021-06-052021-06-21redmi2021-06-092021-06-15redmi2021-06-172021-06-26huawei2021-06-052021-06-26huawei2021-06-092021-06-15huawei2021-06-172021-06-21计算每个品牌总的打折销售天数，注意其中的交叉日期，比如vivo品牌，第一次活动时间为2021-06-05到2021-06-15，第二次活动时间为2021-06-09到2021-06-21其中9号到15号为重复天数，只统计一次，即vivo总打折天数为2021-06-05到2021-06-21共计17天。第5题同时在线问题如下为某直播平台主播开播及关播时间，根据该数据计算出平台最高峰同时在线的主播人数。
如下数据为蚂蚁森林中用户领取的减少碳排放量
id dt lowcarbon 1001 2021-12-12 123 1002 2021-12-12 45 1001 2021-12-13 43 1001 2021-12-13 45 1001 2021-12-13 23 1002 2021-12-14 45 1001 2021-12-14 230 1002 2021-12-15 45 1001 2021-12-15 23… …
找出连续 3 天及以上减少碳排放量在 100 以上的用户
第 2 题 分组问题如下为电商公司用户访问时间数据
id ts(秒)
1001 17523641234
1001 17523641256
1002 17523641278
1001 17523641334
1002 17523641434
1001 17523641534
1001 17523641544
1002 17523641634
1001 17523641638
1001 17523641654某个用户连续的访问记录如果时间间隔小于 60 秒，则分为同一个组，结果为：
id ts(秒) group
1001 17523641234 1
1001 17523641256 1
1001 17523641334 2
1001 17523641534 3
1001 17523641544 3
1001 17523641638 4
1001 17523641654 4
1002 17523641278 1
1002 17523641434 2
1002 17523641634 3某游戏公司记录的用户每日登录数据
id dt
1001 2021-12-12
1002 2021-12-12
1001 2021-12-13
1001 2021-12-14
1001 2021-12-16
1002 2021-12-16
1001 2021-12-19
1002 2021-12-17
1001 2021-12-20计算每个用户最大的连续登录天数，可以间隔一天。解释：如果一个用户在 1,3,5,6 登录游戏，则视为连续 6 天登录。
第 4 题 打折日期交叉问题如下为平台商品促销数据：字段为品牌，打折开始日期，打折结束日期
brand stt edt oppo 2021-06-05 2021-06-09oppo 2021-06-11 2021-06-21vivo 2021-06-05 2021-06-15vivo 2021-06-09 2021-06-21redmi 2021-06-05 2021-06-21redmi 2021-06-09 2021-06-15redmi 2021-06-17 2021-06-26huawei 2021-06-05 2021-06-26huawei 2021-06-09 2021-06-15huawei 2021-06-17 2021-06-21
计算每个品牌总的打折销售天数，注意其中的交叉日期，比如 vivo 品牌，第一次活动时间为 2021-06-05 到 2021-06-15，第二次活动时间为 2021-06-09 到 2021-06-21 其中 9 号到 15号为重复天数，只统计一次，即 vivo 总打折天数为 2021-06-05 到 2021-06-21 共计 17 天。
第 5 题 同时在线问题如下为某直播平台主播开播及关播时间，根据该数据计算出平台最高峰同时在线的主播人数。
id stt edt
1001 2021-06-14 12:12:12 2021-06-14 18:12:12
1003 2021-06-14 13:12:12 2021-06-14 16:12:12
1004 2021-06-14 13:15:12 2021-06-14 20:12:12
1002 2021-06-14 15:12:12 2021-06-14 16:12:12
1005 2021-06-14 15:18:12 2021-06-14 20:12:12
1001 2021-06-14 20:12:12 2021-06-14 23:12:12
1006 2021-06-14 21:12:12 2021-06-14 23:15:12
1007 2021-06-14 22:12:12 2021-06-14 23:10:12第一题：
1)按照用户ID以时间字段分组，计算每个用户单日减少的碳排放量大于100的
selectid, dt, sum(lowcarbon) lowcarbon fromtest1 group byid,dt having lowcarbon > 100;t1
2)等差数列:两个等差数列若等差相同，则相同位置的数列相减得到的结果相同
按照用户分组，同时按照时间排序，计算每条数据的Rank值
selectid, dt, lowcarbon, rank() over(partition by id order bydt) rk from t1;t2
3)将每行数据中的日期减去Rank值
selectid, dt, lowcarbon, date_sub(dt,rk) flag from t2;t3
4)按照用户以及flag分组，找出记录大于等于3条的
selectid, flag, count(*) num fromt3 group byid,flag having num >= 3;
5)最终的HQL
selectid, flag, count(*) num from( selectid, dt, lowcarbon, date_sub(dt,rk) flag from( selectid, dt, lowcarbon, rank() over(partition by id order bydt) rk from( selectid, dt, sum(lowcarbon) lowcarbon fromtest1 group byid,dt having lowcarbon > 100) t1 ) t2 ) t3 group byid,flag having num >= 3;
第二题：
1)将上一行时间数据下移
selectid, ts, lag(ts,1,0) over(partition by id order byts) lagts from test2;t1
2)将当前行的时间减去上一行的时间
selectid, ts, ts-lagts diffts from t1;t2
3)每个用户范围内从第一行到当前行，判断diffts是否大于等于60，若diffts>=60，则加1，否则不变
selectid, ts, sum(if(diffts>=60,1,0)) over(partition by id order byts) groupid from t2;t3
4)最终的HQL
selectid, ts, sum(if(diffts>=60,1,0)) over(partition by id order byts) groupid from( selectid, ts, ts-lagts diffts from( selectid, ts, lag(ts,1,0) over(partition by id order byts) lagts fromtest2 ) t1 ) t2
第三题：
1)将上一行时间下移
selectid, dt, lag(dt,1,'1970-01-01') lagdt from test3;t1
2)将当前行时间减去上一行时间
selectid, dt, datediff(dt,lagdt) diffdt from t1;t2
3)按照用户分组，并按时间排序，计算第一行到当前行大于2的数据总条数
selectid, dt, sum(if(diffdt>2,1,0)) groupid from t2;t3
4)按照用户和groupid分组，求最大时间减去最小时间，然后加1
selectid, groupid, (max(dt)-min(dt)+1) maxday from t3;t4
5)取连续登录天数的最大值
selectid, max(maxday) loginmax fromt4 group by id
6)最终的HQL
selectid, max(maxday) loginmax from( selectid, groupid, (max(dt)-min(dt)+1) maxday from( selectid, dt, sum(if(diffdt>2,1,0)) groupid from( selectid, dt, datediff(dt,lagdt) diffdt from( selectid, dt, lag(dt,1,'1970-01-01') lagdt fromtest3 ) t1 ) t2 ) t3 ) t4 group by id
第四题：
1)将当前行以前的数据中最大的edt放置当前行
selectid, stt, edt, max(edt) over(partition by id order by stt rows unbounded preceding and 1preceding) maxEdt from test4;t1
2)比较开始时间与移动下来的时间，若开始时间大，则不需要操作，否则，需要将移动下来的时间加1替换当前行的开始时间，若第一行数据，maxEdt为null，不需要操作
selectid, if(maxEdt is null,stt,if(stt>maxEdt,stt,date_add(maxEdt,1))) stt, edt from t1;t2
3)将每行数据中的结束日期减去开始日期
selectid, datediff(edt,stt) day from t2;t3
4)按照品牌分组，计算每条数据加1的总和
selectid, sum(if(day>=0,day+1,0)) days fromt3 group by id;
5)最终的HQL
selectid, sum(if(day>=0,day+1,0)) days from( selectid, datediff(edt,stt) day from( selectid, if(maxEdt is null,stt,if(stt>maxEdt,stt,date_add(maxEdt,1))) stt, edt from( selectid, stt, edt, max(edt) over(partition by id order by stt rows unbounded preceding and 1preceding) maxEdt fromtest4 ) t1 ) t2 ) t3 group by id;
第五题：
1)对数据分类，在开始时间后添加1，表示有主播上线，同时在关闭时间后添加-1，表示有主播下线
selectid, stt dt, 1p fromtest5 union selectid, edt dt, -1p from test5;t1
2)按照时间排序，计算累加人数
selectid, dt, sum(p) over(order bydt) sum_p from t1;t2
3)找出同时在线人数最大值
select max(sum_p) from t2;
4)最终的HQL
select max(sum_p) from( selectid, dt, sum(p) over(order bydt) sum_p from( selectid, stt dt, 1p fromtest5 union selectid, edt dt, -1p fromtest5 ) t1 ) t2;