oracle聚合函数的最大层级

聚合函数

SQL基本函数，聚合函数对一组值执行计算，并返回单个值，也被称为组函数。 聚合函数经常与 SELECT 语句的 GROUP BY 子句的HAVING一同使用。

中文名

聚合函数

外文名

aggregate function

应用

软件编程

性质

确定性

别名

组函数

快速

导航

性质

应用

定义

聚合函数对一组值执行计算并返回单一的值。除 COUNT 以外，聚合函数忽略空值，如果COUNT函数的应用对象是一个确定列名，并且该列存在空值，此时COUNT仍会忽略空值。

性质

所有聚合函数都具有确定性。任何时候用一组给定的输入值调用它们时，都返回相同的值。聚合函数可以应用于查询语句的SELECT中，或者HAVING子句中，但不可用于WHERE语句中，因为WHERE是对逐条的行记录进行筛选。

应用

Transact-SQL编程语言提供下列聚合函数：

AVG 返回指定组中的平均值，空值被忽略。

例：select prd_no,avg(qty) from sales group by prd_no

2 COUNT 返回指定组中项目的数量。

例：select count(prd_no) from sales

3 MAX 返回指定数据的最大值。

例：select prd_no,max(qty) from sales group by prd_no

4 MIN 返回指定数据的最小值。

例：select prd_no,min(qty) from sales group by prd_no

5 SUM 返回指定数据的和，只能用于数字列，空值被忽略。

例：select prd_no,sum(qty) from sales group by prd_no

6 COUNT_BIG 返回指定组中的项目数量，与COUNT函数不同的是COUNT_BIG返回bigint值，而COUNT返回的是int值。

例：select count_big(prd_no) from sales

7 GROUPING 产生一个附加的列，当用CUBE或ROLLUP运算符添加行时，输出值为1当所添加的行不是由CUBE或ROLLUP产生时，输出值为0

例：select prd_no,sum(qty),grouping(prd_no) from sales group by prd_no with rollup

8 BINARY_CHECKSUM 返回对表中的行或表达式列表计算的二进制校验值，用于检测表中行的更改。

例：select prd_no,binary_checksum(qty) from sales group by prd_no

9 CHECKSUM_AGG 返回指定数据的校验值，空值被忽略。

例：select prd_no,checksum_agg(binary_checksum()) from sales group by prd_no

10 CHECKSUM 返回在表的行上或在表达式列表上计算的校验值，用于生成哈希索引。

11 STDEV 返回给定表达式中所有值的统计标准偏差。

---------数学函数

1绝对值

S:select abs(-1) value

O:select abs(-1) value from dual

2取整(大)

S:select ceiling(-1001) value

O:select ceil(-1001) value from dual

3取整（小）

S:select floor(-1001) value

O:select floor(-1001) value from dual

4取整（截取）

S:select cast(-1002 as int) value

O:select trunc(-1002) value from dual

5四舍五入

S:select round(123456,4) value 123460

O:select round(123456,4) value from dual 12346

6e为底的幂

S:select Exp(1) value 27182818284590451

O:select Exp(1) value from dual 271828182

7取e为底的对数

S:select log(27182818284590451) value 1

O:select ln(27182818284590451) value from dual; 1

8取10为底对数

S:select log10(10) value 1

O:select log(10,10) value from dual; 1

9取平方

S:select SQUARE(4) value 16

O:select power(4,2) value from dual 16

10取平方根

S:select SQRT(4) value 2

O:select SQRT(4) value from dual 2

11求任意数为底的幂

S:select power(3,4) value 81

O:select power(3,4) value from dual 81

12取随机数

S:select rand() value

O:select sysdbms_randomvalue(0,1) value from dual;

13取符号

S:select sign(-8) value -1

O:select sign(-8) value from dual -1

----------三角函数相关

14圆周率

S:SELECT PI() value 31415926535897931

O:不知道

15sin,cos,tan 参数都以弧度为单位

