SQL中的常用聚合函數(shù)是什么

SQL中的常用聚合函數(shù)是什么，相信很多沒有經(jīng)驗的人對此束手無策，為此本文總結(jié)了問題出現(xiàn)的原因和解決方法，通過這篇文章希望你能解決這個問題。

1）COUNT

語法：COUNT(e1)

參數(shù)：e1為一個表達(dá)式，可以是任意的數(shù)據(jù)類型

返回：返回數(shù)值型數(shù)據(jù)

作用：返回e1指定列不為空的記錄總數(shù)

2）SUM,

語法：SUM(e1)

參數(shù)：e1為類型為數(shù)值型的表達(dá)式

返回：返回數(shù)值型數(shù)據(jù)

作用：對e1指定的列進(jìn)行求和計算

3）MIN, MAX

語法：MIN(e1)、MAX(e1)

參數(shù)：e1為一個字符型、日期型或數(shù)值類型的表達(dá)式。

若e1為字符型，則根據(jù)ASCII碼來判斷最大值與最小值。

返回：根據(jù)e1參數(shù)的類型，返回對應(yīng)類型的數(shù)據(jù)。

作用：MIN(e1)返回e1表達(dá)式指定的列中最小值；

     MAX(e1)返回e1表達(dá)式指定的列中最大值；

4）AVG

語法：AVG(e1)

參數(shù)：e1為一個數(shù)值類型的表達(dá)式

返回：返回一個數(shù)值類型數(shù)據(jù)

作用：對e1表達(dá)式指定的列，求平均值。

5）MEDIAN

語法：MEDIAN(e1)

參數(shù)：e1為一個數(shù)值或日期類型的表達(dá)式

返回：返回一個數(shù)值或日期類型的數(shù)據(jù)

作用：首先，根據(jù)e1表達(dá)式指定的列，對值進(jìn)行排序；

若排序后，總記錄為奇數(shù)，則返回排序隊列中，位于中間的值；

若排序后，總記錄為偶數(shù)，則對位于排序隊列中，中間兩個值進(jìn)行求平均，返回這個平均值；

6）RANK

1）用法1:RANK OVER

  語法：RANK( )  OVER ([ PARTITION BY column1 ] ORDER BY column2 [ASC|DESC])

為分析函數(shù)，為每條記錄產(chǎn)生一個序列號，并返回。

  參數(shù)：column1為列名，指定按照哪一列進(jìn)行分類（分組）

 column2為列名，指定根據(jù)哪列排序，默認(rèn)為升序；

 若指定了分類子句（PARTITION BY），則對每類進(jìn)行排序（每個分類單獨(dú)排序）

  返回：返回一個數(shù)值類型數(shù)據(jù)，作為該記錄的序號！

  作用：為分析函數(shù)，對記錄先按column1分類，再對每個分類進(jìn)行排序，并為每條記錄分配一個序號（每個分類單獨(dú)排序）

  注意：排序字段值相同的記錄，分配相同的序號。存在序號不連續(xù)的情況

  實例：student表記錄了學(xué)生每科的成績，要求按學(xué)科排序，并獲取每科分?jǐn)?shù)前兩名的記錄

student表如下：

SQL> select * from student order by kemu;

 NAME       ID                KEMU      FENSHU

---------- -------------- -------------- ----------------

Li            0113101     物理               80

Luo         0113011     物理               80

Wang     0113077     物理               70

Zhang     0113098    物理               90

Luo         0113011     高數(shù)               80

Wang      0113077    高數(shù)               70

Zhang     0113098    高數(shù)               80

Li             0113101    高數(shù)               90

 8 rows selected

按學(xué)科分類，按成績排序（降序）

SQL> select rank() over(partition by KEMU order by FENSHU desc) as sort,student.* from student;

       SORT    NAME        ID              KEMU      FENSHU

---------- ---------- ---------------- ------------ ----------

         1            Zhang      0113098    物理               90

        2            Li              0113101    物理               80

         2            Luo           0113011    物理               80

         4            Wang       0113077    物理               70

         1            Li              0113101    高數(shù)               90

         2            Luo           0113011    高數(shù)               80

         2            Zhang      0113098    高數(shù)               80

         4            Wang       0113077    高數(shù)               70

由返回記錄可了解，對排序列的值相同的記錄，rank為其分配了相同的序號（SORT NAME列）。

并且之后的記錄的序號是不連續(xù)的。

若獲取每科前兩名，只需對排序后的結(jié)果增加二次查詢即可

select * from 

(select rank() over(partition by KEMU order by FENSHU desc) as sort_id,student.* from student) st 

where st.sort_id<=2;

2)用法2：RANK WITHIN GROUP

