5道Hive典型题目解析_hive 同时在线问题

一、连续问题

如下数据为蚂蚁森林中用户领取的低碳排放量（lowcarbon）

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
2
3
4
5
6
7
8
9
10
11

找出连续 3 天及以上领取的低碳排放量（lowcarbon）在 100 以上的用户

【思路】：连续日期与等差数列作差会变为同一日期，再根据用户、日期进行分组

解析：
假设建立table表名为test1：
（1）按照用户ID及时间字段分组,计算每个用户单日领取的低碳排放量→t1表

select
    id,
    dt,
    sum(lowcarbon) lowcarbon
from test1
group by id,dt
having lowcarbon>100;
1
2
3
4
5
6
7

t1表如下：

id	dt	lowcarbon
1001	2021-12-12	123
1001	2021-12-13	111
1001	2021-12-14	230
1
2
3
4

等差数列法:两个等差数列如果等差相同,则相同位置的数据相减等到的结果相同

（2）按照用户分组,同时按照时间排序,计算每条数据的Rank值→t2表

select
    id,
    dt,
    lowcarbon,
    rank() over(partition by id order by dt) rk
from t1;
1
2
3
4
5
6

t2表如下：

id	dt	lowcarbon	rk
1001	2021-12-12	123	1
1001	2021-12-13	111	2
1001	2021-12-14	230	3
1
2
3
4

（3）将每行数据中的日期减去Rank值→t3表

select
    id,
    dt,
    lowcarbon,
    date_sub(dt,rk) flag
from t2;
1
2
3
4
5
6

t3表如下：

id	dt	lowcarbon	flag
1001	2021-12-12	123	2021-12-11
1001	2021-12-13	111	2021-12-11
1001	2021-12-14	230	2021-12-11
1
2
3
4

（4）按照用户及Flag分组,求每个组有多少条数据,并找出大于等于3条的数据

select
    id,
    flag,
    count(*) ct
from t3
group by id,flag
having ct>=3;
1
2
3
4
5
6
7

最终结果为：

id	flag	ct
1001	2021-12-11	3
1
2

总体执行语句：

select
    id,
    flag,
    count(*) ct
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 test1
            group by id,dt
            having lowcarbon>100)t1)t2)t3
group by id,flag
having ct>=3;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

二、分组问题

将如下数据按照所示结果进行分组

id		ts
1001	17523641234
1001	17523641256
1002	17523641278
1001	17523641334
1002	17523641434
1001	17523641534
1001	17523641544
1002	17523641634
1001	17523641638
1001	17523641654
1
2
3
4
5
6
7
8
9
10
11

对于同一id，时间间隔小于60秒，则分为同一个组

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
1
2
3
4
5
6
7
8
9
10

【思路】：对每个id的前后秒数进行作差，用所得到的差值从第一行到当前行大于等于60的总个数进行分组

解析：
假设建立table表名为test2：
（1）将上一行时间数据下移→t1表
lead:领导
lag:延迟

select
    id,
    ts,
    lag(ts,1,0) over(partition by id order by ts) lagts
from
    test2;
1
2
3
4
5
6

t1表如下：

id	ts	lagts
1001	17523641234	0
1001	17523641256	17523641234
1001	17523641334	17523641256
1001	17523641534	17523641334
1001	17523641544	17523641534
1001	17523641638	17523641544
1001	17523641654	17523641638
1002	17523641278	0
1002	17523641434	17523641278
1002	17523641634	17523641434
1
2
3
4
5
6
7
8
9
10
11

（2）将当前行时间数据减去上一行时间数据→t2表

select
    id,
    ts,
    ts-lagts tsdiff
from
    t1;
1
2
3
4
5
6

t2表如下：

id	ts	tsdiff
1001	17523641234	17523641234
1001	17523641256	22
1001	17523641334	78
1001	17523641534	200
1001	17523641544	10
1001	17523641638	94
1001	17523641654	16
1002	17523641278	17523641278
1002	17523641434	156
1002	17523641634	200
1
2
3
4
5
6
7
8
9
10
11

（3）计算每个用户范围内从第一行到当前行tsdiff大于等于60的总个数(分组号)

select
    id,
    ts,
    sum(if(tsdiff>=60,1,0)) over(partition by id order by ts) groupid
from
    t2;
1
2
3
4
5
6

最终结果为：

id	ts	groupid
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

1
2
3
4
5
6
7
8
9
10
11
12

总体执行语句：

select
    id,
    ts,
    sum(if(tsdiff>=60,1,0)) over(partition by id order by ts) groupid
from
    (select
        id,
        ts,
        ts-lagts tsdiff
    from
        (select
            id,
            ts,
            lag(ts,1,0) over(partition by id order by ts) lagts
        from
            test2)t1)t2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

三、间隔连续问题

某游戏公司记录的用户每日登录数据

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
2
3
4
5
6
7
8
9
10

计算每个用户最大的连续登录天数，可以间隔一天。解释：如果一个用户在 1,3,5,6 登录游戏，则视为连续 6 天登录

