ORACLE 单行函数和分组统计总结Top-N

alter session set nls_date_language='AMERICAN';
DD-MM-RR



单行函数

这些函数仅对单个行进行运算，并且每行返回一个结果。有不同类型的单行函数，本课下面的函数类型：
::字符
::数字
::日期
::转换

单行函数：
:操纵数据项
:接受多个参数并返回一个值
:作用于每一个返回行
:每行返回一个结果
:可以修改数据类型
:可以嵌套
:接受多个参数，参数可以是一个列或者一个表达式

单行函数的特性包括：
:: 作用于查询中返回的每一行
:: 每行返回一个结果
:: 可能返回一个与参数不同类型的数据值
:: 可能需要一个或多个参数
:: 能够用在SELECT、WHERE和ORDER BY子句中；可以嵌套

多行函数
这些函数能够操纵成组的行，每个行组给出一个结果，这些函数也被称为组函数。


::字符函数：接受字符输入，可以返回字符或者数字值
::数字函数：接受数字输入，返回数字值
::日期函数：对DATE数据类型的值进行运算 (除了MONTHS_BETWEEN函数返回一个数字，所有日期函数都返回一个DATE数据类型的值。)
:: 转换函数：从一个数据类型到另一个数据类型转换一个值
:: 通用函数：
: NVL
: NVL2
: NULLIF
: COALSECE
: CASE
: DECODE

大小写处理函数:lower ,upper,initcap
字符函数
字符处理函数  :concat,substr,length,instr   lpad|rpad ,trim,replace


LOWER(column|expression)
转换字符值为小写
UPPER(column|expression)
转换字符值为小写
INITCAP(column|expression)
转换每个单词的首字母值为大写，所有其它值为小写
CONCAT(column1|expression1,column2|expression2)
连接第一个字符值到第二个字符值；等价于连接运算符 (||)
SUBSTR(column|expression,m[,n])
从字符返值中回指定的字符，开始位置在 m，n字符长度 (如果 m 是负数，计数从字符值末尾开始；如果 n 被忽略，返回到串结束的所有字符)。
LENGTH(column|expression)
返回表达式中的字符数
INSTR(column|expression,‘string’, [,m], [n] )
返回一个命名串的数字位置。随意地，你可以提供一个位置m作为查找的开始，在字符串中第n次发现的位置。m和n的默认值是1，意味着在起始开始查找，并且报告第一个发现的位置。
LPAD(column|expression, n,'string')
RPAD(column|expression, n,'string')
填充字符值左、右调节到n字符位置的总宽度
TRIM(leading|trailing|both, trim_character FROM trim_source)
使你能够从一个字符串修整头或尾字符(或两者)。如果trim_character或trim_source是字符文字，你必须放在单引号中。
REPLACE(text, search_string, replacement_string)
从字符串查找一个文本表达式，如果找到，用指定的置换串代替它


