【Oracle数据库系列笔记】聚合函数与其他函数

聚合函数与分组查询

使用SELECT语句不仅可以查询数据，而且可以对数据进行统计和分组。例如：

• 统计记录个数
• 汇总字段记录
• 计算平均值

为了能够实现分组操作，必须在SQL语句中使用聚合函数（也称为多行函数、分组函数）。聚合函数用来接收一组数据来进行计算，根据需要返回一组或单个结果。这组数据可以是表或者是表分成的组。聚合函数通常需要和group by子句配合使用。

ORACLE常用的聚合函数如下：

• AVG()：返回传入列值的平均值
• COUNT()：返回查询的行的数目
• SUM()：返回传输的列的总和
• MIN()：返回查询中的列的最小值
• MAX()：返回查询中的列的最大值

聚合函数的简单示例如下：

SELECT * FROM emp;
-- 	AVG()，返回传入列值的平均值
SELECT AVG(sal) FROM emp;
SELECT AVG(comm) FROM emp;
SELECT SUM(comm)/14 FROM emp;

-- 	COUNT()，返回查询的行的数目
SELECT COUNT(*) FROM emp;
SELECT COUNT(comm) FROM emp;
SELECT COUNT(DISTINCT deptno) FROM emp;

-- 	SUM()，返回传输的列的总和
SELECT SUM(sal) FROM emp;

-- 	MIN()，返回查询中的列的最小值
SELECT MIN(sal) FROM emp;

-- 	MAX()，返回查询中的列的最大值
SELECT MAX(sal) FROM emp;

聚合函数与group by子句配合使用的示例如下：

-- 使用GROUP BY子句分组查询
-- SELECT column,group_function(column)
-- FROM TABLE
-- [WHERE condition]
-- [GROUP BY grou_by_expression]
-- [ORDER BY column];

-- 计算出每个部门的公司所有员工的工资总和（按升序排列）
SELECT deptno,SUM(sal)
FROM emp
GROUP BY deptno
ORDER BY SUM(sal) ASC;

-- 在select列表中的任何列或表达式（非统计函数计算列）必须出现在GROUP BY 子句中
SELECT deptno,SUM(sal) salary
FROM emp
GROUP BY deptno;

-- 在GROUP BY子句中的列或表达式不必一定出现在select列表中
SELECT SUM(sal) sum_sal,AVG(sal)
FROM emp
GROUP BY deptno
ORDER BY deptno;

-- 按照部门和工作分组，计算出工资和
SELECT deptno,job,SUM(sal)
FROM emp
WHERE deptno IN (20,30)
GROUP BY deptno,job
HAVING SUM(sal)>2000
ORDER BY deptno;

其他函数

ORACLE中其他常用的函数主要有：

• NVL()：转换NULL值为一个实际的值
• NVL2(a,b,c)：如果表达式a不为null，则nvl2返回表达式b的值；如果为null，则nvl2返回表达式c的值。
  表达式a可以是任何数据类型
• NULLIF(a,b)：比较两个表达式，如果相等则返回NULL，如果不相等则返回第一个表达式
• COALESCE()：返回列表中第一个非NULL的值
• DECODE(value,if1,then1,if2,then2,if3,then3,else)：根据特定的条件，实现IF-THEN-ELSE条件判断的返回值

上述这些函数的示例如下：

-- NVL()，转换NULL值为一个实际的值
-- 计算emp表中所有员工的年薪
SELECT empno,ename,(sal+comm)*12 "年薪"
FROM emp;
SELECT empno,ename,(sal+NVL(comm,0))*12 "年薪"
FROM emp;

-- NVL2(a,b,c)，如果表达式a不为null，则nvl2返回表达式b的值；如果为null，则nvl2返回表达式c的值
-- 计算emp表中所有员工的年薪，如果没有奖金的话，那么年薪为13个月工资；否则为12个月的工资+奖金
SELECT empno,ename,NVL2(comm,(sal+comm)*12,sal*13) "年薪"
FROM emp;

-- NULLIF(a,b)，比较两个表达式，如果相等则返回NULL，如果不相等则返回第一个表达式
SELECT NULLIF('apple','apple'),NULLIF('apple','orange')
FROM dual;

-- COALESCE()，返回列表中第一个非NULL的值
SELECT ename,COALESCE(comm,mgr,sal,empno,ename) COALESCE FROM emp;

-- DECODE(value,if1,then1,if2,then2,if3,then3,,,,else)，实现IF-THEN-ELSE条件判断的返回值
-- 替换部门编号为实际的部门名称
SELECT ename,DECODE(deptno,10,'财务部',20,'研发部',30,'销售部','运营部') "部门"
FROM emp;