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:将字符串从分组中连接成具有各种选项(如DISTINCTORDER BYSEPARATOR)的字符串。

查询语句: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)标准差函数
 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值