例如：select sin(PI()/2) value 得到1（SQLServer）

16Asin,Acos,Atan,Atan2 返回弧度

17弧度角度互换(SQLServer，Oracle不知道)

DEGREES：弧度-〉角度

RADIANS：角度-〉弧度

---------数值间比较

18 求集合最大值

S:select max(value) value from

(select 1 value

union

select -2 value

union

select 4 value

union

select 3 value)a

O:select greatest(1,-2,4,3) value from dual

19 求集合最小值

S:select min(value) value from

(select 1 value

union

select -2 value

union

select 4 value

union

select 3 value)a

O:select least(1,-2,4,3) value from dual

20如何处理null值(F2中的null以10代替)

S:select F1,IsNull(F2,10) value from Tbl

O:select F1,nvl(F2,10) value from Tbl

--------字符串函数

21求字符序号

S:select ascii('a') value

O:select ascii('a') value from dual

22从序号求字符

S:select char(97) value

O:select chr(97) value from dual

23连接

S:select '11'+'22'+'33' value

O:select CONCAT('11','22')||33 value from dual

23子串位置 --返回3

S:select CHARINDEX('s','sdsq',2) value

O:select INSTR('sdsq','s',2) value from dual

23模糊子串的位置 --返回2,参数去掉中间%则返回7

// 本文转自 C++Builder 研究 - http://wwwccruncom/articleaspi=996&d=dwn1rn

S:select patindex('%d%q%','sdsfasdqe') value

O:oracle没发现，但是instr可以通过第四个参数控制出现次数

select INSTR('sdsfasdqe','sd',1,2) value from dual 返回6

24求子串

S:select substring('abcd',2,2) value

O:select substr('abcd',2,2) value from dual

25子串代替 返回aijklmnef

S:SELECT STUFF('abcdef', 2, 3, 'ijklmn') value

O:SELECT Replace('abcdef', 'bcd', 'ijklmn') value from dual

26子串全部替换

S:Replace

O:select Translate('fasdbfasegas','fa','我' ) value from dual

27长度

S:len,datalength

O:length

28大小写转换 lower,upper

29单词首字母大写

S:没发现

O:select INITCAP('abcd dsaf df') value from dual

30左补空格（LPAD的第一个参数为空格则同space函数）

S:select space(10)+'abcd' value

O:select LPAD('abcd',14) value from dual

31右补空格（RPAD的第一个参数为空格则同space函数）

S:select 'abcd'+space(10) value

O:select RPAD('abcd',14) value from dual

32删除空格

S:ltrim,rtrim

O:ltrim,rtrim,trim

33 重复字符串

S:select REPLICATE('abcd',2) value

O:没发现

34发音相似性比较(这两个单词返回值一样，发音相同)

S:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe')

O:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') from dual

SQLServer中用SELECT DIFFERENCE('Smithers', 'Smythers') 比较soundex的差

返回0-4，4为同音，1最高

--------------日期函数

35系统时间

S:select getdate() value

O:select sysdate value from dual

36前后几日

直接与整数相加减

37求日期

S:select convert(char(10),getdate(),20) value

O:select trunc(sysdate) value from dual

select to_char(sysdate,'yyyy-mm-dd') value from dual

38求时间

S:select convert(char(8),getdate(),108) value

O:select to_char(sysdate,'hh24:mm:ss') value from dual

39取日期时间的其他部分

S:DATEPART 和 DATENAME 函数 （第一个参数决定）

O:to_char函数 第二个参数决定

参数---------------------------------下表需要补充

year yy, yyyy

quarter qq, q (季度)

month mm, m (m O无效)

dayofyear dy, y (O表星期)

day dd, d (d O无效)

week wk, ww (wk O无效)

weekday dw (O不清楚)

Hour hh,hh12,hh24 (hh12,hh24 S无效)

minute mi, n (n O无效)

second ss, s (s O无效)

millisecond ms (O无效)

----------------------------------------------

40当月最后一天

