【数据库设计和SQL基础语法】--查询数据--分组查询

一、分组查询概述

1.1 什么是分组查询

分组查询是一种 SQL 查询技术，通过使用 GROUP BY 子句，将具有相同值的数据行分组在一起，然后对每个组应用聚合函数（如 COUNT、SUM、AVG等）。这允许在数据集中执行汇总和统计操作，以便更清晰地理解和分析数据的特征。分组查询常用于对大量数据进行聚合和摘要，提供有关数据分布和特征的洞察。

1.2 分组查询的作用

以下是分组查询的一些主要作用：

1. 数据汇总： 分组查询可以用于对数据进行汇总，计算每个分组的总和、平均值、最大值、最小值等统计信息。这对于了解数据的整体特征以及各个分组之间的差异非常有用。
2. 数据分类： 当需要按照某个列对数据进行分类时，分组查询是很有帮助的。例如，你可以按照地区、部门、时间等将数据进行分组，以便更好地理解和分析。
3. 统计分析： 分组查询支持对数据进行更深入的统计分析。通过结合分组查询和聚合函数，可以得到更详细的数据摘要，有助于发现数据中的模式和趋势。
4. 筛选数据： 通过将数据分组并应用条件，可以轻松地筛选出符合特定条件的数据子集。这使得可以对关键数据进行更有针对性的分析。
5. 提高查询性能： 在处理大量数据时，分组查询有时可以优化查询性能。通过将数据分组，数据库引擎可以更有效地执行聚合计算，减少处理的数据量，提高查询速度。

示例（使用SQL语句）：

-- 以部门为单位，计算每个部门的员工数量和平均工资
SELECT department, COUNT(*) AS employee_count, AVG(salary) AS average_salary
FROM employees
GROUP BY department;

在上面的示例中，数据按照部门进行分组，然后分别计算每个部门的员工数量和平均工资。这样就能够以更清晰的方式了解不同部门的情况。

二、GROUP BY 子句

2.1 GROUP BY 的基本语法

在 SQL 中，GROUP BY 语句用于对结果集进行分组。其基本语法如下：

SELECT column1, column2, aggregate_function(column3)
FROM table
WHERE condition
GROUP BY column1, column2;

• SELECT: 要检索的列或表达式。
• FROM: 数据来源的表。
• WHERE: （可选）筛选条件，用于过滤要分组的数据。
• GROUP BY: 指定分组的列。查询结果将按照这些列中的值进行分组。
• aggregate_function: 对每个分组执行的聚合函数，如 COUNT、SUM、AVG、MAX、MIN 等。

以下是一个具体的例子：

-- 以部门为单位，计算每个部门的员工数量和平均工资
SELECT department, COUNT(*) AS employee_count, AVG(salary) AS average_salary
FROM employees
GROUP BY department;

在这个例子中，employees 表按照 department 列进行分组，然后对每个部门计算员工数量和平均工资。

Tip：SELECT 中的列必须是 GROUP BY 子句中列的函数，或者是聚合函数。如果在 SELECT 中引用了未在 GROUP BY 中列出的列，那么该列的值将是该分组中第一个遇到的值，这在某些数据库系统中是允许的，但在其他系统中可能导致错误。

2.2 GROUP BY 的多列分组

在 GROUP BY 子句中，你可以指定多列进行分组，以更精细地组织数据。多列分组的基本语法如下：

SELECT column1, column2, aggregate_function(column3)
FROM table
WHERE condition
GROUP BY column1, column2;

这里 column1 和 column2 是你希望用来进行分组的列。查询结果将按照这两列中的值进行分组。

举个例子，假设你有一个订单表（orders），包含了订单信息，包括订单日期（order_date）、客户ID（customer_id）和订单总额（total_amount）。你想要按照订单日期和客户ID对订单进行分组，并计算每个组的订单总额。

-- 按照订单日期和客户ID分组，计算每个组的订单总额
SELECT order_date, customer_id, SUM(total_amount) AS total_order_amount
FROM orders
GROUP BY order_date, customer_id;

