聚合函数理解
聚合函数（Aggregate Functions）是 SQL 中用于对一组值执行计算并返回单个值的函数。它们常用于数据分析和统计，能够帮助我们快速获取数据的摘要信息。以下是常见的 Oracle 数据库聚合函数及其使用场景和用法。
常见的聚合函数
- COUNT()
- SUM()
- AVG()
- MIN()
- MAX()
1. COUNT()
-- 统计所有员工的数量
SELECT COUNT(*) AS total_employees FROM employees;
-- 统计有电子邮件地址的员工数量
SELECT COUNT(email) AS employees_with_email FROM employees;2. SUM()
-- 计算所有订单的总金额
SELECT SUM(amount) AS total_amount FROM orders;
-- 按部门计算每个部门的总薪水
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;3. AVG()
-- 计算所有员工的平均薪水
SELECT AVG(salary) AS average_salary FROM employees;
-- 按部门计算每个部门的平均薪水
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;4. MIN()
-- 找出所有员工中最低的薪水
SELECT MIN(salary) AS min_salary FROM employees;
-- 按部门找出每个部门中最早的入职日期
SELECT department_id, MIN(hire_date) AS earliest_hire_date
FROM employees
GROUP BY department_id;5. MAX()
-- 找出所有员工中最高的薪水
SELECT MAX(salary) AS max_salary FROM employees;
-- 按部门找出每个部门中最晚的入职日期
SELECT department_id, MAX(hire_date) AS latest_hire_date
FROM employees
GROUP BY department_id;复杂示例
假设我们有一个 orders 表，包含以下列：
order_id(订单ID)customer_id(客户ID)order_date(订单日期)amount(订单金额)
开窗函数不会改变本身应该查询出来的记录条数，只会改变使用开窗函数的那一个字段的值，就相当于是一个变量
我们使用聚合函数 更多的其实是想要删除重复值 重复的记录我们想要更直观的表现
SQL> select * from orders;
ORDER_ID CUSTOMER_ID AMOUNT ORDER_DAT
-------------------- --------------- ---------- ---------
ord01 cust01 15 01-JAN-25
ord01 cust01 20 01-JAN-25
ord01 cust02 90 01-JAN-25
ord01 cust03 1900 01-JAN-25
ord02 cust01 76 01-JAN-25
ord02 cust04 12 01-JAN-25
ord03 cust04 45 01-JAN-25
ord03 cust03 45 01-JAN-25
ord03 cust03 45 01-JAN-25
ord04 cust04 1100 01-JAN-25
ord04 cust04 900 01-JAN-25
ord04 cust01 1000 01-JAN-25
12 rows selected.
-----这样的查询sum的用法基本属于没有意义，逻辑是先以order_id分组，再以CUSTOMER_ID分组
--再以AMOUNT分组，如果三个字段的值都一直就会合并成只有一条记录，那么sum就只有45 而不是135
SQL> SELECT order_id,customer_id,amount,SUM(amount) from orders group by order_id,customer_id,amount;
ORDER_ID CUSTOMER_ID AMOUNT SUM(AMOUNT)
-------------------- --------------- ---------- -----------
ord01 cust01 20 20
ord01 cust01 15 15
ord01 cust02 90 90
ord01 cust03 1900 1900
ord04 cust04 900 900
ord04 cust01 1000 1000
ord02 cust01 76 76
ord03 cust04 45 45
ord04 cust04 1100 1100
ord02 cust04 12 12
ord03 cust03 45 90
11 rows selected.
-------如果想知道同一个customer_id下的amount总和 还是得用开窗函数
SQL> SELECT order_id,customer_id,amount,
2 SUM(amount) OVER (PARTITION BY customer_id )AS cumulative_amount
3 from orders;
ORDER_ID CUSTOMER_ID AMOUNT CUMULATIVE_AMOUNT
-------------------- --------------- ---------- -----------------
ord01 cust01 20 1111
ord02 cust01 76 1111
ord04 cust01 1000 1111
ord01 cust01 15 1111
ord01 cust02 90 90
ord03 cust03 45 1990
ord01 cust03 1900 1990
ord03 cust03 45 1990
ord04 cust04 900 2057
ord04 cust04 1100 2057
ord03 cust04 45 2057
ord02 cust04 12 2057
12 rows selected.
-----以客户为分组，查看客户下过订单的次数，每个客户的订单总金额，客户订单的平均金额，最大金额 最小金额
SQL> SELECT customer_id,COUNT(*) AS order_count,SUM(amount) AS total_amount,AVG(amount) AS avg_amount,
2 MIN(amount) AS min_amount,MAX(amount) AS max_amount FROM orders GROUP BY customer_id;
CUSTOMER_ID ORDER_COUNT TOTAL_AMOUNT AVG_AMOUNT MIN_AMOUNT MAX_AMOUNT
--------------- ----------- ------------ ---------- ---------- ----------
cust01 4 1111 277.75 15 1000
cust02 1 90 90 90 90
cust04 4 2057 514.25 12 1100
cust03 3 1990 663.333333 45 1900