SQL中的常用聚合函數是什么

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

1）COUNT

語法：COUNT(e1)

參數：e1為一個表達式，可以是任意的數據類型

返回：返回數值型數據

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

2）SUM,

語法：SUM(e1)

參數：e1為類型為數值型的表達式

返回：返回數值型數據

作用：對e1指定的列進行求和計算

3）MIN, MAX

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

參數：e1為一個字符型、日期型或數值類型的表達式。

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

返回：根據e1參數的類型，返回對應類型的數據。

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

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

4）AVG

語法：AVG(e1)

參數：e1為一個數值類型的表達式

返回：返回一個數值類型數據

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

5）MEDIAN

語法：MEDIAN(e1)

參數：e1為一個數值或日期類型的表達式

返回：返回一個數值或日期類型的數據

作用：首先，根據e1表達式指定的列，對值進行排序；

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

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

6）RANK

1）用法1:RANK OVER

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

為分析函數，為每條記錄產生一個序列號，并返回。

  參數：column1為列名，指定按照哪一列進行分類（分組）

 column2為列名，指定根據哪列排序，默認為升序；

 若指定了分類子句（PARTITION BY），則對每類進行排序（每個分類單獨排序）

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

  作用：為分析函數，對記錄先按column1分類，再對每個分類進行排序，并為每條記錄分配一個序號（每個分類單獨排序）

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

  實例：student表記錄了學生每科的成績，要求按學科排序，并獲取每科分數前兩名的記錄

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     高數               80

Wang      0113077    高數               70

Zhang     0113098    高數               80

Li             0113101    高數               90

 8 rows selected

按學科分類，按成績排序（降序）

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    高數               90

         2            Luo           0113011    高數               80

         2            Zhang      0113098    高數               80

         4            Wang       0113077    高數               70

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

并且之后的記錄的序號是不連續的。

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

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 )

為聚合函數，返回一個值。

參數：expr1為1個或多個常量表達式；

        expr2為如下格式的表達式：

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

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

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

實際是expr1需要與expr3相匹配

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

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

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

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

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

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

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

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

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

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

實例：假設一個員工的薪水為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

由結果可知，薪水為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 ]      

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

e2指定以哪個字段為依據，進行排序；

e3指定以哪個字段為依據，進行分類（分組）；

當指定OVER PARTITION BY子句后，針對分類后的每個類單獨排序；

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

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

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

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

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

該字段不是排序關鍵字段e2

實例：

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

已知表內容如下：

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三條記錄。

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

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