数据库基本功之分组函数

最新推荐文章于 2024-03-19 16:35:02 发布

数据架构

最新推荐文章于 2024-03-19 16:35:02 发布

阅读量1.3k

点赞数 1

分类专栏： Oracle 数据库语言基础文章标签： oracle 数据库 sql

本文链接：https://blog.youkuaiyun.com/u011868279/article/details/129395581

版权

Oracle 数据库语言基础专栏收录该内容

13 篇文章

订阅专栏

五个最重要的分组函数

分组函数是对表中一组记录进行操作,每组只返回一个结果,即首先要对表记录进行分组,然后在进行操作汇总,每组返回一个结果,分组时可能是整个表分为一组,也可能根据条件分成多组

sum() avg() max() min() count()

SQL> 
SQL> conn scott/tiger@PDB1;
Connected.
SQL> show user;
USER is "SCOTT"
SQL> show con_name;

CON_NAME
------------------------------
PDB1
SQL> set pagesize 200 linesize 200
SQL> select sum(sal) sum,avg(sal) avg,max(sal) max, min(sal) min,count(*) count from emp;

       SUM        AVG        MAX        MIN      COUNT
---------- ---------- ---------- ---------- ----------
     24025 1848.07692       3000        800         13

SQL>

group by deptno是分组特性;

SQL> 
SQL> select deptno,sum(sal),avg(sal),count(sal),max(sal),min(sal) from emp group by deptno;

    DEPTNO   SUM(SAL)   AVG(SAL) COUNT(SAL)   MAX(SAL)   MIN(SAL)
---------- ---------- ---------- ---------- ---------- ----------
        30       9400 1566.66667          6       2850        950
        10       3750       1875          2       2450       1300
        20      10875       2175          5       3000        800

SQL>

MIN(),MAX(),count()可以作用于日期类型和字符类型

SQL> 
SQL> select min(hiredate),max(hiredate),min(ename),max(ename),count(hiredate) from emp;

MIN(HIREDATE)   MAX(HIREDATE)   MIN(ENAME)                     MAX(ENAME)                     COUNT(HIREDATE)
--------------- --------------- ------------------------------ ------------------------------ ---------------
17-DEC-80       02-APR-87       ADAMS                          WARD                                        13

SQL> select * from emp;

     EMPNO ENAME                          JOB                                MGR HIREDATE               SAL       COMM     DEPTNO
---------- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ----------
      7369 SMITH                          CLERK                             7902 17-DEC-80              800                    20
      7499 ALLEN                          SALESMAN                          7698 20-FEB-81             1600        300         30
      7521 WARD                           SALESMAN                          7698 22-FEB-81             1250        500         30
      7566 JONES                          MANAGER                           7839 02-APR-81             2975                    20
      7654 MARTIN                         SALESMAN                          7698 28-SEP-81             1250       1400         30
      7698 BLAKE                          MANAGER                           7839 01-MAY-81             2850                    30
      7782 CLARK                          MANAGER                           7839 09-JUN-81             2450                    10
      7788 SCOTT                          ANALYST                           7566 24-JAN-87             3000                    20
      7844 TURNER                         SALESMAN                          7698 08-SEP-81             1500          0         30
      7876 ADAMS                          CLERK                             7788 02-APR-87             1100                    20
      7900 JAMES                          CLERK                             7698 03-DEC-81              950                    30
      7902 FORD                           ANALYST                           7566 03-DEC-81             3000                    20
      7934 MILLER                         CLERK                             7782 23-JAN-82             1300                    10

13 rows selected.

SQL>

COUNT(*)函数返回表中行的总数,包括重复行与数据列中含有空值的行,而其他分组函数的统计都不包括空值的行.(知识点)

COUNT(comm)返回该列所含非空行的数量.

SQL> 
SQL> select count(*),count(comm) from emp;

  COUNT(*) COUNT(COMM)
---------- -----------
        13           4

SQL> select * from emp;

     EMPNO ENAME                          JOB                                MGR HIREDATE               SAL       COMM     DEPTNO
---------- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ----------
      7369 SMITH                          CLERK                             7902 17-DEC-80              800                    20
      7499 ALLEN                          SALESMAN                          7698 20-FEB-81             1600        300         30
      7521 WARD                           SALESMAN                          7698 22-FEB-81             1250        500         30
      7566 JONES                          MANAGER                           7839 02-APR-81             2975                    20
      7654 MARTIN                         SALESMAN                          7698 28-SEP-81             1250       1400         30
      7698 BLAKE                          MANAGER                           7839 01-MAY-81             2850                    30
      7782 CLARK                          MANAGER                           7839 09-JUN-81             2450                    10
      7788 SCOTT                          ANALYST                           7566 24-JAN-87             3000                    20
      7844 TURNER                         SALESMAN                          7698 08-SEP-81             1500          0         30
      7876 ADAMS                          CLERK                             7788 02-APR-87             1100                    20
      7900 JAMES                          CLERK                             7698 03-DEC-81              950                    30
      7902 FORD                           ANALYST                           7566 03-DEC-81             3000                    20
      7934 MILLER                         CLERK                             7782 23-JAN-82             1300                    10