在这个例子中，订单表按照订单日期和客户ID进行了分组，并计算了每个组的订单总额。通过 GROUP BY 子句，你可以看到每个特定日期和客户ID的订单总额。这种多列分组使你能够更详细地了解数据的组织结构。

2.3 GROUP BY 与聚合函数结合

GROUP BY 与聚合函数结合使用是非常常见的数据库查询模式。通过将 GROUP BY 与聚合函数一起使用，可以对分组的数据执行各种聚合计算。以下是一个示例，演示了如何使用 GROUP BY 与聚合函数：
假设有一个销售订单表（sales_orders），包含了订单的信息，如订单日期（order_date）、产品ID（product_id）和销售数量（quantity）。

-- 按照产品ID分组，计算每个产品的总销售数量和平均销售数量
SELECT product_id, SUM(quantity) AS total_sales, AVG(quantity) AS average_sales
FROM sales_orders
GROUP BY product_id;

在这个例子中，我们按照产品ID进行分组，并使用了两个聚合函数，SUM 和 AVG。SUM 计算了每个产品的总销售数量，而 AVG 计算了每个产品的平均销售数量。通过 GROUP BY，查询结果中的每一行表示一个产品ID，以及与之相关的总销售数量和平均销售数量。
其他常用的聚合函数还包括 COUNT、MAX、MIN 等，可以根据需要选择适当的聚合函数。GROUP BY 与聚合函数结合使用，可以提供对数据更详细的摘要信息，帮助分析和理解数据。

三、HAVING 子句

3.1 HAVING 的作用

HAVING 子句是在 SQL 查询中用于过滤分组后的结果集的一种方式。它通常与 GROUP BY 一起使用，用于对分组数据应用条件过滤。HAVING 子句允许你筛选基于聚合函数计算的值，而 WHERE 子句则用于筛选原始数据行。
基本语法如下：

SELECT column1, aggregate_function(column2)
FROM table
WHERE condition
GROUP BY column1
HAVING condition;

其中，HAVING 子句的作用是对分组进行条件筛选，而 WHERE 子句是对原始数据行进行条件筛选。
举个例子，假设你有一个订单表（orders），包含了订单信息，包括订单日期（order_date）、客户ID（customer_id）和订单总额（total_amount）。你想找到总订单额超过1000的客户，并计算其总订单额。

-- 按照客户ID分组，计算每个客户的总订单额，然后筛选总订单额超过1000的客户
SELECT customer_id, SUM(total_amount) AS total_order_amount
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) > 1000;

在这个例子中，首先按照客户ID进行分组，然后使用 HAVING 子句筛选出总订单额超过1000的客户。这种方式可以用来对分组后的结果进行更细粒度的筛选，以便只保留满足特定条件的分组。

3.2 HAVING 的语法

HAVING 子句的语法如下：

SELECT column1, aggregate_function(column2)
FROM table
WHERE condition
GROUP BY column1
HAVING condition;

在这个语法中：

• SELECT: 指定要检索的列或表达式。
• FROM: 指定数据来源的表。
• WHERE: （可选）用于过滤原始数据行的条件。
• GROUP BY: 指定分组的列。
• HAVING: 用于对分组进行条件筛选的子句。

具体来说，HAVING 子句通常用于对分组后的结果应用条件。这些条件基于聚合函数计算的值，而不是原始数据行。这使得你可以过滤出满足特定聚合条件的分组结果。
以下是一个更具体的例子：

-- 按照部门分组，计算每个部门的平均工资，并只显示平均工资大于50000的部门
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

在这个例子中，HAVING 子句筛选出平均工资大于50000的部门，从而仅显示符合条件的分组结果。

四、分组排序

4.1 使用 ORDER BY 对分组结果排序

ORDER BY 子句用于对查询结果进行排序。当与 GROUP BY 一起使用时，ORDER BY 可以用来对分组结果进行排序。以下是使用 ORDER BY 对分组结果排序的基本语法：

SELECT column1, aggregate_function(column2)
FROM table
WHERE condition
GROUP BY column1
HAVING condition
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

在这个语法中：

• ORDER BY: 用于指定排序的列。
• ASC: 升序排序（默认）。
• DESC: 降序排序。

