我要评论前情提要：本篇博客将详细介绍oracle的sql中的常用聚合函数（sum avg max min count）和分组（group by）及分组限制（having）的子句、语法、功能和示例详解
oracle版本：19c
一、聚合函数
聚合函数对行进行操作，以使每个组一个结果。
1.1 聚合函数的类型和语法
avg 平均值
count 求记录与数据个数
max 最大值
min 最小值
sum 求和
listagg 行转列函数
stddev 求标准差
variance 求协方差
median 求中位数
语法
select group_function(column), ... from table [where condition];
1.2 聚合函数使用示例
avg, max, min, sum使用示例
可以使用avg和sum对数字进行运算
-- 计算平均工资，最高工资，最低工资，工资总和 sql> select avg(salary),max(salary),min(salary),sum(salary) from employees; avg(salary) max(salary) min(salary) sum(salary) ----------- ----------- ----------- ----------- 6461.83178 24000 2100 691416
可以使用min和max在数字、字符与日期类型数据上
-- 计算最高工资
sql> select max(salary) from employees;
max(salary)
-----------
24000
-- 计算最低工资
sql> select min(salary) from employees;
min(salary)
-----------
2100
-- 判断日期
-- 判断日期标准：哪个日期离当前时间越近，就越大
-- 先修改日期格式方便查看
sql> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
session altered.
-- 查看最小日期和最大日期
sql> select min(hire_date),max(hire_date) from employees;
min(hire_date) max(hire_date)
------------------- -------------------
2001-01-13 00:00:00 2008-04-21 00:00:00count函数使用示例
count(*)或count(1)返回表中的行数
sql> select count(*) from employees;
count(*)
----------
107
sql> select count(1) from employees;
count(1)
----------
107 count(expr)返回expr具有非空值的行数
-- 表中原本有107行数据，但是count(commission_pct)却只统计出35行，是因为commission_pct中有空值，count不会统计空值
sql> select count(commission_pct) from employees;
count(commission_pct)
---------------------
35
-- 如果想统计所有的行，但是又存在空值，则可以使用nvl将空值进行转换
sql> select count(nvl(commission_pct,0)) from employees;
count(nvl(commission_pct,0))
----------------------------
107使用distinct关键字
count(distinct expr)返回表达式中的不同非null值的数量。
在employees表中显示不同部门值的数量:
-- 如果不想统计重复值可以使用distinct去重
sql> select count(department_id),count(distinct department_id) from employees;
count(department_id) count(distinctdepartment_id)
-------------------- ----------------------------
106 11
-- 不去重统计department_id有106行，去重后只有11行了聚合函数和null值
忽略列中的null值
-- 使用avg计算commission_pct，但是由于commission_pct存在空值会被忽略，导致结果不准确
sql> select avg(commission_pct) from employees;
avg(commission_pct)
-------------------
.222857143使用nvl函数强制将列中的null值进行转换
sql> select avg(nvl(commission_pct,0)) from employees;
avg(nvl(commission_pct,0))
--------------------------
.0728971963二、分组
对数据进行分组
按多列分组
group by子句语法
select column, group_function(column) from table [where condition] [group by group_by_expression] [order by column];
2.1 group by 子句
select列表中所有不在分组（聚合）函数中的列必须在group by子句中
group by列不必在select列表中
示例
按照department_id【部门编号】进行分组，分组以后求每个部门的平均工资
sql> select department_id, avg(salary) from employees group by department_id; -- 先分组再聚合
department_id avg(salary)
------------- -----------
50 3475.55556
40 6500
110 10154
90 19333.3333
30 4150
70 10000
7000
10 4400
20 9500
60 5760
100 8601.33333
department_id avg(salary)
------------- -----------
80 8955.88235
12 rows selected.按照部门分组后再按照岗位分组，最后求不同部门的不同岗位的平均工资并且按照部门升序排序
sql> select department_id,job_id,sum(salary)
2 from employees
3 where department_id>40
4 group by department_id,job_id
5 order by department_id;
department_id job_id sum(salary)
------------- ---------- -----------
50 sh_clerk 64300
50 st_clerk 55700
50 st_man 36400
60 it_prog 28800
70 pr_rep 10000
80 sa_man 61000
80 sa_rep 243500
90 ad_pres 24000
90 ad_vp 34000
100 fi_account 39600
100 fi_mgr 12008
department_id job_id sum(salary)
------------- ---------- -----------
110 ac_account 8300
110 ac_mgr 12008
13 rows selected.求部门的平均工资
-- group by 列不必在select列表中
sql> select avg(salary)
2 from employees
3 group by department_id;
avg(salary)
-----------
3475.55556
6500
10154
19333.3333
4150
10000
7000
4400
9500
5760
8601.33333
avg(salary)
-----------
8955.88235
12 rows selected.
-- 但是select列中不在聚合函数中的列必须在group by列中
sql> select department_id,avg(salary)
2 from employees
3* group by department_id;
department_id avg(salary)
------------- -----------
50 3475.55556
40 6500
110 10154
90 19333.3333
30 4150
70 10000
7000
10 4400
20 9500
60 5760
100 8601.33333
department_id avg(salary)
------------- -----------
80 8955.88235
12 rows selected.2.2 使用聚合函数的非法查询
select列表中不是聚合函数的任何列或表达式都必须在group by子句中：
错误示例
sql> select department_id,count(last_name)
2 from employees;
select department_id,count(last_name)
*
error at line 1:
ora-00937: not a single-group group function
-- 必须添加group by子句以计算每个department_id的last_name。
sql> select department_id,job_id,count(last_name)
2 from employees
3 group by deepartment_id;
group by deepartment_id
*
error at line 3:
ora-00904: "deepartment_id": invalid identifier
-- 在group by中添加job_id或从select列表中删除job_id列。您不能使用where子句来限制组。
您可以使用having子句来限制组。
您不能在where子句中使用聚合函数。
错误示例
sql> select department_id,avg(salary)
2 from employees
3 where avg(salary) > 8000
4 group by department_id;
where avg(salary) > 8000
*
error at line 3:
ora-00934: group function is not allowed here
-- 不能使用where子句来限制分组2.3 使用having子句限制分组结果集
当您使用having子句时，oracle服务器对组的限制如下:
1.行被分组。
2.应用分组功能。
3.显示与having子句匹配的组。
使用having的限制
不能引用非分组列：having条件中不能使用未在group by中指定的列
不能使用别名：不能直接使用select子句中定义的别名，必须使用原始列名或聚合函数
不能包含子查询：having条件中不能嵌套子查询
语法
select column, group_function from table [where condition] [group by group_by_expression] [having group_condition] [order by column];
使用having子句示例
查看平均工资大于8000的部门
sql> select department_id,avg(salary)
2 from employees
3 group by department_id
4 having avg(salary)>8000;
department_id avg(salary)
------------- -----------
110 10154
90 19333.3333
70 10000
20 9500
100 8601.33333
80 8955.88235
6 rows selected.查询工资总和大于13000且不含’rep‘字符的岗位，并且按照工资总和升序排序
sql> select job_id,sum(salary) payroll 2 from employees 3 where job_id not like '%rep%' 4 group by job_id 5 having sum(salary)>13000 6 order by sum(salary); job_id payroll ---------- ---------- pu_clerk 13900 ad_pres 24000 it_prog 28800 ad_vp 34000 st_man 36400 fi_account 39600 st_clerk 55700 sa_man 61000 sh_clerk 64300 9 rows selected.
三、嵌套聚合函数
聚合函数可以嵌套使用
示例
显示最高平均工资
sql> select max(avg(salary))
2 from employees
3 group by department_id;
max(avg(salary))
----------------
19333.3333总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论