一、 分析函数简介
分析函数是基于一组行来计算的。这不同于聚合函数且广泛应用于OLAP环境中。
它是Oracle分析函数专门针对类似于”经营总额”、”找出一组中的百分之多少” 或”计算排名前几位”等问题设计的。
分析函数运行效率高，使用方便。
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是
对于每个组返回多行，而聚合函数对于每个组只返回一行。
二、分析函数语法
<analytic-function>(<argument>,<argument>,...)
over(
<query-partition-clause>
<order-by-clause>
<windowing-clause>
)其中：
- over是关键字，用于标识分析函数。
<analytic-function>是指定的分析函数的名字。Oracle分析函数很多。<argument>为参数，分析函数可以选取0-3个参数。分区子句
<query-partition-clause>的格式为：partition by<value_exp>[,value_expr]...关键字partition by子句根据由分区表达式的条件逻辑地将单个结果集分成N组。这里的”分区partition”和”组group”
都是同义词。排序子句order-by-clause指定数据是如何存在分区内的。其格式为：
order[siblings]by{expr|position|c_alias}[asc|desc][nulls first|nulls last]其中： (1)asc|desc:指定了排列顺序。
(2)nulls first|nulls
last:指定了包含空值的返回行应出现在有序序列中的第一个或最后一个位置。窗口子句windowing-clause
给出一个固定的或变化的数据窗口方法，分析函数将对这些数据进行操作。在一组基于任意变化或固定的窗口中， 可用该子句让分析函数计算出它的值。
格式：{rows|range} {between {unbounded preceding|current row |<value_expr>{preceding|following} }and {unbounded preceding|current row |<value_expr>{preceding|following} }}(1)rows|range:此关键字定义了一个window。
(2)between…and…:为窗品指一个起点和终点。
(3)unbounded preceding：指明窗口是从分区(partition)的第一行开始。
(4)current row:指明窗口是从当前行开始。
注:order by为窗口子句的必须条件，没有order by将会报错:ORA-30485: 在窗口说明中丢失 ORDER BY 表达式
三、窗口子句rows between … and ….
创建一张月销售额表，并插入数据:
create table monthly_performance(
month number(2), --月份
sales number --销售额
);
insert into monthly_performance values(1,1111);
insert into monthly_performance values(2,222);
insert into monthly_performance values(3,33);
insert into monthly_performance values(4,4);
insert into monthly_performance values(5,55);
insert into monthly_performance values(6,666);
insert into monthly_performance values(7,7777);
insert into monthly_performance values(8,888);
insert into monthly_performance values(9,99);
insert into monthly_performance values(10,101);
insert into monthly_performance values(11,1111);
insert into monthly_performance values(12,1212);1. rows between unbounded preceding and unbounded following
在月销售额表中查询出每个月的销售额和所有月份的销售额之和:
SQL> select m.month,m.sales,sum(m.sales) over(order by m.month rows between unbounded preceding and unbounded following ) as total_sales from monthly_performance m;
MONTH SALES TOTAL_SALES
----- ---------- -----------
1 1111 13279
2 222 13279
3 33 13279
4 4 13279
5 55 13279
6 666 13279
7 7777 13279
8 888 13279
9 99 13279
10 101 13279
11 1111 13279
12 1212 13279
12 rows selected
SQL> 这样做虽然达到了我们想要的效果，但是我们不得不考虑的是这条sql语句将会被执行12次，这是相当影响效率的。
2. rows between 1 preceding and 1 following
查询月销售额表中的每个月及前一个月和后一个月的销售额之和
SQL> select m.month,m.sales,sum(m.sales) over(order by m.month rows between 1 preceding and 1 following ) as total_sales from monthly_performance m;
MONTH SALES TOTAL_SALES
----- ---------- -----------
1 1111 1333
2 222 1366
3 33 259
4 4 92
5 55 725
6 666 8498
7 7777 9331
8 888 8764
9 99 1088
10 101 1311
11 1111 2424
12 1212 2323
12 rows selected由此可见，我们指定的数字1并不是指定第几行至第几行的和，而是指定的是当前行数的前一行和后一行。
3. rows between unbonded preceding and current row
查询每月及之前月份的销售额总和:
SQL> select m.month,m.sales,sum(m.sales) over(order by m.month rows between unbounded preceding and current row ) as total_sales from monthly_performance m;
MONTH SALES TOTAL_SALES
----- ---------- -----------
1 1111 1111
2 222 1333
3 33 1366
4 4 1370
5 55 1425
6 666 2091
7 7777 9868
8 888 10756
9 99 10855
10 101 10956
11 1111 12067
12 1212 13279
12 rows selected由上可见，current row 的效果是截止到当前行，当然我们也可以使用current row 作为开始行，如:
查询每月及之后的销售额之和:
SQL> select m.month,m.sales,sum(m.sales) over(order by m.month rows between current row and unbounded following ) as total_sales from monthly_performance m;
MONTH SALES TOTAL_SALES
----- ---------- -----------
1 1111 13279
2 222 12168
3 33 11946
4 4 11913
5 55 11909
6 666 11854
7 7777 11188
8 888 3411
9 99 2523
10 101 2424
11 1111 2323
12 1212 1212
12 rows selected四、分析函数first_value()和last_value()
创建一个部门工资表,并插入数据
create table emp_salary(
empno varchar2(10) , --部门编号
name varchar2(10), --姓名
salary number(5) --工资
);
insert into emp_salary values ('1','Tom',20);
insert into emp_salary values ('1','Ellen',30);
insert into emp_salary values ('1','Joe',10);
insert into emp_salary values ('2','Andy',40);
insert into emp_salary values ('2','Kary',60);
insert into emp_salary values ('2','Erick',50);
insert into emp_salary values ('3','Hou',20);
insert into emp_salary values ('3','Mary',50);
insert into emp_salary values ('3','Secooler',60);1. first_value()
查询每个部门中工资最低的人的姓名:
SQL> select s.empno,s.name,s.salary,first_value(s.name) over(partition by s.empno order by s.salary) as lower_sal_name from emp_salary s ;
EMPNO NAME SALARY LOWER_SAL_NAME
---------- ---------- ------ --------------
1 Joe 10 Joe
1 Tom 20 Joe
1 Ellen 30 Joe
2 Andy 40 Andy
2 Erick 50 Andy
2 Kary 60 Andy
3 Hou 20 Hou
3 Mary 50 Hou
3 Secooler 60 Hou
9 rows selected2. last_value()
查询每个部门中工资最高的人的姓名:
SQL> select s.empno,s.name,s.salary,last_value(s.name) over(partition by s.empno order by s.salary) as lower_sal_name from emp_salary s ;
EMPNO NAME SALARY LOWER_SAL_NAME
---------- ---------- ------ --------------
1 Joe 10 Joe
1 Tom 20 Tom
1 Ellen 30 Ellen
2 Andy 40 Andy
2 Erick 50 Erick
2 Kary 60 Kary
3 Hou 20 Hou
3 Mary 50 Mary
3 Secooler 60 Secooler
9 rows selected由上可以看出，当我们直接使用last_value()函数查询每个部门中工资最高的人的姓名时，得到的答案并不是我们想要的，那为什么会出现以上结果呢?这个因为last_value()的窗口子句默认的是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，因此，要得到我们想要的结果就需要我们自己指定窗口子句:
SQL> select s.empno,s.name,s.salary,last_value(s.name) over(partition by s.empno order by s.salary rows between unbounded preceding and unbounded following) as lower_sal_name from emp_salary s ;
EMPNO NAME SALARY LOWER_SAL_NAME
---------- ---------- ------ --------------
1 Joe 10 Ellen
1 Tom 20 Ellen
1 Ellen 30 Ellen
2 Andy 40 Kary
2 Erick 50 Kary
2 Kary 60 Kary
3 Hou 20 Secooler
3 Mary 50 Secooler
3 Secooler 60 Secooler
9 rows selected
扫一扫
3586
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
