sql开窗函数初体验

一、定义：

开窗函数用于为行定义一个窗口（这里的窗口是指运算将要操作的行的集合），它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。
开窗函数与聚合函数计算方式一样，开窗函数也是对行集组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值。
开窗函数的语法为：分析函数 + over(partition by 列名1 order by 列名2 ), 括号中的两个关键词partition by 和order by 可以只出现一个。over() 前面是一个函数。 可以是 count(), sum(), row_number() –来自百度百科
举个例子说明(使用mysql进行简单说明)：
有一个表 websites，里面的数据如下：

1.1 使用group by进行查询

select count(name) as websites,country from  websites GROUP BY country;

结果如下：

1.2 使用开窗函数

SELECTname, country, COUNT(name) OVER(PARTITION BY country) AS countCountry FROM websites; 

可以看出区别了，group by可以进行分组，但是一组值只会返回一个结果。而开窗函数不同，开窗函数可以每组返回多个值。 在一些复杂的查询环境下，开窗函数更能满足需求。

1.3 使用ORDER BY

select name, country,count(name) over (PARTITION by country ORDER BY id) as countCountry from websites;

结果：

结果解释：
开窗函数没有指定窗口范围的时候，默认统计窗口是从起点到当前行，也就是说，统计到 name = ‘taobao’ 这个网站的时候，从起点到它，都只有一条记录，也就是说此时窗口内只有一条数据，那么count函数获得的值就是1。
当函数计算到第二条数据，也就是 name = ‘runoonb’ 的时候，此时窗口有三个值，其中 country = ‘CN’ 的记录有两条，那么count的值就是2。
……
当计算到第四条数据，也就是 name = ‘*’ 时，此时窗口内一共包含四条数据， country = 'IND’的只有一条，那么该类别的count函数值肯定就是1。

1.4 rows关键字 指定窗口范围

select name, country, count(name) over (PARTITION by country ORDER BY id rows between 1 preceding and 2 following) as countCountry from websites;

这里使用rows关键字，将窗口范围限定在当前记录的前一条 – 当前记录的后两条之间。 也就是说窗口中会有四条数据。
结果：

结果解释：
当sql开始执行，第一次的窗口就包含了第一条数据，以及第一条数据的后面两条数据。因为它前面没有数据，那么此时窗口中就包含了三条数据。该三条数据，都属于’CN’， 所以此时 count函数的值就是3；第二条数据同理， 处理它时的窗口包含它的上一条数据+它+它的后两条数据，依然是有 三条 countrty = ‘CN’ 的数据。 当处理到第三条数据的时候，情况开始变化，它此时的窗口也是四条数据，前一条+它本身+它的后两条，它是属于’CN’的，当前窗口中的四条数据，已经只有两条的 country = 'CN‘ 了，所以此时的count函数的值为2。

二、多种分析函数的使用

2.1 min函数

表 score

#ROWS between 1 preceding and 0 following  当前行前一行 -- 当前行   
#ROWS between unbounded preceding and unbounded following   从起始行到结束行所有行
#rows between unbounded preceding and current row   从起始行到当前行
select *,min(degree) over(partition by CNO ORDER BY degree) as MinDegree from score
select *,min(degree) over(partition by CNO ORDER BY degree desc) as MinDegree from score

结果1：

结果2：

确实验证了 不加rows关键字的话，窗口范围就是默认从起始行到当前行

select CNO,DEGREE,min(degree) over(partition by CNO ) as MinDegree from score;

结果：

所以其实如果不加order by子句 计算范围是整个分区；

2.2 sum开窗函数

select CNO,DEGREE,sum(degree) over(partition by CNO ORDER BY degree) as MinDegree from score;

结果：

2.3 first_value开窗函数

该函数是返回分区的第一个值

select CNO,DEGREE,first_value(degree) over(PARTITION by cno ORDER BY degree) as first_value1 from score;
select CNO,DEGREE,first_value(degree) over (PARTITION by cno order by degree rows BETWEEN 1 preceding and 1 following) as first_value1 from score

结果1：

结果2：

2.4 last_vlaue开窗函数

作用：返回分区最后一个值

2.5 lag函数

lag函数其实是用于统计窗口内向上第n行的值。 比如说我需要查询比当前分数排名高3个名次的分数，就可以用到该函数
lag(column, n,default_value)； 如果没有设置default_value 那就是null

select cno,degree,lag(degree,3,60) over(PARTITION  by cno ORDER BY degree) as lag1 from score;
select cno,degree,lag(degree,3) over(PARTITION  by cno ORDER BY degree) as lag1 from score;

结果1：

结果2：

2.6 lead函数

跟lag相反，向下统计第n行的值

select cno,degree,lead(degree,3,60) over(PARTITION  by cno ORDER BY degree) as lag1 from score;
select cno,degree,lead(degree,3) over(PARTITION  by cno ORDER BY degree) as lag1 from score;

可以发现这里依然受partitiion by 函数制约， degree=‘88’ 的值 往后第三行已经是另一个分区的分数了，所以会返回null

2.7 row_number函数

顾名思义，就是个计算行号的函数

select cno,degree,row_number() over(PARTITION  by cno ORDER BY degree) as rowNUmber from score;

结果：

此时往表中插入几条分数相同的数据
再执行sql，查看结果：

不管你结果degree 是否相同，行号会一直增加。

2.8 rank开窗函数

同样也是计算“行号”的函数，但是不同的是，相同的degree 它会返回相同的行号，但是下一个行号就会是递增的行号
举例： 该表有并列的两个degree 都是返回4，但是 下一个degree 会返回6

select cno,degree,rank() over(PARTITION  by cno ORDER BY degree) as rowNUmber from score;

结果：

2.9 dense_rank开窗函数

select cno,degree,dense_rank() over(PARTITION  by cno ORDER BY degree) as rowNUmber from score;

结果：

与rank函数不同， dense_rank不会新增行号，就算你有并列的数据，下一次依然只 + 1；

hive同样可以使用开窗函数 十分方便，但是就是mapreduce是真的慢