【Oracle】Oracle分析函数详解
Oracle数据库中的函数有多种，⽐如单⾏函数、聚合函数、对象引⽤函数、模型函数、OLAP函数等。本篇将详细介绍Oracle数据库中的分析函数。
⼀分析函数概述
所谓分析函数，是基于⼀组数据⾏计算聚合值，其与聚合函数的不同之处在于，它为每⼀组返回多个数据⾏。⼀组数据⾏称为⼀个窗⼝，由analytic_clause⼦句进⾏定义，对于每⼀⾏，定义⼀个⾏移动窗⼝，窗⼝确定⽤于为当前⾏执⾏计算的⾏的范围，它的⼤⼩可以基于物理⾏或逻辑间隔（如时间）。
⼆分析函数语法
说明：
analytic_function
指出分析函数的名称；
arguments
分析函数的参数，参数数量在0个到3个之间，该参数类型可以是任何数值数据类型，或者任何可以隐式转换为数值数据类型的⾮数值类型。
analytic_clause
使⽤OVER analytic_clause⼦句展⽰在查询结果集上所进⾏的函数操作，该⼦句是在FROM、WHERE、GROUP BY和HAVING⼦句后进⾏计算的。
使⽤PARTITION BY⼦句可以基于⼀个或多个expr将查询结果集划分为多个组，如果忽略该⼦句，则函数会将整个查询结果集当做⼀个组。
order_by_clause⼦句⽤于指定数据是在⼀个分组⾥是如何进⾏排序的。
三分析函数类型
Oracle有以下类型的分析函数，其中，带有*号的函数可以使⽤上⾯的完全语句，包括窗⼝⼦句。
AVG *
COPR *
COUNT *
COVAR_POP *
COVAR_SAMP *
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE *
LAG
LAST
LAST_VALUE *
LEAD
LISTAGG
MAX *
MEDIAN
MIN *
NTH_VALUE *
NTILE
PERCENT_RANK
PERCENTILE_COUNT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT
REGR_(Linear Regression) Functions *
ROW_NUMBER
STDDEV *
STDDEV_POP *
STDDEV_SAMP *
SUM *
VAR_POP *
VAR_SAMP *
VARIANCE *
四分析函数详解
1 演⽰数据库版本
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
2 AVG函数
1）语法结构
AVG函数⽤于返回expr的平均值，如果指定DISTINCT，那么只能指定analytic_clause中的query_partition_clause⼦句，不能指定order_by_clause andwindowing_clause⼦句。
2）⽰例
SQL> SELECT empno,
ename,
job,
mgr,
hiredate,
deptno,
sal,
round(AVG(sal) over(), 2) avg_all_sal,
round(AVG(sal) over(PARTITION BY deptno), 2) avg_dept_sal,
round(AVG(sal) over(PARTITION BY deptno ORDER BY empno
rows BETWEEN 1 preceding AND 1 following),2) avg_sal1,
round(AVG(sal) over(PARTITION BY deptno ORDER BY empno
rows BETWEEN CURRENT ROW AND 1 following),2) avg_sal2
FROM emp;
3 COUNT
1）语法结构
COUNT返回查询的⾏数，如果指定DISTINCT，那么只能指定analytic_clause中的query_partition_clause⼦句，不能指定order_by_clause andwindowing_clause⼦句。如果指定expr，COUNT返回expr⾮空的⾏数，如果指定*，则返回所有⾏数，包括重复⾏和空⾏。
2）⽰例
SQL> SELECT empno,
ename,
job,
mgr,
hiredate,
deptno,
sal,
comm,
COUNT(1) over() qty1,
COUNT(*) over() qty2,
COUNT(comm) over() qty3,
COUNT(DISTINCT deptno) over() qty4,
COUNT(1) over(PARTITION BY deptno) qty5
FROM emp;
4 RANK、DENSE_RANK、ROW_NUMBER
1）语法结构
2）⽰例
SQL> SELECT empno,
ename,
job,
mgr,
hiredate,
sal,
deptno,
--row_number() over(ORDER BY sal) rn1,
row_number() over(PARTITION BY deptno ORDER BY sal) rn2,
--rank()over(order by sal) rn3
rank() over(PARTITION BY deptno ORDER BY sal) r4,
dense_rank() over(PARTITION BY deptno ORDER BY sal) r5
FROM emp;
oracle 时间转换注：这三个函数主要⽤于进⾏排序，ROW_NUMBER函数排序的结果是连续的，⽽DENSE函数排序会出现跳号，DENSE_RANK函数排序，不会出现跳号。
5 FIRST_VALUE
1）语法结构
FIRST_VALUE⽤于返回在⼀个排序结果集中的第⼀个值。
2）⽰例

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。