掌握Hive的强大工具：实用且高效的窗口函数详解
与聚合函数类似，开窗函数也是对行集组进行聚合计算。但是它不像普通聚合函数那样，每组通常只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计算的行集组是窗口。
常见的格式如下：
FUNCTION_NAME([argument_list])
OVER (
[PARTITION BY window_partition,…]
[ORDER BY window_ordering, … [ASC|DESC]])
[ { ROWS | RANGE } BETWEEN frame_start AND frame_end ] );FUNCTION_NAME：函数名称。如row_number()、sum()、first_value()等。
argument_list：函数的参数列表。
PARTITION BY：根据window_partition（分区字段）进行分区，该子句也被称为查询分区子句。类似于group by，都是将数据按照边界值进行分组。而OVER之前的函数在每一个分组之内进行，如果超出了分组，则函数会重新计算。
ORDER BY：将各个分区内的数据，根据window_ordering（排序字段）进行排序。ORDER BY子句会对输入的数据强制排序（窗口函数是SQL语句最后执行的函数，因此可以把SQL结果集想象成输入数据）。ORDER BY子句对于诸如row_number()，lead()，lag()等函数是必须的。如果数据无序，这些函数的结果就没有意义。
ROWS和RANGE分别表示选择前后几行、选择数据范围。
在介绍具体的开窗函数和示例之前，再来了解一下window子句：
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：起点
UNBOUNDED PRECEDING 表示从前面的起点
UNBOUNDED FOLLOWING：表示到后面的终点
后面会有具体应用的示例。
sum() over()
数据：
+---+----------------+---+
|id |date |pv |
+---+----------------+---+
|1 |2015-04-10 |1 |
|1 |2015-04-11 |5 |
|1 |2015-04-12 |7 |
|2 |2015-04-13 |3 |
|2 |2015-04-14 |2 |
|2 |2015-04-15 |4 |
|3 |2015-04-16 |4 |
+---+----------------+---+sum() over()应用的SQL：
SELECT id,
date,
pv,
-- 默认为从起点到当前行进行累加
SUM(pv) OVER(PARTITION BY id ORDER BY date) AS pv1,
--从起点到当前行进行累加，结果同pv1
SUM(pv) OVER(PARTITION BY id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2,
--以id分组内所有行pv累加
SUM(pv) OVER(PARTITION BY id) AS pv3,
--当前行+往前3行
SUM(pv) OVER(PARTITION BY id ORDER BY date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4,
--当前行+往前3行+往后1行
SUM(pv) OVER(PARTITION BY id ORDER BY date ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5,
---当前行+往后所有行S
UM(pv) OVER(PARTITION BY id ORDER BY date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6
FROM data;运行结果：
+---+----------------+---+----+----+----+----+----+----+
|id |date |pv |pv1 |pv2 |pv3 |pv4 |pv5 |pv6 |
+---+----------------+---+----+----+----+----+----+----+
|1 |2015-04-10 |1 |1.0 |1.0 |13.0|1.0 |6.0 |13.0|
|1 |2015-04-11 |5 |6.0 |6.0 |13.0|6.0 |13.0|12.0|
|1 |2015-04-12 |7 |13.0|13.0|13.0|13.0|13.0|7.0 |
|2 |2015-04-13 |3 |3.0 |3.0 |9.0 |3.0 |5.0 |9.0 |
|2 |2015-04-14 |2 |5.0 |5.0 |9.0 |5.0 |9.0 |6.0 |
|2 |2015-04-15 |4 |9.0 |9.0 |9.0 |9.0 |9.0 |4.0 |
|3 |2015-04-16 |4 |4.0 |4.0 |4.0 |4.0 |4.0 |4.0 |
+---+----------------+---+----+----+----+----+----+----+注：这些窗口的划分都是在分区内部进行。如果不指定ROWS BETWEEN，默认统计窗口为从起点到当前行。
row_number() over()
为查询出来的每一行记录生成一个序号。序号从1开始，按照顺序，生成分组内记录的序列，row_number()的值不会存在重复，当排序的值相同时，按照表中记录的顺序进行排列。
示例：利用row_number函数，对表中的数据根据id进行分组，按照pv倒序排序求最大的pv相关信息。
select
t.id,
t.date,
t.pv
from
(select
id,
date,
pv,
row_number() over(partition by id order by pv desc) as rn
from data ) t
where t.rn = 1;结果：
+---+----------------+---+
|id |date |pv |
+---+----------------+---+
|1 |2015-04-12 |7 |
|2 |2015-04-15 |4 |
|3 |2015-04-16 |4 |
+---+----------------+---+rank和dense_rank
rank对查询出来的记录进行排名。与row_number函数不同的是，rank函数考虑到了over子句中排序字段值相同的情况，如果使用rank函数来生成序号，over子句中排序字段值相同的序号是一样的，后面字段值不相同的序号将跳过相同的排名号排下一个，也就是相关行之前的排名数加一，可以理解为根据当前的记录数生成序号，后面的记录依此类推。
dense_rank功能与rank函数类似，但dense_rank函数在生成序号时是连续的。dense_rank函数出现相同排名时，将不跳过相同排名号。
rank值紧接上一次的rank值。在各个分组内，rank()是跳跃排序，有两个第一名时接下来就是第三名，dense_rank()是连续排序，有两个第一名时仍然跟着第二名。
示例：
数据如下：
+---+----------------+---+
|id |date |pv |
+---+----------------+---+
|1 |2015-04-10 |1 |
|1 |2015-04-11 |5 |
|1 |2015-04-12 |7 |
|2 |2015-04-13 |3 |
|2 |2015-04-14 |4 |
|2 |2015-04-15 |4 |
|3 |2015-04-16 |4 |
+---+----------------+---+结果：
+---+----------------+---+----+----------+
|id |date |pv |rank|dense_rank|
+---+----------------+---+----+----------+
|1 |2015-04-12 |7 |1 |1 |
|1 |2015-04-11 |5 |2 |2 |
|1 |2015-04-10 |1 |3 |3 |
|2 |2015-04-14 |4 |1 |1 |
|2 |2015-04-15 |4 |1 |1 |
|2 |2015-04-13 |3 |3 |2 |
|3 |2015-04-16 |4 |1 |1 |
+---+----------------+---+----+----------+row_number、rank和dense_rank的对比：
相同点：都是分组排序
不同点：
row_number：即便出现相同的排序，排名也不会一致，只会进行累加；即排序次序连续，但不会出现同一排名。
rank：当出现相同的排序时，中间会出现一个空缺，即分组内会出现同一个排名，但是排名次序是不连续的。
dense_rank：当出现相同排序时，中间不会出现空缺，即分组内可能会出现同样的次序，且排序名次是连续的。
first_value
含义：取分组内排序后，截止到当前行，第一个值。
示例：按部门分组，统计每个部门员工工资以及该部门最低的员工工资。
数据（后面几个开窗函数也会用到这些数据）：
+-------+-------+---------+------+----------+
|name |dept_no|employ_id|salary|entry_time|
+-------+-------+---------+------+----------+
|mike |1 |1 |10000 |2014-01-29|
|tom |1 |2 |8000 |2013-10-02|
|john |1 |3 |6000 |2014-10-02|
|jerry |2 |4 |6600 |2012-11-03|
|jack |2 |5 |5000 |2010-01-03|
|rose |2 |6 |4000 |2014-11-29|
|steven |3 |7 |5000 |2014-12-02|
|richard|3 |8 |9000 |2013-11-03|
+-------+-------+---------+------+----------+SQL：
SELECT
name,
dept_no,
salary,
first_value(salary) OVER (PARTITION BY dept_no ORDER BY salary ) as fv
FROM data;结果：
+-------+-------+------+----+
|name |dept_no|salary|fv |
+-------+-------+------+----+
|john |1 |6000 |6000|
|tom |1 |8000 |6000|
|mike |1 |10000 |6000|
|rose |2 |4000 |4000|
|jack |2 |5000 |4000|
|jerry |2 |6600 |4000|
|steven |3 |5000 |5000|
|richard|3 |9000 |5000|
+-------+-------+------+----+last_value
取分组内排序后，截止到当前行，最后一个值。但是使用last_value需要特别注意，这涉及到上述所说的window子句。先看一个例子：
按部门分组，统计每个部门员工工资以及该部门最高的员工工资。
执行如下SQL：
SELECT
name,
dept_no,
salary,
last_value(salary) OVER (PARTITION BY dept_no ORDER BY salary ) as fv
FROM data;结果：
+-------+-------+------+-----+
|name |dept_no|salary|fv |
+-------+-------+------+-----+
|john |1 |6000 |6000 |
|tom |1 |8000 |8000 |
|mike |1 |10000 |10000|
|rose |2 |4000 |4000 |
|jack |2 |5000 |5000 |
|jerry |2 |6600 |6600 |
|steven |3 |5000 |5000 |
|richard|3 |9000 |9000 |
+-------+-------+------+-----+正确结果dept_no为1、2、3的部门，最高工资应该分别是10000、6600、9000才对，为什么会出现上述结果呢？
这是因为last_value默认的窗口是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，表示当前行永远是最后一个值，需改成RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。
SELECT
name,
dept_no,
salary,
last_value(salary) OVER (PARTITION BY dept_no ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as fv
FROM data;结果：
+-------+-------+------+-----+
|name |dept_no|salary|fv |
+-------+-------+------+-----+
|john |1 |6000 |10000|
|tom |1 |8000 |10000|
|mike |1 |10000 |10000|
|rose |2 |4000 |6600 |
|jack |2 |5000 |6600 |
|jerry |2 |6600 |6600 |
|steven |3 |5000 |9000 |
|richard|3 |9000 |9000 |
+-------+-------+------+-----+此外：
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：为默认值，即当指定了ORDER BY从句，而省略了window从句 ，表示从开始到当前行（当前行永远是最后一个值）。
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING：表示从当前行到最后一行。
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING：表示所有行。
n PRECEDING m FOLLOWING：表示窗口的范围是[（当前行的行数）- n, （当前行的行数）+ m] row。
cume_dist
如果按升序排列，则统计：小于等于当前值的行数/总行数(number of rows ≤ current row)/(total number of rows）。如果是降序排列，则统计：大于等于当前值的行数/总行数。
示例：
1. 统计小于等于当前工资的人数占总人数的比例。
SELECT
name,
dept_no,
salary,
cume_dist() OVER (ORDER BY salary) as cume_dist
FROM data;结果：
+-------+-------+------+---------+
|name |dept_no|salary|cume_dist|
+-------+-------+------+---------+
|rose |2 |4000 |0.125 |
|jack |2 |5000 |0.375 |
|steven |3 |5000 |0.375 |
|john |1 |6000 |0.5 |
|jerry |2 |6600 |0.625 |
|tom |1 |8000 |0.75 |
|richard|3 |9000 |0.875 |
|mike |1 |10000 |1.0 |
+-------+-------+------+---------+如果要统计大于等于当前工资的人数占总人数的比例，只需更改salary排序规则为降序desc即可。
2. 根据部门统计小于等于当前工资的人数占部门总人数的比例
SELECT
name,
dept_no,
salary,
cume_dist() OVER (PARTITION BY dept_no ORDER BY salary) as cume_dist
FROM data;lead(value_expr[,offset[,default]])
用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL。
示例：根据部门分组，统计每个部门员工的工资以及大于等于该员工工资的下一个员工的工资。
SELECT
name,
dept_no,
salary,
lead(salary,1) OVER (PARTITION BY dept_no ORDER BY salary) as lead
FROM data;结果：
+-------+-------+------+-----+
|name |dept_no|salary|lead |
+-------+-------+------+-----+
|john |1 |6000 |8000 |
|tom |1 |8000 |10000|
|mike |1 |10000 |null |
|rose |2 |4000 |5000 |
|jack |2 |5000 |6600 |
|jerry |2 |6600 |null |
|steven |3 |5000 |9000 |
|richard|3 |9000 |null |
+-------+-------+------+-----+lag(value_expr[,offset[,default]])
与lead相反，用于统计窗口内往上第n行值。第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL。
示例：根据部门分组，统计每个部门员工的工资以及小于等于该员工工资的上一个员工的工资。
SELECT
name,
dept_no,
salary,
lag(salary,1) OVER (PARTITION BY dept_no ORDER BY salary) as lag
FROM data;结果：
+-------+-------+------+----+
|name |dept_no|salary|lag |
+-------+-------+------+----+
|john |1 |6000 |null|
|tom |1 |8000 |6000|
|mike |1 |10000 |8000|
|rose |2 |4000 |null|
|jack |2 |5000 |4000|
|jerry |2 |6600 |5000|
|steven |3 |5000 |null|
|richard|3 |9000 |5000|
+-------+-------+------+----+ntile
NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值。将一个有序的数据集划分为多个桶(bucket)，并为每行分配一个适当的桶数。它可用于将数据划分为相等的小切片，为每一行分配该小切片的数字序号。
NTILE不支持ROWS BETWEEN，比如NTILE(2) OVER(PARTITION BY dept_no ORDER BY salary ROWS BETWEEN 3 PRECEDING - AND CURRENT ROW)。
如果切片不均匀，默认增加第一个切片的分布。
例如：
SELECT name, dept_no, salary,
ntile(2) over(order by salary) n1,-- 全局按照salary升序排列，数据切成3份
ntile(2) over(partition by dept_no order by salary) n2, -- 按照dept_no分组，在分组内按照salary升序排列,数据切成2份
ntile(3) over(partition by dept_no order by salary) n3 -- 按照dept_no分组，在分组内按照salary升序排列,数据切成3份
FROM data;这个函数用什么应用场景呢？假如我们想要统计一个cookie，pv数最多的前1/3的天。
SELECT
id,
date,
pv,
NTILE(3) OVER(PARTITION BY id ORDER BY pv DESC) AS rn
FROM data;rn = 1的记录，就是我们想要的结果。
+---+----------------+---+---+
|id |date |pv |rn |
+---+----------------+---+---+
|1 |2015-04-12 |7 |1 |
|1 |2015-04-11 |5 |2 |
|1 |2015-04-10 |1 |3 |
|2 |2015-04-14 |4 |1 |
|2 |2015-04-15 |4 |2 |
|2 |2015-04-13 |3 |3 |
|3 |2015-04-16 |4 |1 |
+---+----------------+---+---+注意：NTILE、ROW_NUMBER、RANK、DENSE_RANK等序列函数不支持window子句。
推荐阅读
-
掌握Hive的强大工具：实用且高效的窗口函数详解
-
包婷婷 （201550484）作业一 统计软件简介与数据操作-SPSS(Statistical Product and Service Solutions)，"统计产品与服务解决方案"软件。最初软件全称为"(SolutionsStatistical Package for the Social Sciences)，但是随着SPSS产品服务领域的扩大和服务深度的增加，SPSS公司已于2000年正式将英文全称更改为"统计产品与服务解决方案"，标志着SPSS的战略方向正在做出重大调整。为IBM公司推出的一系列用于统计学分析运算、数据挖掘、预测分析和决策支持任务的软件产品及相关服务的总称SPSS，有Windows和Mac OS X等版本。 1984年SPSS总部首先推出了世界上第一个统计分析软件微机版本SPSS/PC+，开创了SPSS微机系列产品的开发方向，极大地扩充了它的应用范围，并使其能很快地应用于自然科学、技术科学、社会科学的各个领域。世界上许多有影响的报刊杂志纷纷就SPSS的自动统计绘图、数据的深入分析、使用方便、功能齐全等方面给予了高度的评价。 R统计软件介绍 R是一套完整的数据处理、计算和制图软件系统。其功能包括:数据存储和处理系统;数组运算工具(其向量、矩阵运算方面功能尤其强大);完整连贯的统计分析工具;优秀的统计制图功能;简便而强大的编程语言:可操纵数据的输入和输出，可实现分支、循环，用户可自定义功能。 与其说R是一种统计软件，还不如说R是一种数学计算的环境，因为R并不是仅仅提供若干统计程序、使用者只需指定数据库和若干参数便可进行一个统计分析。R的思想是:它可以提供一些集成的统计工具，但更大量的是它提供各种数学计算、统计计算的函数，从而使使用者能灵活机动的进行数据分析，甚至创造出符合需要的新的统计计算方法。 该语言的语法表面上类似 C，但在语义上是函数设计语言(functional programming language)的变种并且和Lisp 以及 APL有很强的兼容性。特别的是，它允许在"语言上计算"(computing on the language)。这使得它可以把表达式作为函数的输入参数，而这种做法对统计模拟和绘图非常有用。 R是一个免费的*软件，它有UNIX、LINUX、MacOS和WINDOWS版本，都是可以免费下载和使用的。在R主页那儿可以下载到R的安装程序、各种外挂程序和文档。在R的安装程序中只包含了8个基础模块，其他外在模块可以通过CRAN获得。 二、R语言 R是用于统计分析、绘图的语言和操作环境。R是属于GNU系统的一个*、免费、源代码开放的软件，它是一个用于统计计算和统计制图的优秀工具。 R作为一种统计分析软件，是集统计分析与图形显示于一体的。它可以运行于UNIX,Windows和Macintosh的操作系统上，而且嵌入了一个非常方便实用的帮助系统，相比于其他统计分析软件，R还有以下特点： 1.R是*软件。这意味着它是完全免费,开放源代码的。可以在它的网站及其镜像中下载任何有关的安装程序、源代码、程序包及其源代码、文档资料。标准的安装文件身自身就带有许多模块和内嵌统计函数,安装好后可以直接实现许多常用的统计功能。[2] 2.R是一种可编程的语言。作为一个开放的统计编程环境,语法通俗易懂,很容易学会和掌握语言的语法。而且学会之后,我们可以编制自己的函数来扩展现有的语言。这也就是为什么它的更新速度比一般统计软件,如,SPSS,SAS等快得多。大多数最新的统计方法和技术都可以在R中直接得到。[2] 3. 所有R的函数和数据集是保存在程序包里面的。只有当一个包被载入时,它的内容才可以被访问。一些常用、基本的程序包已经被收入了标准安装文件中,随着新的统计分析方法的出现,标准安装文件中所包含的程序包也随着版本的更新而不断变化。在另外版安装文件中,已经包含的程序包有：base一R的基础模块、mle一极大似然估计模块、ts一时间序列分析模块、mva一多元统计分析模块、survival一生存分析模块等等.[2] 4.R具有很强的互动性。除了图形输出是在另外的窗口处，它的输入输出窗口都是在同一个窗口进行的，输入语法中如果出现错误会马上在窗口口中得到提示,对以前输入过的命令有记忆功能,可以随时再现、编辑修改以满足用户的需要。输出的图形可以直接保存为JPG,BMP,PNG等图片格式,还可以直接保存为PDF文件。另外,和其他编程语言和数据库之间有很好的接口。[2] 5.如果加入R的帮助邮件列表一,每天都可能会收到几十份关于R的邮件资讯。可以和全球一流的统计计算方面的专家讨论各种问题,可以说是全世界最大、最前沿的统计学家思维的聚集地.[2] R是基于S语言的一个GNU项目，所以也可以当作S语言的一种实现，通常用S语言编写的代码都可以不作修改的在R环境下运行。 R的语法是来自Scheme。R的使用与S-PLUS有很多类似之处，这两种语言有一定的兼容性。S-PLUS的使用手册，只要稍加修改就可作为R的使用手册。所以有人说：R，是S-PLUS的一个“克隆”。 但是请不要忘了：R是免费的(R is free)。R语言源代码托管在github，具体地址可以看参考资料。[3] 。 R语言的下载可以通过CRAN的镜像来查找。 R语言有域名为.cn的下载地址，有六个，其中两个由Datagurn，由 中国科学技术大学提供的。R语言Windows版，其中由两个下载地点是Datagurn和 USTC提供的。 三、stata Stata 是一套提供其使用者数据分析、数据管理以及绘制专业图表的完整及整合性统计软件。它提供许许多多功能，包含线性混合模型、均衡重复反复及多项式普罗比模式。用Stata绘制的统计图形相当精美。 新版本的STATA采用最具亲和力的窗口接口，使用者自行建立程序时，软件能提供具有直接命令式的语法。Stata提供完整的使用手册，包含统计样本建立、解释、模型与语法、文献等超过一万余页的出版品。 除此之外，Stata软件可以透过网络实时更新每天的最新功能，更可以得知世界各地的使用者对于STATA公司提出的问题与解决之道。使用者也可以透过Stata. Journal获得许许多多的相关讯息以及书籍介绍等。另外一个获取庞大资源的管道就是Statalist，它是一个独立的listserver，每月交替提供使用者超过1000个讯息以及50个程序。 四、PYTHON