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

2023-12-17 13:51:33

一、分组查询概述

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;

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

举个例子,假设你有一个订单表(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 BYORDER BY 是 SQL 查询中两个不同的子句,它们有着不同的作用:

  1. GROUP BY:
    • 作用: GROUP BY 用于对查询结果进行分组,将相同的值放在一起,然后对每个组应用聚合函数,计算汇总值。
    • 使用场景: 当你想要对数据进行分组,并对每个组应用聚合函数(如 COUNT、SUM、AVG)以计算统计信息时,你会使用 GROUP BY
SELECT column1, COUNT(column2)
FROM table
GROUP BY column1;
  1. 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 的区别

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

  1. ROLLUP:
  • 语法: 使用 ROLLUP 时,你指定一个列列表,表示要进行多层次分组的列。ROLLUP 生成一个包含每个列组合的聚合值,以及每个列的总计值。
  • 示例:
    SELECT column1, column2, aggregate_function(column3)
    FROM table
    GROUP BY ROLLUP (column1, column2);
    
  • 生成结果: 生成的结果包含了每个列的每个组合的聚合值,以及每个列的总计值。
  1. CUBE:
  • 语法: 使用 CUBE 时,你同样指定一个列列表,表示要进行多维度分组的列。CUBE 生成一个包含每个列组合的聚合值,以及所有可能的列组合的总计值。
  • 示例:
    SELECT column1, column2, aggregate_function(column3)
    FROM table
    GROUP BY CUBE (column1, column2);
    
  • 生成结果: 生成的结果包含了每个列的每个组合的聚合值,以及所有可能的列组合的总计值,更全面。
  1. 区别总结:
  • 结果全面性:

    • 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的用途,以及测试和优化查询。最终,文档化查询并遵循最佳实践可确保编写高效、清晰的分组查询。

文章来源:https://blog.csdn.net/gangzhucoll/article/details/135043425
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。