当前位置:   article > 正文

尚硅谷hql面试题_尚硅谷hql刷题网站

尚硅谷hql刷题网站

一、连续问题

如下数据为蚂蚁森林中用户领取的减少碳排放量,找出连续 3 天及以上减少碳排放量在 100 以上的用户。

table1
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

重点思路

连续问题解法思路是生成一个公差相同的数列,两个公差相同的数列依次相减,则结果必然是相同的且值为公差,这样就可以用一个连续的数列来判断所求列是否连续,如下图,相同的结果的为一组,必连续。

步骤1,按照用户id和时间分组,计算出每个用户每天减少的碳排放量
select id,dt,sum(lowcarbon) lowcarbon
from table1
group by id,dt;t1
  • 1
  • 2
  • 3
步骤2,生成等差数列
select id,dt,lowcarbon,rank() over(partition by id order by dt) rk
from t1;t2
  • 1
  • 2
步骤3,将时间列与生成的等差数列相减,得到标志列flag,用于判断连续
select id,dt,lowcarbon,date_sub(dt,rk) flag
from t2;t3
  • 1
  • 2
步骤4,利用标志列,将连续列分组,输出减少碳排放量列大于100的记录条数大于三条的用户id
select id,sum(if(lowcarbon>100,1,0)) s
from t3
group by id,flag
having s>=3;
  • 1
  • 2
  • 3
  • 4
最终hql
select id,sum(if(lowcarbon>100,1,0)) s
from (select id,dt,lowcarbon,date_sub(dt,rk) flag
from (select id,dt,lowcarbon,rank() over(partition by id order by dt) rk
from (select id,dt,sum(lowcarbon) lowcarbon
from table1
group by id,dt)t1)t2)t3
group by id,flag
having s>=3;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

二、分组问题

如下为电商公司用户访问时间数据,某个用户连续的访问记录如果时间间隔小于 60 秒,则分为同一个组。

table2
id ts(秒)
1001 17523641234
1001 17523641256
1002 17523641278
1001 17523641334
1002 17523641434
1001 17523641534
1001 17523641544
1002 17523641634
1001 17523641638
1001 17523641654

重点思路

计算时间间隔的方法是先对用户进行分组,并按照时间排序,然后将ts列整体下移作为新列,然后错位相减,得到相邻时间点之间的时间间隔,在进行判断,小于60分为同一个组,即排序后每出现一次大于60的记录,则组数加一

步骤1,按照id开窗并按照ts进行排序,利用lag()和over()生成新列next_ts,值为上一行的时间值
select id,ts,lag(ts,1,0) over(partition by id order by ts) lag_ts
from table2;t1
  • 1
  • 2
步骤2,计算出时间和上一行时间之间的差值diff_ts
select id,ts,ts-lag_ts diff_ts
from t1;t2
  • 1
  • 2
步骤3,计算每个用户范围内第一行到当前行记录中时间差大于60的记录条数,即为分组
select id,ts,sum(if(diff_ts>=60,1,0)) over(partition by id order by ts) groupid
from t2;
  • 1
  • 2
最终hql
select id,ts,sum(if(diff_ts>=60,1,0)) over(partition by id order by ts) groupid
from (select id,ts,ts-lag_ts diff_ts
from (select id,ts,lag(ts,1,0) over(partition by id order by ts) lag_ts
from table2)t1)t2;
  • 1
  • 2
  • 3
  • 4

三、连续间隔问题

如下是某游戏公司记录的用户每日登录数据,计算每个用户最大的连续登录天数,可以间隔一天。解释:如果一个用户在 1,3,5,6 登录游戏,则视为连续 6 天登录。

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,即为连续天数,找出同一个id中的最大的连续天数即可。
这里查询连续天数的时候有一个小细节,就是不能直接“datediff()+1”这么写,语法有错误,用date_add()函数将大日期提前一天或者用data_sub()函数将小日期推后一天在计算日期差值,而且date相关函数都是可以直接用来计算符合日期格式的字符串的,不需要进行类型转换。我这里写了,也是可以正常执行的,不过属于是画蛇添足了。

步骤1,按照id分区、dt排序开窗,获得上一行dt作为新列
select id,dt,lag(dt,1,'2000-01-01') over(partition by id order by dt) lag_dt
from table3;
  • 1
  • 2
步骤2,日期和上一行日期做差,得到间隔日期
select id,dt,datediff(dt,lag_dt) diff_dt
from t1;
  • 1
  • 2
步骤3,按照用户分组,同时按照时间进行排序,计算每组第一行到当前行的diff_dt>=2的记录总条数sum(if(diff_dt>=2,1,0)),作为组号
select id,dt,sum(if(diff_dt>2,1,0)) over(partition by id order by dt) groupid
from t2;
  • 1
  • 2
步骤4,按照id和组号分组(hql语法:分组中必须出现查询字段,即使分组条件不需要id,也得写上),求最大时间和最小时间的差并加一作为连续天数
select id,datediff(max(date_add(to_date(dt),1)),min(to_date(dt))) day
from t3
group by groupid;
  • 1
  • 2
  • 3
步骤5,取每个用户连续登录的最大值
select id,max(day) day
from t4
group by id;
  • 1
  • 2
  • 3