【思路】：对同一id的前后日期进行作差，计算所得差值从第一行到当前行大于2的数据的总条数进行打标签，最后计算统一标签内的日期差

解析：
假设建立table表名为test3：
（1）将上一行时间数据下移→t1表

select
    id,
    dt,
    lag(dt,1,'1970-01-01') over(partition by id order by dt) lagdt
from
    test3;
1
2
3
4
5
6

t1表如下：

id	dt	lagdt
1001	2021-12-12	1970-01-01
1001	2021-12-13	2021-12-12
1001	2021-12-14	2021-12-13
1001	2021-12-16	2021-12-14
1001	2021-12-19	2021-12-16
1001	2021-12-20	2021-12-19
1002	2021-12-12	1970-01-01
1002	2021-12-16	2021-12-12
1002	2021-12-17	2021-12-16
1
2
3
4
5
6
7
8
9
10

（2）将当前行时间减去上一行时间数据(datediff(dt1,dt2))→t2表

select
    id,
    dt,
    datediff(dt,lagdt) flag
from
    t1;
1
2
3
4
5
6

t2表如下：

id	dt	flag
1001	2021-12-12	18973
1001	2021-12-13	1
1001	2021-12-14	1
1001	2021-12-16	2
1001	2021-12-19	3
1001	2021-12-20	1
1002	2021-12-12	18973
1002	2021-12-16	4
1002	2021-12-17	1

1
2
3
4
5
6
7
8
9
10
11

（3）按照用户分组,同时按照时间排序,计算从第一行到当前行大于2的数据的总条数(sum(if(flag>2,1,0)))，小于2说明是满足题目中所要求的的“连续” →t3表

select
    id,
    dt,
    sum(if(flag>2,1,0)) over(partition by id order by dt) flag
from
    t2
1
2
3
4
5
6

t3表如下：

id	dt	flag
1001	2021-12-12	1
1001	2021-12-13	1
1001	2021-12-14	1
1001	2021-12-16	1
1001	2021-12-19	2
1001	2021-12-20	2
1002	2021-12-12	1
1002	2021-12-16	2
1002	2021-12-17	2
1
2
3
4
5
6
7
8
9
10

（4）按照用户和flag分组,求最大时间减去最小时间 →t4表

select
    id,
    flag,
    datediff(max(dt),min(dt)) days
from
    t3
group by id,flag;
1
2
3
4
5
6
7

t4表如下：

id	flag	days
1001	1	4
1001	2	1
1002	1	0
1002	2	1
1
2
3
4
5

（5）取连续登录天数的最大值并+1

select
    id,
    max(days)+1
from
    t4
group by id;
1
2
3
4
5
6

最终结果：

id	_c1
1001	5
1002	2
1
2
3

总体执行语句：

select
    id,
    max(days)+1
from
    (select
        id,
        flag,
        datediff(max(dt),min(dt)) days
    from
        (select
            id,
            dt,
            sum(if(flag>2,1,0)) over(partition by id order by dt) flag
        from
            (select
                id,
                dt,
                datediff(dt,lagdt) flag
            from
                (select
                    id,
                    dt,
                    lag(dt,1,'1970-01-01') over(partition by id order by dt) lagdt
                from
                    test3)t1)t2)t3
    group by id,flag)t4
group by id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

四、打折日期交叉问题

如下为平台商品促销数据：字段为品牌，打折开始日期，打折结束日期

brand  sdt 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
2
3
4
5
6
7
8
9
10
11

计算每个品牌总的打折销售天数，注意其中的交叉日期，比如 vivo 品牌，第一次活动时间为 2021-06-05 到 2021-06-15，第二次活动时间为 2021-06-09 到 2021-06-21 其中 9 号到 15号为重复天数，只统计一次，即 vivo 总打折天数为 2021-06-05 到 2021-06-21 共计 17 天

【思路】：对同一品牌的不同时间段的日期进行处理，如果开始日期比之前时间的最大结束日期小，就将该开始日期进行更改，然后求天数，最后求不同时间段的总天数和

解析：
假设建立table表名为test4：
（1）将当前行以前的数据中最大的edt放置当前行→t1表

select
    brand,
    sdt,
    edt,
    max(edt) over(partition by brand order by sdt rows between UNBOUNDED PRECEDING and 1 PRECEDING) maxEdt
from test4;
1
2
3
4
5
6

t1表如下：

brand	sdt	edt	maxedt
huawei	2021-06-05	2021-06-26	NULL
huawei	2021-06-09	2021-06-15	2021-06-26
huawei	2021-06-17	2021-06-21	2021-06-26
oppo	2021-06-05	2021-06-09	NULL
oppo	2021-06-11	2021-06-21	2021-06-09
redmi	2021-06-05	2021-06-21	NULL
redmi	2021-06-09	2021-06-15	2021-06-21
redmi	2021-06-17	2021-06-26	2021-06-21
vivo	2021-06-05	2021-06-15	NULL
vivo	2021-06-09	2021-06-21	2021-06-15
1
2
3
4
5
6
7
8
9
10
11

