【Oracle學(xué)習(xí)09】分組函數(shù)

單行函數(shù)在結(jié)果集中返回各行的單值。分組或聚合函數(shù)作用于多行。

9.1 分組函數(shù)

分組函數(shù)

9.1.1 分組函數(shù)定義

分組函數(shù)做用于聚合數(shù)據(jù)，并且針對(duì)每個(gè)組返回單個(gè)結(jié)果。分組函數(shù)對(duì)每個(gè)組返回一個(gè)結(jié)果。

9.1.2 使用分組函數(shù)

AVG,SUM,MIN,MAX,COUNT返回?cái)?shù)字結(jié)果。 MIN,MAX還可以返回日期或字符結(jié)果。

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

1)COUNT

count(*) ：計(jì)算所有行(包括空值和重復(fù)值的行)的行數(shù)目。
count(expr)：若expr是列名，則會(huì)忽略空值。若不是列名，則會(huì)對(duì)每一行都計(jì)算。
count(distinct expr): 去重，若是列名，會(huì)忽略空值。希望有null,可以用nvl(colum,0)來處理。
count(all expr): 等同于count(expr) 。

count

#示例 
# count(1)等同于count(*)會(huì)計(jì)算每一行。
SQL> select count(*) , count(1) ,count(MANAGER_ID),count(distinct manager_id)  from employees;

  COUNT(*)   COUNT(1) COUNT(MANAGER_ID) COUNT(DISTINCTMANAGER_ID)
---------- ---------- ----------------- -------------------------
       107        107               106                        18
#忽略空值。
SQL> select count(distinct department_id),count(distinct nvl(department_id,0)) from employees;
COUNT(DISTINCTDEPARTMENT_ID) COUNT(DISTINCTNVL(DEPARTMENT_ID,0))
---------------------------- -----------------------------------
                          11                                  12

2)SUM

SUM( expr) : expr只能為數(shù)字，并且忽略空值。
SUM(DISTINCT expr)：對(duì)每一個(gè)唯一值進(jìn)行相加計(jì)算。

#SUM( expr) 略過空值
SQL> select sum(2),count(*),count(MANAGER_ID) from employees;
    SUM(2)   COUNT(*) COUNT(MANAGER_ID)
---------- ---------- -----------------
       214        107               106
#
SQL> select sum(salary),sum(distinct salary) from employees;
SUM(SALARY) SUM(DISTINCTSALARY)
----------- -------------------
     691400              397900

3)AVG 函數(shù)

AVG(all expr): 求和除以組中非空行數(shù)。
AVG(DISTINCT expr): 先得到distinct的記錄集，求和再除于distinct記錄集數(shù)。

image.png

#空的處理
SQL> SELECT COUNT(DISTINCT department_id),count(distinct nvl(department_id,0)) FROM   employees;

COUNT(DISTINCTDEPARTMENT_ID) COUNT(DISTINCTNVL(DEPARTMENT_ID,0))
---------------------------- -----------------------------------
                          11                                  12
#avg
SQL> select avg(2),sum(salary),sum(salary)/107,avg(salary),count(*) from employees;
    AVG(2) SUM(SALARY) SUM(SALARY)/107 AVG(SALARY)   COUNT(*)
---------- ----------- --------------- ----------- ----------
         2      691400      6461.68224  6461.68224        107

#AVG(DISTINCT expr) 是除于 distinct的58行，不是107行。
SQL> select sum(salary),sum(salary)/107,avg(salary),sum(distinct salary)/107,avg(distinct salary) from employees;
SUM(SALARY) SUM(SALARY)/107 AVG(SALARY) SUM(DISTINCTSALARY)/107 AVG(DISTINCTSALARY)
----------- --------------- ----------- ----------------------- -------------------
     691400      6461.68224  6461.68224              3718.69159          6980.70175

4）STDDEV/VARIANCE

STDDEV: 樣本標(biāo)準(zhǔn)偏差
VARIANCE:

SQL> select salary from employees where department_id=90;
    SALARY
----------
     24000
     17000
     17000

SQL> select avg(salary),variance(salary),stddev(salary) from employees where department_id=90;

AVG(SALARY) VARIANCE(SALARY) STDDEV(SALARY)
----------- ---------------- --------------
 19333.3333       16333333.3     4041.45188

5)MAX 和MIN

MAX ：作用于NUMBER,DATE,CHAR,VARCHAR2，取最大值，忽略空值。
MIN ：取最小值，忽略空值.

SQL> select min(commission_pct),max(commission_pct) from employees;
MIN(COMMISSION_PCT) MAX(COMMISSION_PCT)
------------------- -------------------
                 .1                  .4