最终hql
select id,max(day) day
from (select id,datediff(max(date_add(to_date(dt),1)),min(to_date(dt))) day
from (select id,dt,sum(if(diff_dt>2,1,0)) over(partition by id order by dt) groupid
from (select id,dt,datediff(cast(dt as date),cast(lag_dt as date)) diff_dt
from (select id,dt,lag(dt,1,'2000-01-01') over(partition by id order by dt) lag_dt
from table3)t1)t2)t3
group by id,groupid)t4
group by id;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

四、打折日期交叉问题

如下为平台商品促销数据:字段为品牌,打折开始日期,打折结束日期,计算每个品牌总的打折销售天数,注意其中的交叉日期,比如 vivo 品牌,第一次活动时间为 2021-06-05 到 2021-06-15,第二次活动时间为 2021-06-09 到 2021-06-21 其中 9 号到 15号为重复天数,只统计一次,即 vivo 总打折天数为 2021-06-05 到 2021-06-21 共计 17 天。

brand stt edt
oppo 2021-06-05 2021-06-09
oppo 2021-06-11 2021-06-21
vivo 2021-06-05 2021-06-15
vivo 2021-06-09 2021-06-21
redmi 2021-06-05 2021-06-21
redmi 2021-06-09 2021-06-15
redmi 2021-06-17 2021-06-26
huawei 2021-06-05 2021-06-26
huawei 2021-06-09 2021-06-15
huawei 2021-06-17 2021-06-21

重点思路

要判断交叉,是否有日期重合,重合和不重合的日期是不同的计算方式,也不可以直接用最大日期减去最小日期,中间会有断层,要将每个品牌的数据放在一起,整体进行考虑,将当前行之前的最大的结束日期传下来,和当前行的开始日期进行比较,由此来判断是否有日期重合,若最大结束日期小于当前行的开始日期,则没有重合,不做变动,否则有重合,将当前行的开始日期变为最大结束日期的下一天,这样处理过后,相当于所有日期区间做了去重的处理,然后每一行做天数计算在求和即可得到每个品牌总的折扣日期,即使天数计算出为负数也没有关系,相当于当前活动被之前的活动全覆盖了,筛掉即可

步骤1,计算当前行之前的最大结束日期作为新列max_edt(组内第一行记录的字段值会是null)
select brand,stt,edt,max(edt) over(partition by brand order by stt rows between unbounded preceding and 1 preceding) max_edt
from table4;
  • 1
  • 2
步骤2,比较开始日期和最大结束日期,按需修改
select brand,if(max_edt is null,stt,if(stt>max_edt,stt,date_add(max_edt,1))) stt,edt
from t1;
  • 1
  • 2
步骤2,计算每一行开始日期和结束日期之间的连续天数,生成新列day(需要判断null)
select brand,datediff(date_add(edt,1),stt) day
from t2;
  • 1
  • 2
步骤3,对每个品牌大于零的天数求和,即为总天数
select brand,sum(day) day
from t3
where day>0
group by brand;
  • 1
  • 2
  • 3
  • 4
最终hql
select brand,sum(day) day
from (select brand,datediff(date_add(edt,1),stt) day
from (select brand,if(max_edt is null,stt,if(stt>max_edt,stt,date_add(max_edt,1))) stt,edt
from (select brand,stt,edt,max(edt) over(partition by brand order by stt rows between unbounded preceding and 1 preceding) max_edt
from table4)t1)t2)t3
where day>0
group by brand;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

五、同时在线问题

如下为某直播平台主播开播及关播时间,根据该数据计算出平台最高峰同时在线的主播人数。

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

重点思路

流式数据处理思想,要在整体上找出同时在线的人数非常困难,需要用到一些辅助手段,增加两个新列,一个是直播开始时间的标志位stt_f,值为1,表示主播在线,另一个是直播关闭时间的标志位edt_f,值为-1,表示主播下线,将开始直播的时间及标志位和关闭直播时间及标志位放在一起排序(表变成三列,id、dt、flag,有个细节是,这个操作并不需要用到列转行函数,可以使用两次查询的联合union来做到),开窗求和第一条记录到当前记录的标志位的值,取最大值即为最高峰人数,此方法还能求得最高峰的时间段,以及所有主播上线下线顺序。
(无所谓的注:此方法有个隐藏的小条件,就是一个主播同时只能 开始一场直播,同样id的主播不会有直播的时间冲突)
类似此类问题场景是还有直播间在线人数等等

步骤1,生成标志列并查询联合,重构表
select id,stt dt,1 flag
from table5
union
select id,edt dt,-1 flag
from table5
order by dt;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
步骤2,按时间排序并且开窗求和
select id,dt,sum(flag) over(order by dt) num
from t1;
  • 1
  • 2
步骤3,求最大的人数和对应的开播时间
select max(num)
from t2;
  • 1
  • 2
最终hql
select max(num)
from (select id,dt,sum(flag) over(order by dt) num
from (select id,stt dt,1 flag
from table5
union
select id,edt dt,-1 flag
from table5)t1)t2;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/Cpp五条/article/detail/656565
推荐阅读
相关标签
  

闽ICP备14008679号