CONCAT('Hello', World') HelloWorld
SUBSTR('HelloWorld',1,5) Hello
LENGTH('HelloWorld') 10
INSTR('HelloWorld', 'W') 6(找到一个给定字符的数字位置)
LPAD(salary, 10,'*') *****24000(用给定的字符左填充字符串到给定的长度)
RPAD(salary, 10, '*') 24000*****(用给定的字符右填充字符串到给定的长度)
TRIM('H' FROM 'HelloWorld') elloWorld 从一个字符串中去除头或尾字符(或两者)

字符处理函数。
:: CONCAT：连接值在一起 (CONCAT 函数有两个输入参数)
:: SUBSTR：选取给定位置和长度的子字符串
:: LENGTH：以数字值显示一个字符串的长度
:: INSTR：找到一个给定字符的数字位置
:: LPAD：用给定的字符左填充字符串到给定的长度
:: RPAD：用给定的字符右填充字符串到给定的长度
:: TRIM：从一个字符串中去除头或尾的字符 (或头和尾) (如果 trim_character 或 trim_source 是一个文字字符，必须放在单引号中。)



数字函数

::ROUND:四舍五入指定小数的值 ROUND(45.926, 2)  45.93
ROUND(45.923,-1)  50 ROUND(45.923,0)   46
::TRUNC: 截断指定小数的值 TRUNC(45.926, 2)  45.92
TRUNC(45.923)   45 TRUNC(45.923,-2)  0
::MOD: 返回除法的余数 MOD(1600, 300)    100

ROUND(column|expression, n)
四舍五入列、表达式或值为n位小数位，或者，如果n被忽略，无小数位。(如果n是负值，小数点左边的数被四舍五入)
TRUNC(column|expression,n)
截断列、表达式或值到n位小数，或者，如果n被忽略，那么n默认为0
MOD(m,n)
返回m除以n的余数

ROUND 函数
ROUND函数四舍五入列、表达式或者n位小数的值。如果第二个参数是0或者缺少，值被四舍五入为整数。如果第二个参数是2，值被四舍五入为两位小数。如果第二个参数是–2，值被四舍五入到小数点左边两位。 ROUND函数也能够被用于日期函数

TRUNC 函数
TRUNC函数截断列、表达式或者n位小数值。
TRUNC函数对参数起的作用类似于ROUND函数。如果第二个参数是0或者缺少，值被截断为整数。如果第二个参数是2，值被截断为两位小数。如果第二个参数是–2，值被截断到小数点左边两位。 象ROUND函数，TRUNC函数也可以被用于日期函数。


MOD函数经常用于确定一个值是奇数还是偶数。

ALTER SESSION SET NLS_DATE_FORMAT = ‘date format model’;


日期的使用

Oracle 数据库用内部数字格式存储日期：世纪，年，月，日，小时，分钟和秒
::默认日期显示格式是DD-MON-RR.
–仅指定年的最后两位数字，允许你存储21世纪日期在20世纪中
–用同样的方式，允许你存储20世纪的日期在21世纪中

，雇员Gietz的HIRE_DATE是以默认格式DD-MON-RR显示的，然而，存储在数据库的日期不是这种格式，所有的日期和时间的组成部分都会被存储。所以，尽管一个HIRE_DATE ，例如07-JUN-94被显示为天、月和年，也还有时间和世纪信息伴随它。完整的日期可能是1994年7月7日 5:10:43 p.m （June 7th, 1994 5:10:43 p.m）。
该日期在内部存储如下：
CENTURY YEAR MONTH DAY HOUR MINUTE SECOND
  19 94    06  07 5    10     43


DATE数据类型总是以4位内部数字存储年信息：
两位数字代表世纪，两位数字代表年。
例如，Oracle数据库存储年为1996或2001，而不是仅仅存96或01。


SYSDATE函数返回：
::Date
::Time

SELECT SYSDATE FROM DUAL; 08-MAR-01
SYSDATE是一个日期函数，它返回当前数据库服务器的日期和时间

用日期计算
::从日期加或者减一个数，结果是一个日期值
::两个日期相减，得到两个日期之间的天数
::用小时数除以24，可以加小时到日期上


运算 结果 说明
date + number 日期 加一个天数到一个日期上
date - number 日期 从一个日期上减一个天数
date - date 天数 用一个日期减另一个日期
date + number/24 日期 加一个小时数到一个日期上



显示所有在部门90中的雇员的名字和从业的周数。雇员的总工作时间以周计算，用当前日期 (SYSDATE) 减去雇员的受顾日期，再除以7。
SELECT last_name,(SYSDATE-hire_date)/7 AS WEEKS
FROM employees
WHERE department_ID=90;

如果被减数大于当前日期，差是负数。


函数 说明 返回值类型
MONTHS_BETWEEN 两个日期之间的月数   数字值(月份的差值)
ADD_MONTHS 加日历月到日期   DATE
NEXT_DAY 下个星期几是几号   DATE
LAST_DAY 指定月的最后一天   DATE
ROUND 四舍五入日期   DATE
TRUNC 截断日期   DATE



日期函数

日期函数对Oracle日期进行操作，除了MONTHS_BETWEEN返回一个数字值，所有日期函数都返回一个DATE数据类型。

:: MONTHS_BETWEEN(date1, date2)：计算date1和date2之间的月数，其结果可以是正的,也可以是负的。如果date1大于date2，结果是正的，反之，结果是负的。结果的小数部分表示月的一部分。

:: ADD_MONTHS(date, n)：添加n个日历月到date。n的值必须是整数，但可以是负的。

:: NEXT_DAY(date, ‘char’)：计算在date之后的下一个周(‘char’)的指定天的日期。char的值可能是一个表示一天的数或者是一个字符串。

:: LAST_DAY(date)：计算包含date的月的最后一天的日期。

:: ROUND(date [,‘fmt’])：返回用格式化模式fmt四舍五入到指定单位的 date ，如果格式模式 fmt 被忽略，date被四舍五入到最近的天。

:: TRUNC(date [, ‘fmt’])：返回用格式化模式fmt截断到指定单位的带天的时间部分的date，如果格式模式fmt被忽略，date被截断到最近的天。

  该列表是一个可用日期函数的一个子集。格式化模式的内容含盖在本课后面。格式化模式的例子是月和天。 

月
JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
一月------------------------------------>十二月


使用日期函数
MONTHS_BETWEEN ('01-SEP-95','11-JAN-94') 19.6774194
ADD_MONTHS ('11-JAN-94',6) '11-JUL-94'
NEXT_DAY ('01-SEP-95','FRIDAY') 下个星期五是几号'08-SEP-95'
LAST_DAY('01-FEB-95') '28-FEB-95'

周
SUNDAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY
周日

使用日期函数
假定SYSDATE = '25-JUL-95':

ROUND(SYSDATE,'MONTH') 01-AUG-95 ROUND(SYSDATE ,'YEAR') 01-JAN-96

TRUNC(SYSDATE,'MONTH') 01-JUL-95 TRUNC(SYSDATE ,'YEAR') 01-JAN-95

TRUNC(TO_DATE('25-JUL-95') ,'YEAR') 01-JAN-95

更多例子：
select TRUNC(TO_DATE(‘25-JUL-95’),‘YEAR’) from dual
第 1 行出现错误:
ORA-01843: 无效的月份
出现错误的原因是语言和区域设置 NLS_LANG 不匹配，用下面的命令测试一下：
select to_char(SYSDATE) from dual
结果是：18-4月 -05
我们再试下面的语句：
select TRUNC(TO_DATE(‘25-7月-95’), ‘YEAR’) from dual

从   到
VARCHAR2 or CHAR NUMBER
VARCHAR2 or CHAR DATE
NUMBER VARCHAR2
DATE VARCHAR2

注：只有当字符串表示一个有效的数时，CHAR到NUMBER转换才能成功。

隐式数据转换不只是在前面提到的数据类型之间进行，还有其他一些隐式数据转换可以进行，例如，VARCHAR2可以被隐式转换为ROWID。


TO_CHAR(number|date,[ fmt],[nlsparams])
转换一个数字或日期值为一个VARCHAR2字符串，带格式化样式fmt。
数字转换：nlsparams 参数指定下面的字符，它由数字格式化元素返回：
:: 小数字符
:: 分组符
:: 本地货币符号
:: 国际货币符号
如果忽略nlsparams或其它参数，该函数在会话中使用默认参数值。


SQL 提供三种函数来从一种数据类型转换值到另一种：

TO_CHAR(number|date,[ fmt],[nlsparams])
指定返回的月和日名字及其缩写的语言。如果忽略该参数，该函数在会话中使用默认日期语言

TO_NUMBER(char,[fmt],[nlsparams])
用由可选格式化样式fmt指定的格式转换包含数字的字符串为一个数字。Nlsparams参数在该函数中的目的与TO_CHAR函数用于数字转换的目的相同

TO_DATE(char,[fmt],[nlsparams])
按照fmt指定的格式转换表示日期的字符串为日期值。如果忽略fmt，格式是 DD-MON-YY。Nlsparams参数的目的与TO_CHAR函数用于日期转换时的目的相同


对日期使用TO_CHAR函数
TO_CHAR(date,'format_model')
格式模板
::必须加单引号，并且区分大小写
::能够包含任一有效的日期格式元素
::有一个fm元素用来删除填补的空，或者前导零
::用一个逗号与日期值分开


SELECT employee_id, TO_CHAR(hire_date, 'MM/YY') Month_Hired
FROM employees
WHERE last_name = 'Higgins';
EMPLOYEE_ID MONTH
205 06/94

日期格式模板的元素
YYYY 数字全写年
YEAR 年的拼写
MM 月的两数字值
MONTH 月的全名
DY 周中天的三字母缩写
DAY 周中天的全名
MON 月的三字母缩写
DD 月的数字天


select TO_CHAR(SYSDATE,'HH24:MI:SS AM') from dual 15:45:32 PM
select TO_CHAR(TO_DATE('21-1月-95'),'YYYY-MM-DD HH24:MI:SS AM') from dual
select TO_CHAR(SYSDATE,'DD "of" MONTH') from dual 12 of OCTOBER
select TO_CHAR(TO_DATE('25-JUL-95'),'DD "of" MONTH') from dual
select TO_CHAR(TO_DATE('25-7月-95'),'DD "of" MONTH') from dual
select to_char(sysdate, 'ddspth') from dual
select TRUNC(TO_DATE(‘25-JUL-95’) ,‘ddspth’) from dual -- NLS_LANG 是英文设置
select TRUNC(TO_DATE(‘25-7月-95’) ,‘ddspth’) from dual -- NLS_LANG 是中文设置
select to_char(sysdate,'ddspth') from dual --fourtheenth

使用在下表中列出的格式，显示时间信息和文字，并且改变数字为拼写的数字。

元素 说明
AM或PM 正午指示
A.M.或P.M. 带句点的正午指示
HH或HH12或HH24 天的小时，或小时(1–12)，或小时(0–23)
MI 分钟 (0–59)
SS 秒 (0–59)
SSSSS 午夜之后的秒 (0–86399)


其它格式

元素 说明
/ . , 在结果中使用标点符号
“of the” 在结果中使用引文串

指定后缀来影响数字显示

元素 说明
TH 序数 (例如，DDTH显示为4TH)
SP 拼写出数字 (例如，DDSP显示为FOUR)
SPTH or THSP 拼写出序数 (例如，DDSPTH显示为FOURTH))


