Oracle 分析函数
分析函数是基于一组行来计算的。这不同于聚集函数且广泛应用于OLAP环境中。
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是
对于每个组返回多行，而聚合函数对于每个组只返回一行。
语法：
analytic_function([arguments])
OVER(
[partition_clause]
[order_by_clause[windowing_clause]]
)
其中：
1 over是关键字，用于标识分析函数。
2 是指定的分析函数的名字。Oracle分析函数很多。
3 为参数，分析函数可以选取0-3个参数。
4 分区子句的格式为：
partition by[,value_expr]...
关键字partition by子句根据由分区表达式的条件逻辑地将单个结果集分成N组。这里的"分区partition"和"组group"
都是同义词。
5 排序子句order-by-clause指定数据是如何存在分区内的。其格式为：
order[siblings]by{expr|position|c_alias}[asc|desc][nulls first|nulls last]
其中：
(1)asc|desc:指定了排列顺序。
(2)nulls first|nulls last:指定了包含空值的返回行应出现在有序序列中的第一个或最后一个位置。
6窗口子句windowing-clause
给出一个固定的或变化的数据窗口方法，分析函数将对这些数据进行操作。在一组基于任意变化或固定的窗口中，
可用该子句让分析函数计算出它的值。
格式：
{rows|range}
{between
{unbounded preceding|current row |{preceding|following}
}and
{unbounded preceding|current row |{preceding|following}
}|{unbounded preceding|current row |{preceding|following
}}
(1)rows|range:此关键字定义了一个window。
(2)between...and...:为窗品指一个起点和终点。
(3)unbounded preceding：指明窗口是从分区(partition)的第一行开始。
(4)current row:指明窗口是从当前行开始
首先，我们从一个简单的例子开始，来一步一步揭开它神秘的面纱，请看下面的SQL：
CREATE TABLE EMPLOY
(
NAME VARCHAR2(10), --姓名
DEPT VARCHAR2(10), --部门
SALARY NUMBER --工资
);
INSERT INTO EMPLOY VALUES ('张三','市场部',4000);
INSERT INTO EMPLOY VALUES ('赵红','技术部',2000);
INSERT INTO EMPLOY VALUES ('李四','市场部',5000);
INSERT INTO EMPLOY VALUES ('李白','技术部',5000);
INSERT INTO EMPLOY VALUES ('王五','市场部',NULL);
INSERT INTO EMPLOY VALUES ('王蓝','技术部',4000);
SELECT
ROW_NUMBER() OVER(ORDER BY SALARY) AS 序号,
NAME AS 姓名,
DEPT AS 部门,
SALARY AS 工资
FROM EMPLOY;
序号 姓名 部门 工资
---------- ---------- ---------- ----------
1 赵红 技术部 2000
2 张三 市场部 4000
3 王蓝 技术部 4000
4 李白 技术部 5000
5 李四 市场部 5000
6 王五 市场部
已选择6行。
看到上面的ROW_NUMBER() OVER()了吗？很多人非常不理解，怎么两个函数能这么写呢？甚至有人怀疑上面的SQL语句是不是真的能执行。其实，ROW_NUMBER是个函数没错，它的作用从它的名字也可以看出来，就是给查询结果集编号。但是，OVER并不是一个函数，而是一个分析语句，它的作用是定义一个作用域（或者可以说是结果集），OVER前面的函数只对OVER定义的结果集起作用。
从上面的SQL我们可以看出，典型的 Oracle 在线分析处理的格式包括两部分：函数部分和OVER分析语句部分。那么，函数部分可以有哪些函数呢？如下：
大家看一下上面SQL的结果集，发现王五的工资是null，当我们按工资排序时，null被放到最后，我们想把 null 放在前边该怎么办呢？
使用NULLS FIRST关键字即可，默认是NULLS LAST，请看下面的SQL：
select
row_number() over(order by salary desc nulls first) as rn,
rank() over(order by salary desc nulls first) as rk,
dense_rank() over(order by salary desc nulls first) as d_rk,
name as 姓名,
dept as 部门,
salary as 工资
from employ;
RN RK D_RK 姓名 部门 工资
---------- ---------- ---------- ---------- ---------- ----------
1 1 1王五 市场部
2 2 2 李四 市场部 5000
3 2 2 李白 技术部 5000
4 4 3 张三 市场部 4000
5 4 3 王蓝 技术部 4000
6 6 4 赵红 技术部 2000
已选择6行。
请注意DENSE_RANK和RANK之间的区别，RANK是等级，排名的意思，李四和李白的工资都是5000，他们并列排名第二。张三和王蓝的工资都是4000，怎么RANK函数的排名是第四，而DENSE_RANK的排名是第三呢？这正是这两个函数之间的区别。由于有两个第二名，所以RANK函数默认没有第三名。
现在又有个新问题，假设让你查询一下每个员工的工资以及工资小于他的所有员工的平均工资，该怎么办呢？怎么？没听明白问题？不要紧，请看下面的SQL：
select
name as 姓名,
salary as 工资,
sum(salary) over(order by salary nulls first
rows between unbounded preceding and current row) as 小于本人工资的总额,
sum(salary) over(order by salary nulls first
rows between current row and unbounded following) as 大于本人工资的总额,
sum(salary) over(order by salary nulls first
rows between unbounded preceding and unbounded following) as 工资总额1,
sum(salary) over() as 工资总额2
from employ;
姓名 工资 小于本人工资的总额 大于本人工资的总额 工资总额1 工资总额2
---------- ---------- ------------------ ------------------ ---------- ----------
王五 20000 20000 20000
赵红 2000 2000 20000 20000 20000
王蓝 4000 6000 18000 20000 20000
张三 4000 10000 14000 20000 20000
李白 5000 15000 10000 20000 20000
李四 5000 20000 5000 20000 20000
已选择6行。
上面SQL 中的OVER部分出现了一个ROWS子句，我们先来看一下ROWS子句的结构：
Rows Between <上限条件> And <下限条件> Unbounded Following
OVER 分析语句还可以有个子句，那就是RANGE，它的使用方式和ROWS十分相似，或者说一模一样，作用也差多不，不过有点区别，如下所示：
delete from employ;
insert into employ values ('张三','市场部',2000);
insert into employ values ('赵红','技术部',2400);
insert into employ values ('李四','市场部',3000);
insert into employ values ('李白','技术部',3200);
insert into employ values ('王五','市场部',4000);
insert into employ values ('王蓝','技术部',5000);
commit;
select
name as 姓名,
dept as 部门,
salary as 工资,
first_value(salary ignore nulls) over(partition by dept) as 部门最低工资,
nth_value(salary, 2) over(partition by dept) as 部门倒数第二工资,
last_value(salary respect nulls) over(partition by dept) as 部门最高工资,
sum(salary) over(order by salary rows between 1 preceding and 1 following) as "rows",
sum(salary) over(order by salary range between 500 preceding and 500 following) as "range"
from employ;
姓名 部门 工资 部门最低工资 部门倒数第二工资 部门最高工资 rows range
---------- ---------- ---------- ------------ ---------------- ------------ ---------- ----------
张三 市场部 2000 4000 2000 3000 4400 4400
赵红 技术部 2400 2400 3200 5000 7400 4400
李四 市场部 3000 4000 2000 3000 8600 6200
李白 技术部 3200 2400 3200 5000 10200 6200
王五 市场部 4000 4000 2000 3000 12200 4000
王蓝 技术部 5000 2400 3200 5000 9000 5000
已选择6行。
上面SQL的RANGE子句的作用是定义一个工资范围，这个范围的上限是当前行的工资-500，下限是当前行工资+500。例如：李四的工资是3000，所以上限是3000-500=2500，下限是3000+500=3500，那么有谁的工资在2500-3500这个范围呢？只有李四和李白，所以RANGE列的值就是3000(李四)+3200(李白)=6200。以上就是ROWS和RANGE得区别。
select
name as 姓名,
salary as 工资,
lag(salary,0) over(order by salary) as lag0,
lag(salary) over(order by salary) as lag1,
lag(salary,2) over(order by salary) as lag2,
lag(salary,3 ,0) ignore nulls over(order by salary) as lag3,
lag(salary,4, -1) respect nulls over(order by salary) as lag4,
lead(salary) over(order by salary) as lead
from employ;
姓名 工资 LAG0 LAG1 LAG2 LAG3 LAG4 LEAD
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
张三 2000 2000 0 -1 2400
赵红 2400 2400 2000 0 -1 3000
李四 3000 3000 2400 2000 0 -1 3200
李白 3200 3200 3000 2400 2000 -1 4000
王五 4000 4000 3200 3000 2400 2000 5000
王蓝 5000 5000 4000 3200 3000 2400
已选择6行。
我们先来看一下LAG和 LEAD 函数的声明，如下：
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
补充1：oracle分析函数技术详解（配上开窗函数over()）
http://blog.csdn.net/haiross/article/details/15336313
补充2：常用Oracle分析函数大全
1.建表：
分析函数是基于一组行来计算的。这不同于聚集函数且广泛应用于OLAP环境中。
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是
对于每个组返回多行，而聚合函数对于每个组只返回一行。
语法：
analytic_function([arguments])
OVER(
[partition_clause]
[order_by_clause[windowing_clause]]
)
其中：
1 over是关键字，用于标识分析函数。
2 是指定的分析函数的名字。Oracle分析函数很多。
3 为参数，分析函数可以选取0-3个参数。
4 分区子句的格式为：
partition by[,value_expr]...
关键字partition by子句根据由分区表达式的条件逻辑地将单个结果集分成N组。这里的"分区partition"和"组group"
都是同义词。
5 排序子句order-by-clause指定数据是如何存在分区内的。其格式为：
order[siblings]by{expr|position|c_alias}[asc|desc][nulls first|nulls last]
其中：
(1)asc|desc:指定了排列顺序。
(2)nulls first|nulls last:指定了包含空值的返回行应出现在有序序列中的第一个或最后一个位置。
6窗口子句windowing-clause
给出一个固定的或变化的数据窗口方法，分析函数将对这些数据进行操作。在一组基于任意变化或固定的窗口中，
可用该子句让分析函数计算出它的值。
格式：
{rows|range}
{between
{unbounded preceding|current row |{preceding|following}
}and
{unbounded preceding|current row |{preceding|following}
}|{unbounded preceding|current row |{preceding|following
}}
(1)rows|range:此关键字定义了一个window。
(2)between...and...:为窗品指一个起点和终点。
(3)unbounded preceding：指明窗口是从分区(partition)的第一行开始。
(4)current row:指明窗口是从当前行开始
首先，我们从一个简单的例子开始，来一步一步揭开它神秘的面纱，请看下面的SQL：
CREATE TABLE EMPLOY
(
NAME VARCHAR2(10), --姓名
DEPT VARCHAR2(10), --部门
SALARY NUMBER --工资
);
INSERT INTO EMPLOY VALUES ('张三','市场部',4000);
INSERT INTO EMPLOY VALUES ('赵红','技术部',2000);
INSERT INTO EMPLOY VALUES ('李四','市场部',5000);
INSERT INTO EMPLOY VALUES ('李白','技术部',5000);
INSERT INTO EMPLOY VALUES ('王五','市场部',NULL);
INSERT INTO EMPLOY VALUES ('王蓝','技术部',4000);
SELECT
ROW_NUMBER() OVER(ORDER BY SALARY) AS 序号,
NAME AS 姓名,
DEPT AS 部门,
SALARY AS 工资
FROM EMPLOY;
序号 姓名 部门 工资
---------- ---------- ---------- ----------
1 赵红 技术部 2000
2 张三 市场部 4000
3 王蓝 技术部 4000
4 李白 技术部 5000
5 李四 市场部 5000
6 王五 市场部
已选择6行。
看到上面的ROW_NUMBER() OVER()了吗？很多人非常不理解，怎么两个函数能这么写呢？甚至有人怀疑上面的SQL语句是不是真的能执行。其实，ROW_NUMBER是个函数没错，它的作用从它的名字也可以看出来，就是给查询结果集编号。但是，OVER并不是一个函数，而是一个分析语句，它的作用是定义一个作用域（或者可以说是结果集），OVER前面的函数只对OVER定义的结果集起作用。
从上面的SQL我们可以看出，典型的 Oracle 在线分析处理的格式包括两部分：函数部分和OVER分析语句部分。那么，函数部分可以有哪些函数呢？如下：
- RANK 给查询结果集编排名
- DENSE_RANK 给查询结果集编排名
- MIN 求最小值
- MAX 求最大值
- AVG 求平均值
- SUM 求总和
- COUNT 求结果集行数
- FIRST_VALUE 求最小值
- LAST_VALUE 求最大值
- FIRST 求最小值， 配合 DENSE_RANK 使用
- LAST 求最大值， 配合 DENSE_RANK 使用
- LAG 向下偏移
- LEAD 向上偏移
- LISTAGG 连接列
- NTILE 平分组
- NTH_VALUE 返回第 n 行的值
- VARIANCE 方差
- VAR_POP 总体方差
- VAR_SAMP 样本方差
- STDDEV 标准偏差
- STDDEV_POP 总体标准偏差
- STDDEV_SAMP 样本标准偏差
- CORR 协方差
- COVAR_POP 总体协方差
- COVAR_SAMP 样本协方差
- CUME_DIST 计算积分分布
- PERCENT_RANK 和 CUME_DIST 类似
- PERCENTILE_CONT 计算值的连续分布模型
- PERCENTILE_DISC 计算值的不连续分布模型
- RATIO_TO_REPORT 计算比率
- REGR_SLOPE 线性回归
- REGR_INTERCEPT 线性回归
- REGR_COUNT 线性回归
- REGR_R2 线性回归
- REGR_AVGX 线性回归
- REGR_AVGY 线性回归
- REGR_SXX 线性回归
- REGR_SYY 线性回归
-
REGR_SXY 线性回归
上面这些函数的作用，我会在后面逐步给大家介绍，大家可以根据函数名猜测一下函数的作用。
假设我想在不改变上面语句查询结果的情况下，追加对部门员工的平均工资和全体员工的平均工资的查询，怎么办呢？用通常的SQL很难查询，但是用分析函数则非常简单，如下SQL所示：
select
row_number() over(order by dept, salary) as 序号,
row_number() over(partition by dept order by salary) as 部门序号,
name as 姓名,
dept as 部门,
salary as 工资,
avg(salary) over(partition by dept) as 部门平均工资,
avg(salary) over() as 全员平均工资
from employ;
序号 部门序号 姓名 部门 工资 部门平均工资 全员平均工资
---------- ---------- ---------- ---------- ---------- ------------ ------------
1 1 赵红 技术部 2000 3666.66667 4000
2 2 王蓝 技术部 4000 3666.66667 4000
3 3 李白 技术部 5000 3666.66667 4000
4 1 张三 市场部 4000 4500 4000
5 2 李四 市场部 5000 4500 4000
6 3 王五 市场部 4500 4000
已选择6行。
请注意序号和部门序号之间的区别，我们在查询部门序号的时候，在OVER表达式中多了两个子句，分别是PARTITION BY和ORDER BY。它们有什么作用呢？在介绍它们的作用之前，我们先来回顾一下OVER的作用，还记得吗？
OVER是一个分析语句，它的作用是定义一个作用域（或者可以说是结果集），OVER前面的函数只对OVER定义的结果集起作用。
ORDER BY的作用大家非常熟悉，用来对结果集排序。PARTITION BY的作用其实也很简单，和GROUP BY的作用相同，用来对结果集分组。
大家看一下上面SQL的结果集，发现王五的工资是null，当我们按工资排序时，null被放到最后，我们想把 null 放在前边该怎么办呢？
使用NULLS FIRST关键字即可，默认是NULLS LAST，请看下面的SQL：
select
row_number() over(order by salary desc nulls first) as rn,
rank() over(order by salary desc nulls first) as rk,
dense_rank() over(order by salary desc nulls first) as d_rk,
name as 姓名,
dept as 部门,
salary as 工资
from employ;
RN RK D_RK 姓名 部门 工资
---------- ---------- ---------- ---------- ---------- ----------
1 1 1王五 市场部
2 2 2 李四 市场部 5000
3 2 2 李白 技术部 5000
4 4 3 张三 市场部 4000
5 4 3 王蓝 技术部 4000
6 6 4 赵红 技术部 2000
已选择6行。
请注意DENSE_RANK和RANK之间的区别，RANK是等级，排名的意思，李四和李白的工资都是5000，他们并列排名第二。张三和王蓝的工资都是4000，怎么RANK函数的排名是第四，而DENSE_RANK的排名是第三呢？这正是这两个函数之间的区别。由于有两个第二名，所以RANK函数默认没有第三名。
现在又有个新问题，假设让你查询一下每个员工的工资以及工资小于他的所有员工的平均工资，该怎么办呢？怎么？没听明白问题？不要紧，请看下面的SQL：
select
name as 姓名,
salary as 工资,
sum(salary) over(order by salary nulls first
rows between unbounded preceding and current row) as 小于本人工资的总额,
sum(salary) over(order by salary nulls first
rows between current row and unbounded following) as 大于本人工资的总额,
sum(salary) over(order by salary nulls first
rows between unbounded preceding and unbounded following) as 工资总额1,
sum(salary) over() as 工资总额2
from employ;
姓名 工资 小于本人工资的总额 大于本人工资的总额 工资总额1 工资总额2
---------- ---------- ------------------ ------------------ ---------- ----------
王五 20000 20000 20000
赵红 2000 2000 20000 20000 20000
王蓝 4000 6000 18000 20000 20000
张三 4000 10000 14000 20000 20000
李白 5000 15000 10000 20000 20000
李四 5000 20000 5000 20000 20000
已选择6行。
上面SQL 中的OVER部分出现了一个ROWS子句，我们先来看一下ROWS子句的结构：
Rows Between <上限条件> And <下限条件> Unbounded Following
- ROWS BETWEEN <上限条件> AND <下限条件>
- 其中“上限条件”可以是如下关键字：
- UNBOUNDED PRECEDING
- PRECEDING
- CURRENT ROW
- “下线条件”可以是如下关键字：
- CURRENT ROW
- FOLLOWING
-
UNBOUNDED FOLLOWING
OVER 分析语句还可以有个子句，那就是RANGE，它的使用方式和ROWS十分相似，或者说一模一样，作用也差多不，不过有点区别，如下所示：
-
RANGE BETWEEN <上限条件>AND <下限条件>
delete from employ;
insert into employ values ('张三','市场部',2000);
insert into employ values ('赵红','技术部',2400);
insert into employ values ('李四','市场部',3000);
insert into employ values ('李白','技术部',3200);
insert into employ values ('王五','市场部',4000);
insert into employ values ('王蓝','技术部',5000);
commit;
select
name as 姓名,
dept as 部门,
salary as 工资,
first_value(salary ignore nulls) over(partition by dept) as 部门最低工资,
nth_value(salary, 2) over(partition by dept) as 部门倒数第二工资,
last_value(salary respect nulls) over(partition by dept) as 部门最高工资,
sum(salary) over(order by salary rows between 1 preceding and 1 following) as "rows",
sum(salary) over(order by salary range between 500 preceding and 500 following) as "range"
from employ;
姓名 部门 工资 部门最低工资 部门倒数第二工资 部门最高工资 rows range
---------- ---------- ---------- ------------ ---------------- ------------ ---------- ----------
张三 市场部 2000 4000 2000 3000 4400 4400
赵红 技术部 2400 2400 3200 5000 7400 4400
李四 市场部 3000 4000 2000 3000 8600 6200
李白 技术部 3200 2400 3200 5000 10200 6200
王五 市场部 4000 4000 2000 3000 12200 4000
王蓝 技术部 5000 2400 3200 5000 9000 5000
已选择6行。
上面SQL的RANGE子句的作用是定义一个工资范围，这个范围的上限是当前行的工资-500，下限是当前行工资+500。例如：李四的工资是3000，所以上限是3000-500=2500，下限是3000+500=3500，那么有谁的工资在2500-3500这个范围呢？只有李四和李白，所以RANGE列的值就是3000(李四)+3200(李白)=6200。以上就是ROWS和RANGE得区别。
上面的 SQL 还用到了FIRST_VALUE，NTH_VALUE 和 LAST_VALUE 三个函数，它们的作用也非常简单，用来求OVER定义集合的最小值，第
n 行的值和最大值。值得注意的是这两个函数有个关键字，IGNORE NULLS 或 RESPECT NULLS，它们的作用正如它们的名字一样，用来忽略NULL值和考虑NULL值。
select
name as 姓名,
salary as 工资,
lag(salary,0) over(order by salary) as lag0,
lag(salary) over(order by salary) as lag1,
lag(salary,2) over(order by salary) as lag2,
lag(salary,3 ,0) ignore nulls over(order by salary) as lag3,
lag(salary,4, -1) respect nulls over(order by salary) as lag4,
lead(salary) over(order by salary) as lead
from employ;
姓名 工资 LAG0 LAG1 LAG2 LAG3 LAG4 LEAD
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
张三 2000 2000 0 -1 2400
赵红 2400 2400 2000 0 -1 3000
李四 3000 3000 2400 2000 0 -1 3200
李白 3200 3200 3000 2400 2000 -1 4000
王五 4000 4000 3200 3000 2400 2000 5000
王蓝 5000 5000 4000 3200 3000 2400
已选择6行。
我们先来看一下LAG和 LEAD 函数的声明，如下：
-
LAG(表达式或字段,偏移量, 默认值) IGNORE NULLS或RESPECT NULLS
要想熟练掌握这些知识还需要一定的时间和练习，一旦你掌握了，你将拥有一项绝世武学，可以纵横 Oracle。
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
补充1：oracle分析函数技术详解（配上开窗函数over()）
http://blog.csdn.net/haiross/article/details/15336313
补充2：常用Oracle分析函数大全
1.建表：
create table earnings -- 打工赚钱表
(
earnmonth varchar2(6), -- 打工月份
area varchar2(20), -- 打工地区
sno varchar2(10), -- 打工者编号
sname varchar2(20), -- 打工者姓名
times int, -- 本月打工次数
singleincome number(10,2), -- 每次赚多少钱
personincome number(10,2) -- 当月总收入
);
2.插入实验数据
insert into earnings values('200912','北平','511601','大魁',11,30,11*30);
insert into earnings values('200912','北平','511602','大凯',8,25,8*25);
insert into earnings values('200912','北平','511603','小东',30,6.25,30*6.25);
insert into earnings values('200912','北平','511604','大亮',16,8.25,16*8.25);
insert into earnings values('200912','北平','511605','贱敬',30,11,30*11);
insert into earnings values('200912','金陵','511301','小玉',15,12.25,15*12.25);
insert into earnings values('200912','金陵','511302','小凡',27,16.67,27*16.67);
insert into earnings values('200912','金陵','511303','小妮',7,33.33,7*33.33);
insert into earnings values('200912','金陵','511304','小俐',0,18,0);
insert into earnings values('200912','金陵','511305','雪儿',11,9.88,11*9.88);
insert into earnings values('201001','北平','511601','大魁',0,30,0);
insert into earnings values('201001','北平','511602','大凯',14,25,14*25);
insert into earnings values('201001','北平','511603','小东',19,6.25,19*6.25);
insert into earnings values('201001','北平','511604','大亮',7,8.25,7*8.25);
insert into earnings values('201001','北平','511605','贱敬',21,11,21*11);
insert into earnings values('201001','金陵','511301','小玉',6,12.25,6*12.25);
insert into earnings values('201001','金陵','511302','小凡',17,16.67,17*16.67);
insert into earnings values('201001','金陵','511303','小妮',27,33.33,27*33.33);
insert into earnings values('201001','金陵','511304','小俐',16,18,16*18);
insert into earnings values('201001','金陵','511305','雪儿',11,9.88,11*9.88);
commit;
3、查看实验数据
SQL> select * from earnings;
EARNMO AREA SNO SNAME TIMES SINGLEINCOME PERSONINCOME
------ -------------------- ---------- -------------------- ---------- ------------ ------------
200912 北平 511601 大魁 11 30 330
200912 北平 511602 大凯 8 25 200
200912 北平 511603 小东 30 6.25 187.5
200912 北平 511604 大亮 16 8.25 132
200912 北平 511605 贱敬 30 11 330
200912 金陵 511301 小玉 15 12.25 183.75
200912 金陵 511302 小凡 27 16.67 450.09
200912 金陵 511303 小妮 7 33.33 233.31
200912 金陵 511304 小俐 0 18 0
200912 金陵 511305 雪儿 11 9.88 108.68
201001 北平 511601 大魁 0 30 0
201001 北平 511602 大凯 14 25 350
201001 北平 511603 小东 19 6.25 118.75
201001 北平 511604 大亮 7 8.25 57.75
201001 北平 511605 贱敬 21 11 231
201001 金陵 511301 小玉 6 12.25 73.5
201001 金陵 511302 小凡 17 16.67 283.39
201001 金陵 511303 小妮 27 33.33 899.91
201001 金陵 511304 小俐 16 18 288
201001 金陵 511305 雪儿 11 9.88 108.68
已选择20行。
4、sum函数按照月份，统计每个地区的总收入
EARNMO AREA SUM(PERSONINCOME)
------ -------------------- -----------------
200912 北平 1179.5
201001 北平 757.5
201001 金陵 1653.48
200912 金陵 975.83
5.rollup函数按照月份，地区统计收入
EARNMO AREA SUM(PERSONINCOME)
------ -------------------- -----------------
200912 北平 1179.5
200912 金陵 975.83
200912 2155.33
201001 北平 757.5
201001 金陵 1653.48
201001 2410.98
4566.31
已选择7行。
6、cube函数按照月份，地区进行收入汇总
select earnmonth, area, sum(personincome)
from earnings
group by cube(earnmonth,area)
order by earnmonth,area nulls last;
EARNMO AREA SUM(PERSONINCOME)
------ -------------------- -----------------
200912 北平 1179.5
200912 金陵 975.83
200912 2155.33
201001 北平 757.5
201001 金陵 1653.48
201001 2410.98
北平 1937
金陵 2629.31
4566.31
已选择9行。
月份 地区 总金额
-------- -------------------- ----------
200912 北平 1179.5
200912 金陵 975.83
200912 全部地区 2155.33
201001 北平 757.5
201001 金陵 1653.48
201001 全部地区 2410.98
所有月份 北平 1937
所有月份 金陵 2629.31
所有月份 全部地区 4566.31
已选择9行。
月份 地区 打工者 收入 排名
------ -------------------- -------------------- ---------- ----------
200912 北平 大魁 330 1
200912 北平 贱敬 330 1
200912 北平 大凯 200 3
200912 北平 小东 187.5 4
200912 北平 大亮 132 5
200912 金陵 小凡 450.09 1
200912 金陵 小妮 233.31 2
200912 金陵 小玉 183.75 3
200912 金陵 雪儿 108.68 4
200912 金陵 小俐 0 5
201001 北平 大凯 350 1
201001 北平 贱敬 231 2
201001 北平 小东 118.75 3
201001 北平 大亮 57.75 4
201001 北平 大魁 0 5
201001 金陵 小妮 899.91 1
201001 金陵 小俐 288 2
201001 金陵 小凡 283.39 3
201001 金陵 雪儿 108.68 4
201001 金陵 小玉 73.5 5
已选择20行。
9、dense_rank() over开窗函数按照月份、地区，求打工收入排序2
月份 地区 打工者 收入 排名
------ -------------------- -------------------- ---------- ----------
200912 北平 大魁 330 1
200912 北平 贱敬 330 1
200912 北平 大凯 200 2
200912 北平 小东 187.5 3
200912 北平 大亮 132 4
200912 金陵 小凡 450.09 1
200912 金陵 小妮 233.31 2
200912 金陵 小玉 183.75 3
200912 金陵 雪儿 108.68 4
200912 金陵 小俐 0 5
201001 北平 大凯 350 1
201001 北平 贱敬 231 2
201001 北平 小东 118.75 3
201001 北平 大亮 57.75 4
201001 北平 大魁 0 5
201001 金陵 小妮 899.91 1
201001 金陵 小俐 288 2
201001 金陵 小凡 283.39 3
201001 金陵 雪儿 108.68 4
201001 金陵 小玉 73.5 5
已选择20行。
10、row_number() over开窗函数按照月份、地区，求打工收入排序3
月份 地区 打工者 收入 排名
------ -------------------- -------------------- ---------- ----------
200912 北平 大魁 330 1
200912 北平 贱敬 330 2
200912 北平 大凯 200 3
200912 北平 小东 187.5 4
200912 北平 大亮 132 5
200912 金陵 小凡 450.09 1
200912 金陵 小妮 233.31 2
200912 金陵 小玉 183.75 3
200912 金陵 雪儿 108.68 4
200912 金陵 小俐 0 5
201001 北平 大凯 350 1
201001 北平 贱敬 231 2
201001 北平 小东 118.75 3
201001 北平 大亮 57.75 4
201001 北平 大魁 0 5
201001 金陵 小妮 899.91 1
201001 金陵 小俐 288 2
201001 金陵 小凡 283.39 3
201001 金陵 雪儿 108.68 4
201001 金陵 小玉 73.5 5
已选择20行。
11、sum累计求和根据月份求出各个打工者收入总和，按照收入由少到多排序
月份 地区 打工者 总收入
------ -------------------- -------------------- ----------
200912 北平 大亮 132
200912 北平 小东 319.5
200912 北平 大凯 519.5
200912 北平 大魁 1179.5
200912 北平 贱敬 1179.5
200912 金陵 小俐 0
200912 金陵 雪儿 108.68
200912 金陵 小玉 292.43
200912 金陵 小妮 525.74
200912 金陵 小凡 975.83
201001 北平 大魁 0
201001 北平 大亮 57.75
201001 北平 小东 176.5
201001 北平 贱敬 407.5
201001 北平 大凯 757.5
201001 金陵 小玉 73.5
201001 金陵 雪儿 182.18
201001 金陵 小凡 465.57
201001 金陵 小俐 753.57
201001 金陵 小妮 1653.48
已选择20行。
12、max，min，avg和sum函数综合运用按照月份和地区求打工收入最高值，最低值，平均值和总额
月份 地区 最高值 最低值 平均值 总额
------ -------------------- ---------- ---------- ---------- ----------
200912 金陵 450.09 0 195.166 975.83
201001 北平 350 0 151.5 757.5
200912 北平 330 132 235.9 1179.5
201001 金陵 899.91 73.5 330.696 1653.48
13、lag和lead函数求出每个打工者上个月和下个月有没有赚钱(personincome大于零即为赚钱)
本月 打工者 上月 下月
------ -------------------- ---- ----
200912 大凯 0 赚了
201001 大凯 赚了 0
200912 大魁 0 没赚
201001 大魁 赚了 0
200912 大亮 0 赚了
201001 大亮 赚了 0
200912 贱敬 0 赚了
201001 贱敬 赚了 0
200912 小东 0 赚了
201001 小东 赚了 0
200912 小凡 0 赚了
201001 小凡 赚了 0
200912 小俐 0 赚了
201001 小俐 没赚 0
200912 小妮 0 赚了
201001 小妮 赚了 0
200912 小玉 0 赚了
201001 小玉 赚了 0
200912 雪儿 0 赚了
201001 雪儿 赚了 0
已选择20行。
2.插入实验数据
insert into earnings values('200912','北平','511601','大魁',11,30,11*30);
insert into earnings values('200912','北平','511602','大凯',8,25,8*25);
insert into earnings values('200912','北平','511603','小东',30,6.25,30*6.25);
insert into earnings values('200912','北平','511604','大亮',16,8.25,16*8.25);
insert into earnings values('200912','北平','511605','贱敬',30,11,30*11);
insert into earnings values('200912','金陵','511301','小玉',15,12.25,15*12.25);
insert into earnings values('200912','金陵','511302','小凡',27,16.67,27*16.67);
insert into earnings values('200912','金陵','511303','小妮',7,33.33,7*33.33);
insert into earnings values('200912','金陵','511304','小俐',0,18,0);
insert into earnings values('200912','金陵','511305','雪儿',11,9.88,11*9.88);
insert into earnings values('201001','北平','511601','大魁',0,30,0);
insert into earnings values('201001','北平','511602','大凯',14,25,14*25);
insert into earnings values('201001','北平','511603','小东',19,6.25,19*6.25);
insert into earnings values('201001','北平','511604','大亮',7,8.25,7*8.25);
insert into earnings values('201001','北平','511605','贱敬',21,11,21*11);
insert into earnings values('201001','金陵','511301','小玉',6,12.25,6*12.25);
insert into earnings values('201001','金陵','511302','小凡',17,16.67,17*16.67);
insert into earnings values('201001','金陵','511303','小妮',27,33.33,27*33.33);
insert into earnings values('201001','金陵','511304','小俐',16,18,16*18);
insert into earnings values('201001','金陵','511305','雪儿',11,9.88,11*9.88);
commit;
3、查看实验数据
SQL> select * from earnings;
EARNMO AREA SNO SNAME TIMES SINGLEINCOME PERSONINCOME
------ -------------------- ---------- -------------------- ---------- ------------ ------------
200912 北平 511601 大魁 11 30 330
200912 北平 511602 大凯 8 25 200
200912 北平 511603 小东 30 6.25 187.5
200912 北平 511604 大亮 16 8.25 132
200912 北平 511605 贱敬 30 11 330
200912 金陵 511301 小玉 15 12.25 183.75
200912 金陵 511302 小凡 27 16.67 450.09
200912 金陵 511303 小妮 7 33.33 233.31
200912 金陵 511304 小俐 0 18 0
200912 金陵 511305 雪儿 11 9.88 108.68
201001 北平 511601 大魁 0 30 0
201001 北平 511602 大凯 14 25 350
201001 北平 511603 小东 19 6.25 118.75
201001 北平 511604 大亮 7 8.25 57.75
201001 北平 511605 贱敬 21 11 231
201001 金陵 511301 小玉 6 12.25 73.5
201001 金陵 511302 小凡 17 16.67 283.39
201001 金陵 511303 小妮 27 33.33 899.91
201001 金陵 511304 小俐 16 18 288
201001 金陵 511305 雪儿 11 9.88 108.68
已选择20行。
4、sum函数按照月份，统计每个地区的总收入
select earnmonth, area, sum(personincome)
from earnings
group by earnmonth,area;
EARNMO AREA SUM(PERSONINCOME)
------ -------------------- -----------------
200912 北平 1179.5
201001 北平 757.5
201001 金陵 1653.48
200912 金陵 975.83
5.rollup函数按照月份，地区统计收入
select earnmonth, area, sum(personincome)
from earnings
group by rollup(earnmonth,area);
EARNMO AREA SUM(PERSONINCOME)
------ -------------------- -----------------
200912 北平 1179.5
200912 金陵 975.83
200912 2155.33
201001 北平 757.5
201001 金陵 1653.48
201001 2410.98
4566.31
已选择7行。
6、cube函数按照月份，地区进行收入汇总
select earnmonth, area, sum(personincome)
from earnings
group by cube(earnmonth,area)
order by earnmonth,area nulls last;
EARNMO AREA SUM(PERSONINCOME)
------ -------------------- -----------------
200912 北平 1179.5
200912 金陵 975.83
200912 2155.33
201001 北平 757.5
201001 金陵 1653.48
201001 2410.98
北平 1937
金陵 2629.31
4566.31
已选择9行。
查询结果如下
小结：sum是统计求和的函数。
group by 是分组函数，按照earnmonth和area先后次序分组。
以上三例都是先按照earnmonth分组，在earnmonth内部再按area分组，并在area组内统计personincome总合。
group by 后面什么也不接就是直接分组。
group by 后面接 rollup 是在纯粹的 group by 分组上再加上对earnmonth的汇总统计。
group by 后面接 cube 是对earnmonth汇总统计基础上对area再统计。
另外那个 nulls last 是把空值放在最后。
rollup和cube区别：
如果是ROLLUP(A, B, C)的话，GROUP BY顺序
(A、B、C)
(A、B)
(A)
最后对全表进行GROUP BY操作。
如果是GROUP BY CUBE(A, B, C)，GROUP BY顺序
(A、B、C)
(A、B)
(A、C)
(A)
(B、C)
(B)
(C)
7、grouping函数在以上例子中，是用rollup和cube函数都会对结果集产生null，这时候可用grouping函数来确认
该记录是由哪个字段得出来的
select decode(grouping(earnmonth),1,'所有月份',earnmonth) 月份,
decode(grouping(area),1,'全部地区',area) 地区, sum(personincome) 总金额
from earnings
group by cube(earnmonth,area)
order by earnmonth,area nulls last;
月份 地区 总金额
-------- -------------------- ----------
200912 北平 1179.5
200912 金陵 975.83
200912 全部地区 2155.33
201001 北平 757.5
201001 金陵 1653.48
201001 全部地区 2410.98
所有月份 北平 1937
所有月份 金陵 2629.31
所有月份 全部地区 4566.31
已选择9行。
8、rank() over开窗函数
按照月份、地区，求打工收入排序
select earnmonth 月份,area 地区,sname 打工者, personincome 收入,
rank() over (partition by earnmonth,area order by personincome desc) 排名
from earnings;
月份 地区 打工者 收入 排名
------ -------------------- -------------------- ---------- ----------
200912 北平 大魁 330 1
200912 北平 贱敬 330 1
200912 北平 大凯 200 3
200912 北平 小东 187.5 4
200912 北平 大亮 132 5
200912 金陵 小凡 450.09 1
200912 金陵 小妮 233.31 2
200912 金陵 小玉 183.75 3
200912 金陵 雪儿 108.68 4
200912 金陵 小俐 0 5
201001 北平 大凯 350 1
201001 北平 贱敬 231 2
201001 北平 小东 118.75 3
201001 北平 大亮 57.75 4
201001 北平 大魁 0 5
201001 金陵 小妮 899.91 1
201001 金陵 小俐 288 2
201001 金陵 小凡 283.39 3
201001 金陵 雪儿 108.68 4
201001 金陵 小玉 73.5 5
已选择20行。
9、dense_rank() over开窗函数按照月份、地区，求打工收入排序2
select earnmonth 月份,area 地区,sname 打工者, personincome 收入,
dense_rank() over (partition by earnmonth,area order by personincome desc) 排名
from earnings;
月份 地区 打工者 收入 排名
------ -------------------- -------------------- ---------- ----------
200912 北平 大魁 330 1
200912 北平 贱敬 330 1
200912 北平 大凯 200 2
200912 北平 小东 187.5 3
200912 北平 大亮 132 4
200912 金陵 小凡 450.09 1
200912 金陵 小妮 233.31 2
200912 金陵 小玉 183.75 3
200912 金陵 雪儿 108.68 4
200912 金陵 小俐 0 5
201001 北平 大凯 350 1
201001 北平 贱敬 231 2
201001 北平 小东 118.75 3
201001 北平 大亮 57.75 4
201001 北平 大魁 0 5
201001 金陵 小妮 899.91 1
201001 金陵 小俐 288 2
201001 金陵 小凡 283.39 3
201001 金陵 雪儿 108.68 4
201001 金陵 小玉 73.5 5
已选择20行。
10、row_number() over开窗函数按照月份、地区，求打工收入排序3
select earnmonth 月份,area 地区,sname 打工者, personincome 收入,
row_number() over (partition by earnmonth,area order by personincome desc) 排名
from earnings;
月份 地区 打工者 收入 排名
------ -------------------- -------------------- ---------- ----------
200912 北平 大魁 330 1
200912 北平 贱敬 330 2
200912 北平 大凯 200 3
200912 北平 小东 187.5 4
200912 北平 大亮 132 5
200912 金陵 小凡 450.09 1
200912 金陵 小妮 233.31 2
200912 金陵 小玉 183.75 3
200912 金陵 雪儿 108.68 4
200912 金陵 小俐 0 5
201001 北平 大凯 350 1
201001 北平 贱敬 231 2
201001 北平 小东 118.75 3
201001 北平 大亮 57.75 4
201001 北平 大魁 0 5
201001 金陵 小妮 899.91 1
201001 金陵 小俐 288 2
201001 金陵 小凡 283.39 3
201001 金陵 雪儿 108.68 4
201001 金陵 小玉 73.5 5
已选择20行。
通过(8)(9)(10)发现rank,dense_rank,row_number的区别：
结果集中如果出现两个相同的数据，那么rank会进行跳跃式的排名，
比如两个第二，那么没有第三接下来就是第四；
但是dense_rank不会跳跃式的排名，两个第二接下来还是第三；
row_number最牛，即使两个数据相同，排名也不一样。
11、sum累计求和根据月份求出各个打工者收入总和，按照收入由少到多排序
select earnmonth 月份,area 地区,sname 打工者,
sum(personincome) over (partition by earnmonth,area order by personincome) 总收入
from earnings;
月份 地区 打工者 总收入
------ -------------------- -------------------- ----------
200912 北平 大亮 132
200912 北平 小东 319.5
200912 北平 大凯 519.5
200912 北平 大魁 1179.5
200912 北平 贱敬 1179.5
200912 金陵 小俐 0
200912 金陵 雪儿 108.68
200912 金陵 小玉 292.43
200912 金陵 小妮 525.74
200912 金陵 小凡 975.83
201001 北平 大魁 0
201001 北平 大亮 57.75
201001 北平 小东 176.5
201001 北平 贱敬 407.5
201001 北平 大凯 757.5
201001 金陵 小玉 73.5
201001 金陵 雪儿 182.18
201001 金陵 小凡 465.57
201001 金陵 小俐 753.57
201001 金陵 小妮 1653.48
已选择20行。
12、max，min，avg和sum函数综合运用按照月份和地区求打工收入最高值，最低值，平均值和总额
select distinct earnmonth 月份, area 地区,
max(personincome) over(partition by earnmonth,area) 最高值,
min(personincome) over(partition by earnmonth,area) 最低值,
avg(personincome) over(partition by earnmonth,area) 平均值,
sum(personincome) over(partition by earnmonth,area) 总额
from earnings;
月份 地区 最高值 最低值 平均值 总额
------ -------------------- ---------- ---------- ---------- ----------
200912 金陵 450.09 0 195.166 975.83
201001 北平 350 0 151.5 757.5
200912 北平 330 132 235.9 1179.5
201001 金陵 899.91 73.5 330.696 1653.48
13、lag和lead函数求出每个打工者上个月和下个月有没有赚钱(personincome大于零即为赚钱)
select earnmonth 本月,sname 打工者,
lag(decode(nvl(personincome,0),0,'没赚','赚了'),1,0) over(partition by sname order by earnmonth) 上月,
lead(decode(nvl(personincome,0),0,'没赚','赚了'),1,0) over(partition by sname order by earnmonth) 下月
from earnings;
本月 打工者 上月 下月
------ -------------------- ---- ----
200912 大凯 0 赚了
201001 大凯 赚了 0
200912 大魁 0 没赚
201001 大魁 赚了 0
200912 大亮 0 赚了
201001 大亮 赚了 0
200912 贱敬 0 赚了
201001 贱敬 赚了 0
200912 小东 0 赚了
201001 小东 赚了 0
200912 小凡 0 赚了
201001 小凡 赚了 0
200912 小俐 0 赚了
201001 小俐 没赚 0
200912 小妮 0 赚了
201001 小妮 赚了 0
200912 小玉 0 赚了
201001 小玉 赚了 0
200912 雪儿 0 赚了
201001 雪儿 赚了 0
已选择20行。
说明：Lag和Lead函数可以在一次查询中取出某个字段的前N行和后N行的数据（可以是其他字段的数据，比如根据字段甲查询上一行或下两行的字段乙）
语法如下：
lag(value_expression [,offset] [,default]) over ([query_partition_clase] order_by_clause)；
lead(value_expression [,offset] [,default]) over ([query_partition_clase] order_by_clause)；
其中：
value_expression：可以是一个字段或一个内建函数。
offset是正整数，默认为1，指往前或往后几点记录．因组内第一个条记录没有之前的行，最后一行没有之后的行，default就是用于处理这样的信息，默认为空。
再讲讲所谓的开窗函数，依本人遇见，开窗函数就是 over([query_partition_clase]
order_by_clause)。比如说，我采用sum求和，rank排序等等，但是我根据什么来呢？over提供一个窗口，可以根据什么什么分组，就用partition
by，然后在组内根据什么什么进行内部排序，就用 order by。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31397003/viewspace-2140411/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