举例说明，假设你有一个销售订单表（sales_orders），包含了订单的信息，如订单日期（order_date）、产品ID（product_id）和销售数量（quantity）。你想要按照产品ID分组，计算每个产品的总销售数量，并按照总销售数量降序排序。

-- 按照产品ID分组，计算每个产品的总销售数量，按照总销售数量降序排序
SELECT product_id, SUM(quantity) AS total_sales
FROM sales_orders
GROUP BY product_id
ORDER BY total_sales DESC;

在这个例子中，ORDER BY total_sales DESC 指定了按照总销售数量降序排序。你可以根据需要指定多个排序条件，以便更精细地控制结果的排序顺序。

总的来说，ORDER BY 子句允许你对查询结果进行排序，使结果更易读或更符合你的需求。

4.2 GROUP BY 与 ORDER BY 的区别

GROUP BY 和 ORDER BY 是 SQL 查询中两个不同的子句，它们有着不同的作用：

1. GROUP BY:
   • 作用： GROUP BY 用于对查询结果进行分组，将相同的值放在一起，然后对每个组应用聚合函数，计算汇总值。
   • 使用场景： 当你想要对数据进行分组，并对每个组应用聚合函数（如 COUNT、SUM、AVG）以计算统计信息时，你会使用 GROUP BY。

SELECT column1, COUNT(column2)
FROM table
GROUP BY column1;

2. ORDER BY:
   • 作用： ORDER BY 用于对查询结果进行排序，可以按照一个或多个列的值进行升序或降序排序。
   • 使用场景： 当你想要对查询结果按照某一列或多列的值进行排序时，你会使用 ORDER BY。

SELECT column1, column2
FROM table
ORDER BY column1 DESC, column2 ASC;

区别总结：

• GROUP BY 用于分组和聚合数据，通常与聚合函数一起使用。
• ORDER BY 用于对查询结果进行排序，以更好地组织展示结果，不涉及数据的分组和聚合。

Tip：如果在 SELECT 语句中使用了 GROUP BY 子句，那么 ORDER BY 子句通常放在 GROUP BY 子句之后。这是因为排序通常是在分组之后进行的。例如：

SELECT column1, COUNT(column2) AS count_column2
FROM table
GROUP BY column1
ORDER BY count_column2 DESC;

五、分组集

5.1 GROUPING SETS 的概念

GROUPING SETS 是 SQL 中用于同时对多个分组集合进行聚合查询的一种语法。它允许你在单个查询中同时指定多个不同的分组，从而获取多个层次上的聚合结果。这样，你可以一次性获取多个聚合级别的数据，而不必多次执行相似的查询。
基本语法如下：

SELECT column1, column2, aggregate_function(column3)
FROM table
GROUP BY GROUPING SETS ((column1, column2), (column1), (column2), ());

其中，GROUPING SETS 子句的参数是一个包含多个分组集合的括号列表。每个分组集合都由一个或多个列组成，代表一个要进行聚合的分组。空括号 () 表示全局总计。
举个例子，假设你有一个销售订单表（sales_orders），包含了订单的信息，如订单日期（order_date）、产品ID（product_id）、区域（region）和销售数量（quantity）。你想同时获取按照产品ID、区域和全局总计的销售数量。

SELECT product_id, region, SUM(quantity) AS total_sales
FROM sales_orders
GROUP BY GROUPING SETS ((product_id, region), (product_id), (region), ());

在这个例子中，GROUPING SETS 子句允许你一次性获取按照产品ID、区域和全局总计的销售数量。这样，你可以在单个查询中获取多个层次上的聚合结果，而不必分别执行多个查询。

5.2 使用 GROUPING SETS 进行多组分组

GROUPING SETS 允许你一次性对多个组进行分组，并在同一查询中获取多个层次上的聚合结果。以下是一个示例，演示如何使用 GROUPING SETS 进行多组分组：
假设有一个销售订单表（sales_orders），包含了订单的信息，如订单日期（order_date）、产品ID（product_id）、区域（region）和销售数量（quantity）。

