MySQL(8)【聚合函数 | group by分组查询】

发布于:2024-11-28 ⋅ 阅读:(34) ⋅ 点赞:(0)

引言

在之前的文章中,我们学习了MySQL中如何对表进行增删改查操作。这次,我们要更进一步,聊聊MySQL中的聚合函数和GROUP BY分组查询。

一、聚合函数

1. 简介

聚合函数它们允许我们对一组值执行计算并返回单个值。这些函数通常与SELECT语句一起使用,特别是在结合GROUP BY语句进行分组查询时,能够揭示出数据集合中的统计信息或总体特征。

函数 说明
COUNT([DISTINCT] expr) 返回查询到的数据的数量。如果使用了DISTINCT,则计算不重复值的数量。
SUM([DISTINCT] expr) 返回查询到的数据的总和(仅对数值类型有效)。如果使用了DISTINCT,则对不重复的值求和。
AVG([DISTINCT] expr) 返回查询到的数据的平均值(仅对数值类型有效)。如果使用了DISTINCT,则对不重复的值计算平均值。
MAX([DISTINCT] expr) 返回查询到的数据的最大值(可适用于数值、字符串、日期等类型)。如果使用了DISTINCT,则从不重复的值中找出最大值。
MIN([DISTINCT] expr) 返回查询到的数据的最小值(可适用于数值、字符串、日期等类型)。如果使用了DISTINCT,则从不重复的值中找出最小值。

2. 使用示例

(1)COUNT() 函数

COUNT() 函数用于计算表中的行数或指定列中非NULL值的数量。

  • 计算表中的总行数

    SELECT COUNT(*) FROM table_name;
    

    这里,* 表示计算所有行,包括NULL值所在的行(因为COUNT(*)不区分NULL值)。

  • 计算指定列中非NULL值的数量

    SELECT COUNT(column_name) FROM table_name;
    

    如果column_name列中有NULL值,这些NULL值不会被计入总数。

  • 使用DISTINCT排除重复值

    SELECT COUNT(DISTINCT column_name) FROM table_name;
    

    这将计算column_name列中不同(非重复)值的数量。

(2)SUM() 函数

SUM() 函数用于计算数值列中所有值的总和。

  • 计算某列的总和

    SELECT SUM(column_name) FROM table_name;
    

    这里,column_name必须是数值类型,否则函数将返回错误或无意义的结果。

  • 结合WHERE子句进行条件求和

    SELECT SUM(column_name) FROM table_name WHERE condition;
    

    这将计算满足condition条件的column_name列的总和。

  • 使用DISTINCT排除重复值后再求和(虽然在实际应用中较少见,但理论上可行):

    SELECT SUM(DISTINCT column_name) FROM table_name;
    

    注意:在大多数情况下,对求和操作使用DISTINCT可能不是必要的,因为它会去除重复值,这可能会影响总和的计算结果。

(3)AVG() 函数

AVG() 函数用于计算数值列中所有值的平均值。

  • 计算某列的平均值

    SELECT AVG(column_name) FROM table_name;
    

    这里,column_name必须是数值类型。AVG()函数会自动忽略NULL值。

  • 结合WHERE子句进行条件平均值的计算

    SELECT AVG(column_name) FROM table_name WHERE condition;
    

    这将计算满足condition条件的column_name列的平均值。

(4)MAX() 函数

MAX() 函数用于找出某列中的最大值。

  • 找出某列的最大值

    SELECT MAX(column_name) FROM table_name;
    

    这里,column_name可以是数值类型、字符串类型或日期类型等。对于字符串类型,MAX()函数将按照字典序返回最大值。

(5)MIN() 函数

MIN() 函数用于找出某列中的最小值。

  • 找出某列的最小值

    SELECT MIN(column_name) FROM table_name;
    

    MAX()函数类似,column_name可以是多种数据类型,MIN()函数将返回该列中的最小值。

二、group by分组查询

1. 基本语法

SELECT column1, column2, ... FROM table GROUP BY column;

2. 按单个列分组

假设我们有一个名为 orders 的表,其中包含 customer_idorder_amount 两个字段,我们想要计算每个客户的订单总数。

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

这个查询将返回每个 customer_id 及其对应的订单数量。

3. 按多个列分组

如果我们还想按订单状态(order_status)进一步细分每个客户的订单数,我们可以按两个列进行分组。

SELECT customer_id, order_status, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id, order_status;

这个查询将返回每个 customer_idorder_status 组合及其对应的订单数量。

4. 结合聚合函数使用

我们还可以结合不同的聚合函数来使用 GROUP BY,以获取更丰富的统计信息。

SELECT customer_id,
       AVG(order_amount) AS average_order_amount,
       MAX(order_amount) AS max_order_amount,
       MIN(order_amount) AS min_order_amount,
       SUM(order_amount) AS total_order_amount
FROM orders
GROUP BY customer_id;

这个查询为每个 customer_id 计算了平均订单金额、最大订单金额、最小订单金额和总订单金额。

5. 使用 HAVING 过滤分组

有时,我们可能想要基于聚合函数的结果来过滤分组。这时,我们可以使用 HAVING 子句。

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;

这个查询返回了订单数大于 1 的 customer_id 及其订单数。

🚨🚨注意事项

  • 当使用 GROUP BY 时,SELECT 列表中的每个非聚合列都必须是 GROUP BY 子句中指定的列。
  • 聚合函数(如 COUNT(), SUM(), AVG(), MAX(), MIN())可以对分组后的数据进行计算。
  • HAVING 子句用于过滤分组后的结果,而 WHERE 子句用于过滤分组前的行。
  • SQL查询中各个关键字的执行先后顺序FROM & JOIN & ON > WHERE > GROUP BY > SELECT & DISTINCT > HAVING > ORDER BY > LIMIT