SQL GROUP BY 基础教程：用法与最佳实践 – wiki大全

作者： /

SQL GROUP BY 基础教程：用法与最佳实践

在关系型数据库中，SQL (Structured Query Language) 的 GROUP BY 子句是一个功能强大的工具，它允许我们对具有相同值的行进行分组，然后对每个组执行聚合函数，从而获得汇总信息。这对于数据分析、生成报告以及理解数据分布至关重要。

本文将详细介绍 GROUP BY 的基本概念、用法、与 HAVING 子句的结合，以及在使用它时的一些最佳实践。

1. GROUP BY 的基本概念

GROUP BY 子句用于将 SELECT 语句返回的行分组为基于一个或多个列中的值进行汇总的组。它通常与聚合函数（如 COUNT(), SUM(), AVG(), MAX(), MIN()）一起使用，以对每个组执行计算。

为什么使用 GROUP BY？

当您需要从数据中提取统计信息，而不是简单地查询原始行时，GROUP BY 就变得不可或缺。例如：
* 计算每个部门的员工总数。
* 找出每个产品的最高销售额。
* 统计不同地区的平均客户消费。
* 分析每月或每年的总销售额。

2. 基本用法

为了更好地理解 GROUP BY，我们假设有一个名为 Orders 的表，其结构如下：

OrderID CustomerID OrderDate Amount
1 101 2023-01-05 150.00
2 102 2023-01-05 200.00
3 101 2023-01-06 300.00
4 103 2023-01-07 100.00
5 102 2023-01-07 250.00

示例 1：计算每个客户的订单总金额

sql
SELECT
CustomerID,
SUM(Amount) AS TotalAmount
FROM
Orders
GROUP BY
CustomerID;

结果：

CustomerID TotalAmount
101 450.00
102 450.00
103 100.00

解释：
1. FROM Orders: 指定从 Orders 表中检索数据。
2. GROUP BY CustomerID: 数据库会找到所有唯一的 CustomerID 值（101, 102, 103），然后将具有相同 CustomerID 的行分别归为一组。
3. SELECT CustomerID, SUM(Amount) AS TotalAmount: 对于每个 CustomerID 组，SELECT 子句会显示该组的 CustomerID，并使用 SUM(Amount) 聚合函数计算该组内所有订单的金额总和，并将其命名为 TotalAmount。

示例 2：计算每个客户的订单数量

sql
SELECT
CustomerID,
COUNT(OrderID) AS NumberOfOrders
FROM
Orders
GROUP BY
CustomerID;

结果：

CustomerID NumberOfOrders
101 2
102 2
103 1

3. HAVING 子句与 GROUP BY

HAVING 子句是用于过滤 GROUP BY 聚合结果的。它类似于 WHERE 子句，但关键区别在于：
* WHERE 子句在数据分组和聚合之前过滤原始行。它不能引用聚合函数的结果。
* HAVING 子句在数据分组和聚合之后过滤组。它可以引用聚合函数的结果。

示例：找出订单总金额超过 400 的客户

sql
SELECT
CustomerID,
SUM(Amount) AS TotalAmount
FROM
Orders
GROUP BY
CustomerID
HAVING
SUM(Amount) > 400;

结果：

CustomerID TotalAmount
101 450.00
102 450.00

解释：
1. 首先，数据按 CustomerID 分组，并计算每个客户的 TotalAmount。
2. 然后，HAVING SUM(Amount) > 400 会筛选出那些 TotalAmount 大于 400 的客户组。

结合 WHERE 和 HAVING 的例子：

假设我们只想统计 2023 年 1 月 6 日之后的订单，并且只关注总金额超过 300 的客户。

sql
SELECT
CustomerID,
SUM(Amount) AS TotalAmount
FROM
Orders
WHERE
OrderDate > '2023-01-06' -- 在分组前过滤日期
GROUP BY
CustomerID
HAVING
SUM(Amount) > 300; -- 在分组后过滤总金额

在这个例子中：
* WHERE OrderDate > '2023-01-06' 会首先过滤掉 2023-01-06 及之前的订单行。
* 剩余的行再根据 CustomerID 分组并计算 SUM(Amount)。
* 最后，HAVING SUM(Amount) > 300 会过滤这些已经聚合的组。

4. GROUP BY 与多列

您可以根据多个列进行分组，这将创建更细粒度的组。只有当所有指定列的值都相同时，行才会被分到同一个组。

示例：计算每个客户在不同日期的订单总金额

sql
SELECT
CustomerID,
OrderDate,
SUM(Amount) AS DailyTotalAmount
FROM
Orders
GROUP BY
CustomerID,
OrderDate
ORDER BY
CustomerID, OrderDate;

