Hive 窗口函数最全讲解和实战

kk大数据

发布于 2019-12-18 16:20:04

1.7K0

发布于 2019-12-18 16:20:04

文章被收录于专栏：kk大数据kk大数据

一、窗口函数的概念

在不同的窗口执行的函数

在深入研究Over字句之前，一定要注意：在SQL处理中，窗口函数都是最后一步执行，而且仅位于Order by子句之前可以想象成sql的输出结果，就是窗口函数输入的结果。

主要的函数有：

over()
partition_by
order_by
window函数
row_number()
rank()
dense_rank()
lag()lead()
first_value()
last_value()

二、实例

建立一个用户消费表：

drop table tempon.t_user_cost;
create external table if not exists tempon.t_user_cost(
name string comment '用户名',
date string comment '月份',
cost int comment '花费'
) comment '用户花费表' 
row format delimited fields terminated by ","
location '/tmp/person_cost'

1、聚合函数 + over()

select name,count(1) over() 
from tempon.t_user_cost
where substring(date,1,7) = '2015-04'

一般，聚合函数返回行数都会比原有行数少。

但我们又想看原始函数，又想看聚合的值，怎么办，在聚合函数上加over()

二、partition by 子句

也叫查询分区子句，将数据按照边界值分组，而over()之前的函数在每个分组内执行。

select name,date,cost,sum(cost) over(partition by month(date))   from tempon.t_user_cost;

三、order by 子句

上面的场景，假如我们想把cost按月进行累加，这时我们引入order by 子句

order by 子句会让输入数据强制排序

select name,date,cost,sum(cost) over(partition by month(date) order by cost) from tempon.t_user_cost;

四、window子句

（不同的窗口互不影响，自己算自己的）

window是为了更加细粒度的划分

两个概念：

如果只使用了partition by子句，未指定order by的话，我们的聚合是分组内的聚合

如果使用了order by子句，未使用window子句，默认从起点到当前行

PRECEDING：往前

FOLLOWING：往后

CURRENT ROW：当前行

UNBOUNDED：起点（UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING：表示到后面的终点）

也就是可以指定，在指定窗口里，可以把当前行的前面N行和当前行的后面N行，聚合。即小窗口的概念，只是这个窗口的口径是行数

select name,date,cost,
sum(cost) over() sample1,  -- 所有行累加
sum(cost) over(partition by name) sample2, -- 按照name相加
sum(cost) over(partition by name order by cost) sample3, --按照name累加
sum(cost) over(partition by name order by cost rows between unbounded preceding and current row) sample4, --和sample3一样的效果
sum(cost) over(partition by name order by cost rows between 1 preceding and current row) sample5, -- 当前行和上一行相加
sum(cost) over(partition by name order by cost rows between 1 preceding and 1 following) sample6, -- 上一行、当前行、后一行相加
sum(cost) over(partition by name order by cost rows between current row and unbounded following) sample7 -- 当前行到末尾
from tempon.t_user_cost;

五、row_number() 和 rank() 和 dense_rank()

这三个函数是为了排序，但是有区别

select name,date,cost,
row_number() over(partition by name order by cost),
rank() over(partition by name order by cost),
dense_rank() over(partition by name order by cost)
  from tempon.t_user_cost;

row_number() 是无脑排序

rank() 是相同的值排名相同，接下来的排名会加

dense_rank()也是相同的值排名相同，接下来的排名不会加

六、lag和lead函数

lag()函数是在窗口内，在指定列上，取上N行的数据，并且有默认值。没有设置默认值的话，为null

select name,date,cost,
  lag(date,1,'1990-01-01') over(partition by name order by date),
  lag(date,2,'1990-01-01') over(partition by name order by date),
  lead(date,1,'1990-01-01') over(partition by name order by date)
    from tempon.t_user_cost

第一个参数是列名，第二个参数是取上多少行的数据，第三个参数是默认值

lead相反，往下取

七、first_value() 和 last_value()

select name,date,cost,
  first_value(date) over(partition by name order by date),
  last_value(date) over(partition by name order by date)
    from tempon.t_user_cost;