SQL> select min(job_id),max(job_id) from employees;
MIN(JOB_ID)          MAX(JOB_ID)
-------------------- --------------------
AC_ACCOUNT           ST_MAN

6) LISTAGG函數(shù)

LISTAGG : 返回列值的字符串匯總。
語法: LISTAGG(expr,['delimiter']) WITHIN GROUP (ORDER_BY_CLAUSE)

SQL> select * from regions order by region_name;
 REGION_ID REGION_NAME
---------- --------------------------------------------------
         2 Americas
         3 Asia
         1 Europe
         4 Middle East and Africa

SQL> select listagg(region_name,',') within group (order by region_name desc ) from regions;

LISTAGG(REGION_NAME,',')WITHINGROUP(ORDERBYREGION_NAMEDESC)
---------------------------------------------------------------------------------------------------------------------
Middle East and Africa,Europe,Asia,Americas
#
select * from countries order by region_id ,country_name desc;
SQL> select listagg(country_name,',') within group (order by region_id,country_name desc ) countries from countries group by region_id;
COUNTRIES
----------------------------------------------------------------------------------------------------
United Kingdom,Switzerland,Netherlands,Italy,Germany,France,Denmark,Belgium
United States of America,Mexico,Canada,Brazil,Argentina
Singapore,Japan,India,HongKong,China,Australia
Zimbabwe,Zambia,Nigeria,Kuwait,Israel,Egypt

9.2 使用GROUP BY 子句分組數(shù)據(jù)

9.2.1 數(shù)據(jù)分組

語法

SQL> select count(distinct department_id),count(distinct nvl(department_id,0)) from employees;
COUNT(DISTINCTDEPARTMENT_ID) COUNT(DISTINCTNVL(DEPARTMENT_ID,0))
---------------------------- -----------------------------------
                          11                                  12
SQL> select distinct department_id from employees group by department_id;
DEPARTMENT_ID
-------------
          100
           30

           20
           70
           90
          110
           50
           40
           80
           10
           60
12 rows selected.

9.2.2 GROUP BY 子句

Group by 出現(xiàn)在where 和order by之間。
列必須是分組特性(grouping attribute,即group by 后的列名或表達(dá)式)或分組函數(shù)中。
Group by column 可以不出現(xiàn)在select 列中。

group by

having

#列必須是分組特性(grouping attribute,即group by 后的列名或表達(dá)式)或分組函數(shù)中
#All the columns in the SELECT list that are not in group functions must be in the GROUP BY clause.

SQL> select end_date,count(*) from job_history; --end_date不該出現(xiàn)
ERROR at line 1:
ORA-00937: not a single-group group function
#start_date不該出現(xiàn)
SQL> select end_date,start_date,count(*) from job_history group by end_date;
select end_date,start_date,count(*) from job_history group by end_date
                *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

#不能用 WHERE clause to restrict groups. 該使用having限制分組
SQL> SELECT   department_id, AVG(salary) FROM     employees
  WHERE    AVG(salary) > 8000
   GROUP BY department_id;
WHERE    AVG(salary) > 8000
         *
ERROR at line 2:
ORA-00934: group function is not allowed here

SQL> SELECT   department_id, AVG(salary) FROM     employees
 having    AVG(salary) > 8000
  GROUP BY department_id;
DEPARTMENT_ID AVG(SALARY)
------------- -----------
          100        8600
           20        9500
           70       10000
           90  19333.3333
          110       10150
           80  8955.88235

6 rows selected.


#終止工作的年份和人數(shù)。
SQL> select to_char(end_date,'yyyy') year, count(*) "number of employees" from job_history group by to_char(end_date,'yyyy') order by count(*) desc;

YEAR     number of employees
-------- -------------------
1999                       4
1998                       3
1993                       2
1997                       1

#The GROUP BY column does not have to be in the SELECT list.
SQL> SELECT   AVG(salary) FROM     employees
GROUP BY department_id ;
-----------
       8600
       4150
       7000
       9500
      10000
 19333.3333
      10150
 3475.55556
       6500
 8955.88235
       4400
       5760

9.2.3 按多列分組

可以數(shù)據(jù)集組進(jìn)一步劃分成多個(gè)子組。

SQL> select department_id,sum(commission_pct) from employees where commission_pct is not null group by department_id;
DEPARTMENT_ID SUM(COMMISSION_PCT)
------------- -------------------
                              .15
           80                7.65