S:不知道

O:select LAST_DAY(sysdate) value from dual

41本星期的某一天（比如星期日）

S:不知道

O:SELECT Next_day(sysdate,7) vaule FROM DUAL;

42字符串转时间

S:可以直接转或者select cast('2004-09-08'as datetime) value

O:SELECT To_date('2004-01-05 22:09:38','yyyy-mm-dd hh24-mi-ss') vaule FROM DUAL;

43求两日期某一部分的差（比如秒）

S:select datediff(ss,getdate(),getdate()+123) value

O:直接用两个日期相减（比如d1-d2=123）

SELECT (d1-d2)246060 vaule FROM DUAL;

44根据差值求新的日期（比如分钟）

S:select dateadd(mi,8,getdate()) value

O:SELECT sysdate+8/60/24 vaule FROM DUAL;

45求不同时区时间

S:不知道

O:SELECT New_time(sysdate,'ydt','gmt' ) vaule FROM DUAL;

-----时区参数,北京在东8区应该是Ydt-------

AST ADT 大西洋标准时间

BST BDT 白令海标准时间

CST CDT 中部标准时间

EST EDT 东部标准时间

GMT 格林尼治标准时间

HST HDT 阿拉斯加—夏威夷标准时间

MST MDT 山区标准时间

NST 纽芬兰标准时间

PST PDT 太平洋标准时间

YST YDT YUKON标准时间

Oracle支持的字符函数和它们的Microsoft SQL Server等价函数:

函数 Oracle Microsoft SQL　Server

把字符转换为ASCII ASCII ASCII

字串连接 CONCAT (expression + expression)

把ASCII转换为字符 CHR CHAR

返回字符串中的开始字符（左起） INSTR CHARINDEX

把字符转换为小写 LOWER LOWER

把字符转换为大写 UPPER UPPER

填充字符串的左边 LPAD N/A

清除开始的空白 LTRIM LTRIM

清除尾部的空白 RTRIM RTRIM

字符串中的起始模式（pattern） INSTR PATINDEX

多次重复字符串 RPAD REPLICATE

字符串的语音表示 SOUNDEX SOUNDEX

重复空格的字串 RPAD SPACE

从数字数据转换为字符数据 TO_CHAR STR

子串 SUBSTR SUBSTRING

替换字符 REPLACE STUFF

将字串中的每个词首字母大写 INITCAP N/A

翻译字符串 TRANSLATE N/A

字符串长度 LENGTH DATELENGTH or LEN

列表中最大的字符串 GREATEST N/A

列表中最小的字符串 LEAST N/A

如果为NULL则转换字串 NVL ISNULL

日期函数

函数 Oracle Microsoft SQL　Server

日期相加 (date column +/- value) or

ADD_MONTHS DATEADD

两个日期的差 (date column +/- value) or

MONTHS_BETWEEN DATEDIFF

当前日期和时间 SYSDATE GETDATE()

一个月的最后一天 LAST_DAY N/A

时区转换 NEW_TIME N/A

日期后的第一个周日 NEXT_DAY N/A

代表日期的字符串 TO_CHAR DATENAME

代表日期的整数 TO_NUMBER

(TO_CHAR)) DATEPART

日期舍入 ROUND CONVERT

日期截断 TRUNC CONVERT

字符串转换为日期 TO_DATE CONVERT

如果为NULL则转换日期 NVL ISNULL

转换函数

函数 Oracle Microsoft SQL　Server

数字转换为字符 TO_CHAR CONVERT

字符转换为数字 TO_NUMBER CONVERT

日期转换为字符 TO_CHAR CONVERT

字符转换为日期 TO_DATE CONVERT

16进制转换为2进制 HEX_TO_RAW CONVERT

2进制转换为16进制 RAW_TO_HEX CONVERT

其它行级别的函数

函数 Oracle Microsoft SQL　Server

返回第一个非空表达式 DECODE COALESCE

当前序列值 CURRVAL N/A

