hive中分析、窗口函数

hql有着sql的方便性，可以方便的用于数据分析。

一、分析窗口函数

分析函数用于为行定义一个窗口，它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。
在SQL语句中，很多查询语句需要进行GROUP BY分组汇总，但是一旦经过分组，SELECT返回的记录孢数就会减少。为了保留所有原始行记录，并且仍可以进行分组数据分析，分析函数应运而生。
分析函数与聚合函数计算方式一样，分析函数也是对行集组进行聚合计算，但是它不像普通聚合仗函数那样每组只返回一个值，分析函数可以为每组返回多个值。
分析函数的语法为：over(partition by排 列名1 order by 列名2 rows between 列名)，括号中的两个关键词partition by 和order by 可以只出现一个。over() 前面是一个函数，如果是聚合函数，那么order by 不能一起使用。

直白的说窗口函数就是，先用over圈定一个窗口，在范围内进行相关行等操作。

常见的窗口函数：

函数名称	含义
FIRST_VALUE	取出分组内排序后，截止到当前行，第一个值
LAST_VALUE	取出分组内排序后，截止到当前行，最后一个值
LEAD(Colin,default	用于统计窗口内往下第n行的值。第一个参数为列名，第二个参数为往下第n行(可选，默认为1)，第三个参数为默认值(当往下第n行为NULL时，取默认值)
LAG(col,n,DEFAULT)	与lead相反，用于统计窗口内往下第n个值。第一个参数为列名，第二个参数为往上第n行(可选，默认为1)

常见的分析函数

函数名称	含义
ROW_NUMBER()	从1开始，按照顺序，生成分组内记录的序列，比如，按照pv降序排列，生成分组内每天的pv名次，ROW_NUMBER()的应用 场景非常多，再比如，获取分组内排序第一的记录，获取一个session中的第一条refer等
RANK()	生成数据项在分组中的排名，排名相等会在名次中留下空位
DENSE_RANK()	生成数据项在分组中的排名，排名相等会在名次中不会留下空位
CUME_DIST()	小于等于当前值的行数除以分组内总行数。比如，统计小于等于当前薪水的人数所占总人数的比例
PERCENT_RANK()	分组内当前行的RANK值-1/分组内 总行数-1
NTILE(n)	用于将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布。NTILE不支持ROWS BETWEEN,比如NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PERCEDING AND CURRENT ROW)

常见的聚合函数

函数名称	含义
count	count(*) – 返回检索到的行的总数，包括含有NULL值的行。count(expr) – 返回expr表达式不是NULL的行的数量count(DISTINCT expr[, expr]) – 返回expr是唯一的且非NULL的行的数量
sum	对组内某列求和
avg	对组内的某列元素求平均值者
min	返回组内最小值
max	返回组内最大值
variance	返回方差
var_samp	无偏样本方差
stddev_pop	标准差
stddev_samp(col)	返回组内某个数字列的无偏样本标准差
covar_pop(col1, col2)	返回组内两个数字列的总体协方差
covar_samp(col1, col2)	返回组内两个数字列的样本协方差
corr(col1, col2)	返回组内两个数字列的皮尔逊相关系数
percentile(BIGINT col, p)	返回组内某个列精确的第p位百分数，p必须在0和1之间
percentile(BIGINT col, array(p1 [, p2]…))	返回组内某个列精确的第p1，p2，……位百分数，p必须在0和1之间
percentile_approx(DOUBLE col, p [, B])	返回组内数字列近似的第p位百分数（包括浮点数），参数B控制近似的精确度，B值越大，近似度越高，默认值为10000。当列中非重复值的数量小于B时，返回精确的百分数
percentile_approx(DOUBLE col, array(p1 [, p2]…) [, B])	同上，但接受并返回百分数数组
histogram_numeric(col, b)	使用b个非均匀间隔的箱子计算组内数字列的柱状图（直方图），输出的数组大小为b，double类型的(x,y)表示直方图的中心和高度
collect_set(col)	返回消除了重复元素的数组
collect_list(col)	返回允许重复元素的数组
ntile(INTEGER x)	该函数将已经排序的分区分到x个桶中，并为每行分配一个桶号。这可以容易的计算三分位，四分位，十分位，百分位和其它通用的概要统计

hive在2.2.0后支持了部分聚合函数应用over()
具体可参考：https://www.jianshu.com/p/acc8b158daef
例如

select
    user_id,
    user_type,
    sales,
    -- 默认从起点到当前所有重复行
    sum(sales) over(partition by user_type order by sales asc) as sales_1,
    -- 从起点到当前所有重复行与sales_1结果相同
    sum(sales) over(partition by user_type order by sales asc range between unbounded preceding and current row) as sales_2,
    -- 从起点到当前行，结果与sale_1结果不同
    sum(sales) over(partition by user_type order by sales asc rows between unbounded preceding and current row) as sales_3,
    -- 当前行加上往前3行
    sum(sales) over(partition by user_type order by sales asc rows between 3 preceding and current row) as sales_4,
    -- 当前范围往上加3行
    sum(sales) over(partition by user_type order by sales asc range between 3 preceding and current row) as sales_5,
    -- 当前行+往前3行+往后1行
    sum(sales) over(partition by user_type order by sales asc rows between 3 preceding and 1 following) as sales_6,
    --
    sum(sales) over(partition by user_type order by sales asc range between 3 preceding and 1 following) as sales_7,
    -- 当前行+之后所有行
    sum(sales) over(partition by user_type order by sales asc rows between current row and unbounded following) as sales_8,
    --
    sum(sales) over(partition by user_type order by sales asc range between current row and unbounded following) as sales_9,
    -- 分组内所有行
    sum(sales) over(partition by user_type) as sales_10
from
    orders
order by
    user_type,
    sales,
    user_id;

二、分析窗口函数的用法

first_value(求组的第一个值)

select id,money,
first_value(money) over (partition by id order by money
rows between 1 preceding and 1 following)
from winfunc

每行对应的数据窗口是从第一行到最后一行
rows between unbounded preceding and unbounded following

lead(money,2) 取后面距离为2的记录值，没有就取null

select id,money,lead(money,2) over(order by money) from winfunc