#
SQL> select department_id,job_id,sum(commission_pct) from employees where commission_pct is not null group by department_id,job_id;
DEPARTMENT_ID JOB_ID               SUM(COMMISSION_PCT)
------------- -------------------- -------------------
           80 SA_REP                              6.15
           80 SA_MAN                               1.5
              SA_REP                               .15

9.2.4 嵌套的分組函數(shù)

單行函數(shù)可以嵌套或者多層嵌套，而分組函數(shù)只能嵌套兩層。

G1(G2(group_item)) = result : 是可以的。如sum(avg(group_item))。
G1(G2(G3(group_item)))這樣是不行的。如max(sum(agv(group_item))).

#可以嵌套
SQL> SELECT   MAX(AVG(salary)) FROM     employees GROUP BY department_id;
MAX(AVG(SALARY))
----------------
      19333.3333
SQL> select department_id,avg(salary) from employees GROUP BY department_id;
DEPARTMENT_ID AVG(SALARY)
------------- -----------
          100        8600
...
           90  19333.3333
          110       10150
           50  3475.55556
           40        6500
           80  8955.88235

SQL> SELECT   department_id,MAX(AVG(salary)) FROM employees GROUP BY department_id;
SELECT   department_id,MAX(AVG(salary)) FROM employees GROUP BY department_id
         *
ERROR at line 1:
ORA-00937: not a single-group group function

#各部分last_name的平均長(zhǎng)度之和
SQL> select sum(avg(length (last_name))) from employees group by department_id;
SUM(AVG(LENGTH(LAST_NAME)))
---------------------------
                 69.9261438

SQL> select department_id,avg(length (last_name)) from employees group by department_id;
DEPARTMENT_ID AVG(LENGTH(LAST_NAME))
------------- ----------------------
          100             5.83333333
           30                    6.5
                                   5
           20                      6
           70                      4
           90                      6
          110                      6
           50             6.02222222
           40                      6
           80             5.97058824
           10                      6
           60                    6.6
12 rows selected.

9.3 使用Having 子句包含或者排除分組行

9.3.1 限制分組結(jié)果

group by 子句將聚合函數(shù)應(yīng)用于組，返回組結(jié)果，having子句可以用來限制組級(jí)結(jié)果。

SQL> select department_id,count(*) from job_history where department_id in (50,60,80,110) group by department_id;

DEPARTMENT_ID   COUNT(*)
------------- ----------
           50          2
           60          1
           80          2
          110          2
#
SQL> select department_id,count(*) from job_history where department_id in (50,60,80,110) group by department_id having count(*) >1;

DEPARTMENT_ID   COUNT(*)
------------- ----------
           50          2
           80          2
          110          2

9.3.2 Having子句

having 通常在 group by 和order by 之間,但也可能出現(xiàn)在group by 前。
having 只能出現(xiàn)在有g(shù)roup by的SQL語句中。

SQL> select job_id ,avg(salary),count(*) from employees group by job_id having avg(salary)> 10000 order by job_id;

JOB_ID               AVG(SALARY)   COUNT(*)
-------------------- ----------- ----------
AC_MGR                     12000          1
AD_PRES                    24000          1
AD_VP                      17000          2
FI_MGR                     12000          1
MK_MAN                     13000          1
PU_MAN                     11000          1
SA_MAN                     12200          5

7 rows selected.

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

【Oracle學(xué)習(xí)09】分組函數(shù)

【Oracle學(xué)習(xí)09】分組函數(shù)

【Oracle學(xué)習(xí)09】分組函數(shù)

9.1 分組函數(shù)

9.1.1 分組函數(shù)定義

9.1.2 使用分組函數(shù)

9.2 使用GROUP BY 子句分組數(shù)據(jù)

9.2.1 數(shù)據(jù)分組

9.2.2 GROUP BY 子句

9.2.3 按多列分組

9.2.4 嵌套的分組函數(shù)

9.3 使用Having 子句包含或者排除分組行

9.3.1 限制分組結(jié)果

9.3.2 Having子句

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

【Oracle學(xué)習(xí)09】分組函數(shù)

【Oracle學(xué)習(xí)09】分組函數(shù)

9.1 分組函數(shù)

9.1.1 分組函數(shù)定義

9.1.2 使用分組函數(shù)

9.2 使用GROUP BY 子句分組數(shù)據(jù)

9.2.1 數(shù)據(jù)分組

9.2.2 GROUP BY 子句

9.2.3 按多列分組

9.2.4 嵌套的分組函數(shù)

9.3 使用Having 子句包含或者排除分組行

9.3.1 限制分組結(jié)果

9.3.2 Having子句

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av