Oracle 19C 内置聚合函数开发与使用文档

1. 引言

聚合函数（Aggregate Functions）是 Oracle 数据库中用于对一组数据进行汇总计算并返回单一结果的函数。它们广泛应用于数据分析、报表生成和统计计算等场景。Oracle 19C 提供了丰富的内置聚合函数，从基本的求和、平均值计算到复杂的统计分析和字符串聚合功能。

本文档详细介绍 Oracle 19C 中常用的聚合函数，包括其语法、功能、使用示例及性能优化建议，旨在帮助开发人员高效地进行数据聚合相关的开发工作。

2. 基本聚合函数

2.1 SUM

• 功能：计算数值列的总和
• 语法：SUM([DISTINCT | ALL] expr)
• 参数：
  • DISTINCT：可选，仅对不同的值求和
  • ALL：可选，对所有值求和（默认）
• 返回类型：NUMBER
• 示例：

  sql

  -- 计算所有员工的工资总和
  SELECT SUM(salary) AS 总工资 FROM employees;
  
  -- 计算不同部门的工资总和
  SELECT department_id, SUM(salary) AS 部门总工资
  FROM employees
  GROUP BY department_id
  ORDER BY department_id;
  
  -- 计算不同工资值的总和（忽略重复值）
  SELECT SUM(DISTINCT salary) AS 不同工资总和 FROM employees;
  

• 注意事项：忽略 NULL 值；如果所有值都是 NULL，返回 NULL

2.2 AVG

• 功能：计算数值列的平均值
• 语法：AVG([DISTINCT | ALL] expr)
• 示例：

  sql

  -- 计算所有员工的平均工资
  SELECT AVG(salary) AS 平均工资 FROM employees;
  
  -- 按部门计算平均工资
  SELECT department_id, AVG(salary) AS 部门平均工资
  FROM employees
  GROUP BY department_id
  HAVING AVG(salary) > 6000
  ORDER BY 2 DESC;
  

• 注意事项：计算方式为总和除以非 NULL 值的数量

2.3 COUNT

• 功能：计算记录数量
• 语法：
  • COUNT(*)：计算所有行的数量，包括 NULL 值
  • COUNT(expr)：计算表达式非 NULL 值的数量
  • COUNT(DISTINCT expr)：计算表达式不同非 NULL 值的数量
• 示例：

  sql

  -- 计算员工总数（包括所有行）
  SELECT COUNT(*) AS 员工总数 FROM employees;
  
  -- 计算有佣金的员工数量
  SELECT COUNT(commission_pct) AS 有佣金的员工数 FROM employees;
  
  -- 计算不同部门的数量
  SELECT COUNT(DISTINCT department_id) AS 部门数量 FROM employees;
  

2.4 MAX

• 功能：返回列中的最大值
• 语法：MAX(expr)
• 支持的数据类型：数值型、字符型、日期型等
• 示例：

  sql

  -- 查找最高工资
  SELECT MAX(salary) AS 最高工资 FROM employees;
  
  -- 查找每个部门的最高工资和对应的员工
  SELECT department_id, MAX(salary) AS 部门最高工资,
         MAX(last_name) KEEP (DENSE_RANK FIRST ORDER BY salary DESC) AS 最高工资员工
  FROM employees
  GROUP BY department_id;
  
  -- 查找最近的雇佣日期
  SELECT MAX(hire_date) AS 最近雇佣日期 FROM employees;
  

2.5 MIN

• 功能：返回列中的最小值
• 语法：MIN(expr)
• 示例：

  sql

  -- 查找最低工资
  SELECT MIN(salary) AS 最低工资 FROM employees;
  
  -- 查找每个部门最早雇佣的员工
  SELECT department_id, MIN(hire_date) AS 最早雇佣日期
  FROM employees
  GROUP BY department_id;
  

3. 统计聚合函数

3.1 STDDEV 和 STDDEV_POP

• 功能：计算总体标准差
• 语法：STDDEV(expr), STDDEV_POP(expr)
• 示例：

  sql

  -- 计算工资的标准差
  SELECT STDDEV(salary) AS 工资标准差 FROM employees;
  

3.2 STDDEV_SAMP

• 功能：计算样本标准差
• 语法：STDDEV_SAMP(expr)

3.3 VARIANCE 和 VAR_POP

• 功能：计算总体方差
• 语法：VARIANCE(expr), VAR_POP(expr)

3.4 VAR_SAMP

• 功能：计算样本方差
• 语法：VAR_SAMP(expr)

3.5 MEDIAN

• 功能：计算中位数
• 语法：MEDIAN(expr)
• 示例：

  sql

  -- 计算工资中位数
  SELECT MEDIAN(salary) AS 工资中位数 FROM employees;
  

• 注意事项：MEDIAN 是一个分析函数，也可作为聚合函数使用，返回中间值或中间两个值的平均值

3.6 PERCENTILE_CONT