下一个序列值 NEXTVAL N/A

如果exp1 = exp2, 返回null DECODE NULLIF

用户登录账号ID数字 UID SUSER_ID

用户登录名 USER SUSER_NAME

用户数据库ID数字 UID USER_ID

用户数据库名 USER USER_NAME

当前用户 CURRENT_USER CURRENT_USER

用户环境(audit trail) USERENV N/A

在CONNECT BY子句中的级别 LEVEL N/A

合计函数

函数 Oracle Microsoft SQL　Server

Average AVG AVG

Count COUNT COUNT

Maximum MAX MAX

Minimum MIN MIN

Standard deviation STDDEV STDEV or STDEVP

Summation SUM SUM

Variance VARIANCE VAR or VARP

Oracle还有一个有用的函数EXTRACT,提取并且返回日期时间或时间间隔表达式中特定的时间域:

EXTRACT(YEAR FROM 日期)

　　（1）oracle使用keep分析函数取最值记录

　　-- 取工资sal最大的雇员姓名及其工资，以及工资sal最少的雇员姓名及其工资select

　　deptno,

　　empno,

　　ename,

　　sal,

　　max(ename) keep(dense_rank FIRST order by sal) over (partition by deptno) as min_sal_man,max(sal) keep(dense_rank FIRST order by sal) over (partition by deptno) as min_sal,max(ename) keep(dense_rank LAST order by sal) over (partition by deptno) as max_sal_man,max(sal) keep(dense_rank LAST order by sal) over (partition by deptno) as max_salfrom emp

　　where deptno=10

　　结果如下：

　　技术分享

　　从语句中可以看到，ename和sal都是用的max()，这样做的目的是为了去除由于keep()函数得到的有重复值的数据结果集。这样用有一个弊端，加入部门20有两个相同的最大SAL的人，部门30有两个相同的最小SAL的人，如果按照这种方法取出来的数据，就不一定准确了，重复的人会被去除掉。

　　我们用下面的语句来修改一下：

　　select

　　deptno,

　　empno,

　　ename,

　　sal,

　　max(ename) keep(dense_rank FIRST order by sal) over (partition by deptno) as min_sal_man,max(sal) keep(dense_rank FIRST order by sal) over (partition by deptno) as min_sal,max(ename) keep(dense_rank LAST order by sal) over (partition by deptno) as max_sal_man,max(sal) keep(dense_rank LAST order by sal) over (partition by deptno) as max_sal,wmsyswm_concat(ename) keep(dense_rank LAST order by sal) over (partition by deptno) as 工资最高的人,wmsyswm_concat(ename) keep(dense_rank FIRST order by sal) over (partition by deptno) as 工资最低的人from emp

　　where deptno=20

　　order by 1, 2 ;

　　我们新增了两个列：工资最高的人，工资最低的人。执行看一下结果：

　　技术分享

　　可以看到，deptno=20时，SCOTT和FORD两个人的工资SAL都是3000，如果用MAX()就只能取出其中一个人的姓名，显然是不对的。

　　然后，我们再来看一下deptno=30时的情况：

　　select

　　deptno,

　　empno,

　　ename,

　　sal,

　　max(ename) keep(dense_rank FIRST order by sal) over (partition by deptno) as min_sal_man,max(sal) keep(dense_rank FIRST order by sal) over (partition by deptno) as min_sal,max(ename) keep(dense_rank LAST order by sal) over (partition by deptno) as max_sal_man,max(sal) keep(dense_rank LAST order by sal) over (partition by deptno) as max_sal,wmsyswm_concat(ename) keep(dense_rank LAST order by sal) over (partition by deptno) as 工资最高的人,wmsyswm_concat(ename) keep(dense_rank FIRST order by sal) over (partition by deptno) as 工资最低的人from emp

　　where deptno=30

　　order by 1, 2 ;

　　deptno=30时的结果如下：

　　技术分享