select last_name ,to_char(hire_date,'fmDd Month YYYY') AS HIREDATE
from employee;

LAST_NAME HIREDATE
King 17 Jun 1987

SELECT last_name,TO_CHAR(hire_date,‘fmDdspth "of" Month YYYY fmHH:MI:SS AM’)
HIREDATE FROM employees;

LAST_NAME HIREDATE
King Seventeenth of June 1987 12:00:00 AM

注意，月以指定的格式模式显示：换句话说，第一个字母是大写其余字母是小写。


对数字使用TO_CHAR函数
TO_CHAR(number,'format_model')

下面是一些你能够和TO_CHAR一起使用的格式化元素，用于显示字符形式的数字值：
9 表示一个数
0 强制显示为零
$ 放置一个浮动美元符号
L 使用浮动本地货币符号
. 打印一个小数点
, 打印一个千位指示



如果你正在转换一个数字到字符数据类型，你可以用下面的格式元素：
元素 说明 举例 结果
9 数字位置 (9的个数决定显示宽度) 999999 1234
0 显示前导0 099999 001234
$ 浮动美圆符号 $999999 $1234
L 浮动本地货币符号 L999999 FF1234
. 小数点位置指定 999999.99 1234.00
, 逗号位置指定 999,999 1,234
MI 右边减号 (负值) 999999MI 1234-
PR 将负数加上括号 999999PR <1234>
EEEE 科学计数法 (格式化必须指定四个E)99.999EEEE 1.234E+03
V 乘10，n次 (n = V 后面 9 的个数) 9999V99 123400
B 将0显示为空格 B9999.99 1234.00


