窗口函数

形式:

函数() Over (PARTITION By 列1，列2，Order By 列3，窗口子句) AS 列别名

窗口子句：（与order by 一起使用）

range/rows between【num】preceding and 【num】 following

order by子句后面没有指定窗口子句，
则默认为：range/rows between unbounded preceding and current row

补充
preceding：往前
following :往后
current row：当前行
unbounded：起点
unbounded precending： 从前面的起点
unbounded following : 到后面的终点

窗口子句举例：

1. range/rows between 1 preceding and 2 following
2. range/rows between unbounded preceding and current row
3. (rows/range) between (unbounded/[num]) precending and ([num] precending/current row/(unbounded /[num]) following)
4. (rows/range) between current row and (current row | (unbounded | [num]) following) 当前行到num行之后
5. (rows/range) between [num] following and (unbounded | [num]) following 后num行到后num行/终点

range和row的区别：

1. range是逻辑窗口（当前行对应值 order by 字段 的范围取值）
2. rows是物理窗口，（order by 子句排序后，取的前N行及后N行的数据计算）

窗口函数：

OLAP函数或分析函数

特点：

1. 输出结果数等于输入数据行数
2. 只是在原来数据上添加列
3. 是整个sql最后执行的部分

好处：

1. 类似Group By的聚合
2. 非顺序的访问数据
3. 可以对于窗口函数使用分析函数、聚合函数和排名函数
4. 简化了SQL代码（消除Join）
5. 消除中间表

场景：

1. 分区排序
2. 动态Group By
3. 分组求Top N
4. 累计计算
5. 层次查询

常用的窗口函数

分析函数

1. row_number() 从1开始，按1递增
2. rank() 1,1,3
3. dense_rank() 1,1,2
4. cume_dist() 小于等于当前值的行数/分组内总行数
5. percent_rank() 分组内当前行的rank值-1/分组内总行数-1
6. ntile(n) 将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布。ntile不支持rows between，比如 ntile(2) over(partition by cookieid order by createtime rows between 3 precending and current row)。

窗口函数

1. first_value：取分组内排序后，截止到当前行，第一个值
2. last_value： 取分组内排序后，截止到当前行，最后一个值
3. lead(col,n,default) ：用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
4. lag(col,n,default) ：与lead相反，用于统计窗口内往上第n行值。第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
5. ratio_to_report() over(partition by … order by …) --Ratio_to_report() 括号中就是分子，over() 括号中就是分母。

聚合函数

1. count(…) over(partition by … order by …)–求分组后的总数。
2. sum(…) over(partition by … order by …)–求分组后的和。
3. max(…) over(partition by … order by …)–求分组后的最大值。
4. min(…) over(partition by … order by …)–求分组后的最小值。
5. avg(…) over(partition by … order by …)–求分组后的平均值。

1. Hive2.1.0及以后支持Distinct
   在聚合函数（SUM, COUNT and AVG）中，支持distinct，但是在ORDER BY 或者 窗口限制不支持。
   COUNT(DISTINCT a) OVER (PARTITION BY c)

2. Hive 2.2.0中在使用ORDER BY和窗口限制时支持distinct
   COUNT(DISTINCT a) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

3. Hive2.1.0及以后支持在OVER从句中支持聚合函数

SELECT rank() OVER (ORDER BY sum(b))
FROM T
GROUP BY a;

Hive 自定义函数函数：

UDF 一进一出 处理原文件内容某些字段包含 [] “”
UDAF 多进一出 sum() avg() max() min()
UDTF 一进多出 ip -> 国家 省 市

Hive4种排序：

1. order by【desc 降序 asc 升序】
   全局排序，只有一个Reducer
2. sort by 【对分区内的数据进行排序】
3. distribute by 【对map输出进行分区】
4. cluster by
   distribute by和sort by 是同一个字段的时可以使用cluster by替代。只能是倒叙排序。

举例：

select * from (
   select *,row_number() over(
      distribute by custo
      sort by recdate asc appid desc
      case when product = carttype
           then '1' 
           else '2' 
      end asc //此处是排序的一个字段。、
      ) as rank
  from table
)t

select *, row_number() over(order by custo desc) as rank from table;

三种分组的区别

1. row_number：1，2，3
2. rank：1，1，3
3. dense_rank：1，1，2

参考：https://www.cnblogs.com/wenBlog/p/10361404.html#4177290
https://www.cnblogs.com/CareySon/p/3411176.html
https://www.cnblogs.com/kuangwong/p/10540662.html
https://www.cnblogs.com/abc8023/p/10910741.html