　　可以看到，deptno=30时，WARD和MARTIN两人的工资最小且均为1250，如果用MAX()的方式，就只能取出其中一个人的名称。

　　这就是因为keep()取出来的数据集是包含多个数据结果的，所以，在语句中使用了wmsyswm_concat()函数，该函数的作用是以逗号分隔连接列的值。

　　注：wm_concat()的功能有点儿类似分析函数listagg() within group() 。

　　（2）使用SQL子查询和聚合函数，查询出最大值和最小值-- 使用子查询查询出最大值和最小值

　　select from

　　(

　　select

　　deptno,

　　listagg(ename,‘,‘) within group (order by deptno) as dept_max_ename,max(sal) as dept_max_sal

　　from emp

　　where (deptno,sal) in (select deptno, max(sal) as max_sal from emp group by deptno)group by deptno

　　) A

　　inner join

　　(

　　select

　　deptno,

　　listagg(ename,‘,‘) within group (order by deptno) as dept_min_ename,min(sal) as dept_min_sal

　　from emp

　　where (deptno,sal) in (select deptno, min(sal) as min_sal from emp group by deptno)group by deptno

　　) B

　　on Adeptno = Bdeptno

　　结果如下：

　　技术分享

　　在这个方案里面，还使用了listagg()分析函数将最值有重复姓名的人合并在一起，用wm_concat()函数替代listagg()也可以wm_concat(ename) as dept_max_ename,

　　wm_concat(ename) as dept_min_ename,

在oracle中，sum一般用于统计某列的和，count用于统计行数。工具：oracle 10g 步骤： 1、oracle中emp表有如下数据： 2、要求按deptno字段统计每个deptno下有多少行数据，可用如下语句： select deptno,count() from emp group by deptno; 3、查询结果： 4、要求按deptno字段统计每个deptno下的sal的和是多少，可用如下语句： select deptno,sum(sal) from emp group by deptno; 5、查询结果： 其中：sum、count等在oracle中叫聚合函数，聚合函数对一组值执行计算并返回单一的值。除了 count以外，聚合函数忽略空值。聚合函数经常与 select语句的group by子句一同使用。

这种情况 想象不出来会是什么数据。

我没看数据的情况，初步分析 跟你的GROUP BY

没什么好办法，就得分贝确认 MAX的值 和AVG的数据

通过GROUP BY 再看

SQL中的单记录函数

1ASCII

返回与指定的字符对应的十进制数;

SQL select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;

A A ZERO SPACE

--------- --------- --------- ---------

65 97 48 32

2CHR

给出整数,返回对应的字符;

SQL select chr(54740) zhao,chr(65) chr65 from dual;

ZH C

-- -

赵 A

3CONCAT

连接两个字符串;

SQL select concat('010-','88888888')||'转23' 高乾竞电话 from dual;

高乾竞电话

----------------

010-88888888转23

4INITCAP

返回字符串并将字符串的第一个字母变为大写;

SQL select initcap('smith') upp from dual;

UPP

-----

Smith

5INSTR(C1,C2,I,J)

在一个字符串中搜索指定的字符,返回发现指定的字符的位置;

C1 被搜索的字符串

C2 希望搜索的字符串

I 搜索的开始位置,默认为1

J 出现的位置,默认为1

SQL select instr('oracle traning','ra',1,2) instring from dual;

INSTRING

---------

9

6LENGTH

返回字符串的长度;

SQL select name,length(name),addr,length(addr),sal,length(to_char(sal)) from nchar_tst;

NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL))

------ ------------ ---------------- ------------ --------- --------------------

高乾竞 3 北京市海锭区 6 999999 7

7LOWER

返回字符串,并将所有的字符小写

SQL select lower('AaBbCcDd')AaBbCcDd from dual;

AABBCCDD

--------

aabbccdd

8UPPER

返回字符串,并将所有的字符大写

SQL select upper('AaBbCcDd') upper from dual;

UPPER

--------

AABBCCDD

