Hive窗口函数案例详解

语法：

分析函数 over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)

常用分析函数：

• 聚合类
  avg()、sum()、max()、min()
• 排名类
  row_number() 按照值排序时产生一个自增编号，不会重复
  rank() 按照值排序时产生一个自增编号，值相等时会重复，会产生空位
  dense_rank() 按照值排序时产生一个自增编号，值相等时会重复，不会产生空位
• 其他类
  lag(列名,往前的行数,[行数为null时的默认值，不指定为null])
  lead(列名,往后的行数,[行数为null时的默认值，不指定为null])
  ntile(n) 把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，ntile返回此行所属的组的编号

注意点：

• over()函数中的分区、排序、指定窗口范围可组合使用也可以不指定，根据不同的业务需求结合使用
• over()函数中如果不指定分区，窗口大小是针对查询产生的所有数据，如果指定了分区，窗口大小是针对每个分区的数据

over()函数中的窗口范围说明：

current row：当前行

unbounded：起点，unbounded preceding 表示从前面的起点， unbounded following表示到后面的终点

n preceding ：往前n行数据

n following：往后n行数据

实战案例1：

原始数据(用户购买明细数据)

    name，orderdate，cost
    jack,2017-01-01,10
    tony,2017-01-02,15
    jack,2017-02-03,23
    tony,2017-01-04,29
    jack,2017-01-05,46
    jack,2017-04-06,42
    tony,2017-01-07,50
    jack,2017-01-08,55
    mart,2017-04-08,62
    mart,2017-04-09,68
    neil,2017-05-10,12
    mart,2017-04-11,75
    neil,2017-06-12,80
    mart,2017-04-13,94
    

    建表加载数据
    vi business.txt

    create table business
    (
    name string, 
    orderdate string,
    cost int
    )ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

    load data local inpath "/opt/module/data/business.txt" into table business;
    

需求

(1)查询在2017年4月份购买过的顾客及总人数

    分析：按照日期过滤、分组count求总人数(分组为什么不是用group by？自己思考)

    select 
    name,
    orderdate,
    cost,
    count(*) over() total_people
    from 
    business
    where date_format(orderdate,'yyyy-MM')='2017-04';

(2)查询顾客的购买明细及月购买总额

    分析：按照顾客分组、sum购买金额

    select 
    name,
    orderdate,
    cost,
    sum(cost) over(partition by name) total_amount
    from 
    business;

(3)上述的场景,要将cost按照日期进行累加

    分析：按照顾客分组、日期升序排序、组内每条数据将之前的金额累加

    select 
    name,
    orderdate,
    cost,
    sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) cumulative_amount
    from 
    business;

(4)查询顾客上次的购买时间

    分析：查询出明细数据同时获取上一条数据的购买时间(肯定需要按照顾客分组、时间升序排序)

    select 
    name,
    orderdate,
    cost,
    lag(orderdate,1) over(partition by name order by orderdate) last_date
    from 
    business;

(5)查询前20%时间的订单信息

    分析：按照日期升序排序、取前20%的数据

    select
    *
    from
    (
    select 
    name,
    orderdate,
    cost,
    ntile(5) over(order by orderdate) sortgroup_num
    from 
    business
    ) t
    where t.sortgroup_num=1;

实战案例2：

原始数据(学生成绩信息)

    name    subject    score
    孙悟空    语文    87
    孙悟空    数学    95
    孙悟空    英语    68
    大海    语文    94
    大海    数学    56
    大海    英语    84
    宋宋    语文    64
    宋宋    数学    86
    宋宋    英语    84
    婷婷    语文    65
    婷婷    数学    85
    婷婷    英语    78
    

    建表加载数据
    vi score.txt

    create table score
    (
    name string,
    subject string, 
    score int
    ) row format delimited fields terminated by "\t";

    load data local inpath '/opt/module/data/score.txt' into table score;

需求：

(1)每门学科学生成绩排名(是否并列排名、空位排名三种实现)

    分析：学科分组、成绩降序排序、按照成绩排名

    select 
    name,
    subject,
    score,
    rank() over(partition by subject order by score desc) rp,
    dense_rank() over(partition by subject order by score desc) drp,
    row_number() over(partition by subject order by score desc) rmp
    from 
    score;

(2)每门学科成绩排名top n的学生

    select 
    *
    from 
    (
    select 
    name,
    subject,
    score,
    row_number() over(partition by subject order by score desc) rmp
    from score
    ) t
    where t.rmp<=3;