Oracle学习笔记_05_分组函数

组函数:avg  sum  max  min   count

group by   

having

group by 增强:rollup      cube     grouping      grouping set

一.分组函数

1.常用分组函数

    AVG([DISTINCT|ALL]n)            -- 求平均值,忽略空值

    COUNT({*|[DISTINCT|ALL]expr})   -- 统计个数,其中expr用来判定非空值(使用*计算所有选定行,包括重复行和带有空值的行)

    MAX([DISTINCT|ALL]expr)         -- 求最大值,忽略空值

    MIN([DISTINCT|ALL]expr)         -- 求最小值,忽略空值

    SUM([DISTINCT|ALL]n)            -- 求和,忽略空值

 注:  (1) DISTINCT 使函数只考虑非重复值,ALL则考虑包括重复值在内的所有值。默认为ALL.

     (2) 带有expr参数的函数的数据类型可以为CHAR,VARCHAR2,NUMBER,DATE.

         (3) 所有分组函数都忽略空值。可以使用NVL,NVL2,或COALESCE函数代替空值

         (4) 使用GROUP BY 时,Oralce服务器隐式地按照升序对结果集进行排序。可以使用ORDER BY 更改排序结果。 

         (5)组函数默认忽略空值。可以使用NVL 函数强制分组函数包含空值,如

  select avg(nvl(comm,0)) from emp;  

2.语法

    SELECT [column,] group_function(column), ...
    FROM table
    [WHERE condition]
    [GROUP BY column]
    [ORDER BY column];

3.avg()   /   sum()    

  只适用于数值型

select avg(salary),sum(salary)
from employees
where department_id = 80;

4.max() / min()

   适用于数值型、字符型、日期型

select max(salary),max(last_name),max(hire_date),min(salary),min(last_name),min(hire_date)
from employees;

5.count()

coung(*)包含空值、重复值,count(expr)过滤空值,count(distinct expr)既过滤空值,也过滤重复值

(1) count(1)/count(2)/count(*)表示将表中的每条记录用1/2/*来充当

select count(employee_id),count(1),count(2),count(salary),count(*),count(hire_date),count(commission_pct)
from employees;

(2) avg = sum / count

select avg(commission_pct),sum(commission_pct)/count(commission_pct),sum(commission_pct)/107
from employees;     --1和2相等

(3)  使用NVL 函数强制分组函数包含空值

select avg(nvl(commission_pct,0)),sum(commission_pct)/107
from employees;     --1,2相等

二.group by

  使用GROUP BY 子句可以将表中的行分成更小的组,然后使用分组函数返回每一组的汇总信息。

1.语法

    SELECT column, group_function(column)
    FROM table
    [WHERE condition]
    [GROUP BY group_by_expression]    --即为对哪些列进行分组
    [ORDER BY column];

注: (1)SELECT 中出现的列,如果未出现在分组函数中,则GROUP BY子句必须包含这些列

        (2)WHERE 子句可以某些行在分组之前排除在外

       (3)不能在GROUP BY 中使用列别名

       (4) 默认情况下GROUP BY列表中的列按升序排列

       (5) GROUP BY 的列可以不出现在分组中 

2.示例

按多个字段进行分组

select department_id,job_id,avg(salary),sum(salary)
from employees
group by department_id , job_id
order by department_id;

分组函数的嵌套

 select max(avg(sal)) from emp group by deptno;

三.having

(1)若过滤条件中出现了组函数,那么必须使用havin替换where. (2)若过滤条件中没有组函数,建议使用where,比having 效率高。

select department_id,max(salary)
from employees
--where max(salary) > 100000      --错误
having max(salary) > 100000
group by department_id;

四.group by 增强

 1.Rollup 

在Group By 中使用Rollup 产生常规分组汇总行 以及分组小计:

SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BY ROLLUP(department_id, job_id);

1——常规分组行;

2, 3 ——分层小计行; Rollup 后面跟了n个字段,就将进行n+1次分组,从右到左每次减少一个字段进行分组;然后进行union 

 2.Cube 

在Group By 中使用Cube 产生Rollup结果集 + 多维度的交叉表数据源:

SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BY CUBE (department_id, job_id) ;

1——常规分组行;

2, 3 、 4 ——分层小计行;其中3是交叉表数据源需要的 job_id 维度层面的小计。 Cube 后面跟了n个字段,就将进行2的N次方的分组运算,然后进行;

3.Grouping

  Grouping函数: Rollup 和 Cube有点抽象,他分别相当于n+1 和 2的n次方常规 Group by 运算;那么在Rollup 和 Cube的结果集中如何很明确的看出哪些行是针对那些列或者列的组合进行分组运算的结果的? 答案是可以使用Grouping 函数; 没有被Grouping到返回1,否则返回0.

SELECT department_id DEPTID, job_id JOB,
SUM(salary),
GROUPING(department_id) GRP_DEPT,
GROUPING(job_id) GRP_JOB
FROM employees
WHERE department_id < 50
GROUP BY ROLLUP(department_id, job_id);

第1行, department_id 和 job_id都被用到了,所以都返回0;   第2行, job_id 没有被用到,所以返回1;   第3行, department_id 和job_id 都没有被用到,所以都返回1 

4.Grouping Set 

使用Grouping Set 来代替多次UNION: 

SELECT department_id, job_id,
manager_id,avg(salary)
FROM employees
GROUP BY GROUPING SETS
((department_id,job_id), (job_id,manager_id));

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏一个爱吃西瓜的程序员

学习SQL【4】-聚合与排序

随着表中记录(数据行)的不断积累,存储数据逐渐增加,有时我们可能希望计算出这些数据的合计值或者平均值等,这个时候就需要使用SQL语句的汇总操作等方法。 一:对表...

36210
来自专栏飞总聊IT

总结一下SQL NULL吧

这篇文章主要回答网友姜锐(森原)。 网上并没有太好的文章总结NULL,比较有效的办法是自己去读SQL标准了。通常SQL98最重要。 我总结一下NULL在标准里...

32711
来自专栏Golang语言社区

mysql的空值与NULL的区别

Mysql数据库是一个基于结构化数据的开源数据库。SQL语句是MySQL数据库中核心语言。不过在MySQL数据库中执行SQL语句,需要小心两个陷阱。   陷阱一...

2817
来自专栏数据之美

图文并茂详解 SQL JOIN

Join是关系型数据库系统的重要操作之一,一般关系型数据库中包含的常用Join:内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另...

2718
来自专栏JMCui

SQL优化一(SQL使用技巧)

1、行列转换:   decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值);   select decode(sign(变量1-变量...

4733
来自专栏Jackson0714

详解SQL集合运算

3938
来自专栏数据和云

如何理解并正确使用MySql索引

索引是存储引擎用于快速查找记录的一种数据结构,通过合理的使用数据库索引可以大大提高系统的访问性能,本文主要介绍在MySql数据库中索引类型,以及如何创建出更加合...

3596
来自专栏栗霖积跬步之旅

第五章:排序检索数据

表名:products 字段:product_id、product_name、product_price、vend_id(供应商) 根据数据库设计理论,如...

1897
来自专栏Jackson0714

基础很重要~~04.表表达式-上篇

34512
来自专栏一个爱吃西瓜的程序员

学习SQL【9】-集合与联结

现在我们开始学习使用2张以上的表的SQL语句。通过以行方向为单位的集合运算符和以列方向为单位的联结,就可以将分散在多张表中的数据组合成期望的结果。 表的加减法 ...

30812

扫码关注云+社区

领取腾讯云代金券