数据库--高阶--分析函数(包括排名函数、窗口函数、统计函数)]

http://www.cnblogs.com/Azhu/archive/2012/04/09/2438346.html

OVER分析函数

OVER子句用于为行为定义一个窗口，以便进行特定的运算。可以把行的窗口简单地认为是运算将要操作的一个行的集合。使用OVER子句提供窗口作为上下文，对窗口中的一组值进行操作，而不是使用GROUP BY子句提供的上下文。这样就不必对数据进行分组，还能够在同一行中同时返回基础行的列和聚合列。带有空括号的OVER子句会提供所有行进行计算。这里的“所有行”并不一定是在FROM子句中出现的那些表中的所有行，而是在FROM、WHERE、GROUP BY，以及HAVING处理阶段完成后仍然可用的那些行。注意，只有在SELECT和ORDER BY处理阶段才允许使用OVER子句。

配合PARTITION BY和ORDER BY等

1.评级函数：用于等级、百分点、n分片等

RANK()	返回数据项在分组中的排名，排名相等会在名次中留下空位
DENSE_RANK()	返回数据项在分组中的排名，排名相等会在名次中不会留下空位
CUME_DIST()	返回特定值对于一组值的位置“cumulative distribution”(累积分布)
PERCENT_RANK()(	返回某个值相对于一组值的百分比排名
NTILE()	返回n分片后的值
ROW_NUMBER()	为每条记录返回一个数字

比如Eg：某产品类型有两个并列第一
RANK()：第一二为1，第三位3
DENSE_RANK()：第一二为1，第三位2

SELECT column_name,
RANK() OVER （ORDER BY column_name DESC） AS rank,
DENSE_RANK() OVER （ORDER BY column_name DESC） AS dense_rank
FROM table_name

注意：order by 时，desc NULL 值排在首位，ASC时NULL值排在末尾

RANK() OVER (ORDER BY column_name DESC NULLS LAST)

进一步，按照month 来分，即所需要排列的信息先以month 的值来分组，在分组中排序，各个分组间不干涉

RANK() OVER(PARTITION BY month ORDER BY column_name DESC)

还可以与ROLLUP、CUBE、GROUP SETS （只显示小计信息） 结合使用

2 窗口函数（最为重要）：

http://blog.chinaunix.net/uid-7450061-id-2054536.html        写的很好 

http://www.blogjava.net/pengpenglin/archive/2008/06/28/211334.html#part1

可以计算一定范围内、一定值域内、或者一段时间内的累积和以及移动平均值等。
可以结合聚集函数SUM() 、AVG() 等使用。
可以结合FIRST_VALUE() 和LAST_VALUE()，返回窗口的第一个和最后一个值

计算累积和：
eg：统计1-12月的累积销量，即1月为1月份的值，2月为1.2月份值的和，3月为123月份的和，12月为1-12月份值的和

SELECT
month,SUM(amount) month_amount,
SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount
FROM table_name
GROUP BY month
ORDER BY month;

其中：
SUM( SUM(amount)) 内部的SUM(amount)为需要累加的值，在上述可以换为 month_amount
ORDER BY month 按月份对查询读取的记录进行排序，就是窗口范围内的排序
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 定义起点和终点，UNBOUNDED PRECEDING 为起点，表明从第一行开始, CURRENT ROW为默认值，就是这一句等价于：
ROWS UNBOUNDED PRECEDING
PRECEDING：在前  的意思。
FOLLOWING: 在后  的意思。
计算前3个月之间的和

SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS cumulative_amount

也可以

SUM( SUM(amount)) OVER (ORDER BY month 3 PRECENDING) AS cumulative_amount

前后一个月之间的和

SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS cumulative_amount

窗体第一条和最后一条的值

FIRST_VALUE(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS xxxx;

LAST_VALUE(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS xxxx;

这样就可以获得该行上一行和下一行的值

3.报表函数：
用于执行跨越分组和组内分区的计算  （这里的组应该指的是 ORDER BY 的分组）

SUM(column_name1) OVER(PARTITION BY  column_name2)

这样，会将数据按column_name2分组，然后求column_name1的和，但在表输出的时候是按ORDER BY 格式输出
这样输出列会重复，例如相同的column_name2 会输出相同的column_name1

4.LAG()、LEAD()
获得相对于当前记录指定距离的那条记录的数据
LAG()为向前、LEAD()为向后

LAG(column_name1,1) OVER(ORDER BY column_name2)
LEAD(column_name1,1) OVER(ORDER BY column_name2)

这样就获得前一条、后一条的数据

5.FIRST、LAST
获得一个排序分组中的第一个值和组后一个值。可以与分组分组函数结合。

SELECT
MIN(month) KEEP(DENSE_RANK FIRST ORDER BY SUM(amount)) AS highest_sales_month,
MIN(month) KEEP(DENSE_RANK LAST ORDER BY SUM(amount)) AS lows_sales_month
FROM table_name
GROUP BY month
ORDER BY month;

这样就可以求得一年中销量最高和最低的月份。
注意：输出的是月份，但是用SUM(amount)来判断。

6.使用线性回归函数


7.使用假想评级与分布函数