語法： RANK( expr1 ) WITHIN GROUP ( ORDER BY expr2 )

為聚合函數(shù)，返回一個值。

參數(shù)：expr1為1個或多個常量表達(dá)式；

        expr2為如下格式的表達(dá)式：

             expr2的格式為'expr3 [ DESC | ASC ] [ NULLS { FIRST | LAST } ]'

其中，expr1需要與expr2相匹配，

即：expr1的常量表達(dá)式的類型、數(shù)量必須與ORDER BY子句后的expr2表達(dá)式的類型、數(shù)量相同

實際是expr1需要與expr3相匹配

如：RANK(a) WITHIN GROUP (ORDER BY b ASC NULLS FIRST);

   其中，a為常量，b需要是與相同類型的表達(dá)式

   RANK(a,b) WITHIN GROUP (ORDER BY c DESC NULLS LAST, d DESC NULLS LAST);

   其中，a與b都為常量；c是與a類型相同的表達(dá)式、d是與b類型相同的表達(dá)式；

返回：返回數(shù)值型數(shù)據(jù)，該值為假定記錄在表中的序號。

作用：確定一條假定的記錄，在表中排序后的序號。

  如：假定一條記錄（假設(shè)為r1）的expr2指定字段值為常量expr1，則將r1插入表中后，

與原表中的記錄，按照ORDER BY expr2排序后，該記錄r1在表中的序號為多少，返回該序號。

注釋： NULLS FIRST指定，將ORDER BY指定的排序字段為空值的記錄放在前邊；

NULLS LAST指定，將ORDER BY指定的排序字段為空值的記錄放在后邊；

實例：假設(shè)一個員工的薪水為1500，求該員工的薪水在員工表中的排名為多少？

已知員工表如下：

SQL> select * from employees;

EMP_ID     EMP_NAME     SALARY

---------- -------------------- ---------------

10001      ZhangSan             500

10002      LiSi                         1000

10003      WangWu               1500

10004      MaLiu                     2000

10005      NiuQi                      2500

SQL> select rank(1500) within group (order by salary) as "rank number" from employees;

rank number

-----------

          3

由結(jié)果可知，薪水為1500的員工，在表中按升序排序，序號為3

7）FIRST、LAST

語法：agg_function（e1） KEEP (DENSE_RANK FIRST ORDER BY e2 [NULLS {FIRST|LAST}]) [OVER PARTITION BY e3 ]

agg_function（e1） KEEP (DENSE_RANK LAST  ORDER BY e2 [NULLS {FIRST|LAST}]) [OVER PARTITION BY e3 ]      

參數(shù)：agg_function為一個聚合函數(shù)，可以為 MIN、MAX、SUM、AVG、COUNT、VARIANCE或STDDEV

e2指定以哪個字段為依據(jù)，進(jìn)行排序；

e3指定以哪個字段為依據(jù)，進(jìn)行分類（分組）；

當(dāng)指定OVER PARTITION BY子句后，針對分類后的每個類單獨(dú)排序；

DENSE_RANK為排序后的記錄分配序號，并且序號為連續(xù)的。

NULLS {FIRST|LAST}指定排序字段e1的值若為空，則拍在序列前邊（NULLS FIRST）或者后邊（NULLS LAST）

DENSE_RANK后的FIRST/LAST確定選取通過DENSE_RANK排好序后的序列中，序號最小/最大的記錄。序號相同時，返回多條記錄

當(dāng)序號相同，返回多條記錄時，agg_function(e1)聚合函數(shù)繼續(xù)對這多條記錄的e1字段做聚合操作。

作用：如果agg_function為min(e1)，獲取排序后的FIRST或LAST的多條記錄中，某字段e1的最小值

該字段不是排序關(guān)鍵字段e2

實例：

已知員工表有薪水字段，獎金字段。要求獲取薪水最低的員工中，獎金最高的員工的記錄。

已知表內(nèi)容如下：

SQL> select * from employees order by salary;

 EMP_ID     EMP_NAME           SALARY  COMMISSION

---------- ---------------------------- ------------  ------------

10001      ZhangSan                    500        200

10002      LiSi                                500        300

10003      WangWu                      500        100

10004      MaLiu                           2000       500

10005      NiuQi                            2500       200

10006      ShangDuo                   2500       300

10007      BaiQi                             2500       400

SQL> select max(commission) keep(dense_rank first order by salary asc) as commission from employees;

COMMISSION

----------

       300

首先，按salary排序后，獲取薪水最低的記錄，分別為員工10001、10002、10003三條記錄。

聚合函數(shù)max(commission)對3條記錄獲取獎金最高的為員工10002，獎金為300。

看完上述內(nèi)容，你們掌握SQL中的常用聚合函數(shù)是什么的方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！