• 功能：计算连续分布的百分位数
• 语法：PERCENTILE_CONT(percentile) WITHIN GROUP (ORDER BY expr)
• 示例：

  sql

  -- 计算工资的75百分位数
  SELECT PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) AS 75分位工资
  FROM employees;
  

3.7 PERCENTILE_DISC

• 功能：计算离散分布的百分位数
• 语法：PERCENTILE_DISC(percentile) WITHIN GROUP (ORDER BY expr)

4. 字符串聚合函数

4.1 LISTAGG

• 功能：将多行字符串连接成单行字符串
• 语法：LISTAGG(expr, delimiter) [WITHIN GROUP (ORDER BY order_expr)] [OVER (query_partition_clause)]
• 示例：

  sql

  -- 按部门聚合员工姓名，用逗号分隔
  SELECT department_id,
         LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name) AS 员工列表
  FROM employees
  GROUP BY department_id
  ORDER BY department_id;
  
  -- 限制聚合结果长度
  SELECT department_id,
         LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name) 
         ON OVERFLOW TRUNCATE '...' AS 员工列表
  FROM employees
  GROUP BY department_id;
  

• Oracle 19C 新特性：支持ON OVERFLOW子句处理结果过长的情况，可选择错误或截断

4.2 STRAGG

• 功能：字符串聚合（Oracle 内部函数，建议使用 LISTAGG）
• 注意事项：非官方支持函数，行为可能随版本变化

5. 高级聚合函数

5.1 COLLECT

• 功能：将列值收集到集合中
• 语法：COLLECT(expr)
• 示例：

  sql

  -- 创建集合类型
  CREATE TYPE num_tab AS TABLE OF NUMBER;
  
  -- 收集每个部门的员工ID
  SELECT department_id, COLLECT(employee_id) AS 员工ID集合
  FROM employees
  GROUP BY department_id;
  

5.2 JSON_ARRAYAGG

• 功能：将结果集聚合为 JSON 数组
• 语法：JSON_ARRAYAGG(expr [FORMAT JSON] [ORDER BY sort_expr] [NULL ON NULL])
• 示例：

  sql

  -- 生成部门员工的JSON数组
  SELECT department_id,
         JSON_ARRAYAGG(
           JSON_OBJECT(
             'id' VALUE employee_id,
             'name' VALUE last_name,
             'salary' VALUE salary
           ) ORDER BY last_name
         ) AS 员工信息
  FROM employees
  GROUP BY department_id;
  

5.3 JSON_OBJECTAGG

• 功能：将结果集聚合为 JSON 对象
• 语法：JSON_OBJECTAGG(key_expr VALUE value_expr [FORMAT JSON] [ORDER BY sort_expr])

5.4 XMLAGG

• 功能：将结果集聚合为 XML 元素
• 语法：XMLAGG(XMLELEMENT(name, expr) [ORDER BY sort_expr])
• 示例：

  sql

  -- 生成部门员工的XML
  SELECT department_id,
         XMLAGG(
           XMLELEMENT("employee", 
             XMLATTRIBUTES(employee_id AS "id"),
             last_name
           ) ORDER BY last_name
         ) AS 员工XML
  FROM employees
  GROUP BY department_id;
  

6. 分组扩展函数

6.1 ROLLUP

• 功能：生成多层级的聚合结果，从最细粒度到总计
• 语法：GROUP BY ROLLUP(expr1, expr2, ...)
• 示例：

  sql

  -- 按部门和职位进行ROLLUP聚合
  SELECT department_id, job_id, COUNT(*), SUM(salary)
  FROM employees
  GROUP BY ROLLUP(department_id, job_id)
  ORDER BY department_id, job_id;
  

• 结果说明：返回每个部门每个职位的聚合、每个部门的总计、以及整体总计

6.2 CUBE

• 功能：生成所有可能组合的聚合结果
• 语法：GROUP BY CUBE(expr1, expr2, ...)
• 示例：

  sql

  -- 按部门和职位进行CUBE聚合
  SELECT department_id, job_id, COUNT(*), SUM(salary)
  FROM employees
  GROUP BY CUBE(department_id, job_id)
  ORDER BY department_id, job_id;
  

• 与 ROLLUP 的区别：CUBE 生成所有维度组合的聚合，而 ROLLUP 只生成层级式的聚合

6.3 GROUPING SETS

• 功能：指定需要聚合的列组合
• 语法：GROUP BY GROUPING SETS((expr1, expr2), (expr3), ...)
• 示例：

  sql

  -- 按指定组合进行聚合
  SELECT department_id, job_id, manager_id, SUM(salary)
  FROM employees
  GROUP BY GROUPING SETS(
    (department_id, job_id),  -- 部门+职位的聚合
    (department_id, manager_id),  -- 部门+经理的聚合
    ()  -- 总计
  );
  

6.4 GROUPING 和 GROUPING_ID