转换字符串到数字,用to_number函数格式化:
to_number(char[,'format_model'])

转换字符串到日期,用to_date函数格式化:
to_date(char,['format_model'])

这些函数有一个fx修饰符，该修饰符指示对字符参数和一个TO_DATE函数模板的数据格式的精确匹配

Fx 修饰符指定 TO_DATE 函数对于字符参数和日期格式化样式的额外匹配：
:: 在字符参数中，标点符号和引号中的文本必须完全匹配格式化样式中相应的部分。
:: 字符参数不能有额外的空格。如果无fx，Oracle 服务器将忽略额外的空格。
:: 在字符参数中数字数据必须与格式化样式中的元素有相应的数字个数。如果无 fx，在字符参数中的可以忽略前导 0。

显示所有在May 24, 1999 参加工作的雇员的名字和受雇日期。因为使用了 fx 修饰符，需要精确的匹配，并且在单词 ‘May’ 之后的空格不认可。

SELECT last_name, hire_date
FROM employees
WHERE hire_date = TO_DATE('May 24, 1999', 'fxMonth DD, YYYY');


幻灯片 39 区分年和世纪 ,好好区分


为了找到 1990 年以前工作的雇员，要使用 RR 格式表示年，因为现在的年大于 1999，RR 格式将日期中年的部分解释为从 1950 到 1999。
另一方面，下面的命令导致无选择行返回，因为 YY 格式将日中年的部分解释为当前世纪 (2090)。
SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-yyyy')
FROM employees
WHERE TO_DATE(hire_date, 'DD-Mon-yy') < '01-Jan-1990';
no rows selected

嵌套:

F3(F2(F1(col,arg1),arg2),arg3)

例子
select last_name NVL(TO_CHAR(manager_id),'No Manager')
FROM employees
Where manager_id IS NULL;

nvl函数作用:用文字串代替空值.