-- 使用 GROUPING SETS 进行多组分组，计算销售数量的总和
SELECT product_id, region, SUM(quantity) AS total_sales
FROM sales_orders
GROUP BY GROUPING SETS ((product_id, region), (product_id), (region), ());

在这个例子中，GROUP BY GROUPING SETS 指定了三个不同的分组集合：

1. (product_id, region): 按照产品ID和区域进行分组。
2. (product_id): 按照产品ID进行分组。
3. (region): 按照区域进行分组。
4. ()（空括号）: 表示全局总计。

这样，查询结果将包含按照产品ID和区域、按照产品ID、按照区域以及全局总计的销售数量。你可以在同一查询中获得这些不同层次的汇总信息。

六、ROLLUP 和 CUBE

6.1 ROLLUP 的使用

ROLLUP 是 SQL 中用于进行多层次聚合的操作符之一。它允许你在查询中指定多个层次的分组，并在同一查询中获取这些层次的汇总结果。ROLLUP 会生成包含从最精细到最总体的所有可能的组合的聚合结果。
基本的 ROLLUP 语法如下：

SELECT column1, column2, aggregate_function(column3)
FROM table
GROUP BY ROLLUP (column1, column2);

在这个语法中，ROLLUP 子句指定了要进行多层次分组的列，生成的结果将包含每个列组合的聚合值，以及每个列的总计值。
举个例子，假设你有一个销售订单表（sales_orders），包含了订单的信息，如订单日期（order_date）、产品ID（product_id）、区域（region）和销售数量（quantity）。

-- 使用 ROLLUP 进行多层次聚合，计算销售数量的总和
SELECT product_id, region, SUM(quantity) AS total_sales
FROM sales_orders
GROUP BY ROLLUP (product_id, region);

在这个例子中，ROLLUP (product_id, region) 将生成按照产品ID和区域、按照产品ID、按照区域和全局总计的销售数量的聚合结果。这样，你可以在同一查询中获得不同层次的汇总信息。
ROLLUP 提供了一种方便的方式，通过单一查询获取多个层次上的聚合结果，避免了多次执行类似的查询。需要注意的是，ROLLUP 生成的总计行会有 NULL 值，表示在该列上的总计。

6.2 CUBE 的使用

CUBE 是 SQL 中用于进行多维度聚合的操作符之一。它允许在同一查询中指定多个维度，并生成包含所有可能组合的聚合结果。CUBE 操作符生成的结果比 ROLLUP 更全面，因为它包含了所有可能的组合。
基本的 CUBE 语法如下：

SELECT column1, column2, aggregate_function(column3)
FROM table
GROUP BY CUBE (column1, column2);

在这个语法中，CUBE 子句指定了要进行多维度分组的列，生成的结果将包含每个列组合的聚合值，以及所有可能的列组合的总计值。
举个例子，假设你有一个销售订单表（sales_orders），包含了订单的信息，如订单日期（order_date）、产品ID（product_id）、区域（region）和销售数量（quantity）。

-- 使用 CUBE 进行多维度聚合，计算销售数量的总和
SELECT product_id, region, SUM(quantity) AS total_sales
FROM sales_orders
GROUP BY CUBE (product_id, region);

在这个例子中，CUBE (product_id, region) 将生成按照产品ID、按照区域、按照产品ID和区域、以及全局总计的销售数量的聚合结果。这样，你可以在同一查询中获得多个维度上的汇总信息。
CUBE 提供了一种方便的方式，通过单一查询获取多个维度上的聚合结果，避免了多次执行类似的查询。需要注意的是，CUBE 生成的总计行会有 NULL 值，表示在该列上的总计。

6.3 ROLLUP 与 CUBE 的区别

ROLLUP 和 CUBE 都是 SQL 中用于进行多层次聚合的操作符，它们的主要区别在于生成的聚合结果的全面性和维度的不同。

1. ROLLUP:

• 语法： 使用 ROLLUP 时，你指定一个列列表，表示要进行多层次分组的列。ROLLUP 生成一个包含每个列组合的聚合值，以及每个列的总计值。
• 示例：

  SELECT column1, column2, aggregate_function(column3)
  FROM table
  GROUP BY ROLLUP (column1, column2);
  

