【Oracle11g SQL详解】GROUP BY 和 HAVING 子句:分组与过滤

news/2024/12/5 5:51:30/

GROUP BY 和 HAVING 子句:分组与过滤

在 Oracle 11g 中,GROUP BY 子句用于根据一个或多个列对查询结果进行分组,而 HAVING 子句用于对分组后的结果进行过滤。这两者常结合聚合函数使用,用以实现复杂的数据统计和分析。本文将系统讲解其语法、应用场景及实践案例。


一、GROUP BY 子句的基本语法
sql">SELECT 列名1, 聚合函数(列名2), ...
FROM 表名
[WHERE 条件]
GROUP BY 列名1, 列名2, ...
[HAVING 条件];

说明:

  • GROUP BY:指定分组的列或表达式。
  • 聚合函数:如 SUMCOUNTAVGMAXMIN 等,用于对分组数据进行统计计算。
  • HAVING:用于过滤分组后的数据,与 WHERE 类似,但只能用于分组结果。

二、GROUP BY 的应用
1. 按单列分组

根据单个列的值对数据分组,并对每组数据应用聚合函数。

示例:计算每个部门的总薪资

sql">SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

结果: 每个部门一行,总薪资通过 SUM 函数计算得出。


2. 按多列分组

分组可以基于多个列的组合值。

示例:统计每个部门中不同职位的员工人数

sql">SELECT department_id, job_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id, job_id;

结果: 每个部门中每种职位的员工数量。


3. 使用表达式分组

分组依据可以是计算结果或表达式。

示例:按薪资级别分组并统计员工人数

sql">SELECT CASE WHEN salary >= 10000 THEN '高薪'WHEN salary >= 5000 THEN '中等薪资'ELSE '低薪'END AS salary_level, COUNT(*) AS employee_count
FROM employees
GROUP BY CASE WHEN salary >= 10000 THEN '高薪'WHEN salary >= 5000 THEN '中等薪资'ELSE '低薪'END;

结果: 返回按薪资级别分组后的员工数量。


三、HAVING 子句的应用

HAVING 子句用于对分组后的结果进行进一步过滤。它与 WHERE 的区别在于:

  • WHERE 用于分组前过滤数据。
  • HAVING 用于分组后过滤分组结果。

1. HAVING 的基本使用

示例:筛选总薪资超过 50000 的部门

sql">SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 50000;

说明: HAVING 条件中直接使用聚合函数进行筛选。


2. HAVING 与 WHERE 联合使用

示例:筛选出仅统计薪资大于 3000 的员工,并返回总薪资超过 20000 的部门

sql">SELECT department_id, SUM(salary) AS total_salary
FROM employees
WHERE salary > 3000
GROUP BY department_id
HAVING SUM(salary) > 20000;

说明:

  • WHERE 筛选薪资大于 3000 的员工,减少数据量。
  • HAVING 筛选分组后总薪资大于 20000 的部门。

3. HAVING 中的复杂条件

示例:筛选平均薪资大于 5000 且员工人数超过 5 的部门

sql">SELECT department_id, AVG(salary) AS avg_salary, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000 AND COUNT(*) > 5;

说明: 使用多个条件组合对分组结果进行过滤。


四、GROUP BY 和 HAVING 的进阶用法
1. GROUP BY ROLLUP

ROLLUP 是一种扩展分组的功能,用于生成分组的汇总数据。

示例:统计每个部门的总薪资,并增加所有部门的总薪资行

sql">SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP(department_id);

结果:

  • 返回每个部门的总薪资。
  • 额外增加一行显示所有部门的总薪资。

2. GROUP BY CUBE

CUBE 生成跨多个分组维度的汇总数据。

示例:统计每个部门和每种职位的薪资总额,同时增加汇总数据

sql">SELECT department_id, job_id, SUM(salary) AS total_salary
FROM employees
GROUP BY CUBE(department_id, job_id);

结果:

  • 每个部门和职位的薪资总额。
  • 每个部门的汇总。
  • 所有部门和职位的总汇总。

3. GROUPING 函数

GROUPING 用于判断当前行是否为汇总行,配合 ROLLUPCUBE 使用。

示例:判断并标识汇总行

sql">SELECT department_id, job_id, SUM(salary) AS total_salary,GROUPING(department_id) AS is_dept_summary,GROUPING(job_id) AS is_job_summary
FROM employees
GROUP BY CUBE(department_id, job_id);