select to_char(next_day(add_months(hire_date,6),'FRIDAY'),'fmDay,Month DDth,YYYY)
"Next 6 Month Review"
FROM employees
order by hire_date;



通用函数

这些函数可用于任意数据类型，并且适用于空值
:: NVL (expr1, expr2)
:: NVL2 (expr1, expr2, expr3)
:: NULLIF (expr1, expr2)
:: COALESCE (expr1, expr2, ..., exprn)


函数 说明
NVL 转换空值为一个实际值
NVL2 如果expr1非空，NVL2返回expr2；如果expr1为空，NVL2返回expr3。参数 expr1可以是任意数据类型。
NULLIF 比较两个表达式，如果相等返回空；如果不相等，返回第一个表达式
COALESCE 返回表达式列表中的第一个非空表达式


NVL函数
转换一个空值到一个实际的值
:: 可用的数据类型可以是日期、字符和数字
:: 数据类型必须匹配：
–NVL(commission_pct,0)
–NVL(hire_date,'01-JAN-97')
–NVL(job_id,'No Job Yet')

语法: NVL(expr1, expr2)

在语法中：
expr1 是包含空值的源值或者表达式
expr2 是用于转换空值的目的值
你可以使用 NVL 函数来转换任何数据类型，但返回值通常总是与 expr1 的数据类型相同。


NVL 用于转换各种数据类型
数据类型 转换例子
NUMBER NVL(number_column,9)
DATE NVL(date_column, '01-JAN-95')
CHAR or VARCHAR2 NVL(character_column, 'Unavailable')

正确的
SELECT last_name, salary, commission_pct,
(salary*12) + (salary*12*nvl(commission_pct,0)) AN_SAL

错误的
SELECT last_name, salary, commission_pct,
(salary*12) + (salary*12*commission_pct) AN_SAL


NVL2 函数
NVL2 函数检查第一个表达式，如果第一个表达式不为空，那么 NVL2 函数返回第二个表达式；如果第一个表达式为空，那么第三个表达式被返回。


语法
NVL(expr1, expr2, expr3)
在语法中：
expr1 是可能包含空的源值或表达式
expr2 expr1 非空时的返回值
expr3 expr1 为空时的返回值

参数 expr1 可以是任何数据类型，参数 expr2 和 expr3 可以是除 LONG 之外的任何数据类型。如果 expr2 和 expr3 的数据类型不同，Oracle 服务器在比较它们之前将转换 expr3 为 expr2 的数据类型，除非 expr3 是一个 null 常数，在这种情况下，不需要数据类型转换。

返回值的数据类型总是与 expr2 的数据类型相同，除非 expr2 是字符数据，在这种情况下，返回值的数据类型是 VARCHAR2。


select last_name,salary,commission_pct,
NVL2(commission_pct,'SAL+COMM','SAL') income
from employees
where department_id IN(50,80);

LAST_NAME SALARY COMMISSION_PCT INCOME
Zlokey 23244     .2 SAL+COMM
Abel 23423 SAL



NULLIF 函数
NULLIF 函数比较两个表达式，如果相等，函数返回空，如果不相等，函数返回第一个表达式。第一个表达式不能为 NULL。

语法
NULLIF (expr1, expr2)
在语法中：
expr1 是对于 expr2 的被比较原值
expr2 是对于 expr1 的被比较原值。(如果它不等于 expr1，expr1 被返回)。

注：NULLIF 函数在逻辑上等同于下面的 CASE 表达式。CASE 表达式将在后面讨论：
CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END

select first_name,length(first_name) "expr1",
last_name,length(last_name) "expr2",
nullif(length(first_name),length(last_name)) result
from employees;

FIRST_NAME expr1 LAST_NAME expr2 RESULT
Steven 6 King 4 6
neena 5 Kochhar 7 5
Bruce 5 Ernst 5
Trenna 6 Mourgos 7 6



使用COALESCE 函数
COALESCE 函数返回列表中的第一个非空表达式../././././

语法
COALESCE (expr1, expr2, ... exprn)

在语法中：
expr1 如果它非空，返回该表达式
expr2 如果第一个表达式为空并且该表达式非空，返回该表达式
exprn 如果前面的表达式都为空，返回该表达式

select last_name
coalesce(commission_pct,salary,10) comm
from employees
order by commission_pct

LAST_NAME COMM
Grant   15
Zlokey   .2
Taylor   .2
King 24000

在例子中显示，如果 COMMISSION_PCT 值是非空，显示它。如果 COMMISSION_PCT 值是空，则显示 SALARY 。如果 COMMISSION_PCT 和 SALARY 值都是空，那么显示10。



条件表达式
:: 在SQL 语句中提供IF-THEN-ELSE 逻辑的使用

:: 两种用法：
–CASE表达式
–DECODE函数

注：CASE 表达式是 Oracle9i 服务器新发布的。CASE 表达式与 ANSI SQL 兼容；DECODE 是特殊的 Oracle 语法。

CASE表达式
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2  
  WHEN comparison_exprn THEN return_exprn
  ELSE else_expr]
END

所有的表达式 ( expr、comparison_expr 和 return_expr) 必须是相同的数据类型，
可以是 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2


SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
FROM employees;

LAST_NAME JOB_ID SALARY REVISED_SALARY
Lorentz IT_PROD 4200 4620
Mourgos ST_MAN 5800 5800
Rajs ST_CLERK 3500 4025

