最近在深入了解Hive，尚硅谷的這5道題很經典，有引導意義，分步解題也很有用，故記錄之，方便回看
1.連續問題
如下資料為螞蟻森林中用戶領取的減少碳排放量
找出連續 3 天及以上減少碳排放量在 100 以上的用戶
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
建表
create table mayi
(
id int,
dt string,
lowcarbon int
)
row format delimited
fields terminated by '\t';
load data local inpath "/home/hadoop/test/mayi.txt" into table mayi;
select *
from mayi;
--1)求出每日lowcarbon在100以上的用戶記錄
select id, dt, sum(lowcarbon) lowcarbon
from mayi
group by id, dt
having lowcarbon > 100;
-- t1
-- 2）等引數列法
-- 兩個等差資料如果等差相同，則相同位置的資料相減得到的相同
-- 按照用戶分組，同時按照時間排序，計算每條資料的Rank
select id,
dt,
lowcarbon,
rank() over (partition by id order by dt) rk
from t1;
-- t2
-- 3)將每行資料中的日期間區Rank值
select id,
dt,
lowcarbon,
date_sub(dt, rk) flag
from t2;
-- t3
-- 4) 按照用戶及 Flag 分組，求每個組有多少條資料，并找出大于等于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 mayi
group by id, dt
having lowcarbon > 100) t1) t2) t3
group by id, flag
having ct >= 3;
2.分組問題
如下為電商公司用戶訪問時間資料
id ts(秒)
1001 17523641234
1001 17523641256
1002 17523641278
1001 17523641334
1002 17523641434
1001 17523641534
1001 17523641544
1002 17523641634
1001 17523641638
1001 17523641654
某個用戶連續的訪問記錄如果時間間隔小于 60 秒，則分為同一個組，結果為：
id ts(秒) group
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
create table elec
(
id int,
ts bigint
)
row format delimited
fields terminated by '\t';
load data local inpath "/home/hadoop/test/elec.txt" into table elec;
select *
from elec;
-- 1)將上一行時間資料下移
-- lead:領導
-- lag:延遲
select id,
ts,
lag(ts, 1, 0) over (partition by id order by ts) lagts
from elec;
-- t1
-- 2)將當前行時間資料減去上一行時間資料
select id,
ts,
ts - lagts tsdiff
from t1;
-- t2
select id,
ts,
ts - lagts tsdiff
from (select id,
ts,
lag(ts, 1, 0) over (partition by id order by ts) lagts
from elec) t1;
-- 3)計算每個用戶范圍內從第一行到當前行tsdiff大于等于60的總個數(分組號)
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 elec) t1) t2;
3.間隔連續問題
某游戲公司記錄的用戶每日登錄資料
注意：有的時候hql過于復雜,需要spark任務也比較多,可能需要建立臨時表分多步運行
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 天登錄，
create table user_login
(
id int,
dt string
)
row format delimited
fields terminated by '\t';
load data local inpath "/home/hadoop/test/user_login.txt" into table user_login;
select *
from user_login;
-- 思路二：分組
-- 1001 2021-12-12
-- 1001 2021-12-13
-- 1001 2021-12-14
-- 1001 2021-12-16
-- 1001 2021-12-19
-- 1001 2021-12-20
-- 1)將上一行時間資料下移
-- 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
select id,
dt,
lag(dt, 1, '1970-01-01') over (partition by id order by dt) lagdt
from user_login;
-- t1
-- 2)將當前行時間減去上一行時間資料(datediff(dt1,dt2))
-- 1001 2021-12-12 564564
-- 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
select id,
dt,
datediff(dt, lagdt) flag
from t1;
create table user_login_tmp as
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 user_login)t1;
select * from user_login_tmp;
-- t2
-- 3)按照用戶分組,同時按照時間排序,計算從第一行到當前行大于2的資料的總條數(sum(if(flag>2,1,0)))
-- 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
select id,
dt,
sum(if(flag > 2, 1, 0)) over (partition by id order by dt) flag
from t2;
select id,
dt,
sum(if(flag > 2, 1, 0)) over (partition by id order by dt) flag2
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 user_login)t1)t2;
select id,
dt,
sum(if(flag > 2, 1, 0)) over (partition by id order by dt) flag2
from user_login_tmp;
-- t3
-- 4)按照用戶和flag分組,求最大時間減去最小時間并加上1
select id,
flag,
datediff(max(dt), min(dt)) days
from t3
group by id, flag;
-- t4
-- 5)取連續登錄天數的最大值
select id,
max(days) + 1
from t4
group by id;
-- 6)最終HQL
-- 6)最終HQL
-- set yarn.scheduler.maximum-allocation-mb=4;
-- set spark.executor.cores=4;
-- set spark.executor.instances=6;
-- set spark.executor.memory="3.2g";
-- set spark.yarn.executor.memoryOverhead="0.8G";
-- set spark.executor.cores=1;
-- set spark.executor.instances=24;
-- set spark.default.parallelism=20;
-- set spark.driver.memory="819MB";
-- set spark.driver.memoryOverhead="205MB";
set spark.executor.instances = 20;
set spark.executor.cores = 1;
set spark.executor.memory = 1g;
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 user_login) t1) t2) t3
group by id, flag) t4
group by id;
-- join配置
-- set hive.auto.convert.join=false;
--
-- set mapred.map.tasks.speculative.execution=true;
-- set mapred.reduce.tasks.speculative.execution=true;
4.打折日期交叉問題
如下為平臺商品促銷資料：欄位為品牌，打折開始日期，打折結束日期
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
– 計算每個品牌總的打折銷售天數，注意其中的交叉日期，比如 vivo 品牌，第一次活動時間為 2021-06-05 到 2021-06-15，
– 第二次活動時間為 2021-06-09 到 2021-06-21 其中 9 號到 15號為重復天數，只統計一次，即 vivo 總打折天數為 2021-06-05 到 2021-06-21 共計 17 天，
drop table if exists discount;
create table discount
(
id string,
stt string,
edt string
)
row format delimited
fields terminated by '\t';
load data local inpath "/home/hadoop/test/discount.txt" into table discount;
select *
from discount;
-- id 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
-- select * from business;
-- select name,orderdate,cost,
-- sum(cost) over() as sample1
-- ,--所有行相加
-- sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4
-- ,--和 sample3 一樣,由起點到當前行的聚合
-- sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6
-- -- ,--當前行和前邊一行及后面一行
-- from business
-- 1)將當前行以前的資料中最大的edt放置當前行
select
id,
stt,
edt,
max(edt) over(partition by id order by stt rows between UNBOUNDED PRECEDING and 1 PRECEDING) maxEdt
from discount;
-- t1
-- 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
-- 2)比較開始時間與移動下來的資料,如果開始時間大,則不需要操作,
-- 反之則需要將移動下來的資料 加一 替換當前行的開始時間
-- 如果是第一行資料,maxEDT為null,則不需要操作
select
id,
if(maxEdt is null,stt,if(stt>maxEdt,stt,date_add(maxEdt,1))) stt,
edt
from t1;
-- t2
-- redmi 2021-06-05 2021-06-21
-- redmi 2021-06-22 2021-06-15
-- redmi 2021-06-22 2021-06-26
-- 3)將每行資料中的結束日期減去開始日期
select
id,
datediff(edt,stt) days
from
t2;
-- t3
-- redmi 16
-- redmi -7
-- redmi 4
-- 4)按照品牌分組,計算每條資料加一的總和
select
id,
sum(if(days>=0,days+1,0)) days
from
t3
group by id;
-- redmi 22
-- 5)最終HQL
select
id,
sum(if(days>=0,days+1,0)) days
from
(select
id,
datediff(edt,stt) days
from
(select
id,
if(maxEdt is null,stt,if(stt>maxEdt,stt,date_add(maxEdt,1))) stt,
edt
from
(select
id,
stt,
edt,
max(edt) over(partition by id order by stt rows between UNBOUNDED PRECEDING and 1 PRECEDING) maxEdt
from discount)t1)t2)t3
group by id;
5.同時在線問題
如下為某直播平臺主播開播及關播時間，根據該資料計算出平臺最高峰同時在線的主播人數，
id stt edt
1003 2021-06-14 12:12:12 2021-06-14 18:12:12
1004 2021-06-14 13:12:12 2021-06-14 16: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
drop table if exists living;
create table living
(
id string,
stt string,
edt string
)
row format delimited
fields terminated by '\t';
load data local inpath "/home/hadoop/test/living.txt" into table living;
select *
from living;
-- 流式！
-- 1)對資料分類,在開始資料后添加正1,表示有主播上線,同時在關播資料后添加-1,表示有主播下
select id,stt dt,1 p from living
union
select id,edt dt,-1 p from living;
-- t1
-- 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)按照時間排序,計算累加人數
select
id,
dt,
sum(p) over(order by dt) sum_p
from
(select id,stt dt,1 p from living
union
select id,edt dt,-1 p from living)t1;
-- t2
-- 3)找出同時在線人數最大值
select
max(sum_p)
-- *
from
(select
id,
dt,
sum(p) over(order by dt) sum_p
from
(select id,stt dt,1 p from living
union
select id,edt dt,-1 p from living)t1)t2
where dt='2021-06-14 20:12:12';
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/421702.html
標籤：其他