数据库Oracle组函数和分组函数

最新推荐文章于 2024-04-26 17:17:13 发布

有园人

最新推荐文章于 2024-04-26 17:17:13 发布

阅读量545

点赞数 1

分类专栏：数据库学习不忘初心，方得始终文章标签：数据库Oracle组函数和分组函数

本文链接：https://blog.csdn.net/qq_42031653/article/details/98367499

版权

不忘初心，方得始终同时被 2 个专栏收录

66 篇文章 0 订阅

订阅专栏

数据库学习

29 篇文章 1 订阅

订阅专栏

组函数：

组函数操作行集，给出每组的结果。组函数不象单行函数，组函数对行的集合进行操作，对每组给出一个结果。这些集合可能是整个表或者是表分成的组。

组函数与单行函数区别：

单行函数对查询到每个结果集做处理，而组函数只对分组数据做处理。
单行函数对每个结果集返回一个结果，而组函数对每个分组返回一个结果。

组函数的类型：

•AVG 平均值
• COUNT 计数
• MAX 最大值
• MIN 最小值
• SUM 合计

组函数的语法：

使用组函数的原则：

• 用于函数的参数的数据类型可以是 CHAR、VARCHAR2、NUMBER 或 DATE。
• 所有组函数忽略空值。为了用一个值代替空值，用 NVL、NVL2 或 COALESCE 函数。

组函数的使用：

1.使用 AVG 和和 SUM

AVG(arg)函数：对分组数据做平均值运算。
arg:参数类型只能是数字类型。

SUM(arg)函数：对分组数据求和。
arg:参数类型只能是数字类型。

例：求雇员表中的的平均薪水与薪水总额。

SQL> select sum(e.salary) "薪水总额：",avg(e.salary) "平均薪水：" from employees e;
     薪水总额：      平均薪水：
---------- ----------
    681816 6432.22641

2.用使用 MIN 和和 MAX

MIN(arg)函数：求分组中最小数据。
arg:参数类型可以是字符、数字、日期。

MAX(arg)函数：求分组中最大数据。
arg:参数类型可以是字符、数字、日期。

例：求雇员表中的最高薪水与最低薪水。

SQL> select min(e.salary) MIN_SALARY,max(e.salary) MAX_SALARY from employees e;
MIN_SALARY MAX_SALARY
---------- ----------
      2100      24000

3.用使用 COUNT 函数

COUNT 函数：返回一个表中的行数。
COUNT 函数有三种格式：
• COUNT(*)
• COUNT(expr)
• COUNT(DISTINCT expr)

COUNT(*):

返回表中满足 SELECT 语句标准的行数，包括重复行，包括有空值列的行。如果WHERE 子句包括在 SELECT 语句中，COUNT(*) 返回满足 WHERE 子句条件的行数.

例：返回查询结果的总条数：

SQL> select count(*) from employees;
  COUNT(*)
----------
       106

COUNT(expr)

返回在列中的由 expr 指定的非空值的数

例：显示部门 80 中有佣金的雇员人数

SQL> select count(e.commission_pct) from employees e where e.department_id=80;
COUNT(E.COMMISSION_PCT)
-----------------------
                     33

