Oracle实践｜内置函数之聚合函数

原创

Aion

修改于 2024-06-14 16:43:19

1300

修改于 2024-06-14 16:43:19

文章被收录于专栏：开发运维工程师

序言

背景说明

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);

万事俱备，下面就开始学习之旅吧。

2 聚合统计函数

计数--COUNT

这个函数必须常用，可以说每一个项目都不可以缺少的一个函数，却少了这个函数都感觉写的代码没灵魂了。计数函数是计算命中的行数，常常被用作聚合或分析函数。通常情况下，此函数还会结合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;

求和--SUM

比较常用的一个内置函数，例如你是一个老板，你的员工中每个经理的累计工资总额，当然你也可以将工资都导入到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;

平均--AVG

同上面几个函数一样，这个函数除了会计算工资之外，还会在面试的笔试题中计算工资，当然了，学习这些函数也是让我们在工作中以备不时之需。使用时可以参考SUM 函数。

【定义】

AVG(column)：计算数值列的平均值。

【示例】

分析全部员工的平均薪资、分析某一个部门员工的平均薪资

-- 分析全部员工的平均薪资（我们都是被平均的那个）
SELECT AVG(salary) FROM employees;
-- 分析某一个部门员工的平均薪资
SELECT AVG(salary) FROM employees WHERE department_id = 10;

最大/小值--MAX/MIN

常用获取一组数据中最大值和最小值的函数。

【定义】

MAX(column)：返回数值列的最大值。

MIN(column)：返回数值列的最小值。

分组统计

分组统计也是在统计学中常用的函数，这些函数我也不是很常用，所以不是很了解。

【定义】

GROUPING SETS, ROLLUP, CUBE: 多维汇总。

ROLLUP 多维汇总

有些场景，例如我们分组完后，还想知道总薪资是多少，那么这个函数可以帮助你（为了方便演示，这里也查询出来了所有数据）。ROLLUP函数结果集中最后一列返回NULL，表示对所有分组列进行汇总。

举例说明：按照领导分组，看下哪一个管理者手下薪资总和情况，顺便也把纳入计算的薪资统计下。下面就是一个很好的例子。

SELECT SUM(salary), manager_id 
FROM employees
GROUP BY ROLLUP ( manager_id );

CUBE 多维汇总

现在有个需求，想要统计每个部门编号以及部门中员工的数量，下面使用 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);

唯一性检查--DISTINCT

重复性的数值列不再统计。

【定义】

COUNT(DISTINCT column)：计算列中唯一值的数量。

【使用场景】

DISTINCT可以与ORDER BY子句一起使用，以对结果进行排序。例如多次考试，我们只会获取考试通过的一次。

【示例】

查询每个部门里面最高工资

SELECT COUNT(DISTINCT department_id), MAX(SALARY)  FROM employees GROUP BY department_id;

总结

我也想学习一下大佬，每日学习一点知识，丰富自己的脑子，至少知道自己写了点啥。后续有发现缺少或者缺失的再做补充。

我正在参与2024腾讯技术创作特训营最新征文，快来和我瓜分大奖！

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

腾讯技术创作特训营S7

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

登录后参与评论

0 条评论

热度