尚硅谷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

步骤2，生成等差数列

select id,dt,lowcarbon,rank() over(partition by id order by dt) rk
from t1;t2

步骤3，将时间列与生成的等差数列相减，得到标志列flag，用于判断连续

select id,dt,lowcarbon,date_sub(dt,rk) flag
from t2;t3

步骤4，利用标志列，将连续列分组，输出减少碳排放量列大于100的记录条数大于三条的用户id

select id,sum(if(lowcarbon>100,1,0)) s
from t3
group by id,flag
having s>=3;

最终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;

二、分组问题

如下为电商公司用户访问时间数据，某个用户连续的访问记录如果时间间隔小于 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

步骤2，计算出时间和上一行时间之间的差值diff_ts

select id,ts,ts-lag_ts diff_ts
from t1;t2

步骤3，计算每个用户范围内第一行到当前行记录中时间差大于60的记录条数，即为分组

select id,ts,sum(if(diff_ts>=60,1,0)) over(partition by id order by ts) groupid
from t2;

最终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,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;

步骤2，日期和上一行日期做差，得到间隔日期

select id,dt,datediff(dt,lag_dt) diff_dt
from t1;

步骤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;

步骤4，按照id和组号分组（hql语法：分组中必须出现查询字段，即使分组条件不需要id，也得写上），求最大时间和最小时间的差并加一作为连续天数

select id,datediff(max(date_add(to_date(dt),1)),min(to_date(dt))) day
from t3
group by groupid;

步骤5，取每个用户连续登录的最大值

select id,max(day) day
from t4
group by id;

最终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;

四、打折日期交叉问题

如下为平台商品促销数据：字段为品牌，打折开始日期，打折结束日期，计算每个品牌总的打折销售天数，注意其中的交叉日期，比如 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;

步骤2，比较开始日期和最大结束日期，按需修改

select brand,if(max_edt is null,stt,if(stt>max_edt,stt,date_add(max_edt,1))) stt,edt
from t1;

步骤2，计算每一行开始日期和结束日期之间的连续天数，生成新列day（需要判断null）

select brand,datediff(date_add(edt,1),stt) day
from t2;

步骤3，对每个品牌大于零的天数求和，即为总天数

select brand,sum(day) day
from t3
where day>0
group by brand;

最终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;

五、同时在线问题

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

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;

步骤2，按时间排序并且开窗求和

select id,dt,sum(flag) over(order by dt) num
from t1;

步骤3，求最大的人数和对应的开播时间

select max(num)
from t2;

最终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;