ORACLE 分析函数
关键字: oracle
http://www.itpub.net/thread-1002085-1-1.html
分析函数语法解析
分析函数的语法很简单,但使用时却发现有很多令人迷惑的地方。本文就但从语法层次来分析其中的迷惑之处。
1 分析函数整体语法
分析函数的语法如下:
analytic_function([ arguments ]) OVER (analytic_clause)
这里:
Analytic_function是分析函数的名称;
arguments是分析函数的参数;
over用来标识函数是一个分析函数;
analytic_clause用来确定分析函数的操作规则。包括query_partition_clause、order_by_clause和windowing_clause三个子句。
2 Analytic_function
分析函数的名称。Oracle10gR2带的内置分析函数有39个;对于用户自定义的分析函数,分析函数名称需要满足标识符规则。
3 Arguments
分析函数所带参数,内置分析函数一般带0-3个参数。参数可以是任何数字类型或是可以隐式转换为数字类型的数据类型。Oracle根据最高数字优先级别确定函数参数,并且隐式地将需要处理的参数转换为数字类型。
用户自定义分析函数的参数,可以根据实际情况使用。
4 Over
用以标识函数是一个分析函数,对于既可作为聚集函数又可作为分析函数的函数,Oracle无法识别,必须用over来标识此函数为分析函数。也不是说只可用作分析函数的函数就可以省略该关键字,over关键字是必须的,是分析函数就必须使用。
Over后面的()中是analytic_clause,即使analytic_clause的三个部分全不使用,()也必不可省,否则Oracle因不能识别函数为分析函数而报错。
5 Analytic_clause
Analytic_clause的语法如下:
[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]
这里:
query_partition_clause是查询分组子句;
order_by_clause是分组排序子句;
windowing_clause是窗口范围子句。
分析函数在查询结果集确定之后才开始进行计算,Analytic_clause就是用来定义函数怎样对查询结果集进行分组计算的。
根据Oracle对查询和分析函数的处理方法可知,在select和order by子句中都可以使用分析函数。
query_partition_by、order_by_clause和windowing_clause三个子句是可选的,将三个子句分别简记为p,o,w。
合法的组合方式有如下6种:
1). Pow
(query_partition_clause order_by_clause windowing_clause)
分组,排序,定义窗口范围
2). Po
(query_partition_clause order_by_clause)
分组,排序,窗口默认为range between unbounded preceding and current row
3). P
(query_partition_clause)
分组,不排序,没有窗口
4). Ow
(order_by_clause windowing_clause)
分组为整个查询结果集,排序,定义窗口范围
5). O
(order_by_clause)
分组为整个查询结果集,排序,窗口默认为range between unbounded preceding and current row
6). Null
()
分组为整个查询结果集,不排序,没有窗口
因为只有存在order_by_clause,才能有windowing_clause,故不存在如下两种形式的组合:
pw(query_partition_clause windowing_clause)
w(windowing_clause)
总结:
1). 对于是否存在order_by_clause,分析函数可以分为两类,含有order_by_clause的一般称为windowing function,不含的称为reporting function。
2). Windowing function,对查询结果集进行分组,排序,根据窗口范围计算分组中每一行的函数结果。
3). Reporting function,对查询结果集进行分组,不排序,窗口范围为整个分组,在每一个分组内,计算整个分组的函数值,再将函数值分别赋给分组内的每一行。
注意事项:
1).Analytic_clause内不能包含任何分析函数。
2). 用户自定义分析函数和内置函数分析函数都可以使用over (analytic_clause)。
5.1 Query_partition_clause
query_partition_clause的语法如下:
PARTITION BY value_expr[, value_expr ]...
这里:
partition by是关键字,表示要将查询结果集分组;
value_expr是分组表达式。
Partition by子句根据一个或多个value_expr将查询结果集分成若干组。分组的原理与group by分组的原理类似,将value_expr相同的行集中在一起。
若不使用该子句,那末函数将整个查询结果集作为一个分组。
value_expr可以为常量、表列、非分析函数、函数表达式,或者前面这些元素的任意组合表达式。
5.2 Order_by_clause
Order_by_clause的语法如下:
ORDER BY expr [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
[, expr [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] ]...
这里:
order by是关键字,表示要将分组排序;
expr是排序表达式;
asc|desc和nulls first|null last是排序的附加规则。
Order_by_clause根据一个或多个expr对分组进行排序。
Expr只能为表达式,不能为位置编号或列别名。
注意事项:
1). 当窗口范围为
Range between unbounded preceding and current row 或
Range between current row and unbounded following 时,
可以在order_by_clause中使用多个expr对分组进行排序。其他range窗口范围只能使用一个expr。使用rows定义窗口范围无此限制。
2). asc|desc 指定排序是按expr升序还是降序排序,若不指定则默认按asc进行升序排序。
3). nulls first|nulls last 指定若返回行包含空值,该值应该出现在排序序列的开始还是末尾。升序排序的默认值是nulls last,降序排序的默认值是nulls first。
5.3 Windowing_clause
Windowing_clause的语法如下:
{ ROWS | RANGE }
{ BETWEEN
{ UNBOUNDED PRECEDING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING } } AND
{ UNBOUNDED FOLLOWING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING } }
| { UNBOUNDED PRECEDING | CURRENT ROW | value_expr PRECEDING }
}
这里,
rows 是关键字,指定窗口由物理行构成;
range 是关键字,指定窗口由逻辑偏移量构成;
between…and是关键字,用来指定窗口的起始点和终结点;
Unbounded preceding指明窗口开始于分组的第一行;
Current row,作为起始点,指明窗口开始于当前行或当前值;作为终结点,指明窗口结束于当前行或当前值;
Unbounded following指明窗口结束于分组的最后一行;
Value_expr为物理或逻辑偏移量表达式。
Windowing_clause用来指定分组中当前行的计算范围。
不论rows还是range窗口,窗口总是在分组中从上至下滑动的。
窗口范围可以由between…and限定,也可以不用between…and,不用的都表示窗口到当前行结束。
ROWS窗口,是由分组排序后分组中若干连续的行构成的窗口。
以下是全部合法的ROWS窗口范围:
1). Rows between unbounded preceding and unbounded following
窗口开始于分组第一行,结束于分组最后一行。
2). Rows [between] unbounded preceding [and current row]
窗口开始于分组第一行,结束于当前行。
3). Rows between unbounded preceding and value_expr preceding
窗口开始于分组第一行,结束于当前行前value_expr行。
4). Rows between unbounded preceding and value_expr following
窗口开始于分组第一行,结束于当前行后value_expr行。
5). Rows between current row and unbounded following
窗口开始于当前行,结束于分组最后一行。
6). Rows [between current row and] current row
窗口开始于当前行,结束于当前行。
7). Rows between current row and value_expr following
窗口开始于当前行,结束于当前行后value_expr行。
8). Rows between value_expr preceding and unbounded following
窗口开始于当前行前value_expr行,结束于分组最后一行。
9). Rows [between value_expr] preceding [and current row]
窗口开始于当前行前value_expr行,结束于当前行。
10). Rows between value_expr1 preceding and value_expr2 preceding
窗口开始于当前行前value_expr1行,结束于当前行前value_expr2行。这里一定要满足value_expr1>=value_expr2。
11). Rows between value_expr1 preceding and value_expr2 following
窗口开始于当前行前value_expr1行,结束于当前行后value_expr2行。
12). Rows between value_expr following and unbounded following
窗口开始于当前行后value_expr行,结束于分组最后一行。
13). Rows between value_expr1 following and value_expr2 following
窗口开始于当前行后value_expr1行,结束于当前行后value_expr2行。这里一定要满足value_expr1<=value_expr2
14). Rows unbounded preceding
与2等价。
15). Rows current row
与6等价。
16). Rows value_expr preceding
与9等价。
RANGE窗口,相当于给order_by_clause中的expr加一个where限定条件,分组中满足条件
(当order by expr asc时,where expr between a and b
当order by expr desc时 where expr between b and a)的所有行构成一个逻辑窗口。
其中a由分组中第ra行的值计算而来,b由分组中第rb行的值计算而来,且ra<=rb。
根据range中是否包含unbounded,可以分为两类,含unbounded range窗口,和不含unbounded range窗口。
对于前者,有ra<=rb。当order by expr asc时,a<=b,须使用where expr between a and b;当order by expr desc时,a>b,须使用where expr between b and a。
对于后者,有ra=rb。总有a<=b,使用where expr between a and b。
对于在order_by_clause中可以使用多个expr的窗口:
Range between unbounded preceding and current row
Range between current row and unbounded following
当它们使用多个expr排序时(注意不是一个)分别等价于:
Rows between unbounded preceding and current row
Rows between current row and unbounded following
假设分组第一行的值为first_value,最后一行的值为last_value。当前行的值为current_value。
1). Range between unbounded preceding and unbounded following
Where expr between first_value and last_value –-when asc
or expr between last_value and first_value --when desc
2). Range [between] unbounded preceding [and current row]
Where expr between first_value and current_value
or expr between current_value and first_value
3). Range between unbounded preceding and value_expr preceding
Where expr between first_value and current_value-value_expr
or expr between current_value-value_expr and first_value
4). Range between unbounded preceding and value_expr following
Where expr between first_value and current_value+value_expr
or expr between current_value+value_expr and first_value
5). Range between current row and unbounded following
Where expr between current_value and last_value
or expr between last_value and current_value
6). Range [between current row and] current row
Where expr = current_value
7). Range between current row and value_expr following
Where expr between current_value and current_value+value_expr
8). Range between value_expr preceding and unbounded following
Where expr between current_value-value_expr and last_value
9). Range [between value_expr] preceding [and current row]
Where expr between current_value-value_expr and current_value
10). Range between value_expr1 preceding and value_expr2 preceding
这里一定要满足value_expr1>=value_expr2。
Where expr between current_value-value_expr1 and current_value-value_expr2
11). Range between value_expr1 preceding and value_expr2 following
Where expr between current_value-value_expr1 and current_value+value_expr2
12). Range between value_expr following and unbounded following
Where expr between current_value+value_expr and last_value
or expr between last_value and current_value+value_expr
13). Range between value_expr1 following and value_expr2 following
这里一定要满足value_expr1<=value_expr2。
Where expr between current_value+value_expr1 and current_value+value_expr2
14). Range unbounded preceding
与2等价。
15). Range current row
与6等价。
16). Range value_expr preceding
与9等价。
注意事项:
1).若windowing_clause由rows指定,则:
(1)value_expr是物理偏移量,它必须是常量或值为非负数的表达式。
(2)若value_expr是起点的一部分,那末它必须在终点之前对行求值。
2).若windowing_clause由range指定,则:
(1)value_expr是逻辑偏移量。它必须是常量或值为非负数的表达式或时间间隔文字常量。
(2)若value_expr值为一个数字,那末order_by_clause中 expr必须为数字或date类型。
(3)若value_expr为一个间隔值,那末order_by_clause中expr必须是一个date类型。
3).若完全忽略windowing_clause,那末默认的窗口范围为 range between unbounded preceding and current row 。
6 分析函数简例
下面的例子都基于scott模式下的emp表。
SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7839 KING PRESIDENT 1981-11-17 5000.00 10
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7788 SCOTT ANALYST 7566 1982-12-9 3000.00 20
7876 ADAMS CLERK 7788 1983-1-12 1100.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
6.1 E1
SELECT SUM(sal) over() sal_sum FROM emp;
1). 聚集函数用作分析函数时,不需要group by子句;
2). 分析函数作为表列可以有别名,但必须放在over()之后。
6.2 E2
SELECT empno, ename, job, SUM(sal) over(PARTITION BY mgr ORDER BY job)
FROM emp;
1).
分析函数的分组与选择列也没有任何关系,比如上例,在选择列中可以有mgr,也可以没有mgr;
2).
分析函数的排序与选择列也没有任何关系,比如上例,在选择列中可以没有job。
6.3 E3
SELECT empno, ename, mgr, sal
FROM emp
ORDER BY empno,
row_number() over(PARTITION BY mgr ORDER BY sal),
SUM(1) over(PARTITION BY empno)
1). 分析函数可用于order by子句中;
2). Order by后可以用多个分析函数,也可以与其他排序列混合使用。
6.4 E4(pow)
SELECT empno,
ename,
mgr,
sal,
hiredate,
SUM(sal) over(PARTITION BY mgr ORDER BY hiredate RANGE BETWEEN unbounded preceding AND unbounded following)
FROM emp;
EMPNO ENAME MGR SAL HIREDATE SAL_SUM
7902 FORD 7566 3000.00 1981-12-3 6000
7788 SCOTT 7566 3000.00 1982-12-9 6000
7499 ALLEN 7698 1600.00 1981-2-20 6550
7521 WARD 7698 1250.00 1981-2-22 6550
7844 TURNER 7698 1500.00 1981-9-8 6550
7654 MARTIN 7698 1250.00 1981-9-28 6550
7900 JAMES 7698 950.00 1981-12-3 6550
7934 MILLER 7782 1300.00 1982-1-23 1300
7876 ADAMS 7788 1100.00 1983-1-12 1100
7566 JONES 7839 2975.00 1981-4-2 8275
7698 BLAKE 7839 2850.00 1981-5-1 8275
7782 CLARK 7839 2450.00 1981-6-9 8275
7369 SMITH 7902 800.00 1980-12-17 800
7839 KING 5000.00 1981-11-17 5000
按mgr分组,hiredate排序,窗口范围为整个分组。
分析函数sum对每个窗口中的所有雇员薪水求和,再将它赋给每一个雇员,故相同mgr的雇员的sal_sum都是相同的。
6.5 E5(po)
SELECT empno,
ename,
mgr,
sal,
hiredate,
SUM(sal) over(PARTITION BY mgr ORDER BY hiredate) sal_sum
FROM emp;
EMPNO ENAME MGR SAL HIREDATE SAL_SUM
7902 FORD 7566 3000.00 1981-12-3 3000
7788 SCOTT 7566 3000.00 1982-12-9 6000
7499 ALLEN 7698 1600.00 1981-2-20 1600
7521 WARD 7698 1250.00 1981-2-22 2850
7844 TURNER 7698 1500.00 1981-9-8 4350
7654 MARTIN 7698 1250.00 1981-9-28 5600
7900 JAMES 7698 950.00 1981-12-3 6550
7934 MILLER 7782 1300.00 1982-1-23 1300
7876 ADAMS 7788 1100.00 1983-1-12 1100
7566 JONES 7839 2975.00 1981-4-2 2975
7698 BLAKE 7839 2850.00 1981-5-1 5825
7782 CLARK 7839 2450.00 1981-6-9 8275
7369 SMITH 7902 800.00 1980-12-17 800
7839 KING 5000.00 1981-11-17 5000
按mgr分组,hiredate排序,没有指定窗口范围,默认为range between unbounded preceding and current row。
注意窗口是由hiredate确定,默认的窗口范围为不晚于当前雇员雇佣日期的hiredate。因又按hiredate升序,故sum对分组中当前雇员前面(包括当前雇员)的所有雇员求和,再赋给当前雇员。
可以将其补齐为pow的形式,等价于:
SELECT empno,
ename,
mgr,
sal,
hiredate,
SUM(sal) over(PARTITION BY mgr ORDER BY hiredate RANGE BETWEEN unbounded preceding AND CURRENT ROW) sal_sum
FROM emp;
再看一个例子:
SELECT empno,
ename,
mgr,
sal,
hiredate,
COUNT(1) over(PARTITION BY mgr ORDER BY hiredate DESC) sal_sum
FROM emp;
EMPNO ENAME MGR SAL HIREDATE SAL_SUM
7788 SCOTT 7566 3000.00 1982-12-9 1
7902 FORD 7566 3000.00 1981-12-3 2
7900 JAMES 7698 950.00 1981-12-3 1
7654 MARTIN 7698 1250.00 1981-9-28 2
7844 TURNER 7698 1500.00 1981-9-8 3
7521 WARD 7698 1250.00 1981-2-22 4
7499 ALLEN 7698 1600.00 1981-2-20 5
7934 MILLER 7782 1300.00 1982-1-23 1
7876 ADAMS 7788 1100.00 1983-1-12 1
7782 CLARK 7839 2450.00 1981-6-9 1
7698 BLAKE 7839 2850.00 1981-5-1 2
7566 JONES 7839 2975.00 1981-4-2 3
7369 SMITH 7902 800.00 1980-12-17 1
7839 KING 5000.00 1981-11-17 1
这个例子统计相同经理下当前雇员雇佣日期不早于自己的雇员个数。
6.6 E6(p)
SELECT empno,
ename,
mgr,
sal,
hiredate,
COUNT(1) over(PARTITION BY mgr) sal_sum
FROM emp;
EMPNO ENAME MGR SAL HIREDATE SAL_SUM
7788 SCOTT 7566 3000.00 1982-12-9 2
7902 FORD 7566 3000.00 1981-12-3 2
7844 TURNER 7698 1500.00 1981-9-8 5
7521 WARD 7698 1250.00 1981-2-22 5
7900 JAMES 7698 950.00 1981-12-3 5
7499 ALLEN 7698 1600.00 1981-2-20 5
7654 MARTIN 7698 1250.00 1981-9-28 5
7934 MILLER 7782 1300.00 1982-1-23 1
7876 ADAMS 7788 1100.00 1983-1-12 1
7698 BLAKE 7839 2850.00 1981-5-1 3
7566 JONES 7839 2975.00 1981-4-2 3
7782 CLARK 7839 2450.00 1981-6-9 3
7369 SMITH 7902 800.00 1980-12-17 1
7839 KING 5000.00 1981-11-17 1
按mgr分组,不排序,没有规定窗口范围。
这是个reporting function,不排序表明没有窗口,计算范围则为整个分组。窗口范围等价于range/rows between unbounded preceding and unbounded following。分析函数计算分组中的雇员总数,再将其赋给每一个雇员。
可以将其补齐为pow形式,等价于:
SELECT empno,
ename,
mgr,
sal,
hiredate,
COUNT(1) over(PARTITION BY mgr ORDER BY NULL ROWS BETWEEN unbounded preceding AND unbounded following) sal_sum
FROM emp;
6.7 E7(ow)
SELECT empno,
ename,
mgr,
sal,
hiredate,
SUM(sal) over(ORDER BY hiredate RANGE BETWEEN 30 preceding AND 30 following) sal_sum
FROM emp;
EMPNO ENAME MGR SAL HIREDATE SAL_SUM
7369 SMITH 7902 800.00 1980-12-17 800
7499 ALLEN 7698 1600.00 1981-2-20 2850
7521 WARD 7698 1250.00 1981-2-22 2850
7566 JONES 7839 2975.00 1981-4-2 5825
7698 BLAKE 7839 2850.00 1981-5-1 5825
7782 CLARK 7839 2450.00 1981-6-9 2450
7844 TURNER 7698 1500.00 1981-9-8 2750
7654 MARTIN 7698 1250.00 1981-9-28 2750
7839 KING 5000.00 1981-11-17 8950
7900 JAMES 7698 950.00 1981-12-3 8950
7902 FORD 7566 3000.00 1981-12-3 8950
7934 MILLER 7782 1300.00 1982-1-23 1300
7788 SCOTT 7566 3000.00 1982-12-9 3000
7876 ADAMS 7788 1100.00 1983-1-12 1100
分组为整个查询结果集,按hiredate排序,窗口范围为hiredate between current_hiredate – 30 and current_hiredate + 30。
在整个查询结果集中,分析函数计算与当前雇员日期相差在30天的雇员的工资总和,再将结果赋给当前雇员。
补齐pow形式,等价如下:
SELECT empno,
ename,
mgr,
sal,
hiredate,
SUM(sal) over(PARTITION BY NULL ORDER BY hiredate RANGE BETWEEN 30 preceding AND 30 following) sal_sum
FROM emp;
6.8 E7(o)
SELECT empno,
ename,
mgr,
sal,
hiredate,
SUM(sal) over(ORDER BY hiredate) sal_sum
FROM emp;
EMPNO ENAME MGR SAL HIREDATE SAL_SUM
7369 SMITH 7902 800.00 1980-12-17 800
7499 ALLEN 7698 1600.00 1981-2-20 2400
7521 WARD 7698 1250.00 1981-2-22 3650
7566 JONES 7839 2975.00 1981-4-2 6625
7698 BLAKE 7839 2850.00 1981-5-1 9475
7782 CLARK 7839 2450.00 1981-6-9 11925
7844 TURNER 7698 1500.00 1981-9-8 13425
7654 MARTIN 7698 1250.00 1981-9-28 14675
7839 KING 5000.00 1981-11-17 19675
7900 JAMES 7698 950.00 1981-12-3 23625
7902 FORD 7566 3000.00 1981-12-3 23625
7934 MILLER 7782 1300.00 1982-1-23 24925
7788 SCOTT 7566 3000.00 1982-12-9 27925
7876 ADAMS 7788 1100.00 1983-1-12 29025
分组为整个查询结果集,按hiredate排序,窗口默认为range between unbounded preceding and current row。
在整个查询结果集中,分析函数计算当前雇员雇佣日期不晚于自己的雇员薪水总和,再将其赋给当前雇员。
补齐pow形式,等价于:
SELECT empno,
ename,
mgr,
sal,
hiredate,
SUM(sal) over(PARTITION BY NULL ORDER BY hiredate RANGE BETWEEN unbounded preceding AND CURRENT ROW) sal_sum
FROM emp;
6.9 E8()
SELECT empno,
ename,
mgr,
sal,
hiredate,
SUM(sal) over() sal_sum
FROM emp;
EMPNO ENAME MGR SAL HIREDATE SAL_SUM
7369 SMITH 7902 800.00 1980-12-17 29025
7499 ALLEN 7698 1600.00 1981-2-20 29025
7521 WARD 7698 1250.00 1981-2-22 29025
7566 JONES 7839 2975.00 1981-4-2 29025
7654 MARTIN 7698 1250.00 1981-9-28 29025
7698 BLAKE 7839 2850.00 1981-5-1 29025
7782 CLARK 7839 2450.00 1981-6-9 29025
7788 SCOTT 7566 3000.00 1982-12-9 29025
7839 KING 5000.00 1981-11-17 29025
7844 TURNER 7698 1500.00 1981-9-8 29025
7876 ADAMS 7788 1100.00 1983-1-12 29025
7900 JAMES 7698 950.00 1981-12-3 29025
7902 FORD 7566 3000.00 1981-12-3 29025
7934 MILLER 7782 1300.00 1982-1-23 29025
分组为整个查询结果集,不排序,没有规定窗口。
这是个reporting function。分析函数计算整个查询结果集中雇员薪水总和,再将其值赋给每一雇员。
补齐pow形式等价于:
SELECT empno,
ename,
mgr,
sal,
hiredate,
SUM(sal) over(PARTITION BY NULL ORDER BY NULL RANGE BETWEEN unbounded preceding AND unbounded following) sal_sum
FROM emp;
关键字: oracle
http://www.itpub.net/thread-1002085-1-1.html
分析函数语法解析
分析函数的语法很简单,但使用时却发现有很多令人迷惑的地方。本文就但从语法层次来分析其中的迷惑之处。
1 分析函数整体语法
分析函数的语法如下:
analytic_function([ arguments ]) OVER (analytic_clause)
这里:
Analytic_function是分析函数的名称;
arguments是分析函数的参数;
over用来标识函数是一个分析函数;
analytic_clause用来确定分析函数的操作规则。包括query_partition_clause、order_by_clause和windowing_clause三个子句。
2 Analytic_function
分析函数的名称。Oracle10gR2带的内置分析函数有39个;对于用户自定义的分析函数,分析函数名称需要满足标识符规则。
3 Arguments
分析函数所带参数,内置分析函数一般带0-3个参数。参数可以是任何数字类型或是可以隐式转换为数字类型的数据类型。Oracle根据最高数字优先级别确定函数参数,并且隐式地将需要处理的参数转换为数字类型。
用户自定义分析函数的参数,可以根据实际情况使用。
4 Over
用以标识函数是一个分析函数,对于既可作为聚集函数又可作为分析函数的函数,Oracle无法识别,必须用over来标识此函数为分析函数。也不是说只可用作分析函数的函数就可以省略该关键字,over关键字是必须的,是分析函数就必须使用。
Over后面的()中是analytic_clause,即使analytic_clause的三个部分全不使用,()也必不可省,否则Oracle因不能识别函数为分析函数而报错。
5 Analytic_clause
Analytic_clause的语法如下:
[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]
这里:
query_partition_clause是查询分组子句;
order_by_clause是分组排序子句;
windowing_clause是窗口范围子句。
分析函数在查询结果集确定之后才开始进行计算,Analytic_clause就是用来定义函数怎样对查询结果集进行分组计算的。
根据Oracle对查询和分析函数的处理方法可知,在select和order by子句中都可以使用分析函数。
query_partition_by、order_by_clause和windowing_clause三个子句是可选的,将三个子句分别简记为p,o,w。
合法的组合方式有如下6种:
1). Pow
(query_partition_clause order_by_clause windowing_clause)
分组,排序,定义窗口范围
2). Po
(query_partition_clause order_by_clause)
分组,排序,窗口默认为range between unbounded preceding and current row
3). P
(query_partition_clause)
分组,不排序,没有窗口
4). Ow
(order_by_clause windowing_clause)
分组为整个查询结果集,排序,定义窗口范围
5). O
(order_by_clause)
分组为整个查询结果集,排序,窗口默认为range between unbounded preceding and current row
6). Null
()
分组为整个查询结果集,不排序,没有窗口
因为只有存在order_by_clause,才能有windowing_clause,故不存在如下两种形式的组合:
pw(query_partition_clause windowing_clause)
w(windowing_clause)
总结:
1). 对于是否存在order_by_clause,分析函数可以分为两类,含有order_by_clause的一般称为windowing function,不含的称为reporting function。
2). Windowing function,对查询结果集进行分组,排序,根据窗口范围计算分组中每一行的函数结果。
3). Reporting function,对查询结果集进行分组,不排序,窗口范围为整个分组,在每一个分组内,计算整个分组的函数值,再将函数值分别赋给分组内的每一行。
注意事项:
1).Analytic_clause内不能包含任何分析函数。
2). 用户自定义分析函数和内置函数分析函数都可以使用over (analytic_clause)。
5.1 Query_partition_clause
query_partition_clause的语法如下:
PARTITION BY value_expr[, value_expr ]...
这里:
partition by是关键字,表示要将查询结果集分组;
value_expr是分组表达式。
Partition by子句根据一个或多个value_expr将查询结果集分成若干组。分组的原理与group by分组的原理类似,将value_expr相同的行集中在一起。
若不使用该子句,那末函数将整个查询结果集作为一个分组。
value_expr可以为常量、表列、非分析函数、函数表达式,或者前面这些元素的任意组合表达式。
5.2 Order_by_clause
Order_by_clause的语法如下:
ORDER BY expr [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
[, expr [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] ]...
这里:
order by是关键字,表示要将分组排序;
expr是排序表达式;
asc|desc和nulls first|null last是排序的附加规则。
Order_by_clause根据一个或多个expr对分组进行排序。
Expr只能为表达式,不能为位置编号或列别名。
注意事项:
1). 当窗口范围为
Range between unbounded preceding and current row 或
Range between current row and unbounded following 时,
可以在order_by_clause中使用多个expr对分组进行排序。其他range窗口范围只能使用一个expr。使用rows定义窗口范围无此限制。
2). asc|desc 指定排序是按expr升序还是降序排序,若不指定则默认按asc进行升序排序。
3). nulls first|nulls last 指定若返回行包含空值,该值应该出现在排序序列的开始还是末尾。升序排序的默认值是nulls last,降序排序的默认值是nulls first。
5.3 Windowing_clause
Windowing_clause的语法如下:
{ ROWS | RANGE }
{ BETWEEN
{ UNBOUNDED PRECEDING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING } } AND
{ UNBOUNDED FOLLOWING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING } }
| { UNBOUNDED PRECEDING | CURRENT ROW | value_expr PRECEDING }
}
这里,
rows 是关键字,指定窗口由物理行构成;
range 是关键字,指定窗口由逻辑偏移量构成;
between…and是关键字,用来指定窗口的起始点和终结点;
Unbounded preceding指明窗口开始于分组的第一行;
Current row,作为起始点,指明窗口开始于当前行或当前值;作为终结点,指明窗口结束于当前行或当前值;
Unbounded following指明窗口结束于分组的最后一行;
Value_expr为物理或逻辑偏移量表达式。
Windowing_clause用来指定分组中当前行的计算范围。
不论rows还是range窗口,窗口总是在分组中从上至下滑动的。
窗口范围可以由between…and限定,也可以不用between…and,不用的都表示窗口到当前行结束。
ROWS窗口,是由分组排序后分组中若干连续的行构成的窗口。
以下是全部合法的ROWS窗口范围:
1). Rows between unbounded preceding and unbounded following
窗口开始于分组第一行,结束于分组最后一行。
2). Rows [between] unbounded preceding [and current row]
窗口开始于分组第一行,结束于当前行。
3). Rows between unbounded preceding and value_expr preceding
窗口开始于分组第一行,结束于当前行前value_expr行。
4). Rows between unbounded preceding and value_expr following
窗口开始于分组第一行,结束于当前行后value_expr行。
5). Rows between current row and unbounded following
窗口开始于当前行,结束于分组最后一行。
6). Rows [between current row and] current row
窗口开始于当前行,结束于当前行。
7). Rows between current row and value_expr following
窗口开始于当前行,结束于当前行后value_expr行。
8). Rows between value_expr preceding and unbounded following
窗口开始于当前行前value_expr行,结束于分组最后一行。
9). Rows [between value_expr] preceding [and current row]
窗口开始于当前行前value_expr行,结束于当前行。
10). Rows between value_expr1 preceding and value_expr2 preceding
窗口开始于当前行前value_expr1行,结束于当前行前value_expr2行。这里一定要满足value_expr1>=value_expr2。
11). Rows between value_expr1 preceding and value_expr2 following
窗口开始于当前行前value_expr1行,结束于当前行后value_expr2行。
12). Rows between value_expr following and unbounded following
窗口开始于当前行后value_expr行,结束于分组最后一行。
13). Rows between value_expr1 following and value_expr2 following
窗口开始于当前行后value_expr1行,结束于当前行后value_expr2行。这里一定要满足value_expr1<=value_expr2
14). Rows unbounded preceding
与2等价。
15). Rows current row
与6等价。
16). Rows value_expr preceding
与9等价。
RANGE窗口,相当于给order_by_clause中的expr加一个where限定条件,分组中满足条件
(当order by expr asc时,where expr between a and b
当order by expr desc时 where expr between b and a)的所有行构成一个逻辑窗口。
其中a由分组中第ra行的值计算而来,b由分组中第rb行的值计算而来,且ra<=rb。
根据range中是否包含unbounded,可以分为两类,含unbounded range窗口,和不含unbounded range窗口。
对于前者,有ra<=rb。当order by expr asc时,a<=b,须使用where expr between a and b;当order by expr desc时,a>b,须使用where expr between b and a。
对于后者,有ra=rb。总有a<=b,使用where expr between a and b。
对于在order_by_clause中可以使用多个expr的窗口:
Range between unbounded preceding and current row
Range between current row and unbounded following
当它们使用多个expr排序时(注意不是一个)分别等价于:
Rows between unbounded preceding and current row
Rows between current row and unbounded following
假设分组第一行的值为first_value,最后一行的值为last_value。当前行的值为current_value。
1). Range between unbounded preceding and unbounded following
Where expr between first_value and last_value –-when asc
or expr between last_value and first_value --when desc
2). Range [between] unbounded preceding [and current row]
Where expr between first_value and current_value
or expr between current_value and first_value
3). Range between unbounded preceding and value_expr preceding
Where expr between first_value and current_value-value_expr
or expr between current_value-value_expr and first_value
4). Range between unbounded preceding and value_expr following
Where expr between first_value and current_value+value_expr
or expr between current_value+value_expr and first_value
5). Range between current row and unbounded following
Where expr between current_value and last_value
or expr between last_value and current_value
6). Range [between current row and] current row
Where expr = current_value
7). Range between current row and value_expr following
Where expr between current_value and current_value+value_expr
8). Range between value_expr preceding and unbounded following
Where expr between current_value-value_expr and last_value
9). Range [between value_expr] preceding [and current row]
Where expr between current_value-value_expr and current_value
10). Range between value_expr1 preceding and value_expr2 preceding
这里一定要满足value_expr1>=value_expr2。
Where expr between current_value-value_expr1 and current_value-value_expr2
11). Range between value_expr1 preceding and value_expr2 following
Where expr between current_value-value_expr1 and current_value+value_expr2
12). Range between value_expr following and unbounded following
Where expr between current_value+value_expr and last_value
or expr between last_value and current_value+value_expr
13). Range between value_expr1 following and value_expr2 following
这里一定要满足value_expr1<=value_expr2。
Where expr between current_value+value_expr1 and current_value+value_expr2
14). Range unbounded preceding
与2等价。
15). Range current row
与6等价。
16). Range value_expr preceding
与9等价。
注意事项:
1).若windowing_clause由rows指定,则:
(1)value_expr是物理偏移量,它必须是常量或值为非负数的表达式。
(2)若value_expr是起点的一部分,那末它必须在终点之前对行求值。
2).若windowing_clause由range指定,则:
(1)value_expr是逻辑偏移量。它必须是常量或值为非负数的表达式或时间间隔文字常量。
(2)若value_expr值为一个数字,那末order_by_clause中 expr必须为数字或date类型。
(3)若value_expr为一个间隔值,那末order_by_clause中expr必须是一个date类型。
3).若完全忽略windowing_clause,那末默认的窗口范围为 range between unbounded preceding and current row 。
6 分析函数简例
下面的例子都基于scott模式下的emp表。
SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7839 KING PRESIDENT 1981-11-17 5000.00 10
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7788 SCOTT ANALYST 7566 1982-12-9 3000.00 20
7876 ADAMS CLERK 7788 1983-1-12 1100.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
6.1 E1
SELECT SUM(sal) over() sal_sum FROM emp;
1). 聚集函数用作分析函数时,不需要group by子句;
2). 分析函数作为表列可以有别名,但必须放在over()之后。
6.2 E2
SELECT empno, ename, job, SUM(sal) over(PARTITION BY mgr ORDER BY job)
FROM emp;
1).
分析函数的分组与选择列也没有任何关系,比如上例,在选择列中可以有mgr,也可以没有mgr;
2).
分析函数的排序与选择列也没有任何关系,比如上例,在选择列中可以没有job。
6.3 E3
SELECT empno, ename, mgr, sal
FROM emp
ORDER BY empno,
row_number() over(PARTITION BY mgr ORDER BY sal),
SUM(1) over(PARTITION BY empno)
1). 分析函数可用于order by子句中;
2). Order by后可以用多个分析函数,也可以与其他排序列混合使用。
6.4 E4(pow)
SELECT empno,
ename,
mgr,
sal,
hiredate,
SUM(sal) over(PARTITION BY mgr ORDER BY hiredate RANGE BETWEEN unbounded preceding AND unbounded following)
FROM emp;
EMPNO ENAME MGR SAL HIREDATE SAL_SUM
7902 FORD 7566 3000.00 1981-12-3 6000
7788 SCOTT 7566 3000.00 1982-12-9 6000
7499 ALLEN 7698 1600.00 1981-2-20 6550
7521 WARD 7698 1250.00 1981-2-22 6550
7844 TURNER 7698 1500.00 1981-9-8 6550
7654 MARTIN 7698 1250.00 1981-9-28 6550
7900 JAMES 7698 950.00 1981-12-3 6550
7934 MILLER 7782 1300.00 1982-1-23 1300
7876 ADAMS 7788 1100.00 1983-1-12 1100
7566 JONES 7839 2975.00 1981-4-2 8275
7698 BLAKE 7839 2850.00 1981-5-1 8275
7782 CLARK 7839 2450.00 1981-6-9 8275
7369 SMITH 7902 800.00 1980-12-17 800
7839 KING 5000.00 1981-11-17 5000
按mgr分组,hiredate排序,窗口范围为整个分组。
分析函数sum对每个窗口中的所有雇员薪水求和,再将它赋给每一个雇员,故相同mgr的雇员的sal_sum都是相同的。
6.5 E5(po)
SELECT empno,
ename,
mgr,
sal,
hiredate,
SUM(sal) over(PARTITION BY mgr ORDER BY hiredate) sal_sum
FROM emp;
EMPNO ENAME MGR SAL HIREDATE SAL_SUM
7902 FORD 7566 3000.00 1981-12-3 3000
7788 SCOTT 7566 3000.00 1982-12-9 6000
7499 ALLEN 7698 1600.00 1981-2-20 1600
7521 WARD 7698 1250.00 1981-2-22 2850
7844 TURNER 7698 1500.00 1981-9-8 4350
7654 MARTIN 7698 1250.00 1981-9-28 5600
7900 JAMES 7698 950.00 1981-12-3 6550
7934 MILLER 7782 1300.00 1982-1-23 1300
7876 ADAMS 7788 1100.00 1983-1-12 1100
7566 JONES 7839 2975.00 1981-4-2 2975
7698 BLAKE 7839 2850.00 1981-5-1 5825
7782 CLARK 7839 2450.00 1981-6-9 8275
7369 SMITH 7902 800.00 1980-12-17 800
7839 KING 5000.00 1981-11-17 5000
按mgr分组,hiredate排序,没有指定窗口范围,默认为range between unbounded preceding and current row。
注意窗口是由hiredate确定,默认的窗口范围为不晚于当前雇员雇佣日期的hiredate。因又按hiredate升序,故sum对分组中当前雇员前面(包括当前雇员)的所有雇员求和,再赋给当前雇员。
可以将其补齐为pow的形式,等价于:
SELECT empno,
ename,
mgr,
sal,
hiredate,
SUM(sal) over(PARTITION BY mgr ORDER BY hiredate RANGE BETWEEN unbounded preceding AND CURRENT ROW) sal_sum
FROM emp;
再看一个例子:
SELECT empno,
ename,
mgr,
sal,
hiredate,
COUNT(1) over(PARTITION BY mgr ORDER BY hiredate DESC) sal_sum
FROM emp;
EMPNO ENAME MGR SAL HIREDATE SAL_SUM
7788 SCOTT 7566 3000.00 1982-12-9 1
7902 FORD 7566 3000.00 1981-12-3 2
7900 JAMES 7698 950.00 1981-12-3 1
7654 MARTIN 7698 1250.00 1981-9-28 2
7844 TURNER 7698 1500.00 1981-9-8 3
7521 WARD 7698 1250.00 1981-2-22 4
7499 ALLEN 7698 1600.00 1981-2-20 5
7934 MILLER 7782 1300.00 1982-1-23 1
7876 ADAMS 7788 1100.00 1983-1-12 1
7782 CLARK 7839 2450.00 1981-6-9 1
7698 BLAKE 7839 2850.00 1981-5-1 2
7566 JONES 7839 2975.00 1981-4-2 3
7369 SMITH 7902 800.00 1980-12-17 1
7839 KING 5000.00 1981-11-17 1
这个例子统计相同经理下当前雇员雇佣日期不早于自己的雇员个数。
6.6 E6(p)
SELECT empno,
ename,
mgr,
sal,
hiredate,
COUNT(1) over(PARTITION BY mgr) sal_sum
FROM emp;
EMPNO ENAME MGR SAL HIREDATE SAL_SUM
7788 SCOTT 7566 3000.00 1982-12-9 2
7902 FORD 7566 3000.00 1981-12-3 2
7844 TURNER 7698 1500.00 1981-9-8 5
7521 WARD 7698 1250.00 1981-2-22 5
7900 JAMES 7698 950.00 1981-12-3 5
7499 ALLEN 7698 1600.00 1981-2-20 5
7654 MARTIN 7698 1250.00 1981-9-28 5
7934 MILLER 7782 1300.00 1982-1-23 1
7876 ADAMS 7788 1100.00 1983-1-12 1
7698 BLAKE 7839 2850.00 1981-5-1 3
7566 JONES 7839 2975.00 1981-4-2 3
7782 CLARK 7839 2450.00 1981-6-9 3
7369 SMITH 7902 800.00 1980-12-17 1
7839 KING 5000.00 1981-11-17 1
按mgr分组,不排序,没有规定窗口范围。
这是个reporting function,不排序表明没有窗口,计算范围则为整个分组。窗口范围等价于range/rows between unbounded preceding and unbounded following。分析函数计算分组中的雇员总数,再将其赋给每一个雇员。
可以将其补齐为pow形式,等价于:
SELECT empno,
ename,
mgr,
sal,
hiredate,
COUNT(1) over(PARTITION BY mgr ORDER BY NULL ROWS BETWEEN unbounded preceding AND unbounded following) sal_sum
FROM emp;
6.7 E7(ow)
SELECT empno,
ename,
mgr,
sal,
hiredate,
SUM(sal) over(ORDER BY hiredate RANGE BETWEEN 30 preceding AND 30 following) sal_sum
FROM emp;
EMPNO ENAME MGR SAL HIREDATE SAL_SUM
7369 SMITH 7902 800.00 1980-12-17 800
7499 ALLEN 7698 1600.00 1981-2-20 2850
7521 WARD 7698 1250.00 1981-2-22 2850
7566 JONES 7839 2975.00 1981-4-2 5825
7698 BLAKE 7839 2850.00 1981-5-1 5825
7782 CLARK 7839 2450.00 1981-6-9 2450
7844 TURNER 7698 1500.00 1981-9-8 2750
7654 MARTIN 7698 1250.00 1981-9-28 2750
7839 KING 5000.00 1981-11-17 8950
7900 JAMES 7698 950.00 1981-12-3 8950
7902 FORD 7566 3000.00 1981-12-3 8950
7934 MILLER 7782 1300.00 1982-1-23 1300
7788 SCOTT 7566 3000.00 1982-12-9 3000
7876 ADAMS 7788 1100.00 1983-1-12 1100
分组为整个查询结果集,按hiredate排序,窗口范围为hiredate between current_hiredate – 30 and current_hiredate + 30。
在整个查询结果集中,分析函数计算与当前雇员日期相差在30天的雇员的工资总和,再将结果赋给当前雇员。
补齐pow形式,等价如下:
SELECT empno,
ename,
mgr,
sal,
hiredate,
SUM(sal) over(PARTITION BY NULL ORDER BY hiredate RANGE BETWEEN 30 preceding AND 30 following) sal_sum
FROM emp;
6.8 E7(o)
SELECT empno,
ename,
mgr,
sal,
hiredate,
SUM(sal) over(ORDER BY hiredate) sal_sum
FROM emp;
EMPNO ENAME MGR SAL HIREDATE SAL_SUM
7369 SMITH 7902 800.00 1980-12-17 800
7499 ALLEN 7698 1600.00 1981-2-20 2400
7521 WARD 7698 1250.00 1981-2-22 3650
7566 JONES 7839 2975.00 1981-4-2 6625
7698 BLAKE 7839 2850.00 1981-5-1 9475
7782 CLARK 7839 2450.00 1981-6-9 11925
7844 TURNER 7698 1500.00 1981-9-8 13425
7654 MARTIN 7698 1250.00 1981-9-28 14675
7839 KING 5000.00 1981-11-17 19675
7900 JAMES 7698 950.00 1981-12-3 23625
7902 FORD 7566 3000.00 1981-12-3 23625
7934 MILLER 7782 1300.00 1982-1-23 24925
7788 SCOTT 7566 3000.00 1982-12-9 27925
7876 ADAMS 7788 1100.00 1983-1-12 29025
分组为整个查询结果集,按hiredate排序,窗口默认为range between unbounded preceding and current row。
在整个查询结果集中,分析函数计算当前雇员雇佣日期不晚于自己的雇员薪水总和,再将其赋给当前雇员。
补齐pow形式,等价于:
SELECT empno,
ename,
mgr,
sal,
hiredate,
SUM(sal) over(PARTITION BY NULL ORDER BY hiredate RANGE BETWEEN unbounded preceding AND CURRENT ROW) sal_sum
FROM emp;
6.9 E8()
SELECT empno,
ename,
mgr,
sal,
hiredate,
SUM(sal) over() sal_sum
FROM emp;
EMPNO ENAME MGR SAL HIREDATE SAL_SUM
7369 SMITH 7902 800.00 1980-12-17 29025
7499 ALLEN 7698 1600.00 1981-2-20 29025
7521 WARD 7698 1250.00 1981-2-22 29025
7566 JONES 7839 2975.00 1981-4-2 29025
7654 MARTIN 7698 1250.00 1981-9-28 29025
7698 BLAKE 7839 2850.00 1981-5-1 29025
7782 CLARK 7839 2450.00 1981-6-9 29025
7788 SCOTT 7566 3000.00 1982-12-9 29025
7839 KING 5000.00 1981-11-17 29025
7844 TURNER 7698 1500.00 1981-9-8 29025
7876 ADAMS 7788 1100.00 1983-1-12 29025
7900 JAMES 7698 950.00 1981-12-3 29025
7902 FORD 7566 3000.00 1981-12-3 29025
7934 MILLER 7782 1300.00 1982-1-23 29025
分组为整个查询结果集,不排序,没有规定窗口。
这是个reporting function。分析函数计算整个查询结果集中雇员薪水总和,再将其值赋给每一雇员。
补齐pow形式等价于:
SELECT empno,
ename,
mgr,
sal,
hiredate,
SUM(sal) over(PARTITION BY NULL ORDER BY NULL RANGE BETWEEN unbounded preceding AND unbounded following) sal_sum
FROM emp;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/219982/viewspace-524820/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/219982/viewspace-524820/