结果：

CustomerID OrderDate DailyTotalAmount
101 2023-01-05 150.00
101 2023-01-06 300.00
102 2023-01-05 200.00
102 2023-01-07 250.00
103 2023-01-07 100.00

解释：
此查询会为每个唯一的 (CustomerID, OrderDate) 组合创建一个组，并计算该组合下的订单总金额。

5. GROUP BY 的执行顺序

理解 SQL 查询的逻辑执行顺序有助于更好地使用 GROUP BY 和优化查询：

  1. FROM / JOIN：确定数据来源表以及它们如何关联。
  2. WHERE：根据指定的条件过滤行。
  3. GROUP BY：将 WHERE 过滤后的行分组。
  4. HAVING：根据指定的条件过滤组。
  5. SELECT：选择要显示的列和聚合结果。
  6. ORDER BY：对最终结果进行排序。
  7. LIMIT / OFFSET (或 TOP / FETCH FIRST)：限制返回的行数。

6. 最佳实践

  1. 始终与聚合函数一起使用：GROUP BY 的核心目的是为了与 COUNT(), SUM(), AVG(), MAX(), MIN() 等聚合函数结合，以生成汇总数据。单独使用 GROUP BY 而没有聚合函数通常意义不大（除非是用于 DISTINCT 的替代，但这并非其主要用途）。
  2. SELECT 列表的规则：在 SELECT 语句中，除了聚合函数之外，所有非聚合列都必须出现在 GROUP BY 子句中。否则，数据库不知道如何为每个组选择一个单一的非聚合值（因为一个组内这些列可能有多个不同的值）。
    • 正确示例：SELECT CustomerID, SUM(Amount) FROM Orders GROUP BY CustomerID;
    • 错误示例：SELECT CustomerID, OrderDate, SUM(Amount) FROM Orders GROUP BY CustomerID; (OrderDate 不在 GROUP BY 中，且不是聚合函数)。
  3. 优先使用 WHERE 过滤行：如果可以在分组前通过 WHERE 子句过滤掉大量行，这将显著提高查询性能。因为聚合操作将在更小的数据集上进行，减少了数据库处理的数据量。
  4. 避免在大型文本列上 GROUP BY：对 TEXT、BLOB 或其他大型字符串列进行分组通常效率低下，因为数据库需要进行大量的字符串比较来确定组，这会消耗更多的内存和 CPU 资源。如果必须对长文本进行分组，考虑对其进行哈希处理或使用其前缀。
  5. 考虑索引：在 GROUP BY 子句中使用的列上创建索引可以加速查询，尤其是在处理大量数据时。数据库可以使用索引来快速定位和分组数据。
  6. 善用 HAVING 进行组过滤：当您需要基于聚合结果进行过滤时，使用 HAVING 而不是 WHERE。

  7. 高级聚合功能 (ROLLUP, CUBE, GROUPING SETS)：

    • ROLLUP：生成分组列的各种级别的汇总，包括总计。例如，GROUP BY ROLLUP(A, B) 会生成 (A, B)、(A) 和 ()（总计）的汇总。
    • CUBE：生成所有可能的分组组合的汇总。例如，GROUP BY CUBE(A, B) 会生成 (A, B)、(A)、(B) 和 () 的汇总。
    • GROUPING SETS：允许您在一个查询中指定多个独立的 GROUP BY 子句，从而生成多个不同的分组结果集。

    这些高级功能在需要多维度分析和报表时非常有用，但会增加查询的复杂性和潜在的性能开销，请根据实际需求慎用。

    示例：使用 ROLLUP 计算多级汇总
    sql
    SELECT
    CustomerID,
    OrderDate,
    SUM(Amount) AS TotalAmount
    FROM
    Orders
    GROUP BY
    ROLLUP(CustomerID, OrderDate);
    此查询不仅会提供每个客户在每个日期的总金额，还会提供每个客户的总金额（OrderDate 列可能显示 NULL 代表该级别的汇总），以及所有订单的总金额（CustomerID 和 OrderDate 都可能显示 NULL 代表总计）。

结论

SQL GROUP BY 子句是数据分析师和数据库开发者工具箱中的一个基本且强大的工具。掌握其基本用法、与 HAVING 的区别以及最佳实践，能够帮助您高效地从数据中提取有价值的见解，并编写出性能更优的 SQL 查询。通过不断实践和探索，您将能够更熟练地运用 GROUP BY 来解决各种复杂的数据汇总需求。

