尚硅谷hql面试题

最新推荐文章于 2024-02-07 19:55:00 发布

原创

最新推荐文章于 2024-02-07 19:55:00 发布 · 544 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql #mysql #数据库

文章介绍了如何使用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 175236416