9RPAD和LPAD(粘贴字符)

RPAD 在列的右边粘贴字符

LPAD 在列的左边粘贴字符

SQL select lpad(rpad('gao',10,''),17,'')from dual;

LPAD(RPAD('GAO',1

-----------------

gao

不够字符则用来填满

10LTRIM和RTRIM

LTRIM 删除左边出现的字符串

RTRIM 删除右边出现的字符串

SQL select ltrim(rtrim(' gao qian jing ',' '),' ') from dual;

LTRIM(RTRIM('

-------------[nextpage]

gao qian jing

11SUBSTR(string,start,count)

取子字符串,从start开始,取count个

SQL select substr('13088888888',3,8) from dual;

SUBSTR('

--------

08888888

12REPLACE('string','s1','s2')

string 希望被替换的字符或变量

s1 被替换的字符串

s2 要替换的字符串

SQL select replace('he love you','he','i') from dual;

REPLACE('HELOVEYOU','HE','I')

------------------------------

i love you

13SOUNDEX

返回一个与给定的字符串读音相同的字符串

SQL create table table1(xm varchar(8));

SQL insert into table1 values('weather');

SQL insert into table1 values('wether');

SQL insert into table1 values('gao');

SQL select xm from table1 where soundex(xm)=soundex('weather');

XM

--------

weather

wether

14TRIM('s' from 'string')

LEADING 剪掉前面的字符

TRAILING 剪掉后面的字符

如果不指定,默认为空格符

15ABS

返回指定值的绝对值

SQL select abs(100),abs(-100) from dual;

ABS(100) ABS(-100)

--------- ---------

100 100

16ACOS

给出反余弦的值

SQL select acos(-1) from dual;

ACOS(-1)

---------

31415927

17ASIN

给出反正弦的值

SQL select asin(05) from dual;

ASIN(05)

---------

52359878

18ATAN

返回一个数字的反正切值

SQL select atan(1) from dual;

ATAN(1)

---------

78539816

19CEIL

返回大于或等于给出数字的最小整数

SQL select ceil(31415927) from dual;

CEIL(31415927)

---------------

4

20COS

返回一个给定数字的余弦

SQL select cos(-31415927) from dual;

COS(-31415927)

---------------

本文的适合人群是刚刚入门的初学者，大家要掌握好了这些Oracle函数，这些肯定会非常有用的，为大家学好Oracle数据库打下一个坚实的基础。

DBMS_RANDOM包提供了内置的随机数生成器。

大多数语言的随机函数生成器都需要提供一个种子，在Oracle中，选取种子的过程叫做初始化，再执行随机函数之前，可以不用显式地执行初始化，Oracle会自动使用日期、用户ID、进程ID来进行初始化。当然，也可以显式地初始化或者叫自定义初始化。

val参数表示种子数字

字符串长度最多可以为2000

RANDOM函数的返回值取值范围是[ -2 31 ,2 31 ]，因此如果我们若想得到[-1,1]范围内的伪随机结果：

如果是[0,1]，取绝对值即可

若想[m,n] (m<n)这种形式，例如[3,9]

[m,n] (m<n)这种形式的一般表达式为

select abs(dbms_randomrandom/power(2,32))(n-m)+m from dual;

在RANDOM函数的示例中，我们已经讨论了 [0,1] 区间和 [m,n]（m<n） 区间上的随机数的生成方法，而VALUE函数即可以生成 [0,1] 上的随机数，又可以生成 [m,n]（m<n） 上的随机数，

当没有参数时，返回 [0,1] 上的随机数。

low参数表示最小值，high参数表示最大值，返回 [low,high] 区间内的随机数

opt参数控制字符串的大小写格式，可选值及含义如下：

‘u’或'U'：表示大写字母

‘l’或‘L’：表示小写字母

‘a’或‘A’：表示大小写混合

‘x’或‘X’：表示大写字母和数字混合

‘p’或‘P’：表示任意可显示字符

len表示字符串长度