5道Hive典型题目解析

￡神魔★判官ぃ 2022-09-10 13:21 192阅读 0赞

## 一、连续问题 ##

如下数据为蚂蚁森林中用户领取的低碳排放量（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
    … …

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

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

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

select
        id,
        dt,
        sum(lowcarbon) lowcarbon
    from test1
    group by id,dt
    having lowcarbon>100;

t1表如下：

id	dt	lowcarbon
    1001	2021-12-12	123
    1001	2021-12-13	111
    1001	2021-12-14	230

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

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

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

t2表如下：

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

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

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

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

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

select
        id,
        flag,
        count(*) ct
    from t3
    group by id,flag
    having ct>=3;

最终结果为：

id	flag	ct
    1001	2021-12-11	3

**总体执行语句：**

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;

## 二、分组问题 ##

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

id		ts
    1001	17523641234
    1001	17523641256
    1002	17523641278
    1001	17523641334
    1002	17523641434
    1001	17523641534
    1001	17523641544
    1002	17523641634
    1001	17523641638
    1001	17523641654

对于同一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

**【思路】：对每个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;

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

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

select
        id,
        ts,
        ts-lagts tsdiff
    from
        t1;

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

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

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

最终结果为：

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

**总体执行语句：**

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;

## 三、间隔连续问题 ##

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

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 天登录

**【思路】：对同一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;

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

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

select
        id,
        dt,
        datediff(dt,lagdt) flag
    from
        t1;

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

（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

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

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

select
        id,
        flag,
        datediff(max(dt),min(dt)) days
    from
        t3
    group by id,flag;

t4表如下：

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

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

select
        id,
        max(days)+1
    from
        t4
    group by id;

最终结果：

id	_c1
    1001	5
    1002	2

**总体执行语句：**

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;

## 四、打折日期交叉问题 ##

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

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

计算每个品牌总的打折销售天数，注意其中的交叉日期，比如 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;

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

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

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

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

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

select
        brand,
        datediff(edt,sdt) days
    from
        t2

t3表如下：

brand	days
    huawei	21
    huawei	-12
    huawei	-6
    oppo	4
    oppo	10
    redmi	16
    redmi	-7
    redmi	4
    vivo	10
    vivo	5

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

select
        brand,
        sum(if(days>=0,days+1,0)) days
    from
        t3
    group by id;

最终执行结果：

brand	days
    huawei	22
    oppo	16
    redmi	22
    vivo	17

**总体执行语句：**

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;

## 五、同时在线问题 ##

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

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、-1后，合并后对增加列进行累加，求的最大值，即为最大同时在线人数**

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

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

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

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

select
        id,
        dt,
        sum(p) over(order by dt) sum_p
    from
        t1；

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

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

select
        max(sum_p)
    from
        t2;

最终结果：

**总体执行语句：**

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;

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

[2021_Hive_33_42]: https://www.bilibili.com/video/BV1Cb4y1r7p2?p=33