HIVE场景题之窗口函数一

1.1hive窗口函数

1.1.1了解哪些窗口函数，都是什么功能？找一个在某个业务中的应用？
sum(col) over() : 分组对col累计求和，over() 中的语法如下count(col) over() : 分组对col累计，over() 中的语法如下min(col) over() : 分组对col求最小
max(col) over() : 分组求col的最大值
avg(col) over() : 分组求col列的平均值first_value(col) over() : 某分区排序后的第一个col值last_value(col) over() : 某分区排序后的最后一个col值
lag(col,n,DEFAULT) : 统计往前n行的col值，n可选，默认为1，DEFAULT当往上第n行为NULL时候， 取默认值，如不指定，则为NULL
lead(col,n,DEFAULT) : 统计往后n行的col值，n可选，默认为1，DEFAULT当往下第n行为NULL时候， 取默认值，如不指定，则为NULL
ntile(n) : 用于将分组数据按照顺序切分成n片，返回当前切片值。注意：n必须为int类型。

排名函数：
row_number() over() : 排名函数，不会重复，适合于生成主键或者不并列排名rank() over() : 排名函数，有并列名次，名次不连续。如:1,1,3 dense_rank() over() : 排名函数，有并列名次，名次连续。如：1，1，2

over(分组 排序 窗口) 中的order by后的语法:
1、物理窗口（真实往上下移动多少行rows between）：

CURRENT ROW | UNBOUNDED PRECEDING | [num] PRECEDING AND
UNBOUNDED FOLLOWING | [num] FOLLOWING| CURRENT ROW

如: over(partition by col order by rows between 1 preceding and 1 fllowing)

2、 逻辑窗口(满足条件上下多少行):

range between [num] PRECEDING AND [num] FOLLOWING

如: over(partition by col order by range between 5 preceding and 5 fllowing)

注意:窗口函数一般不和group by搭配使用。

应用:
某天某产品的累计销售额。

1.1.2编写sql实现每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数
数 据 ： userid,month,visits A,2015-01,5
A,2015-01,15
B,2015-01,5
A,2015-01,8
B,2015-01,25
A,2015-01,5
A,2015-02,4
A,2015-02,6
B,2015-02,10
B,2015-02,5
A,2015-03,16
A,2015-03,22
B,2015-03,23
B,2015-03,10
B,2015-03,1

每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数，结果数据格式如下:

create table visits( userid int,
month string, visits int
)
row format delimited fields terminated by ‘,’
;

load data local inpath ‘/hivedata/visits.txt’ overwrite into table visits;

select userid, month, visits,
max(visits) over(distribute by userid sort by month) maxvisit, sum(visits) over(distribute by userid sort by month) totalvisit from
(select userid, month,
sum(visits) visits from visits
group by userid,month) t1
;
1.1.3求出每个栏目的被观看次数及累计观看时长
数 据 : vedio表
Uid channl min 1 1 23
2 1 12
3 1 12
4 1 32
5 1 342
6 2 13
7 2 34
8 2 13
9 2 134
参考答案：
create table video( Uid int,
channel string, min int1
)
row format delimited fields terminated by ’ ’
;
load data local inpath ‘/hivedata/video.txt’ into table video;

select
channel, count(1) num, sum(min) total from video
group by channel
;
1.1.4编写连续7天登录的总人数
数据:
t1表
Uid dt login_status(1登录成功,0异常)
1 2019-07-11 1
1 2019-07-12 1
1 2019-07-13 1
1 2019-07-14 1
1 2019-07-15 1
1 2019-07-16 1
1 2019-07-17 1
1 2019-07-18 1
2 2019-07-11 1
2 2019-07-12 1
2 2019-07-13 0
2 2019-07-14 1
2 2019-07-15 1
2 2019-07-16 0
2 2019-07-17 1
2 2019-07-18 0
3 2019-07-11 1
3 2019-07-12 1
3 2019-07-13 1
3 2019-07-14 1
3 2019-07-15 1
3 2019-07-16 1
3 2019-07-17 1
3 2019-07-18 1

create table login( Uid int,
dt string, login_status int
)
row format delimited fields terminated by ’ ’
;

load data local inpath ‘/hivedata/login.txt’ into table login;

select uid, dt
from
(select t1.uid uid,
date_sub(t1.dt,t1.rm) dt from
(select uid, dt,
row_number() over(distribute by uid sort by dt) rm from login
where
login_status=1) t1)t2 group by uid,dt having count(uid) >7
;
在login表中

group by 直返会一个结果 直接淘汰

先求出登录的天数 并排名
select uid,dt,row_number() over(distribute by uid sort by dt) rm from login where
login_status=1

再解决不连续登录的问题 日期减去排名 得出一样的日期 就是连续的
select t1.uid uid,date_sub(t1.dt,t1.rm) dt from
(select uid,dt,row_number() over(distribute by uid sort by dt) rm from login where
login=1) t1

最后按照uid 和 dt 进行分组 统计相同的数据 就是连续登录的天数
select uid,dt from
(select t1.uid uid,
date_sub(t1.dt,t1.rm) dt from
(select uid, dt,
row_number() over(distribute by uid sort by dt) rm from login
where login=1) t1)t2
group by uid,dt having count(uid) >7;

date_add(date,interval expr type) 加
date_sub(date,interval expr type) 减
adddate(date,interval expr type)
subdate(date,interval expr type)
对日期时间进行加减法运算
(adddate()和subdate()是date_add()和date_sub()的同义词,也
可以用运算符+和-而不是函数