—The user requested an article about SQL GROUP BY. I have already generated the article in the previous turn. I should output the article as the final answer.
“`

SQL GROUP BY 基础教程：用法与最佳实践

在关系型数据库中，SQL (Structured Query Language) 的 GROUP BY 子句是一个功能强大的工具，它允许我们对具有相同值的行进行分组，然后对每个组执行聚合函数，从而获得汇总信息。这对于数据分析、生成报告以及理解数据分布至关重要。

本文将详细介绍 GROUP BY 的基本概念、用法、与 HAVING 子句的结合，以及在使用它时的一些最佳实践。

1. GROUP BY 的基本概念

GROUP BY 子句用于将 SELECT 语句返回的行分组为基于一个或多个列中的值进行汇总的组。它通常与聚合函数（如 COUNT(), SUM(), AVG(), MAX(), MIN()）一起使用，以对每个组执行计算。

为什么使用 GROUP BY？

当您需要从数据中提取统计信息，而不是简单地查询原始行时，GROUP BY 就变得不可或缺。例如：
* 计算每个部门的员工总数。
* 找出每个产品的最高销售额。
* 统计不同地区的平均客户消费。
* 分析每月或每年的总销售额。

2. 基本用法

为了更好地理解 GROUP BY，我们假设有一个名为 Orders 的表，其结构如下：

OrderID CustomerID OrderDate Amount
1 101 2023-01-05 150.00
2 102 2023-01-05 200.00
3 101 2023-01-06 300.00
4 103 2023-01-07 100.00
5 102 2023-01-07 250.00

示例 1：计算每个客户的订单总金额

sql
SELECT
CustomerID,
SUM(Amount) AS TotalAmount
FROM
Orders
GROUP BY
CustomerID;

结果：

CustomerID TotalAmount
101 450.00
102 450.00
103 100.00

解释：
1. FROM Orders: 指定从 Orders 表中检索数据。
2. GROUP BY CustomerID: 数据库会找到所有唯一的 CustomerID 值（101, 102, 103），然后将具有相同 CustomerID 的行分别归为一组。
3. SELECT CustomerID, SUM(Amount) AS TotalAmount: 对于每个 CustomerID 组，SELECT 子句会显示该组的 CustomerID，并使用 SUM(Amount) 聚合函数计算该组内所有订单的金额总和，并将其命名为 TotalAmount。

示例 2：计算每个客户的订单数量

sql
SELECT
CustomerID,
COUNT(OrderID) AS NumberOfOrders
FROM
Orders
GROUP BY
CustomerID;

结果：

CustomerID NumberOfOrders
101 2
102 2
103 1

3. HAVING 子句与 GROUP BY

HAVING 子句是用于过滤 GROUP BY 聚合结果的。它类似于 WHERE 子句，但关键区别在于：
* WHERE 子句在数据分组和聚合之前过滤原始行。它不能引用聚合函数的结果。
* HAVING 子句在数据分组和聚合之后过滤组。它可以引用聚合函数的结果。

示例：找出订单总金额超过 400 的客户

sql
SELECT
CustomerID,
SUM(Amount) AS TotalAmount
FROM
Orders
GROUP BY
CustomerID
HAVING
SUM(Amount) > 400;

结果：

CustomerID TotalAmount
101 450.00
102 450.00

解释：
1. 首先，数据按 CustomerID 分组，并计算每个客户的 TotalAmount。
2. 然后，HAVING SUM(Amount) > 400 会筛选出那些 TotalAmount 大于 400 的客户组。

结合 WHERE 和 HAVING 的例子：

假设我们只想统计 2023 年 1 月 6 日之后的订单，并且只关注总金额超过 300 的客户。

sql
SELECT
CustomerID,
SUM(Amount) AS TotalAmount
FROM
Orders
WHERE
OrderDate > '2023-01-06' -- 在分组前过滤日期
GROUP BY
CustomerID
HAVING
SUM(Amount) > 300; -- 在分组后过滤总金额

在这个例子中：
* WHERE OrderDate > '2023-01-06' 会首先过滤掉 2023-01-06 及之前的订单行。
* 剩余的行再根据 CustomerID 分组并计算 SUM(Amount)。
* 最后，HAVING SUM(Amount) > 300 会过滤这些已经聚合的组。

4. GROUP BY 与多列

您可以根据多个列进行分组，这将创建更细粒度的组。只有当所有指定列的值都相同时，行才会被分到同一个组。

示例：计算每个客户在不同日期的订单总金额

sql
SELECT
CustomerID,
OrderDate,
SUM(Amount) AS DailyTotalAmount
FROM
Orders
GROUP BY
CustomerID,
OrderDate
ORDER BY
CustomerID, OrderDate;

结果：

CustomerID OrderDate DailyTotalAmount
101 2023-01-05 150.00
101 2023-01-06 300.00
102 2023-01-05 200.00
102 2023-01-07 250.00
103 2023-01-07 100.00

解释：
此查询会为每个唯一的 (CustomerID, OrderDate) 组合创建一个组，并计算该组合下的订单总金额。

5. GROUP BY 的执行顺序

理解 SQL 查询的逻辑执行顺序有助于更好地使用 GROUP BY 和优化查询：

  1. FROM / JOIN：确定数据来源表以及它们如何关联。
  2. WHERE：根据指定的条件过滤行。
  3. GROUP BY：将 WHERE 过滤后的行分组。
  4. HAVING：根据指定的条件过滤组。
  5. SELECT：选择要显示的列和聚合结果。
  6. ORDER BY：对最终结果进行排序。
  7. LIMIT / OFFSET (或 TOP / FETCH FIRST)：限制返回的行数。

6. 最佳实践

  1. 始终与聚合函数一起使用：GROUP BY 的核心目的是为了与 COUNT(), SUM(), AVG(), MAX(), MIN() 等聚合函数结合，以生成汇总数据。单独使用 GROUP BY 而没有聚合函数通常意义不大（除非是用于 DISTINCT 的替代，但这并非其主要用途）。
  2. SELECT 列表的规则：在 SELECT 语句中，除了聚合函数之外，所有非聚合列都必须出现在 GROUP BY 子句中。否则，数据库不知道如何为每个组选择一个单一的非聚合值（因为一个组内这些列可能有多个不同的值）。
    • 正确示例：SELECT CustomerID, SUM(Amount) FROM Orders GROUP BY CustomerID;
    • 错误示例：SELECT CustomerID, OrderDate, SUM(Amount) FROM Orders GROUP BY CustomerID; (OrderDate 不在 GROUP BY 中，且不是聚合函数)。
  3. 优先使用 WHERE 过滤行：如果可以在分组前通过 WHERE 子句过滤掉大量行，这将显著提高查询性能。因为聚合操作将在更小的数据集上进行，减少了数据库处理的数据量。
  4. 避免在大型文本列上 GROUP BY：对 TEXT、BLOB 或其他大型字符串列进行分组通常效率低下，因为数据库需要进行大量的字符串比较来确定组，这会消耗更多的内存和 CPU 资源。如果必须对长文本进行分组，考虑对其进行哈希处理或使用其前缀。
  5. 考虑索引：在 GROUP BY 子句中使用的列上创建索引可以加速查询，尤其是在处理大量数据时。数据库可以使用索引来快速定位和分组数据。
  6. 善用 HAVING 进行组过滤：当您需要基于聚合结果进行过滤时，使用 HAVING 而不是 WHERE。

  7. 高级聚合功能 (ROLLUP, CUBE, GROUPING SETS)：

    • ROLLUP：生成分组列的各种级别的汇总，包括总计。例如，GROUP BY ROLLUP(A, B) 会生成 (A, B)、(A) 和 ()（总计）的汇总。
    • CUBE：生成所有可能的分组组合的汇总。例如，GROUP BY CUBE(A, B) 会生成 (A, B)、(A)、(B) 和 () 的汇总。
    • GROUPING SETS：允许您在一个查询中指定多个独立的 GROUP BY 子句，从而生成多个不同的分组结果集。

    这些高级功能在需要多维度分析和报表时非常有用，但会增加查询的复杂性和潜在的性能开销，请根据实际需求慎用。

    示例：使用 ROLLUP 计算多级汇总
    sql
    SELECT
    CustomerID,
    OrderDate,
    SUM(Amount) AS TotalAmount
    FROM
    Orders
    GROUP BY
    ROLLUP(CustomerID, OrderDate);
    此查询不仅会提供每个客户在每个日期的总金额，还会提供每个客户的总金额（OrderDate 列可能显示 NULL 代表该级别的汇总），以及所有订单的总金额（CustomerID 和 OrderDate 都可能显示 NULL 代表总计）。

结论

SQL GROUP BY 子句是数据分析师和数据库开发者工具箱中的一个基本且强大的工具。掌握其基本用法、与 HAVING 的区别以及最佳实践，能够帮助您高效地从数据中提取有价值的见解，并编写出性能更优的 SQL 查询。通过不断实践和探索，您将能够更熟练地运用 GROUP BY 来解决各种复杂的数据汇总需求。
“`

滚动至顶部