（2）比较开始时间与移动下来的数据,如果开始时间大，则不需要操作，反之则需要将移动下来的数据加一替换当前行的开始时间，如果是第一行数据,maxEDT为null,则不需要操作→t2表

select
    brand,
    if(maxEdt is null,sdt,if(sdt>maxEdt,sdt,date_add(maxEdt,1))) sdt,
    edt
from t1;
1
2
3
4
5

t2表如下：

brand	sdt	edt
huawei	2021-06-05	2021-06-26
huawei	2021-06-27	2021-06-15
huawei	2021-06-27	2021-06-21
oppo	2021-06-05	2021-06-09
oppo	2021-06-11	2021-06-21
redmi	2021-06-05	2021-06-21
redmi	2021-06-22	2021-06-15
redmi	2021-06-22	2021-06-26
vivo	2021-06-05	2021-06-15
vivo	2021-06-16	2021-06-21
1
2
3
4
5
6
7
8
9
10
11

（3）将每行数据中的结束日期减去开始日期→t3表

select
    brand,
    datediff(edt,sdt) days
from
    t2
1
2
3
4
5

t3表如下：

brand	days
huawei	21
huawei	-12
huawei	-6
oppo	4
oppo	10
redmi	16
redmi	-7
redmi	4
vivo	10
vivo	5
1
2
3
4
5
6
7
8
9
10
11

（4）按照品牌分组,计算每条数据加一的总和

select
    brand,
    sum(if(days>=0,days+1,0)) days
from
    t3
group by id;
1
2
3
4
5
6

最终执行结果：

brand	days
huawei	22
oppo	16
redmi	22
vivo	17
1
2
3
4
5

总体执行语句：

select
    brand,
    sum(if(days>=0,days+1,0)) days
from
    (select
        brand,
        datediff(edt,sdt) days
    from
        (select
            brand,
            if(maxEdt is null,sdt,if(sdt>maxEdt,sdt,date_add(maxEdt,1))) sdt,
            edt
        from 
            (select
                brand,
                sdt,
                edt,
                max(edt) over(partition by brand order by sdt rows between UNBOUNDED PRECEDING and 1 PRECEDING) maxEdt
            from test4)t1)t2)t3
group by brand;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

五、同时在线问题

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

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
2
3
4
5
6
7
8
9

【思路】：对所有上线主播和下线主播数据分别添加列1、-1后，合并后对增加列进行累加，求的最大值，即为最大同时在线人数

解析：
假设建立table表名为test5：
（1）对数据分类,在开始数据后添加正1,表示主播上线,同时在关播数据后添加-1,表示主播下线→t1表

select id,sdt dt,1 p from test5
union
select id,edt dt,-1 p from test5;
1
2
3

t1表如下：

_u1.id	_u1.dt	_u1.p
1001	2021-06-14 12:12:12	1
1001	2021-06-14 18:12:12	-1
1001	2021-06-14 20:12:12	1
1001	2021-06-14 23:12:12	-1
1002	2021-06-14 15:12:12	1
1002	2021-06-14 16:12:12	-1
1003	2021-06-14 13:12:12	1
1003	2021-06-14 16:12:12	-1
1004	2021-06-14 13:15:12	1
1004	2021-06-14 20:12:12	-1
1005	2021-06-14 15:18:12	1
1005	2021-06-14 20:12:12	-1
1006	2021-06-14 21:12:12	1
1006	2021-06-14 23:15:12	-1
1007	2021-06-14 22:12:12	1
1007	2021-06-14 23:10:12	-1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

（2）按照时间排序,计算累加人数→t2表

select
    id,
    dt,
    sum(p) over(order by dt) sum_p
from
    t1；
1
2
3
4
5
6

t2表如下：

id	dt	sum_p
1001	2021-06-14 12:12:12	1
1003	2021-06-14 13:12:12	2
1004	2021-06-14 13:15:12	3
1002	2021-06-14 15:12:12	4
1005	2021-06-14 15:18:12	5
1002	2021-06-14 16:12:12	3
1003	2021-06-14 16:12:12	3
1001	2021-06-14 18:12:12	2
1001	2021-06-14 20:12:12	1
1004	2021-06-14 20:12:12	1
1005	2021-06-14 20:12:12	1
1006	2021-06-14 21:12:12	2
1007	2021-06-14 22:12:12	3
1007	2021-06-14 23:10:12	2
1001	2021-06-14 23:12:12	1
1006	2021-06-14 23:15:12	0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

（3）找出同时在线人数最大值

select
    max(sum_p)
from
    t2;
1
2
3
4

最终结果：

5
1

总体执行语句：

select
    max(sum_p)
from
    (select
        id,
        dt,
        sum(p) over(order by dt) sum_p
    from
        (select id,sdt dt,1 p from test5
        union
        select id,edt dt,-1 p from test5)t1)t2;
1
2
3
4
5
6
7
8
9
10
11

参考视频：【尚硅谷】2021最新版Hive高级进阶教程—33~42