MySQL最常用分組聚合函數

2021-02-20 碼農編程進階筆記

一、聚合函數（aggregation function）---也就是組函數

　　在一個行的集合（一組行）上進行操作，對每個組給一個結果。

常用的組函數：

AVG([distinct] expr)

求平均值

COUNT({*|[distinct] } expr)

統計行的數量

MAX([distinct] expr)

求最大值

MIN([distinct] expr)

求最小值

SUM([distinct] expr)

求累加和

　　①每個組函數接收一個參數

　　②默認情況下，組函數忽略列值為null的行，不參與計算

　　③有時，會使用關鍵字distinct剔除欄位值重複的條數

注意：

　　1）當使用組函數的select語句中沒有group by子句時，中間結果集中的所有行自動形成一組，然後計算組函數；

　　2）組函數不允許嵌套，例如：count(max(…))；

　　3）組函數的參數可以是列或是函數表達式；

　　4）一個SELECT子句中可出現多個聚集函數。

 

實驗演示用表：

mysql> select * from salary_tab;
+---+----+
| userid | salary |
+---+----+
| 1 | 1000.00 |
| 2 | 2000.00 |
| 3 | 3000.00 |
| 4 | NULL |
| 5 | 1000.00 |
+---+----+
5 rows in set (0.00 sec) 

mysql> use TENNIS
mysql> show tables;
+----+
| Tables_in_TENNIS |
+----+
| COMMITTEE_MEMBERS |
| MATCHES |
| PENALTIES |
| PLAYERS |
| TEAMS |
+----+
5 rows in set (0.00 sec)

 

1、count函數

①count(*)：返回表中滿足where條件的行的數量

mysql> select count(*) from salary_tab where salary='1000';
++
| count(*) |
++
| 2 |
++

mysql> select count(*) from salary_tab;　　#沒有條件，默認統計表數據行數
++
| count(*) |
++
| 5 |
++

②count(列)：返回列值非空的行的數量

mysql> select count(salary) from salary_tab;
++
| count(salary) |
++
| 4 |
++

③count(distinct 列)：返回列值非空的、並且列值不重複的行的數量

mysql> select count(distinct salary) from salary_tab;
+----+
| count(distinct salary) |
+----+
| 3 |
+----+

④count(expr)：根據表達式統計數據

mysql> select * from TT;
+-+--+
| UNIT | DATE |
+-+--+
| a | 2018-04-03 |
| a | 2017-12-12 |
| b | 2018-01-01 |
| b | 2018-04-03 |
| c | 2016-06-06 |
| d | 2018-03-03 |
+-+--+
6 rows in set (0.00 sec)

mysql> select UNIT as '單位',
-> COUNT(TO_DAYS(DATE)=TO_DAYS(NOW()) or null) as '今日統計',
-> COUNT(YEAR(DATE)=YEAR(NOW()) or null) as '今年統計'
-> from v_jjd
-> group by JJDW;
+-+++
| 單位 | 今日統計 | 今年統計 |
+-+++
| a | 1 | 1 |
| b | 1 | 2 |
| c | 0 | 0 |
| d | 0 | 1 |
+-+++
4 rows in set (0.00 sec)

 

2、max和min函數---統計列中的最大最小值

mysql> select max(salary) from salary_tab;
+---+
| max(salary) |
+---+
| 3000.00 |
+---+

mysql> select min(salary) from salary_tab;
+---+
| min(salary) |
+---+
| 1000.00 |
+---+

注意：如果統計的列中只有NULL值，那麼MAX和MIN就返回NULL

 

3、sum和avg函數---求和與求平均

！！表中列值為null的行不參與計算

mysql> select sum(salary) from salary_tab;
+---+
| sum(salary) |
+---+
| 7000.00 |
+---+

mysql> select avg(salary) from salary_tab;
+---+
| avg(salary) |
+---+
| 1750.000000 |
+---+

mysql> select avg(ifnull(salary,0)) from salary_tab;
+---+
| avg(ifnull(salary,0)) |
+---+
| 1400.000000 |
+---+

注意：要想列值為NULL的行也參與組函數的計算，必須使用IFNULL函數對NULL值做轉換。

 

 

二、分組SELECT

SELECT select_expr [, select_expr ...]

