hive 函数--开窗函数1

开窗函数

初识

窗口： 函数运行时计算的数据集的范围
函数： 运行的函数！

仅仅支持以下函数：

Windowing functions：

LEAD:

LEAD (scalar_expression [,offset] [,default])： 返回当前行以下N行的指定列的列值！如果找不到，就采用默认值

LAG:

LAG (scalar_expression [,offset] [,default])： 返回当前行以上N行的指定列的列值！如果找不到，就采用默认值

FIRST_VALUE:

FIRST_VALUE(列名,[false(默认)])： 返回当前窗口指定列的第一个值，第二个参数如果为true,代表加入第一个值为null，跳过空值，继续寻找！

LAST_VALUE:

LAST_VALUE(列名,[false(默认)])： 返回当前窗口指定列的最后一个值，第二个参数如果为true,代表加入第一个值为null，跳过空值，继续寻找！

统计类的函数(一般都需要结合over使用)：

min,max,avg,sum,count

排名分析：

RANK
ROW_NUMBER
DENSE_RANK
CUME_DIST
PERCENT_RANK
NTILE

注意：

不是所有的函数在运行都是可以通过改变窗口的大小，来控制计算的数据集的范围！
所有的排名函数和LAG,LEAD，支持使用over()，但是在over()中不能定义 window_clause

格式：

函数 over( partition by 字段 ,order by 字段 window_clause )
窗口的大小可以通过windows_clause来指定：

(rows | range) between (unbounded | [num]) preceding and ([num] preceding | current row | (unbounded | [num]) following)
(rows | range) between 起始范围 and 结束范围

(rows | range) between current row and (current row | (unbounded | [num]) following)

(rows | range) between [num] following and (unbounded | [num]) following

特殊情况：

• 在over()中既没有出现windows_clause，也没有出现order by，窗口默认为rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
• 在over()中(没有出现windows_clause)，指定了order by，窗口默认为rows between UNBOUNDED PRECEDING and CURRENT ROW

窗口函数和分组有什么区别？

1. 如果是分组操作，select后只能写分组后的字段
2. 如果是窗口函数，窗口函数是在指定的窗口内，对每条记录都执行一次函数
3. 如果是分组操作，有去重效果，而partition不去重！

开窗函数格式： 函数名(列) OVER(选项)

OVER 关键字表示把函数当成开窗函数而不是聚合函数。
OVER 关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。

insert overwrite table `...` partition(platform_id)
select
t.user_id,...,t.platform_id from (select u.*,row_number() over (partition by u.user_id order by u.updated_at desc) as rn from `...` u) t where t.rn=1;

PARTITION BY 子句：

开窗函数的 OVER 关键字后括号中的可以使用 PARTITION BY 子句来定义行的分区来供进行聚合计算。与 GROUP BY 子句不同，PARTITION BY 子句创建的分区是独立于结果集的，创建的分区只是供进行聚合计算的，而且不同的开窗函数所创建的分区也不互相影响。

在同一个SELECT语句中可以同时使用多个开窗函数，而且这些开窗函数并不会相互干
扰。

--显示每一个人员的信息、所属城市的人员数以及同龄人的人数：
select fname,
       fcity,
       fage,
       fsalary,
       count(*) over(partition by fcity) 所属城市的人个数,
       count(*) over(partition by fage) 同龄人个数
  from t_person

ORDER BY子句：

ORDER BY 字段名 RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2

例1：

select fname,
       fcity,
       fage,
       fsalary,
       sum(fsalary) over(order by fsalary rows between unbounded preceding and current row) 到当前行工资求和
  from t_person

这里的开窗函数“SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)”表示按照FSalary进行排序，然后计算从第一行（UNBOUNDED PRECEDING）到当前行（CURRENT ROW）的和，这样的计算结果就是按照工资进行排序的工资值的累积和。

例2：

select fname,
       fcity,
       fage,
       fsalary,
       sum(fsalary) over(order by fsalary range between unbounded preceding and current row) 到当前行工资求和
  from t_person

例2的SQL语句与例1中的SQL语句唯一不同的就是“ROWS”被替换成了“RANGE”。“ROWS”是按照行数进行范围定位的，而“RANGE”则是按照值范围进行定位的，这两个不同的定位方式主要用来处理并列排序的情况。比如 Lily、Swing、Bill这三个人的工资都是2000元，如果按照
“ROWS”进行范围定位，则计算从第一条到当前行的累积和，而如果如果按照 “RANGE”进行范围定位，则仍然计算从第一条到当前行的累积和，不过由于等于2000元的工资有三个人，所以计算的累积和为从第一条到2000元工资的人员结束，所以对 Lily、Swing、Bill这三个人进行开窗函数聚合计算的时候得到的都是7000（“1000+2000+2000+2000 ”）。

row_number() over(partition by 分组列 order by 排序列 desc)

row_number() over 具有分组排序的功能

例3：

SELECT FName, FSalary,
SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) 后面一到三之和
FROM T_Person;

这里的开窗函数“SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING)”表示按照FSalary进行排序，然后计算从当前行后一行（1 FOLLOWING）到后三行（3 FOLLOWING）的工资和。注意最后一行没有后续行，其计算结果为
空值NULL而非0。

SELECT FName, FSalary,FAge,
MAX(FSalary) OVER(ORDER BY FAge) 此行之前最大值
FROM T_Person;

这里的开窗函数“MAX(FSalary) OVER(ORDER BY FAge)”是“MAX(FSalary) OVER(ORDER BY FAge RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)”的简化写法，它表示按照FSalary进行排序，然后计算从第一行（UNBOUNDED PRECEDING）到当前行（CURRENT ROW）的人员的最大工资值。

Tips

• count()在分组后，统计一个组内所有的数据！

参考文献：

https://www.cnblogs.com/lihaoyang/p/6756956.html