如果 JOB_ID 是 IT_PROG，薪水增加 10%；如果 JOB_ID 是 ST_CLERK，薪水增加 15%；如果 JOB_ID 是 SA_REP，薪水增加 20%。对于所有其他的工作角色，不增加薪水。
可以用 DECODE 函数写相同功能的语句。


DECODE函数
DECODE(col|expression, search1, result1 [, search2, result2,...,][, default])

DECODE 函数在比较表达式 (expression) 和每个查找 (search) 值后解码表达式，如果表达式与查找相同，返回结果。
如果省略默认值，当没有查找值与表达式相匹配时返回一个空值。



使用DECODE函数

SELECT last_name, job_id, salary,
DECODE(job_id, 'IT_PROG', 1.10*salary,
'ST_CLERK',       1.15*salary,
'SA_REP',         1.20*salary,
  salary) //default
REVISED_SALARY
FROM employees;

LAST_NAME JOB_ID SALARY REVISED_SALARY
Lorentz IT_PROD 4200 4620
Mourgos ST_MAN 5800 5800
Rajs ST_CLERK 3500 4025

等同于
IF job_id = 'IT_PROG' THEN salary = salary*1.10
IF job_id = 'ST_CLERK' THEN salary = salary*1.15
IF job_id = 'SA_REP' THEN salary = salary*1.20
ELSE salary = salary


SELECT last_name, salary,
DECODE (TRUNC(salary/2000, 0),
0, 0.00,
1, 0.09,
2, 0.20,
3, 0.30,
4, 0.40,
5, 0.42,
6, 0.44,
   0.45) TAX_RATE
FROM employees
WHERE department_id = 80;

第五单 用组函数合计数据

组函数类型:
AVG 平均值
::COUNT 计数
::MAX 最大值
::MIN 最小值
::STDDEV 标准差
::SUM 合计
::VARIANCE 方差


函数 说明
AVG([DISTINCT|ALL]n) n 的平均值，忽略空值

COUNT({*|[DISTINCT|ALL]expr}) 行数，expr 求除了空计算(用 * 计数所有行，
包括重复和带空值的行)

MAX([DISTINCT|ALL]expr) expr的最大值，忽略空值
MIN([DISTINCT|ALL]expr) expr的最小值，忽略空值
STDDEV([DISTINCT|ALL]x) n 的标准差，忽略空值
SUM([DISTINCT|ALL]n) 合计 n 的值，忽略空值
VARIANCE([DISTINCT|ALL]x) n 的方差，忽略空值


组函数可以用在select 子句中




使用组函数的原则
::DISTINCT 使得函数只考虑不重复的值；ALL 使得函数考虑每个值，包括重复值。默认值是 ALL ，因此不需要指定。
::用于函数的参数的数据类型可以是 CHAR、VARCHAR2、NUMBER 或 DATE。
::所有组函数忽略空值。为了用一个值代替空值，用 NVL、NVL2 或 COALESCE 函数。/./././././
::当使用 GROUP BY 子句时，Oracle 服务器隐式以升序排序结果集。为了覆盖该默认顺序，DESC 可以被用于 ORDER BY 子句。

强调用 DISTINCT 和组函数忽略空值。ALL 是默认。


SQL> create table test9
  2  (id varchar2(10),
  3  name varchar2(10))
  4  tablespace users;

SQL> insert into test9 values('kjat','wps')
  2  ;

已创建 1 行。

SQL> insert into test9 values(null ,null);

已创建 1 行。

SQL> insert into test9 values('kk',null);

已创建 1 行。

SQL> select distinct id
  2  from test9;

ID
----------
kjat
kk


SQL> select count(*) from test9;

  COUNT(*)
----------
         3

SQL> select count(distinct *) from test9;
select count(distinct *) from test9
                      *
ERROR 位于第 1 行:
ORA-00936: 缺少表达式


SQL> select count(distinct id) from test9;

COUNT(DISTINCTID)
-----------------
                2


SQL> select count(distinct name) from test9;

COUNT(DISTINCTNAME)
-------------------
                  1

SQL> select count(1) from test9;

  COUNT(1)
----------
         3

SQL> select count(2) from test9;

  COUNT(2)
----------
         3

SQL> select count(id) from test9;

COUNT(ID)
----------
         2

SQL> select count(name) from test9;

COUNT(NAME)
-----------
          1


SQL> insert into test9 values('kjat','ks'); //表中有两个kjat列

已创建 1 行。

SQL> select * from test9;

ID         NAME
---------- ----------
kjat       wps
//null
kk
kjat       ks


SQL> select count(distinct id) from test9;

COUNT(DISTINCTID)
-----------------
                2

