oracle 组函数的使用

组函数的使用

1.特点：每组返回一个结果集。

avg:求平均值，会忽略null值。
count:求总个数，会包含相同值和null
max:求最大值，会忽略null值。
min:求最小值，会忽略null值。

sum:求总数，会忽略null值。

max，min ：求最大和最小值。
max和min里面可以是数字类型和字符类型

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

  MAX(SAL)   MIN(SAL) MAX(ENAME) MIN(ENAME)
---------- ---------- ---------- ----------
      5000        800 WARD       ADAMS

 这里的字符类型比较的是它的ASSCII值。

AVG,sum,variance,stddev 这些函数只能是数字类型的。

 

count函数的使用：
返回的是一个列的中行数。

count函数的三种形式：

count(*):返回行数，包含相同值，null值。

SQL> select count(*) total_rows from emp;

TOTAL_ROWS
----------
        14

count(expr)：返回不含null值的总和，会忽略null值。
SQL> select count(comm) from emp;

COUNT(COMM)
-----------
          4

count(distinct expr)：返回不含null值和不相同的值的总和，会忽略null值。

SQL> select count(distinct sal) from emp;

COUNT(DISTINCTSAL)
------------------
                12

在这几个函数中除了count(*)不会会忽略null值。
其他的函数都会忽略null，因为如果不忽略那么在计算的时候
时候，就会产生null值。null与任何数计算都为null。

但是在计算的时候，有时候需要那些没有值的列。
比如：计算所有员工的平均奖金。

SQL> select avg(comm) from emp;

 AVG(COMM)
----------
       550

SQL> select count(comm) from emp;

COUNT(COMM)
-----------
          4

SQL> select sum(comm) from emp;

 SUM(COMM)
----------
      2200

 这里只是计算四个人的。题目需要计算所有人员工。
 这里我们需要有NVL函数来处理null值。

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

AVG(NVL(COMM,0))
----------------
      157.142857

聚合函数：
如果select子句中的字段没有在聚合函数中，那么就必须出现在
group by 子句中。而出现在group by子句中不一定要出现在select子句中。

SQL> select deptno , avg(sal) from emp;
select deptno , avg(sal) from emp
       *
ERROR at line 1:
ORA-00937: not a single-group group function

正确写法：
SQL> select deptno , avg(sal) from emp
  2  group by deptno;

    DEPTNO   AVG(SAL)
---------- ----------
        30 1566.66667
        20       2175
        10 2916.66667

注意：聚合函数不能够用在where子句后面。

SQL> select deptno , avg(sal) from emp
  2  where avg(sal)>2000;
where avg(sal)>2000
      *
ERROR at line 2:
ORA-00934: group function is not allowed here

我们可以使用having来过滤分组过后的数据。

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

    DEPTNO   AVG(SAL)
---------- ----------
        20       2175
        10 2916.66667

having是过滤分组过后的数据，在having组函数不能用列别名。

select deptno , avg(sal)avg_sal from emp
group by deptno
having avg_sal>2000
SQL> /
having avg_sal>2000
       *
ERROR at line 3:
ORA-00904: "AVG_SAL": invalid identifier

因为这里是先从表中分组，在过滤统计过后的数据，然后在查询(这里是查询部门编号和分组后的平均工资)。
而给的别名是在分组过后。所以这里会报错：无效标示符 avg_sal.
having子句也可以用在group By 的前面，但是建议用在group by 的后面，以便于
逻辑理解。

having 和 where的区别：

1.having只能用在group by 后面。有having肯定有group by子句。
2.where子句用在having前面。
3.having 是用于分组统计过后的过滤，而where是过滤的是原始数据，也就是分组统计之前的数据。

如果要提高一个公司或者部门的平均工资，可以先过滤掉原始数据中低工资。
再算平均工资。