Oracle 数据库提供了丰富的内置函数,涵盖数值处理、字符串操作、日期和时间处理、逻辑判断、集合处理、数据分析、数据类型转换等多个方面。上一个章节学习了数学类的函数,本章节想学习下分析类函数。下面就随着我一起来学习下这个内置函数吧,有解释不到之处,还望批评指正。
聚合函数对一组值进行计算,并返回单个值。它们通常与GROUP BY子句一起使用,用于汇总数据。在Oracle中,常见的聚合函数有:个数、和、平均数、最大值、最小值等。聚合函数通常是我们分析数据或者统计数据时较为常用。
本篇示例是基于Oracle DB 19c EE (19.17.0.0.0)
版本操作,所操作的环境依旧是oracle提供的在线测试环境。如果有不同之处,请指出。
上次有同学咨询我说,这个在线操作工具这里补充下, 注册后登录即可食用,方便快捷,用作测试是一个不错的选择。
为了演示,下面创建一张员工表(employees),然后插入一些数据来进行测试。
--- 创建表结构
CREATE TABLE employees (
employee_id NUMBER(6) NOT NULL,
first_name VARCHAR2(20),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25) NOT NULL,
phone_number VARCHAR2(15),
hire_date DATE NOT NULL,
job_id VARCHAR2(10) NOT NULL,
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
manager_id NUMBER(6),
department_id NUMBER(4),
CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);
--- 给字段增加注释信息
COMMENT ON TABLE employees IS '员工信息表';
COMMENT ON COLUMN employees.employee_id IS '员工ID';
COMMENT ON COLUMN employees.first_name IS '员工名';
COMMENT ON COLUMN employees.last_name IS '员工姓';
COMMENT ON COLUMN employees.email IS '员工邮箱';
COMMENT ON COLUMN employees.phone_number IS '员工电话';
COMMENT ON COLUMN employees.hire_date IS '雇佣日期';
COMMENT ON COLUMN employees.job_id IS '工作ID';
COMMENT ON COLUMN employees.salary IS '员工薪资';
COMMENT ON COLUMN employees.commission_pct IS '佣金';
COMMENT ON COLUMN employees.manager_id IS '上级经理ID';
COMMENT ON COLUMN employees.department_id IS '部门ID';
--- 插入几条测试数据
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
VALUES (101, 'John', 'Doe1', 'john.doe1@example.com', '123-4561', TO_DATE('2000-01-01', 'YYYY-MM-DD'), 'IT_PROG', 60000, NULL, 200789, 50);
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
VALUES (102, 'John', 'Doe2', 'john.doe2@example.com', '123-4562', TO_DATE('2000-02-01', 'YYYY-MM-DD'), 'IT_PROG', 20000, NULL, 200789, 10);
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
VALUES (103, 'John', 'Doe3', 'john.doe3example.com', '123-4563', TO_DATE('2000-03-01', 'YYYY-MM-DD'), 'IT_PROG', 30000, NULL, 200789, 10);
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
VALUES (104, 'John', 'Doe4', 'john.doe4@example.com', '123-4564', TO_DATE('2000-04-01', 'YYYY-MM-DD'), 'IT_PROG', 90000, NULL, 200799, 20);
万事俱备,下面就开始学习之旅吧。
这个函数必须常用,可以说每一个项目都不可以缺少的一个函数,却少了这个函数都感觉写的代码没灵魂了。计数函数是计算命中的行数,常常被用作聚合或分析函数。通常情况下,此函数还会结合DISTINCT使用,在面试的笔试题的时候,我记得经常遇到这种类型的题目。
【定义】
COUNT(*):计算行数,需要扫描表,性能一般。
COUNT(1):计算行数,不需要扫描表,性能较快。
COUNT(column):计算非NULL值的个数,扫描非NULL的列,性能较快。
COUNT(DISTINCT column):计算某一列中不同值的数量,它会跳过重复的值,只计算不同的值,由于DISTINCT会使用排序,所以性能较慢。
【使用场景】
COUNT函数除了会在列上做统计使用之外,还可以在WHERE子句、HAVING子句、ORDER BY子句中使用,这有点类似TO_CHAR和TO_DATE,不同的场景使用情况下,性能不同。后续再做一篇专门性的博文来讲述这些函数。
【示例】
例如
1、老板想知道,员工数量;
2、老板想知道给多少员工发薪资的范围在60000元以上,查询薪资大于>= 60000的员工数;
3、老板还想知道每个经理下面有多少员工数
等等。
--- 查询员工数量
SELECT COUNT(1) FROM employees t1;
--- 查询薪资大于60000的(salary >= 60000)员工数量
SELECT COUNT(1) FROM employees t1 WHERE t1.salary >= 60000;
--- 查询 每个经理下员工的数量
SELECT COUNT(1), manager_id FROM employees t1 GROUP BY manager_id;
比较常用的一个内置函数,例如你是一个老板,你的员工中每个经理的累计工资总额,当然你也可以将工资都导入到Excle中使用Excel的SUM函数来计算,哈哈哈~。
【定义】
SUM(column):计算数值列的总和,在计算过程中,SUM函数会忽略这些NULL值进行计算。
【使用场景】
SUM函数还可以与其他SQL函数(如GROUP BY、HAVING等)结合使用,以执行更复杂的查询和计算。此外,SUM函数还常用于分析类的统计,统计结果集的每一行中计算累积总和。
【示例】
看了每个员工情况,老板想知道这个月一共发出去多少薪资,那么这个函数就用到了。其次,老板还想知道每个部门有多少薪资发放。
SELECT SUM(salary) sumSalary FROM employees t1;
SELECT SUM(salary) sumSalary, department_id FROM employees t1 GROUP BY department_id;
同上面几个函数一样,这个函数除了会计算工资之外,还会在面试的笔试题中计算工资,当然了,学习这些函数也是让我们在工作中以备不时之需。使用时可以参考SUM 函数。
【定义】
AVG(column):计算数值列的平均值。
【示例】
分析全部员工的平均薪资、分析某一个部门员工的平均薪资
-- 分析全部员工的平均薪资(我们都是被平均的那个)
SELECT AVG(salary) FROM employees;
-- 分析某一个部门员工的平均薪资
SELECT AVG(salary) FROM employees WHERE department_id = 10;
常用获取一组数据中最大值和最小值的函数。
【定义】
MAX(column):返回数值列的最大值。
MIN(column):返回数值列的最小值。
分组统计也是在统计学中常用的函数,这些函数我也不是很常用,所以不是很了解。
【定义】
GROUPING SETS, ROLLUP, CUBE: 多维汇总。
有些场景,例如我们分组完后,还想知道总薪资是多少,那么这个函数可以帮助你(为了方便演示,这里也查询出来了所有数据)。ROLLUP函数结果集中最后一列返回NULL,表示对所有分组列进行汇总。
举例说明:按照领导分组,看下哪一个管理者手下薪资总和情况,顺便也把纳入计算的薪资统计下。下面就是一个很好的例子。
SELECT SUM(salary), manager_id
FROM employees
GROUP BY ROLLUP ( manager_id );
现在有个需求,想要统计每个部门编号以及部门中员工的数量,下面使用 GROUP BY
和 GROUP BY CUBE
分别统计,可以看出来区别吧。
SELECT count(department_id), department_id FROM employees GROUP BY department_id;
SELECT count(department_id), department_id FROM employees GROUP BY CUBE(department_id);
重复性的数值列不再统计。
【定义】
COUNT(DISTINCT column):计算列中唯一值的数量。
【使用场景】
DISTINCT可以与ORDER BY子句一起使用,以对结果进行排序。例如多次考试,我们只会获取考试通过的一次。
【示例】
查询每个部门里面最高工资
SELECT COUNT(DISTINCT department_id), MAX(SALARY) FROM employees GROUP BY department_id;
我也想学习一下大佬,每日学习一点知识,丰富自己的脑子,至少知道自己写了点啥。后续有发现缺少或者缺失的再做补充。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。