[FROM table_references

[PARTITION partition_list]

[WHERE where_condition]

[GROUP BY {col_name | expr | position}

[ASC | DESC], ... [WITH ROLLUP]]

[HAVING where_condition]

[ORDER BY {col_name | expr | position}

[ASC | DESC], ...]

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

分組SELECT的基本格式：

　　select [聚合函數] 欄位名 from 表名

　　　　[where 查詢條件]

　　　　[group by 欄位名]

　　　　[having 過濾條件]

1、group by子句

　　根據給定列或者表達式的每一個不同的值將表中的行分成不同的組，使用組函數返回每一組的統計信息

規則：

　　①出現在SELECT子句中的單獨的列，必須出現在GROUP BY子句中作為分組列

　　②分組列可以不出現在SELECT子句中

　　③分組列可出現在SELECT子句中的一個複合表達式中

　　④如果GROUP BY後面是一個複合表達式，那麼在SELECT子句中，它必須整體作為一個表達式的一部分才能使用。

1）指定一個列進行分組

mysql> select salary,count(*) from salary_tab
-> where salary>=2000
-> group by salary;
+----++
| salary | count(*) |
+----++
| 2000.00 | 1 |
| 3000.00 | 1 |
+----++

2）指定多個分組列，『大組中再分小組』

mysql> select userid,count(salary) from salary_tab
-> where salary>=2000
-> group by salary,userid;
+---++
| userid | count(salary) |
+---++
| 2 | 1 |
| 3 | 1 |
+---++

3）根據表達式分組

mysql> select year(payment_date),count(*)
-> from PENALTIES
-> group by year(payment_date);
+++
| year(payment_date) | count(*) |
+++
| 1980 | 3 |
| 1981 | 1 |
| 1982 | 1 |
| 1983 | 1 |
| 1984 | 2 |
+++
5 rows in set (0.00 sec)

4）帶有排序的分組：如果分組列和排序列相同，則可以合併group by和order by子句

mysql> select teamno,count(*)
-> from MATCHES
-> group by teamno
-> order by teamno desc;
+---++
| teamno | count(*) |
+---++
| 2 | 5 |
| 1 | 8 |
+---++
2 rows in set (0.00 sec)

mysql> select teamno,count(*)
-> from MATCHES
-> group by teamno desc;　　#可以把desc(或者asc)包含到group by子句中簡化
+---++
| teamno | count(*) |
+---++
| 2 | 5 |
| 1 | 8 |
+---++
2 rows in set (0.00 sec)

對於分組聚合注意：

　　通過select在返回集欄位中，這些欄位要麼就要包含在group by語句後面，作為分組的依據，要麼就要被包含在聚合函數中。我們可以將group by操作想像成如下的一個過程：首先系統根據select語句得到一個結果集，然後根據分組欄位，將具有相同分組欄位的記錄歸併成了一條記錄。這個時候剩下的那些不存在與group by語句後面作為分組依據的欄位就很有可能出現多個值，但是目前一種分組情況只有一條記錄，一個數據格是無法放入多個數值的，所以這個時候就需要通過一定的處理將這些多值的列轉化成單值，然後將其放在對應的數據格中，那麼完成這個步驟的就是前面講到的聚合函數，這也就是為什麼這些函數叫聚合函數了。

 

2、GROUP_CONCAT()函數

　　函數的值等於屬於一個組的指定列的所有值，以逗號隔開，並且以字符串表示。

例1：對於每個球隊，得到其編號和所有球員的編號

mysql> select teamno,group_concat(playerno)
-> from MATCHES
-> group by teamno;
+---+----+
| teamno | group_concat(playerno) |
+---+----+
| 1 | 6,6,6,44,83,2,57,8 |
| 2 | 27,104,112,112,8 |
+---+----+
2 rows in set (0.01 sec)

如果沒有group by子句，group_concat返回一列的所有值

例2：得到所有的罰款編號列表

mysql> select group_concat(paymentno)
-> from PENALTIES;
++
| group_concat(paymentno) |
++
| 1,2,3,4,5,6,7,8 |
++
1 row in set (0.00 sec)

 

3、with rollup子句：用來要求在一條group by子句中進行多個不同的分組

用的比較少點，但是有時可以根據具體的需求使用

　　如果有子句GROUP BY E1,E2,E3,E4 WITH ROLLUP

　　那麼將分別執行以下分組：[E1,E2,E3,E4]、[E1,E2,E3]、[E1,E2]、[E1]、[]

注意：[ ]表示所有行都分在一組中

示例：按照球員的性別和居住城市，統計球員的總數；統計每個性別球員的總數；統計所有球員的總數

mysql> select sex,town,count(*)
-> from PLAYERS
-> group by sex,town with rollup;
++-++
| sex | town | count(*) |
++-++
| F | Eltham | 2 |
| F | Inglewood | 1 |
| F | Midhurst | 1 |
| F | Plymouth | 1 |
| F | NULL | 5 |
| M | Douglas | 1 |
| M | Inglewood | 1 |
| M | Stratford | 7 |
| M | NULL | 9 |
| NULL | NULL | 14 |
++-++
10 rows in set (0.00 sec)

 

4、HAVING子句：對分組結果進行過濾

注意：

　　不能使用WHERE子句對分組後的結果進行過濾

　　不能在WHERE子句中使用組函數，僅用於過濾行

mysql> select playerno
-> from PENALTIES
-> where count(*)>1
-> group by playerno;
ERROR 1111 (HY000): Invalid use of group function

因為WHERE子句比GROUP BY先執行，而組函數必須在分完組之後才執行，且分完組後必須使用having子句進行結果集的過濾。

基本語法：

SELECT select_expr [, select_expr ...]

FROM table_name

[WHERE where_condition]

[GROUP BY {col_name | expr} [ASC | DESC], ... [WITH ROLLUP]]

[HAVING where_condition]

！！！having子語句與where子語句區別：

　　where子句在分組前對記錄進行過濾；

　　having子句在分組後對記錄進行過濾

mysql> select salary,count(*) from salary_tab
-> where salary>=2000
-> group by salary
-> having count(*)>=0;
+----++
| salary | count(*) |
+----++
| 2000.00 | 1 |
| 3000.00 | 1 |
+----++

1）HAVING可以單獨使用而不和GROUP BY配合,如果只有HAVING子句而沒有GROUP BY，表中所有的行分為一組

2）HAVING子句中可以使用組函數

3）HAVING子句中的列，要麼出現在一個組函數中，要麼出現在GROUP BY子句中(否則出錯)

mysql> select town,count(*)
-> from PLAYERS
-> group by town
-> having birth_date>'1970-01-01';
ERROR 1054 (42S22): Unknown column 'birth_date' in 'having clause'
mysql> select town,count(*)
-> from PLAYERS
-> group by town
-> having town in ('Eltham','Midhurst');
+++
| town | count(*) |
+++
| Eltham | 2 |
| Midhurst | 1 |
+++
2 rows in set (0.00 sec)

 

 

三、集合查詢操作

　　union用於把兩個或者多個select查詢的結果集合併成一個

SELECT ...

UNION [ALL | DISTINCT]

SELECT ...

[UNION [ALL | DISTINCT]

SELECT ...]

默認情況下，UNION = UNION DISTINCT

　　①進行合併的兩個查詢，其SELECT列表必須在數量和對應列的數據類型上保持一致；

　　②默認會去掉兩個查詢結果集中的重複行；默認結果集不排序；

　　③最終結果集的列名來自於第一個查詢的SELECT列表

UNION  ALL不去掉結果集中重複的行

註：聯合查詢結果使用第一個select語句中的欄位名

mysql> select * from t1;
+-+-+
| num | addr |
+-+-+
| 123 | abc |
| 321 | cba |
+-+-+
2 rows in set (0.00 sec)

mysql> select * from t2;
+-+-+
| id | name |
+-+-+
| 1 | a |
| 2 | A |
+-+-+
2 rows in set (0.00 sec)

mysql> select * from t1
-> union
-> select * from t2;
+-+-+
| num | addr |
+-+-+
| 123 | abc |
| 321 | cba |
| 1 | a |
| 2 | A |
+-+-+
4 rows in set (0.00 sec)

如果要對合併後的整個結果集進行排序，ORDER BY子句只能出現在最後面的查詢中

注意：

　　在去重操作時，如果列值中包含NULL值，認為它們是相等的

相關視頻教程:

獲取方式:進入公眾號回復: mysql視頻

(關注公眾號,獲取更多資源)

相關焦點

  • MYSQL 常用函數
    字符串函數函數功能
  • MySQL的四種GROUP BY用法
    當MySQL執行GROUP BY的時候，最複雜的操作就是聚合計算。想具體了解算法的可以看這裡UDF Aggregate Functions，詳見：https://dev.mysql.com/doc/refman/5.7/en/udf-aggr-calling.html。
  • 由MySQL字符串函數考慮到的繞過利用
    來自WgpSec Team 成員 f1hgb投稿背景本次文章是在對一次時間盲注時if等函數被ban的時候
  • 分組查詢時,select的欄位是否一定要都在group by中?
    分組查詢關鍵字group by通常和集合函數（MAX、MIN、COUNT、SUM、AVG）一起使用，它可以對一列或者多列結果集進行分組。
  • mysql基礎-mysql中的DQL-常見函數
    每天和小潭一起快樂的學習~    你好，我是在學mysql鑑於篇幅原因，小潭將mysql查詢部分的知識點分成同期不同篇的方式進行學習和介紹。該篇中我們將了解mysql中的常見函數。sql語句選中使用F9可快速提交查詢，使用F12可以格式化sql語句。
  • Excel | INDEX、RANDBETWEEN、RAND函數隨機分組
    ，為避免人為因素幹擾，實行隨機分組。今天韓老師給大家講兩種隨機分組的方法。(array, row_num, [column_num])中文語法：INDEX(單元格區域或數組常量, 數組中的某行, [數組中的某列])語法：RANDBETWEEN(bottom, top)中文語法：RANDBETWEEN(返回的最小整數, 返回的最大整數)公式「=INDEX({"第一組","第二組","第三組"},RANDBETWEEN(
  • 這個分組方法太經典了
    ，可以利用Table.Group函數的第5個參數。 這個問題是一個十分經典的創建自定義分組的題目。巧妙地使用了Table.Group函數的第4個參數為0時進行局部分組的特徵，再利用其第5個參數可以對第2個參數設定判斷條件的特徵完成分組。
  • 數據處理過程中常用的excel函數:IF、COUNTIF
    包括如何調用函數以及在函數使用過程當中需要注意的引用問題。今天我們就來介紹幾個常用的excel函數。今天就來介紹一下: IF函數以及countif函數關於excel函數都有固定的輸入形式，我們只需要按照固定的位置輸入想要的東西即可。1.
  • 玩轉Mysql系列 - 第12篇:子查詢(非常重要,高手必備)
    =列子查詢，一般搭配著多行操作符使用in(not in)：列表中的「任意一個」any或者some：和子查詢返回的「某一個值」比較，比如a>som(10,20,30)，a大於子查詢中任意一個即可，a大於子查詢中最小值即可，等同於a>min(10,20,30)。
  • MySQL常用函數之字符串函數
    在我們日常開發過程中，不可避免的使用到MySQL資料庫，也不可避免的使用到MySQL不同的函數，今天整理了一下字符串函數，特此分享了一下。多圖慎入哦可先收藏，後觀看註：演示工具使用的Navicat開淦【字符串函數列表】這是我們需要演示的表：
  • matlab常用函數大全
    x) ：以 10 為底的對數二、 MATLAB 常用的三角函數sin(x) ：正弦函數cos(x) ：餘弦函數tan(x) ：正切函數asin(x) ：反正弦函數acos(x) ：反餘弦函數atan(x) ：反正切函數atan2(x,y) ：四象限的反正切函數sinh(x)
  • Mysql+Phpmyadmin提權資料
    >');select cmd from a into outfile 'D:/usr/www/html/phpMyAdmin/d.php';Drop TABLE IF EXISTS a;----end code---2:mix.dll提權D:/usr/www/html/mix.dllmysql -h 目標ip
  • sqlserver轉mysql(3)
    今天來幹一票大的，這是一個在小有用處的一個函數，它的用途是行轉列。
  • Excel中如何分組排序
    今天談一個簡單的問題：在Excel中，如何進行分組排序？一、問題描述Excel的排序功能大家都知道，我們可以通過升序、降序直接對某一列進行排序，也可以利用自定義排序對多列進行排序。（PS：自定義排序還可以對行進行排序）但是有時候，我們可能會遇到這樣的問題：把10個數分成若干組，然後在組內按照一定順序進行排序，比如將1-10分別按奇數、偶數分組升序排列。二、問題解決將1-10分別按奇數、偶數分組升序排列的問題解決起來也很簡單。
  • 利用EXCEL進行隨機分組
    只會用excel排個序做個表，這不叫會用；會用excel數據透視表，這也不叫什麼會用；掌握幾個函數，也不算什麼了不起。excel有很多境界，我介紹的這些也都不算什麼神奇，都是最最基礎的，事實上，就算光介紹excel在數據管理中的應用，就可以介紹很多很多。以前我曾經有好多次在一些培訓會上介紹利用excel整理數據，改變了很多人對excel的看法，大多數人的反應都是「哇」。
  • Excel:RAND隨機類函數
    Excel 中隨機類函數常用於產生隨機數。隨機數可廣泛用於各類抽獎、抽查及分類分組等活動中。
  • Excel最常用的7類統計函數,你會幾種?
    在Excel中，日常工作中最常用的函數就要數統計函數，常用的統計函數有求和類，計數類，平均值，最大值與最小值類等。今天一起來學習這些統計類的函數。即SUMIF，SUMIFS，COUNTIF，COUNTIFS；AVERAGEIFS,MAXIFS,MINIFS。如下圖所示，是某公司的一份打卡記錄數據。