hive分析相关窗口函数一之聚合相关

1、背景：

窗口函数不同于我们熟悉的常规函数及聚合函数，它为每行数据进行一次计算，特点是输入多行（一个窗口）、返回一个值。在报表等数据分析常见中，有很多应用，比如去重、排名、同比、环比、连续登陆等问题。

window 执行顺序

2、含义解释

开窗函数的表达式为

Function (arg1,..., argn) OVER ([PARTITION BY <...>] [ORDER BY <....>]
[<window_expression>])

其具体的开窗函数包含了排序、统计好聚合相关
聚合相关的，比如：sum(…)、 max(…)、min(…)、avg(…)等.
排序相关的，比如：rank(…)、row_number(…)等
统计和比较相关，比如：lead(…)、lag(…)、 first_value(…)等.

PARTITION BY 表示将数据先按 字段 进行分区
ORDER BY 表示将各个分区内的数据按 排序字段 进行排序
window_expression 用于确定窗口的边界：
preceding : 往前
following : 往后
current row :当前行
unbounded :起点
unbounded preceding :从前面的起点
unbounded following :从后面的终点
注意
1）、如果不指定 PARTITION BY，则不对数据进行分区，换句话说，所有数据看作同一个分区；
2）、如果不指定 ORDER BY，则不对各分区做排序，通常用于那些顺序无关的窗口函数，例如 SUM()
3）、如果不指定窗口子句，则默认采用以下的窗口定义：
a、若不指定 ORDER BY，默认使用分区内所有行 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
b、若指定了 ORDER BY，默认使用分区内第一行到当前值 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

过程：
按窗口定义，将所有输入数据分区、再排序（如果需要的话）
对每一行数据，计算它的窗口范围
将窗口内的行集合输入窗口函数，计算结果填入当前行
sum() : 针对传参类型为DECIMAL的，返回类型一致；除此之外的浮点型为DOUBLE；传参类型为整数类型的，返回类型为BIGINT。 sum 窗口函数返回所有输入值的表达式总和，忽略NULL值。
AVG()：参数类型为DECIMAL的返回类型为DECIMAL，其他为DOUBLE AVG 窗口函数返回输入表达式值的平均值，忽略 NULL 值。
COUNT()： 返回 BIGINT COUNT 窗口函数计算输入行数。 COUNT(*) 计算目标表中的所有行，包括Null值；COUNT(expression) 计算特定列或表达式中具有非 NULL 值的行数。
MAX() 与传参类型一致 MAX窗口函数返回表达式在所有输入值中的最大值，忽略 NULL 值。
MIN() 与传参类型一致 MIN窗口函数返回表达式在所有输入值中的最小值，忽略 NULL 值。

3、开窗

（聚合相关）

create external  table  test(
cookie_id  string,
create_time String,
pv INt

) row format Delimited
fields terminated by ','
stored as testfile  location '/**/**';

查询数据结果：
cookie1 2015-04-10      1
cookie1 2015-04-11      5
cookie1 2015-04-12      7
cookie1 2015-04-13      3
cookie1 2015-04-14      2
cookie1 2015-04-15      4
cookie1 2015-04-16      4

sum (结果和order by相关，默认为升序)

select 
cookieid,
createtime,
pv,
sum(pv) over(partition by cookieid  order by createtime) as pv1,
sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and currend row) as pv2，-- 从起点到当前行，结果同pv1
sum(pv) over (partition by cookieid) as pv3, -- 分组内所有行
sum(pv)  over (partition by cookieid order by createtime Rows between 3 preceding and currend row ) as pv4,-- 当前行+往前三行
sum(pv) over (partition by cookieid order by createtime  rows between 3 preceding and 1 following) as pv5,  --当前行+往前3行+往后1行
sum(pv) over(partition by cookieid  order by  createtime rows between current row and unbounded following) as pv6,-- 当前行+往后所有的行
from  test；

结果为：
cookieid createtime     pv      pv1     pv2     pv3     pv4     pv5      pv6

cookie1  2015-04-10      1       1       1       26      1       6       26
cookie1  2015-04-11      5       6       6       26      6       13      25
cookie1  2015-04-12      7       13      13      26      13      16      20
cookie1  2015-04-13      3       16      16      26      16      18      13
cookie1  2015-04-14      2       18      18      26      17      21      10
cookie1  2015-04-15      4       22      22      26      16      20      8
cookie1  2015-04-16      4       26      26      26      13      13      4

如果不指定ROWS BETWEEN,默认从起点到当前行。
如果不指定ORDER BY，则将分组内所有的值累加。
理解ROWS BETWEEN含义，已叫window子句。
precdding ：往前
following: 往后
current row :当前行
unbounded :起点 unbounded precedding 表示从前面起点， unbounded following：表示到后面终点。

–其他AVG，MIN，MAX，和SUM用法一样。

参考：
http://lxw1234.com/archives/tag/hive-window-functions
https://www.studytime.xin/article/hive-knowledge-window-function.html