SQL> select count(id) from test9;

COUNT(ID)
----------
         3

注意:AVG、SUM、VARIANCE 和 STDDEV 函数只能被用于数字数据类型。



COUNT 函数有三中格式：
::COUNT(*)
::COUNT(expr)
::COUNT(DISTINCT expr)

COUNT(*) 返回表中满足 SELECT 语句标准的行数，包括重复行，包括有空值列的行。如果 WHERE 子句包括在 SELECT 语句中，COUNT(*) 返回满足 WHERE 子句条件的行数。

COUNT(expr) COUNT(expr) 返回对于表达式expr 非空值的行数

COUNT(DISTINCT expr)    返回对于表达式expr 非空并且值不相同的行数



所有组函数忽略列中的空值

select avg(commission_pct)
from employees;

AVG(COMMISSION_PCT)
-------------------
         .222857143
平均值只基于表中的那些 COMMISSION_PCT 列的值有效的行的计算。平均值计算是用付给所有雇员的总佣金除以接受佣金的雇员数 (4)。

select avg(nvl(commission_pct,0))
from employees;

AVG(NVL(COMMISSION_PCT,0))
--------------------------
                .072897196

平均值被基于所有表中的行来计算，不管 COMMISSION_PCT 列是否为空。平均值的计算是用付给所有雇员的总佣金除以公司的雇员总数 (20)。




创建数据组:group by 子句语法


原则 /././././很重要
::如果在 SELECT 子句中包含了组函数，就不能选择单独的结果，除非单独的列出现在 GROUP BY 子句  中。如果你未能在 GROUP BY 子句中包含一个字段列表，你会收到一个错误信息。
::使用 WHERE 子句，你可以在划分行成组以前过滤行。
::在 GROUP BY 子句中必须包含列。
::在 GROUP BY 子句中你不能用列别名。
::默认情况下，行以包含在 GROUP BY 列表中的字段的升序排序。你可以用 ORDER BY 子句覆盖这个默认值。分组结果被以分组列隐式排序，可以用 ORDER BY 指定不同的排序顺序，但只能用组函数或分组列。 ././././././././.

在SELECT 列表中的不在组函数中的所有列必须在GROUP BY 子句中

即:当使用 GROUP BY 子句时，确保在 SELECT 列表中的所有没有包括在组函数中的列必须在 GROUP BY 子句中。

select department_id,avg(salary)     //salary不用在group by 中,但department_id要在
from employees
group by department_id;



GROUP BY 列不必在SELECT 列表中
select avg(salary)
from employees
group by department_id;


可以在 ORDER BY 子句中使用组函数。
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY AVG(salary);

按department_id升序
SQL> select department_id,avg(salary)
  2  from employees
  3  group by department_id;

DEPARTMENT_ID AVG(SALARY)
------------- -----------
           10        4400
           20        9500
           30  4316.66667
           40        6500
           50  3475.55556
           60        5760
           70       10000
           80  8955.88235
           90  19333.3333
          100        8600
          110       10150

DEPARTMENT_ID AVG(SALARY)
------------- -----------
                     7000

已选择12行。

按avg(salary)升序
SQL>  select department_id,avg(salary)
  2   from employees
  3   group by department_id
  4  order by avg(salary);

DEPARTMENT_ID AVG(SALARY)
------------- -----------
           50  3475.55556
           30  4316.66667
           10        4400
           60        5760
           40        6500
                     7000
          100        8600
           80  8955.88235
           20        9500
           70       10000
          110       10150

DEPARTMENT_ID AVG(SALARY)
------------- -----------
           90  19333.3333

已选择12行。



SELECT COUNT(*) total,
SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1995,1,0))"1 995",
SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1996,1,0))"1 996",
SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1997,1,0))"1 997",
SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1998,1,0))"1 998"
FROM employees;

SELECT department_id dept_id,job_id,SUM(salary)
FROM employees
GROUP BY department_id,job_id;

GROUP BY 子句指定你怎样分组行：
:: 首先，用部门号分组行。
:: 第二，在部门号的分组中再用 job ID 分组行。
如此 SUM 函数被用于每个部门号分组中的所有 job ID 的 salary 列。



::在SELECT 列表中的任何列或表达式（非计算列）必须在GROUP BY 子句中
::在GROUP BY 子句中的列或表达式不必在SELECT 列表中
::在 SELECT 列表中的任何没有使用聚集函数的列或表达式必须放在 GROUP BY 子句中


SELECT department_id,COUNT(last_name)
FROM employees;

SELECT department_id, COUNT(last_name)
*
ERROR at line 1: //列未包含在group by子句中
ORA-00937: not a single-group group function