COUNT(DISTINCT expr：

使用 DISTINCT 关键字禁止计算在一列中的重复值。

例：显示 EMPLOYEES 表中不重复的部门数。

SQL> select count(e.department_id) from employees e;
COUNT(E.DEPARTMENT_ID)
----------------------
                   105

SQL> select count(distinct e.department_id) from employees e;
COUNT(DISTINCTE.DEPARTMENT_ID)
------------------------------
                            11

4.组函数和 Null ：

所有组函数忽略列中的空值。
在组函数中使用 NVL 函数来处理空值。

例：计算有佣金的员工的佣金平均值。

SQL> select avg(e.commission_pct) from  employees e where e.commission_pct is not null;
AVG(E.COMMISSION_PCT)
---------------------
    0.223529411764706

SQL> select avg(e.commission_pct) from  employees e;
AVG(E.COMMISSION_PCT)
---------------------
    0.223529411764706

例：计算所有员工的佣金的平均值。

SQL> select avg(nvl(e.commission_pct,0)) from employees e;
AVG(NVL(E.COMMISSION_PCT,0))
----------------------------
          0.0716981132075472

创建数据组(GROUPBY)：

可以根据需要将查询到的结果集信息划分为较小的组，用 GROUP BY 子句实现。

GROUP BY 子句用法：

GROUP BY 子句：GROUP BY 子句可以把表中的行划分为组。然后可以用组函数返回每一组的摘要信息

使用分组原则：

• 如果在 SELECT 子句中包含了组函数，就不能选择单独的结果，除非单独的列出现在 GROUP BY 子句中。如果未能在 GROUP BY 子句中包含一个字段列表，你会收到一个错误信息。
• 使用 WHERE 子句，你可以在划分行成组以前过滤行。
• 在 GROUP BY 子句中必须包含列。
• 在 GROUP BY 子句中你不能用列别名。
• 默认情况下，行以包含在 GROUP BY 列表中的字段的升序排序。可以用 ORDER BY子句覆盖这个默认值。

GROUP BY 子句的使用：

我们可以根据自己的需要对数据进行分组，在分组时，只要将需要做分组的列的列名添加到 GROUP BY 子句后侧就可以。GROUP BY 列不必在 SELECT 列表中。

例：求每个部门的平均薪水

SQL> select avg(e.salary),e.department_id from employees e where e.department_id is not null group by e.department_id;
AVG(E.SALARY) DEPARTMENT_ID
------------- -------------
8601.33333333           100
         4150            30
19333.3333333            90
         9500            20
        10000            70
        10154           110
3475.55555555            50
8936.36363636            80
         6500            40
         5760            60
         4400            10
11 rows selected

多于一个列的分组:

例：显示在每个部门中付给每个工作岗位的合计薪水的报告。

SQL> select sum(e.salary),e.job_id,e.department_id from employees e
  2  group by e.department_id,e.job_id;
SUM(E.SALARY) JOB_ID     DEPARTMENT_ID
------------- ---------- -------------
         8300 AC_ACCOUNT           110
        34000 AD_VP                 90
        55700 ST_CLERK              50
       233900 SA_REP                80
        36400 ST_MAN                50
        61000 SA_MAN                80
        12008 AC_MGR               110
        24000 AD_PRES               90
        28800 IT_PROG               60
        12008 FI_MGR               100
        13900 PU_CLERK              30
        64300 SH_CLERK              50
        13000 MK_MAN                20
        39600 FI_ACCOUNT           100
         7000 SA_REP     
        10000 PR_REP                70
        11000 PU_MAN                30
         4400 AD_ASST               10
         6000 MK_REP                20
         6500 HR_REP                40
20 rows selected

GROUP BY 子句执行的顺序

先进行数据查询，在对数据进行分组，然后执行组函数。

约束分结果（having子句）

having子句通常都是和group by一起用，可以放在group by的前面，也可以放在后面（建议：放在group by的后面，方便自己查看。），having子句是用来弥补group by子句不能用where条件语句的不足，它的出现就是为了和where子句一样的效果，

HAVING 的用法：

例：显示那些最高薪水大于 $10,000 的部门的部门号和最高薪水

SQL> select max(e.salary),e.department_id from employees e 
  2  group by e.department_id
  3  having max(e.salary)>10000;
MAX(E.SALARY) DEPARTMENT_ID
------------- -------------
        12008           100
        11000            30
        24000            90
        13000            20
        12008           110
        14000            80
6 rows selected

例：查询那些最高薪水大于 $10,000 的部门的部门号和平均薪水

SQL> select avg(e.salary),e.department_id from employees e
  2  group by e.department_id
  3  having max(e.salary)>10000;
AVG(E.SALARY) DEPARTMENT_ID
------------- -------------
8601.33333333           100
         4150            30
19333.3333333            90
         9500            20
        10154           110
8936.36363636            80
6 rows selected

嵌套组函数：

在使用组函数时我们也可以根据需要来做组函数的嵌套使用。

例：显示部门中的最大平均薪水：

SQL> select max(avg(e.salary))from employees e group by e.department_id;
MAX(AVG(E.SALARY))
------------------
  19333.3333333333

组函数小节练习：

1.显示所有雇员的最高、、最低、、合计和平均薪水，，列标签分别为：Maximum 、Minimum 、Sum 和和 Average 。四舍五入结果为最近的整数：

SQL> select max(e.salary) Maximum,min(e.salary) Minimum,sum(e.salary) Sum,avg(e.salary) Average   from employees e
  2  ;
   MAXIMUM    MINIMUM        SUM    AVERAGE
---------- ---------- ---------- ----------
     24000       2100     681816 6432.22641

2.修改上题显示每中工作类型的最低、最高、合计和平均薪水：

SQL> select e.job_id,max(e.salary) Maximum,min(e.salary) Minimum,sum(e.salary) Sum,avg(e.salary) Average   from employees e group by e.job_id;
JOB_ID        MAXIMUM    MINIMUM        SUM    AVERAGE
---------- ---------- ---------- ---------- ----------
IT_PROG          9000       4200      28800       5760
AC_MGR          12008      12008      12008      12008
AC_ACCOUNT       8300       8300       8300       8300
ST_MAN           8200       5800      36400       7280
PU_MAN          11000      11000      11000      11000
AD_ASST          4400       4400       4400       4400
AD_VP           17000      17000      34000      17000
SH_CLERK         4200       2500      64300       3215
FI_ACCOUNT       9000       6900      39600       7920
FI_MGR          12008      12008      12008      12008
PU_CLERK         3100       2500      13900       2780
SA_MAN          14000      10500      61000      12200
MK_MAN          13000      13000      13000      13000
PR_REP          10000      10000      10000      10000
AD_PRES         24000      24000      24000      24000
SA_REP          11500       6100     240900 8306.89655
MK_REP           6000       6000       6000       6000
ST_CLERK         3600       2100      55700       2785
HR_REP           6500       6500       6500       6500
19 rows selected

3.写一个查询显示每一工作岗位的人数：

SQL> select e.job_id,count(e.employee_id) from employees e group by e.job_id;
JOB_ID     COUNT(E.EMPLOYEE_ID)
---------- --------------------
AC_ACCOUNT                    1
AC_MGR                        1
AD_ASST                       1
AD_PRES                       1
AD_VP                         2
FI_ACCOUNT                    5
FI_MGR                        1
HR_REP                        1
IT_PROG                       5
MK_MAN                        1
MK_REP                        1
PR_REP                        1
PU_CLERK                      5
PU_MAN                        1
SA_MAN                        5
SA_REP                       29
SH_CLERK                     20
ST_CLERK                     20
ST_MAN                        5
19 rows selected

4.确定经理人数，不需要列出他们，列标签是 Number ofManagers 。

SQL> select count(distinct e.manager_id) from employees e;
COUNT(DISTINCTE.MANAGER_ID)
---------------------------
                         18

5.写一个查询显示最高和最低薪水之间的差。列标签是DIFFERENCE 。

SQL> select (max(e.salary)-min(e.salary)) DIFFERENCE from employees e;
DIFFERENCE
----------
     21900

6.显示经理号和经理付给雇员的最低薪水。。排除那些经理未知的人。排除最低薪水小于等于 $6,000 的组。按薪水降序排序输出。

SQL> select e.manager_id, min(e.salary)  from employees e where e.manager_id is not null
  2  group by e.manager_id
  3  having min(e.salary)<=6000
  4  order by min(e.salary) desc;
MANAGER_ID MIN(E.SALARY)
---------- -------------
       201          6000
       100          5800
       101          4400
       103          4200
       123          2500
       114          2500
       124          2500
       122          2200
       120          2200
       121          2100
10 rows selected

7. 写一个查询显示每个部门的名字、地点、人数和部门中所有雇员的平均薪水。四舍五入薪水到两位小数

SQL> select d.department_name,d.location_id,count(e.employee_id),round(avg(e.salary),2) from employees e,departments d
  2  where e.department_id=d.department_id
  3  group by d.department_name,d.location_id;
DEPARTMENT_NAME                LOCATION_ID COUNT(E.EMPLOYEE_ID) ROUND(AVG(E.SALARY),2)
------------------------------ ----------- -------------------- ----------------------
Administration                        1700                    1                   4400
Marketing                             1800                    2                   9500
Sales                                 2500                   33                8936.36
Purchasing                            1700                    6                   4150
Finance                               1700                    6                8601.33
IT                                    1400                    5                   5760
Executive                             1700                    3               19333.33
Shipping                              1500                   45                3475.56
Accounting                            1700                    2                  10154
Human Resources                       2400                    1                   6500
Public Relations                      2700                    1                  10000

8.创建一个查询显示雇员总数，和在 2001 、2002 、2003 和受雇的雇员人数。创建适当的列标题。

SQL> select count(e.employee_id),sum(decode(to_char(e.hire_date,'yyyy'),'2001',1,0)) "2001",sum(decode(to_char(e.hire_date,'yyyy'),'2002',1,0)) "2002",sum(decode(to_char(e.hire_date,'yyyy'),'2003',1,0)) "2003" from employees e;
COUNT(E.EMPLOYEE_ID)       2001       2002       2003
-------------------- ---------- ---------- ----------
                 106          1          7          6

9.创建一个混合查询显示工作岗位和工作岗位的薪水合计，并门且合计部门 20 、50 、80 和和 90 的工作岗位的薪水。给每
列一个恰当的列标题

SQL> select e.job_id,sum(e.salary),sum(decode(e.department_id,20,e.salary)) "dept_20",sum(decode(e.department_id,50,e.salary)) "dept_50",sum(decode(e.department_id,20,e.salary)) "dept_80",sum(decode(e.department_id,90,e.salary)) "dept_90" from employees e group by e.job_id;
JOB_ID     SUM(E.SALARY)    dept_20    dept_50    dept_80    dept_90
---------- ------------- ---------- ---------- ---------- ----------
IT_PROG            28800                                  
AC_MGR             12008                                  
AC_ACCOUNT          8300                                  
ST_MAN             36400                 36400            
PU_MAN             11000                                  
AD_ASST             4400                                  
AD_VP              34000                                       34000
SH_CLERK           64300                 64300            
FI_ACCOUNT         39600                                  
FI_MGR             12008                                  
PU_CLERK           13900                                  
SA_MAN             61000                                  
MK_MAN             13000      13000                 13000 
PR_REP             10000                                  
AD_PRES            24000                                       24000
SA_REP            240900                                  
MK_REP              6000       6000                  6000 
ST_CLERK           55700                 55700            
HR_REP              6500                                  
19 rows selected

有园人

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
数据库Oracle组函数和分组函数

组函数：组函数操作行集，给出每组的结果。组函数不象单行函数，组函数对行的集合进行操作，对每组给出一个结果。这些集合可能是整个表或者是表分成的组。组函数与单行函数区别：单行函数对查询到每个结果集做处理，而组函数只对分组数据做处理。单行函数对每个结果集返回一个结果，而组函数对每个分组返回一个结果。组函数的类型：•AVG 平均值• COUNT 计数• MAX 最大值...
复制链接

扫一扫

专栏目录