Oracle数据库中有哪些分组函数

# Oracle数据库中有哪些分组函数

## 一、分组函数概述

在Oracle数据库中，分组函数（Group Functions）也称为聚合函数（Aggregate Functions），是对一组行执行计算并返回单个值的函数。这些函数通常与GROUP BY子句一起使用，用于对数据进行分组统计和分析。

### 1.1 分组函数的特点
- 处理多行数据并返回汇总结果
- 忽略NULL值（除COUNT(*)和GROUPING函数外）
- 常用于SELECT语句的查询列表和HAVING子句
- 可以与DISTINCT一起使用去除重复值

### 1.2 基本语法
```sql
SELECT 分组函数(列名), ...
FROM 表名
[WHERE 条件]
[GROUP BY 分组列]
[HAVING 分组条件]
[ORDER BY 排序列];

二、常用分组函数详解

2.1 COUNT函数

基本用法

-- 计算行数（包含NULL值）
SELECT COUNT(*) FROM employees;

-- 计算特定列非NULL值的数量
SELECT COUNT(commission_pct) FROM employees;

-- 计算不重复值的数量
SELECT COUNT(DISTINCT department_id) FROM employees;

注意事项

• COUNT(*)计算所有行数，包括NULL值
• COUNT(列名)只计算该列非NULL值的数量
• 与DISTINCT联用可计算唯一值的数量

2.2 SUM函数

基本用法

-- 计算工资总和
SELECT SUM(salary) FROM employees;

-- 计算各部门工资总和
SELECT department_id, SUM(salary) 
FROM employees
GROUP BY department_id;

注意事项

• 只能用于数值型数据
• 忽略NULL值
• 与DISTINCT联用可对唯一值求和

2.3 AVG函数

基本用法

-- 计算平均工资
SELECT AVG(salary) FROM employees;

-- 计算各部门平均工资（保留2位小数）
SELECT department_id, ROUND(AVG(salary), 2)
FROM employees
GROUP BY department_id;

注意事项

• 只计算非NULL值
• 结果是浮点数，可用ROUND函数格式化
• 与DISTINCT联用可计算唯一值的平均值

2.4 MAX/MIN函数

基本用法

-- 查找最高和最低工资
SELECT MAX(salary), MIN(salary) FROM employees;

-- 查找各部门最早和最晚雇佣日期
SELECT department_id, MIN(hire_date), MAX(hire_date)
FROM employees
GROUP BY department_id;

注意事项

• 适用于数值、日期和字符数据类型
• 对于字符数据，基于字符的排序规则比较
• 忽略NULL值

2.5 VARIANCE/STDDEV函数

基本用法

-- 计算工资方差和标准差
SELECT VARIANCE(salary), STDDEV(salary) FROM employees;

-- 计算各部门工资标准差
SELECT department_id, STDDEV(salary)
FROM employees
GROUP BY department_id;

注意事项

• VARIANCE计算总体方差
• STDDEV计算总体标准差
• 这两个函数在统计分析中特别有用

三、高级分组函数

3.1 GROUPING函数

基本用法

SELECT department_id, job_id, SUM(salary),
       GROUPING(department_id) AS dept_grouping,
       GROUPING(job_id) AS job_grouping
FROM employees
GROUP BY ROLLUP(department_id, job_id);

功能说明

• 与ROLLUP、CUBE或GROUPING SETS一起使用
• 返回1表示该列是聚合行中的小计/总计
• 返回0表示该列是常规分组行

3.2 GROUP_ID函数

基本用法

SELECT department_id, job_id, SUM(salary),
       GROUP_ID()
FROM employees
GROUP BY GROUPING SETS(
  (department_id, job_id),
  (department_id),
  (department_id, job_id)
);

功能说明

• 用于标识重复的分组集
• 返回0表示第一次出现的分组
• 返回1表示第二次出现的相同分组

3.3 GROUPING SETS

基本用法

-- 多维度聚合
SELECT department_id, job_id, manager_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS(
  (department_id, job_id),
  (job_id, manager_id),
  (department_id)
);

功能说明

• 允许在单个查询中指定多个分组集
• 比UNION ALL多个查询更高效
• 可以组合ROLLUP和CUBE使用

3.4 ROLLUP函数

基本用法

-- 生成层次化小计
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY ROLLUP(department_id, job_id);

功能说明

• 从右向左逐步减少分组列
• 生成层次化的小计和总计
• 适用于需要分层汇总的场景

3.5 CUBE函数

基本用法

-- 生成所有可能的组合小计
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY CUBE(department_id, job_id);

功能说明

• 生成所有可能的分组组合
• 比ROLLUP产生更多的小计行
• 适用于多维数据分析

四、分组函数应用实例

4.1 基础统计查询

-- 公司整体薪资统计
SELECT 
  COUNT(*) AS 员工总数,
  SUM(salary) AS 工资总额,
  AVG(salary) AS 平均工资,
  MAX(salary) AS 最高工资,
  MIN(salary) AS 最低工资,
  STDDEV(salary) AS 工资标准差
FROM employees;

4.2 多维度分析

-- 按部门和职位统计
SELECT 
  NVL(department_id, 0) AS 部门ID,
  NVL(job_id, '所有职位') AS 职位,
  COUNT(*) AS 人数,
  SUM(salary) AS 工资总额,
  ROUND(AVG(salary), 2) AS 平均工资
FROM employees
GROUP BY ROLLUP(department_id, job_id)
ORDER BY department_id, job_id;

4.3 销售数据分析

-- 销售业绩分析
SELECT 
  EXTRACT(YEAR FROM order_date) AS 年份,
  EXTRACT(MONTH FROM order_date) AS 月份,
  product_category AS 产品类别,
  COUNT(DISTINCT customer_id) AS 客户数,
  SUM(quantity) AS 销售数量,
  SUM(amount) AS 销售金额,
  RANK() OVER (PARTITION BY EXTRACT(YEAR FROM order_date) 
               ORDER BY SUM(amount) DESC) AS 排名
FROM sales
GROUP BY 
  EXTRACT(YEAR FROM order_date),
  EXTRACT(MONTH FROM order_date),
  ROLLUP(product_category)
HAVING SUM(amount) > 10000
ORDER BY 年份, 月份, 产品类别;

五、性能优化建议

1. 索引优化：为GROUP BY列创建适当索引
2. 减少分组列：只选择必要的分组维度
3. 使用HAVING替代WHERE：WHERE在分组前过滤，HAVING在分组后过滤
4. 避免过度使用DISTINCT：会增加处理开销
5. 考虑物化视图：对频繁使用的聚合查询创建物化视图

六、总结

Oracle数据库提供了丰富的分组函数，从基础的COUNT、SUM到高级的ROLLUP、CUBE等，能够满足各种复杂的数据分析需求。合理使用这些函数可以： - 简化复杂的统计查询 - 提高数据分析效率 - 生成多维度报表 - 支持决策支持系统

掌握这些分组函数的使用方法和适用场景，是进行高效SQL编程和数据分析的重要基础。在实际应用中，应根据具体业务需求选择最合适的函数组合，并注意性能优化，以获得最佳的查询效率。 “`