• 功能：标识聚合行的分组级别
• 语法：GROUPING(expr), GROUPING_ID(expr1, expr2, ...)
• 示例：

  sql

  -- 使用GROUPING标识聚合行
  SELECT 
    department_id,
    job_id,
    GROUPING(department_id) AS dept_group_flag,
    GROUPING(job_id) AS job_group_flag,
    SUM(salary) AS 总工资
  FROM employees
  GROUP BY ROLLUP(department_id, job_id);
  

• 返回值：GROUPING 返回 1 表示该行是该列的聚合行，0 表示是细节行

7. 聚合函数与空值处理

1. NULL 值处理规则：

   • 所有聚合函数都会自动忽略 NULL 值
   • COUNT (*) 是例外，它会计算包含 NULL 值的行

2. 处理 NULL 的示例：

   sql

   -- 计算平均佣金（自动忽略NULL）
   SELECT AVG(commission_pct) AS 平均佣金比例 FROM employees;
   
   -- 将NULL视为0进行计算
   SELECT AVG(NVL(commission_pct, 0)) AS 平均佣金比例（含0） FROM employees;
   

3. 使用 COALESCE 处理多列 NULL：

   sql

   -- 计算多个绩效指标的平均值，将NULL替换为0
   SELECT AVG(COALESCE(performance_score, 0)) AS 平均绩效
   FROM employee_performance;
   

8. 聚合函数与分析函数的区别

特性	聚合函数	分析函数
结果行数	通常返回单行或按组返回较少行	保留原始行数，每行都有聚合结果
使用方式	与 GROUP BY 配合使用	使用 OVER () 子句
典型用途	生成汇总报表	每行数据附加聚合信息（如累计和）

示例对比：

sql

-- 聚合函数：每个部门返回一行
SELECT department_id, AVG(salary) AS 部门平均工资
FROM employees
GROUP BY department_id;

-- 分析函数：保留所有行，附加部门平均工资
SELECT employee_id, last_name, department_id, salary,
       AVG(salary) OVER (PARTITION BY department_id) AS 部门平均工资
FROM employees;

9. 性能考虑与最佳实践

9.1 索引优化

• 为 GROUP BY 和聚合函数中使用的列创建适当的索引
• 考虑创建复合索引，包含 GROUP BY 列和聚合列
• 示例：

  sql

  -- 为分组和聚合列创建索引
  CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary);
  

9.2 过滤先行

• 在聚合前尽量过滤数据，减少聚合的数据量
• 优先使用 WHERE 子句过滤，其次使用 HAVING 子句

  sql

  -- 推荐：先过滤再聚合
  SELECT department_id, SUM(salary)
  FROM employees
  WHERE hire_date > TO_DATE('2020-01-01', 'YYYY-MM-DD')
  GROUP BY department_id;
  

9.3 避免不必要的 DISTINCT

• DISTINCT 会增加计算开销，确认确实需要去重时再使用
• 比较：

  sql

  -- 开销较大
  SELECT SUM(DISTINCT salary) FROM employees;
  
  -- 如果不需要去重，使用更高效的方式
  SELECT SUM(salary) FROM employees;
  

9.4 处理大型数据集

• 对于超大型数据集，考虑使用并行查询

  sql

  SELECT /*+ PARALLEL(employees, 4) */
         department_id, SUM(salary)
  FROM employees
  GROUP BY department_id;
  

• 考虑使用物化视图预先计算聚合结果

9.5 LISTAGG 性能优化

• 对大型数据集使用 LISTAGG 时，设置适当的 MAX_STRING_SIZE
• 使用 ON OVERFLOW 子句避免溢出错误
• 考虑分批次聚合长字符串

9.6 避免在聚合函数中使用复杂表达式

• 将复杂表达式提前计算或使用虚拟列

  sql

  -- 不推荐：聚合函数中包含复杂计算
  SELECT SUM(salary * 1.15 * (1 + NVL(commission_pct, 0))) FROM employees;
  
  -- 推荐：使用子查询提前计算
  SELECT SUM(total_compensation)
  FROM (
    SELECT salary * 1.15 * (1 + NVL(commission_pct, 0)) AS total_compensation
    FROM employees
  );
  

10. 总结

Oracle 19C 提供了丰富的内置聚合函数，从基本的 SUM、AVG 到高级的 LISTAGG、JSON_ARRAYAGG，再到分组扩展的 ROLLUP、CUBE 等，满足了各种数据聚合和分析需求。

掌握这些聚合函数的使用方法，能够帮助开发人员高效地生成汇总报表、进行数据分析和统计计算。在实际应用中，应根据具体业务场景选择合适的聚合函数，并遵循性能优化最佳实践，特别是在处理大型数据集时。

通过合理使用聚合函数和分组技术，可以显著简化 SQL 代码，提高开发效率，同时保证查询性能，为业务决策提供有力的数据支持。