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

2023-12-17 13:51:33

一、分组查询概述

1.1 什么是分组查询

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

1.2 分组查询的作用

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

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

示例（使用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 查询中两个不同的子句，它们有着不同的作用：

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

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

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 指定了三个不同的分组集合：

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

这样，查询结果将包含按照产品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 中用于进行多层次聚合的操作符，它们的主要区别在于生成的聚合结果的全面性和维度的不同。

ROLLUP:

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

示例：

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

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

CUBE:

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

示例：

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

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

区别总结：

结果全面性：
- ROLLUP 生成的结果包含每个列的每个组合的聚合值，以及每个列的总计值。
- CUBE 生成的结果不仅包含每个列的每个组合的聚合值，还包含所有可能的列组合的总计值。
维度数量：
- ROLLUP 用于指定一组列进行分组。
- CUBE 用于指定一组列进行多维度分组。
语法：
- ROLLUP 使用 ROLLUP 子句。
- CUBE 使用 CUBE 子句。

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

七、最佳实践和注意事项

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

选择适当的聚合函数： 根据你的需求选择正确的聚合函数，如 COUNT、SUM、AVG、MAX、MIN 等。确保聚合函数与你关心的信息一致。
理解 GROUP BY 子句的含义： GROUP BY 子句指定了分组的条件，确保你理解每个分组的含义，以便正确计算聚合函数。
使用别名提高可读性： 为列和聚合函数使用有意义的别名，提高查询结果的可读性。
```
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;
```
*谨慎使用 SELECT ： 尽量避免使用 SELECT *，而是选择明确指定所需的列。这有助于提高查询的性能和可维护性。
合理使用 WHERE 子句： 在 GROUP BY 之前使用 WHERE 子句过滤数据，以减小分组的数据集，提高查询性能。
了解 HAVING 子句的使用场景： HAVING 子句用于在分组后对聚合结果进行筛选，要谨慎使用。通常，它用于过滤聚合值，而不是原始数据行。
避免在 GROUP BY 中使用过多列： 尽量保持 GROUP BY 中列的数量较少，以防止生成过多的组合，从而降低性能。
理解 ROLLUP 和 CUBE 的用途： ROLLUP 和 CUBE 允许你在一个查询中获得多个分组层次的聚合结果。选择使用它们时要确保理解它们的效果。
考虑索引的影响： 确保表中使用了适当的索引，以提高 GROUP BY 操作的性能。
测试和优化： 对于复杂的分组查询，进行测试和性能优化是重要的。使用数据库性能分析工具，确保查询在处理大量数据时仍然高效。
文档化查询： 对于复杂的查询，添加注释以解释查询的目的和分组策略，提高查询的可理解性。

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

八、总结

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

文章来源:https://blog.csdn.net/gangzhucoll/article/details/135043425
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！