• 生成结果： 生成的结果包含了每个列的每个组合的聚合值，以及每个列的总计值。

2. CUBE:

• 语法： 使用 CUBE 时，你同样指定一个列列表，表示要进行多维度分组的列。CUBE 生成一个包含每个列组合的聚合值，以及所有可能的列组合的总计值。
• 示例：

  SELECT column1, column2, aggregate_function(column3)
  FROM table
  GROUP BY CUBE (column1, column2);
  

• 生成结果： 生成的结果包含了每个列的每个组合的聚合值，以及所有可能的列组合的总计值，更全面。

3. 区别总结：

• 结果全面性：

  • ROLLUP 生成的结果包含每个列的每个组合的聚合值，以及每个列的总计值。
  • CUBE 生成的结果不仅包含每个列的每个组合的聚合值，还包含所有可能的列组合的总计值。

• 维度数量：

  • ROLLUP 用于指定一组列进行分组。
  • CUBE 用于指定一组列进行多维度分组。

• 语法：

  • ROLLUP 使用 ROLLUP 子句。
  • CUBE 使用 CUBE 子句。

选择使用 ROLLUP 还是 CUBE 取决于你需要的分组层次和全面性。如果你只需要在一组列上进行层次分组，可以使用 ROLLUP。如果你希望同时获取多个列的所有可能组合的总计值，可以使用 CUBE。

七、 最佳实践和注意事项

在进行分组查询时，有一些最佳实践和注意事项可以帮助你编写更有效和可维护的 SQL 查询：

1. 选择适当的聚合函数： 根据你的需求选择正确的聚合函数，如 COUNT、SUM、AVG、MAX、MIN 等。确保聚合函数与你关心的信息一致。

2. 理解 GROUP BY 子句的含义： GROUP BY 子句指定了分组的条件，确保你理解每个分组的含义，以便正确计算聚合函数。

3. 使用别名提高可读性： 为列和聚合函数使用有意义的别名，提高查询结果的可读性。

   SELECT department, COUNT(*) AS employee_count
   FROM employees
   GROUP BY department;
   

4. *谨慎使用 SELECT ： 尽量避免使用 SELECT *，而是选择明确指定所需的列。这有助于提高查询的性能和可维护性。

5. 合理使用 WHERE 子句： 在 GROUP BY 之前使用 WHERE 子句过滤数据，以减小分组的数据集，提高查询性能。

6. 了解 HAVING 子句的使用场景： HAVING 子句用于在分组后对聚合结果进行筛选，要谨慎使用。通常，它用于过滤聚合值，而不是原始数据行。

7. 避免在 GROUP BY 中使用过多列： 尽量保持 GROUP BY 中列的数量较少，以防止生成过多的组合，从而降低性能。

8. 理解 ROLLUP 和 CUBE 的用途： ROLLUP 和 CUBE 允许你在一个查询中获得多个分组层次的聚合结果。选择使用它们时要确保理解它们的效果。

9. 考虑索引的影响： 确保表中使用了适当的索引，以提高 GROUP BY 操作的性能。

10. 测试和优化： 对于复杂的分组查询，进行测试和性能优化是重要的。使用数据库性能分析工具，确保查询在处理大量数据时仍然高效。

11. 文档化查询： 对于复杂的查询，添加注释以解释查询的目的和分组策略，提高查询的可理解性。

通过遵循这些最佳实践，你可以更好地编写和优化分组查询，以满足业务需求并提高查询性能。

八、总结

分组查询是SQL中重要的功能，通过GROUP BY子句将数据按指定列分组，结合聚合函数计算统计信息。ROLLUP和CUBE提供了多层次聚合的方式。在实践中，选择适当的聚合函数和理解GROUP BY的含义至关重要。使用别名、谨慎使用SELECT *、合理利用WHERE子句，都有助于提高可读性和性能。注意避免过多列的GROUP BY，理解HAVING的用途，以及测试和优化查询。最终，文档化查询并遵循最佳实践可确保编写高效、清晰的分组查询。