oracle分片窗口函数,oracle评级函数

1.评级函数：

用于等级、百分点、n分片等

函数

申明

RANK()

返回数据项在分组中的排名，排名相等会在名次中留下空位

DENSE_RANK()

返回数据项在分组中的排名，排名相等会在名次中不会留下空位

CUME_DIST()

返回特定值对于一组值的地位“cumulative distribution”(累积分布)

PERCENT_RANK()(

返回某个值相对于一组值的百分比排名

NTILE()

返回n分片后的值

ROW_NUMBER()

为每笔记录返回一个数字

RANK()、DENSE_RANK()

RANK()在呈现等级雷同的元素时预留为空，DENSE_RANK()不会。

Eg：某产品类型有两个并列第一

RANK()：第一二为1，第三位3

DENSE_RANK()：第一二为1，第三位2

1

SELECT

2 column_name，

3 RANK() OVER (ORDER

BY column_name DESC) AS

rank，

4 DENSE_RANK() OVER (ORDER

BY SUM(column_name) DESC)

AS dense_rank

5 FROM table_name

OVER 须要，括号内为编号次序

重视：order by 时，desc NULL 值排在首位，ASC时NULL值排在末尾

可以经由过程NULLS LAST、NULLS FIRST把握

RANK() OVER

(ORDER BY column_name

DESC NULLS LAST)

PARTITION BY分组分列次序

RANK() OVER(PARTITION

BY month ORDER

BY column_name DESC)

如许，就会遵守month 来分，即所须要分列的信息先以month

的值来分组，在分组中排序，各个分组间不干涉干与

ROLLUP、CUBE、GROUP SETS (只显示小计信息) 与 RANK() 连络应用

CUME_DIST()、PERCENT_RANK()

反百分比函数：PERCENTILE_DISC(x)，

PERCENTILE_CONT(x)

NTILE()

ROW_NUMBER()

ROW_NUMBER()从1开端，为每笔记录返回一个数字

SELECT

ROW_NUMBER() OVER(ORDER BY column_name DESC)AS row_name

FROM table_name;

2.窗口函数:

可以策画必然局限内、必然值域内、或者一段时候内的累积和以及移动均匀值等。

可以连络凑集函数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 CURRENTROW 定义出发点和终点，UNBOUNDED PRECEDING 为出发点，注解从第一行开端， CURRENTROW为默认值，就是这一句等价于：

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)

LEAG(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.应用假想评级与分布函数