13 rows selected.

SQL>

2. 在组函数中使用NVL函数

Nvl.非空函数.如果是空则为0

SQL> 
SQL> -- 如果comm是空值,统计为0
SQL> select deptno,avg(nvl(comm,0)) from emp group by deptno;

    DEPTNO AVG(NVL(COMM,0))          -- 非空值统计数量为6,即所有部门
---------- ----------------
        30       366.666667
        10                0
        20                0

SQL> select deptno,avg(comm) from emp group by deptno;

    DEPTNO  AVG(COMM)            -- 没有非空函数处理,则不统计null值
---------- ----------
        30        550
        10
        20

SQL>

上面两个例子结果为何不一样？
除的时候基数不一样.有非空值函数的处理.
查询emp表得知,部门30的comm总计为2200,总数量为6,非空数量为4;2200/6=366.666667,2200/4=550

3. GROUP BY创建组

SQL> 
SQL> select deptno,avg(nvl(sal,0)) from emp group by deptno;

    DEPTNO AVG(NVL(SAL,0))
---------- ---------------
        30      1566.66667
        10            1875
        20            2175

SQL>

group by后面的列也叫分组特性,一旦使用了group by, select后面只能有两种列,一个是组函数列,而另一个是分组特性列(可选)。

3.1 对分组结果进行过滤

SQL> 
SQL> select deptno,avg(sal) avgcomm from emp group by deptno having avg(sal) > 2000;

    DEPTNO    AVGCOMM
---------- ----------
        20       2175

-- 错误的,应该使用HAVING子句
SQL> select deptno,avg(sal) avgcomm from emp where avg(sal)>2000 group by deptno;
select deptno,avg(sal) avgcomm from emp where avg(sal)>2000 group by deptno     -- 而且where后不可以直接使用分组函数
                                              *
ERROR at line 1:
ORA-00934: group function is not allowed here


SQL> select deptno,avg(sal) avgcomm from emp where avg(sal)> 2000;
select deptno,avg(sal) avgcomm from emp where avg(sal)> 2000          -- where后不可以直接使用分组函数
                                              *
ERROR at line 1:
ORA-00934: group function is not allowed here


SQL> select deptno,sum(sal) from emp where sal>2000 group by deptno;

    DEPTNO   SUM(SAL)
---------- ----------
        30       2850
        10       2450
        20       8975

SQL> select deptno,sum(sal) from emp where sal>2000 group by deptno having sum(sal)>8000;

    DEPTNO   SUM(SAL)
---------- ----------
        20       8975

SQL> select deptno,sum(sal) from emp where sal>2000 group by deptno having sum(sal) > 7000 order by 2;

    DEPTNO   SUM(SAL)
---------- ----------
        20       8975

SQL>

SELECT FROM Where Group by Having order by

3.2 对分组结果排序

SQL> 
SQL> select deptno,avg(nvl(sal,0)) avgcomm from emp group by deptno order by avg(nvl(sal,0));

    DEPTNO    AVGCOMM
---------- ----------
        30 1566.66667
        10       1875
        20       2175

SQL>

-- 排序的列不在select投影选项中也是可以的,这是因为order by是在select投影前完成的.

3.3 知识点

确保SELECT列表中,除了组函数以外,所有列都包含在GROUP BY子句中.
如例子中的deptno

4. 分组函数的嵌套

(知识点)单行函数可以嵌套任意层,但分组函数最多可以嵌套两层.

比如:count(sum(avg)))会返回错误"ORA-00935:group function is nested too deeply".
在分组函数内可以嵌套单行函数,如:要计算各个部门ename值的平均长度之和

SQL> select deptno,sum(sal) from emp group by deptno;

    DEPTNO   SUM(SAL)
---------- ----------
        30       9400
        10       3750
        20      10875

SQL> select avg(sum(sal)) from emp group by deptno;

AVG(SUM(SAL))
-------------
   8008.33333

SQL> select count(avg(sum(sal))) from emp group by deptno;
select count(avg(sum(sal))) from emp group by deptno
                 *
ERROR at line 1:
ORA-00935: group function is nested too deeply


SQL>