说明:

  • 如果 GROUPING(department_id) 返回 1,表示当前行是部门的汇总数据。

五、性能优化建议
  1. 减少不必要的分组列

    • 只分组需要统计的列,减少资源开销。
  2. 适当使用索引

    • 对分组列建立索引,优化查询性能。
  3. 谨慎使用复杂表达式

    • 在分组表达式复杂时,可提前处理为中间结果表。

六、练习示例
  1. 统计每个部门的员工人数和平均薪资,并筛选出员工人数大于 5 且平均薪资超过 4000 的部门:
sql">SELECT department_id, COUNT(*) AS employee_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5 AND AVG(salary) > 4000;
  1. 按部门和职位分组统计总薪资,输出汇总信息:
sql">SELECT department_id, job_id, SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP(department_id, job_id);
  1. 统计每种薪资级别的员工人数,并筛选人数超过 10 的薪资级别:
sql">SELECT CASE WHEN salary >= 10000 THEN '高薪'WHEN salary >= 5000 THEN '中等薪资'ELSE '低薪'END AS salary_level,COUNT(*) AS employee_count
FROM employees
GROUP BY CASE WHEN salary >= 10000 THEN '高薪'WHEN salary >= 5000 THEN '中等薪资'ELSE '低薪'END
HAVING COUNT(*) > 10;

七、小结

GROUP BYHAVING 子句是 SQL 中用于分组统计和结果过滤的关键工具。通过灵活使用它们,可以实现各种复杂的数据分析需求。掌握其语法和高级用法,将为数据库查询与分析奠定坚实的基础。


http://www.ppmy.cn/news/1551133.html

相关文章

android 新增一个系统服务

android如何新增一个系统服务呢? 两个方案,一个是新增一个systemserver下的binder服务,一个是新增一个native的独立进程服务 这里选的是第一种,但坐下来感觉第二种更简单一些 因为systemserver要遵循的规则特别多 先是在startOthe…

【Python-Open3D学习笔记】004Mesh生成方法

PointCloud的TriangleMesh生成方法 本文使用csv文件作为点云数据(csv —> DataFrame —> PointCloud) 文章目录 PointCloud的TriangleMesh生成方法0. 数据展示1. Poisson表面重建2. Alpha Shapes3. 检测边界4. 小结 0. 数据展示 数据可视化方法…

heapq模块常用方法

heapq.heapify(x) import heapq# heapq.heapify(x)将列表x转换为堆 # 在堆数据结构中,最小元素总是位于根节点(索引为 0) # 这个操作是在原列表上进行的,会改变列表的顺序 my_list [3, 1, 4, 1, 5, 9, 2, 6, 5, 3, 5] heapq.hea…

【智能摄像头带电源控制的系统实现远程监控的过程】

以下是一个详细的实现方案: 一、系统概述 智能摄像头带电源控制的远程监控系统,以智能摄像头为核心,集成电源管理、网络通信、远程控制等功能,通过互联网或局域网实现远程监控和管理。 二、关键组件与技术 1. **智能摄像头**&…

宠物领养平台:SpringBoot技术解密

第2章 开发环境与技术 本章节对开发宠物领养系统需要搭建的开发环境,还有宠物领养系统开发中使用的编程技术等进行阐述。 2.1 Java语言 Java语言是当今为止依然在编程语言行业具有生命力的常青树之一。Java语言最原始的诞生,不仅仅是创造者感觉C语言在编…

LVS-DR工作模式简介(相对nat性能更高)

LVS-DR工作模式简介(相对nat性能更高)1.工作原理:共享ip修改mac(保证识别回包的mac地址)共享ip:虚拟网卡接口lo修改mac:内核参数在这种模式下,LVS调度器(也称为Director)和后端真实服务器(Real Server)都配置有相同的V…

电销卡风控管理系统

开发背景: 1、很多业务场景下为确保服务质量,需要对手机呼出的电话进行录音备份管理,在线试听,AI质检过滤风险关键词以及话术进行预警,防止挂羊头卖猪肉。 2、对于风险客户和在跟进客户进行拦截呼叫,一个团…

Flutter 1.2:flutter配置gradle环境

1、在android的模块中进行gradle环境配置 ①在 gradle-wrapper.properties文件中将url配置为阿里云镜像,因为gradle的服务器在国外,国内下载非常慢,也可在官网进行下载 gradle版本下载 gradle版本匹配 阿里云镜像gradle下载 可以通过复制链…