oracle 11g 007 sql 第5章 Group Functions 笔记

oracle 11g 007 sql 第5章 Group Functions and groub by and having 笔记

5.1 what are group Functions ------什么叫做组函数

常见的组函数分为：AVG(平均值)、count（计数函数）、max（最大值）、min（最小值）还有
Stddev（标准方差）、SUM（求和值）。

组函数的语法：
SELECT     group_function(column), ...
FROM   table
[WHERE   condition]
[ORDER BY  column];

5.2组函数是输入多个，输出一个的。

5.2.1 组函数不包括Null值，组函数是对多行进行操作的。

5.2.2组函数的例子：求头衔为'%REP%'的平均值、最大值、最小值、总值。

SQL> SELECT AVG(salary), MAX(salary),
  2         MIN(salary), SUM(salary)
  3  FROM   employees
  4  WHERE  job_id LIKE '%REP%';
 
AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY)
----------- ----------- ----------- -----------
8272.727272       11500        6000      273000

5.2.3组函数还可以用在date里，例子如下：

SQL> select min(hire_date),max(hire_date)
  2  from employees;
 
MIN(HIRE_DATE) MAX(HIRE_DATE)
-------------- --------------
1987/6/17      2000/4/21

5.2.4使用组函数count的例子：count是统计有多少数据。

COUNT(*) returns the number of rows in a table:

SQL> select count(*) from
  2  employees where department_id=50;
 
  COUNT(*)
----------
        45

COUNT(expr) returns the number of rows with non-null values for expr:
求出非空的值有多少行

SQL> select count(commission_pct)
  2  from employees
  3  where department_id=80;
 
COUNT(COMMISSION_PCT)
---------------------
                   34
                  
5.3 在count里使用distinct 关键字：
5.3.1.using the distinct keyword

5.3.2.COUNT(DISTINCT expr) returns the number of distinct non-null values of expr.
在count里使用distinct求出非空值的数。

5.3.3.To display the number of distinct department values in the EMPLOYEES table
在employees表中求出distinct的count(department_id)有几行记录。

SQL> select count (distinct department_id)
  2  from employees;
 
COUNT(DISTINCTDEPARTMENT_ID)
----------------------------
                          11
                         
5.4 Group Functions and Null Values

组函数会忽略哪些包含的Null值。
Group functions ignore null values in the column例子：

SQL> select avg(commission_pct)
  2  from employees;
 
AVG(COMMISSION_PCT)
-------------------
  0.222857142857143
 
Null值必须在组函数列里面。
The NVL function forces group functions to include null values例子:

SQL> select avg(nvl(commission_pct,0))
  2  from employees;
 
AVG(NVL(COMMISSION_PCT,0))
--------------------------
        0.0728971962616822

5.5 Grouping rows:GROUP BY clause、HAVING clause 用法：

5.5.1 GROUP BY clause：

group by与语法如下：

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

You can divide rows in a table into smaller groups by using the GROUP BY clause.
使用group by子句，你能在这一张表划分较小的的组（行）。

5.5.2 Using the GROUP BY Clause

使用 group by 的例子：求雇员表中的平均工资且按照部门ID划分。

SQL> select department_id,avg(salary)
  2  from employees
  3  group by department_id;
 
DEPARTMENT_ID AVG(SALARY)
------------- -----------
          100        8600
           30        4150
                     7000
           20        9500
           70       10000
           90 19333.33333
          110       10150
           50 3475.555555
           40        6500
           80 8955.882352
           10        4400
           60        5760
 
12 rows selected

5.5.3 Using the GROUP BY Clause on Multiple Columns
使用 group by 按照多行进行划分。例子如下：

SQL> select department_id,job_id,sum(salary)
  2  from employees
  3  group by department_id,job_id
  4  order by department_id;
 
DEPARTMENT_ID JOB_ID     SUM(SALARY)
------------- ---------- -----------
           10 AD_ASST           4400
           20 MK_MAN           13000
           20 MK_REP            6000
           30 PU_CLERK         13900
           30 PU_MAN           11000
           40 HR_REP            6500
           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           12000
          110 AC_ACCOUNT        8300
          110 AC_MGR           12000
              SA_REP            7000
 
20 rows selected

5.6 Restricting Group Results with the HAVING Clause

having的语法如下：

SELECT    column, group_function
FROM      table
[WHERE    condition]
[GROUP BY group_by_expression]
[HAVING   group_condition]
[ORDER BY column];

having语句的用法例子如下：

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

以上就是having的用法，注意的是大家可能会想干嘛不用where子句，这里如果使用where子句会报错，因为where子句不能使用组函数。

5.7 Nesting group functions
嵌套组函数例子如下：

SQL> select max(avg(salary))
  2  from employees
  3  group by department_id;
 
MAX(AVG(SALARY))
----------------
19333.3333333333