hive 窗口函数倒数第二个 hive窗口函数lead

一、窗口函数的概念

窗口就是数据内部按某个字段分区，在分区内做一些事情。
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() 里面没有加分区，也就是对全局数据分一个区，结果输出到每一行上。
如果 over(partition name)，也就是对每个名字分区，结果显示到对应的名字上。
首先，其他字段是照常输出的，over 函数是最后计算的

2、partition by 子句

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

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

3、order by 子句

对 某一个字段分区，分区内对另一个字段排序。
排好序后，对于不同的聚合函数效果不一样。
如下是和 sum 函数一起使用，就是按排序逐行累加
当然可以随意指定按某一个规则累加
rows between * and *
当前行： current row
往前n行： n preceding
往后n行： n following
往前无限行： unbouded preceding
往后无限行： unbouded following

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

和 count 函数一起使用，则是逐行计数累加

4、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;

5、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()也是相同的值排名相同，接下来的排名不会加。不会占据排名的坑位。

6、lag和lead函数

lag()函数是在窗口内，在指定列上，取上N行的数据，并且有默认值。没有设置默认值的话，为null
lag(dt,1,‘1990-01-01’) 就是在窗口分区内，往上取1行的数据，填到本行中。如果是第一行，则取 1990-01-01

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相反，往下取

7、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;

当前分区的第一个值和最后一个值

8、NTILE 函数

ntile 函数可以把分组顺序按顺序切片，并返回当前记录所在的切片值
比如：我们来造一个表，需求是，求销量前30%和后70%的平均销量
可以先用 ntile 对数据切片分成10组，然后取第1,2,3 组
（但是数据量小的时候，这样取30%可能不准）
数据：

1,35.2
2,89
3,32
4,22
5,100
6,300
7,12
8,65
9,21
10,45
11,21
12,55
13,89

建表：

drop table tmp.t_shop_cost;
create external table if not exists tmp.t_shop_cost(
name string comment '店铺名',
amount double comment '销量'
) comment '店铺销量' 
row format delimited fields terminated by ","
location '/tmp/shop_cost';

计算：

create table tmp.t_shop_cost_tmp
as
select
 name,
 amount,
 NTILE(10) OVER (order by amount desc) as rn
from tmp.t_shop_cost;

当然这里用 ntile 取 30% 貌似不太准确
我们使用下面的计算准确的结果

select case when category=1 then '30%' else '70%' end,
        avg(amount) avg_price
  from (
        select name,
               amount,  
               case when rn <= ceiling(0.3*all_count) then 1 else 2 end category
        from (
            select 
            name,
            amount,
            count(1) over() all_count,
            row_number() over(order by amount desc) rn
            from tmp.t_shop_cost
        ) t1
) t2 group by category;

select
  new_rn,
  max(case when new_rn=1 then 'avg_price_first_30%' when new_rn=2 then 'avg_price_last_70%' end) as avg_price_name,
  avg(amount) avg_price
from 
(
  select 
    name,
    amount,
    rn,
    case when rn in (1,2,3) then 1 else 2 end as new_rn
  from tmp.t_shop_cost_tmp
)a
group by new_rn;