MySQL函数之聚合函数

目录

1.聚合函数

(1)AVG:计算一组值或表达式的平均值

(2)count:计算表中的行数

(3)sum:计算一组值或表达式的总和

(4)min/max:在一组值中找到最小值和最大值

(5)instr: 返回子字符串在字符串中第一次出现的位置

(6)group_concat:将字符串从分组中连接成具有各种选项(如DISTINCT，ORDER BY和SEPARATOR)的字符串。

(7)标准差函数

---

1.聚合函数

(1)AVG:计算一组值或表达式的平均值

查询语句：SELECT expense_id,AVG(total) AS total FROM bms_bills_memo GROUP BY  expense_id
结果：    1	    3.00000000
          5	    100111.00000000
          6	    178.50000000
          7	    40084.40000000
          8	    114.25000000
          12	50346.25000000
          13	200000.00000000

可以使用FORMAT函数来格式化AVG函数返回的平均值

查询语句：SELECT expense_id,FORMAT(AVG(total),2) AS total FROM bms_bills_memo GROUP BY  expense_id
结果：1	3.00
      5	100,111.00
      6	178.50
      7	40,084.40
      8	114.25
      12 50,346.25
      13 200,000.00

可以使用AVG()函数通过添加DISTINCT运算符来计算不同总价的平均值

select expense_id,AVG(distinct total) FROM bms_bills_memo GROUP BY  expense_id

可以用AVG函数中的HAVING子句中为分组的平均值设置条件

select expense_id,AVG(distinct total) as totalsum FROM bms_bills_memo GROUP BY  expense_id HAVING totalsum > 50

AVG()函数忽略计算中的NULL值

AVG()函数可以和控制流函数相结合

要计算Classic Cars产品线的平均价格与所有产品的平均价格的比例

SELECT AVG(IF(productline='Classic Cars',buyprice,NULL)) / AVG(buyprice) 'Classic Cars/ Products'
FROM products;

(2)count:计算表中的行数

COUNT(*)函数返回由SELECT语句返回的结果集中的行数。COUNT(*)函数计算包含NULL和非NULL值的行，即：所有行

查询语句：SELECT expense_id,COUNT(*) AS count FROM bms_bills_memo GROUP BY  expense_id
结果：    1	15
          5	4
          6	2
          7	5
          8	4
          12 4
          13 2

COUNT(expression)返回不包含NULL值的行数。

查询语句：select count(supplier_name) from bms_bills_memo
结果：13
总共21条，这里只返回supplier_name不是null的函数

MySQL COUNT(DISTINCT expression)返回不包含NULL值的唯一行数。

查询语句：select count( distinct supplier_name) from bms_bills_memo
结果：4
总共21条，去掉supplier_name是null，再去掉重复的，件数为4

可以结合distinct函数对某个项目去重并去掉null后，计算件数

SELECT expense_id,COUNT(distinct total) AS count FROM bms_bills_memo GROUP BY  expense_id

还可以结合having函数和控制流函数进行处理，这里就不一一介绍了

(3)sum:计算一组值或表达式的总和

如果在没有返回匹配行SELECT语句中使用SUM函数，则SUM函数返回NULL，而不是0。

DISTINCT运算符允许计算集合中的不同值。

SUM函数忽略计算中的NULL值。

FORMAT()函数用于格式化SUM()函数的返回值

结合group by，having，limit等进行使用

如果没有匹配的行，则SUM函数返回NULL值。 有时，您希望SUM函数返回0而不是NULL。 在这种情况下，可以使用COALESCE函数。COALESCE函数接受两个参数，如果第一个参数为NULL，则返回第二个参数，否则返回第一个参数

SELECT COALESCE(SUM(quantityOrdered * priceEach),0)
FROM orderdetails
WHERE productCode = 'S1_212121';

(4)min/max:在一组值中找到最小值和最大值

SELECT expense_id,MAX(total) ,MIN(total)FROM bms_bills_memo GROUP BY  expense_id

(5)instr: 返回子字符串在字符串中第一次出现的位置

查询语句：select instr('kjgy iku','gy')
结果      3

INSTR()函数返回字符串中子字符串第一次出现的位置。如果在str中找不到子字符串，则INSTR()函数返回零(0)。

INSTR函数默认是不区分大小写，可以通过BINARY来指定区分大小写

查询语句：select instr('kjgy iku',BINARY 'Gy')
结果：    0

INSTR函数与LIKE运算符可以实现和LIKE运算符相同的功能

//instr
SELECT 
    productName
FROM
    products
WHERE
    INSTR(productname,'Car') > 0;


//like
SELECT 
    productname
FROM
    products
WHERE
    productname LIKE '%Car%';

(6)group_concat:将字符串从分组中连接成具有各种选项(如DISTINCT，ORDER BY和SEPARATOR)的字符串。

查询语句：select expense_id,GROUP_CONCAT(distinct total order by total SEPARATOR '-') from bms_bills_memo GROUP BY expense_id
结果：5	111.0000-333.0000-200000.0000
     6	123.0000-234.0000
     7	88.0000-123.0000-200000.0000
     8	3.0000-33.0000-88.0000-333.0000
     12	66.0000-88.0000-1231.0000-200000.0000
     13	200000.0000

一些常见的示例

• 用逗号分隔的用户角色，如“管理员，作者，编辑人员”。
• 生成逗号分隔的用户的爱好，如“设计，编程，阅读”。
• 为博客帖子，文章或产品创建标签，例如“mysql，mysql聚合函数，mysql教程”。

GROUP_CONCAT函数可以与CONCAT_WS函数相结合

• 首先，使用CONCAT_WS函数连接每个客户联系人的姓氏和名字，结果是联系人的全名。
• 然后，使用GROUP_CONCAT函数来创建列表。

SELECT 
    GROUP_CONCAT(CONCAT_WS(', ', contactLastName, contactFirstName)
        SEPARATOR ';')
FROM
    customers;

(7)标准差函数