正确的是:
SELECT department_id, count(last_name)
FROM employees
GROUP BY department_id;



非法使用group by 函数的查询

::不能使用WHERE 子句来约束分组
::可以使用HAVING 子句来约束分组
::在WHERE 子句中不能使用组函数作为条件，只能用非计算列

././././././ 用 WHERE 子句约束选择的行，用 HAVING 子句约束组。


select department_id,avg(salary)
from employees
where avg(salary)>1000
group by department_id; //不能使用where子句约束分组.


正确的写法:
select department_id,avg(salary)
from employees
group by department_id
having avg(salary)>1000;


having子句

当你使用 HAVING 子句时，Oracle 服务器执行下面的步骤：
1. 行被分组。
2. 组函数被用于分组。
3. 匹配 HAVING 子句的标准的组被显示。



Oracle 服务器以下面的顺序求子句的值：
:: 如果语句包含一个 WHERE 子句，服务器建立候选行。
:; 服务器确定在 GROUP BY 子句中指定的分组。
:; HAVING 子句进一步约束结果那些在 HAVING 子句中不满足分组标准的组。


下面的例子显示那些最高薪水大于 $10,000 的部门的部门号和平均薪水。
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING max(salary)>10000;


SELECT job_id, SUM(salary) PAYROLL
FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING SUM(salary) > 13000
ORDER BY SUM(salary);
显示那些合计薪水册超过 $13,000 的每个工作岗位的 job ID 和合计薪水。该例子排除了销售代表，并且用合计月薪排序列表

PU_CLERK|13900
AC_MGR|24000
AD_PRES|24000
IT_PROG|28800
AD_VP|34000
ST_MAN|36400
FI_ACCOUNT|39600
ST_CLERK|55700
SA_MAN|61000
SH_CLERK|64300



组函数可以被嵌套两层深度
SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id;



执行Top-N 分析

    Top-N 分析查询的高级结构是：

SELECT [column_list], ROWNUM
FROM   (SELECT [column_list]
FROM table
ORDER BY Top-N_column [asc|desc])
WHERE ROWNUM <= N;


执行“Top-N”分析
   Top-N查询使用一个带有下面描述的元素的一致的嵌套查询结构：
:: 子查询或者内建视图产生数据的排序列表，该子查询或者内建视图包含ORDER BY子句来确保排序   以想要的顺序排列。为了取回最大值，需要用DESC参数。

:: 在最后的结果集中用外查询限制行数。外查询包括下面的组成部分：
- ROWNUM伪列，它为从子查询返回的每一行指定一个从1开始的连续的值
- 一个WHERE子句，它指定被返回的n行，外WHERE子句必须用一个<或者<=操作。



Top-N 分析的例子
  为了从EMPLOYEES表中显示挣钱最多的3 个人的名字及其薪水:

SELECT ROWNUM as RANK, last_name, salary
FROM (SELECT last_name,salary
      FROM employees
      ORDER BY salary DESC)
WHERE ROWNUM <= 3;


SQL> SELECT ROWNUM as RANK, last_name, salary
  2  FROM (SELECT last_name,salary
  3        FROM employees
  4        ORDER BY salary DESC)
  5  WHERE ROWNUM <= 3;

      RANK LAST_NAME                     SALARY
---------- ------------------------- ----------
         1 King                           24000
         2 Kochhar                        17000
         3 De Haan                        17000


SQL> select rownum as rank,last_name,salary
  2  from employees
  3  where rownum <=3
  4  order by salary;

      RANK LAST_NAME                     SALARY
---------- ------------------------- ----------
         2 Kochhar                        17000
         3 De Haan                        17000
         1 King                           24000

SQL> select rownum as rank,last_name,salary
  2  from employees
  3  where rownum <=3
  4  order by salary desc;

      RANK LAST_NAME                     SALARY
---------- ------------------------- ----------
         1 King                           24000
         2 Kochhar                        17000
         3 De Haan                        17000


        下面是用内建视图进行Top-N分析的另一个例子，该例子用内建视图E显示公司中4个资格最老的雇员。 //使用了表别名.

SQL> SELECT ROWNUM as SENIOR,E.last_name, E.hire_date
  2  FROM  (SELECT last_name,hire_date
  3         FROM employees
  4         ORDER BY hire_date) E
  5  WHERE rownum <= 4;

    SENIOR LAST_NAME                 HIRE_DATE
---------- ------------------------- ----------
         1 King                      17-6月 -87
         2 Whalen                    17-9月 -87
         3 Kochhar                   21-9月 -89
         4 Hunold                    03-1月 -90

:: 内建视图是一个带有别名的子查询
:: 用子查询和外查询能够进行Top-N 分析