GROUP BY 与 HAVING 子句的高级用法与优化 - 智猿学院-IT界的百科全书

好的，各位老铁们，大家好！我是你们的老朋友，编程界的老司机，今天要跟大家聊聊SQL中的两位重量级选手：GROUP BY 和 HAVING。别看它们名字平平无奇，但用好了，那可是能让你的数据分析起飞的🚀。

今天咱们不搞那些枯燥的理论，就用大白话，结合实际案例，把 GROUP BY 和 HAVING 给你安排得明明白白，让你看完就能上手，成为SQL界的一颗闪耀的星🌟。

一、GROUP BY：数据分组的魔法师🧙

想象一下，你是一家电商平台的老板，手里握着海量的交易数据。你想知道每个省份的销售额是多少，哪个省份的销售额最高？如果没有 GROUP BY，你可能要手动去统计，那画面太美，我不敢看🙈。

GROUP BY 的作用就像一个魔法师，它可以按照你指定的列，把数据分成不同的组。比如，你想按省份分组，它就会把所有相同省份的数据放到一起，形成一个个小组。

1. GROUP BY 的基本语法：

SELECT column1, column2, ... , aggregate_function(column)
FROM table_name
WHERE condition
GROUP BY column1, column2, ...
ORDER BY column1, column2, ...;

column1, column2, ...：你要分组的列，比如省份、城市、产品类别等等。
aggregate_function(column)：聚合函数，比如 SUM()、AVG()、COUNT()、MAX()、MIN() 等等，用于对每个分组的数据进行计算。

2. 举个栗子🌰：

假设我们有一张名为 orders 的表，记录了用户的订单信息，包括 order_id（订单ID）、user_id（用户ID）、province（省份）、product_name（产品名称）和 amount（订单金额）。

order_id	user_id	province	product_name	amount
1	101	广东	手机	5000
2	102	广东	电脑	8000
3	101	广东	手机壳	50
4	103	上海	手机	6000
5	104	上海	电脑	9000
6	102	广东	充电宝	100
7	105	北京	手机	7000
8	106	北京	电脑	10000

现在，我们想统计每个省份的销售总额，就可以这样写：

SELECT province, SUM(amount) AS total_amount
FROM orders
GROUP BY province;

这条SQL语句会按照 province 列进行分组，然后使用 SUM(amount) 函数计算每个省份的销售总额，并将结果命名为 total_amount。

执行结果如下：

province	total_amount
北京	17000
上海	15000
广东	13150

3. GROUP BY 的注意事项：

SELECT 列表中，除了聚合函数之外，其他的列都必须出现在 GROUP BY 子句中。
GROUP BY 子句可以包含多个列，表示按照多个列进行分组。
GROUP BY 子句的顺序不影响结果，但为了代码的可读性，建议按照逻辑顺序排列。

二、HAVING：分组后的过滤器🕵️‍♂️

GROUP BY 负责分组，但分组之后，如果你想对分组后的数据进行筛选，怎么办呢？这时候，HAVING 子句就派上用场了。

HAVING 子句就像一个过滤器，它可以根据你指定的条件，过滤掉不符合条件的分组。注意，HAVING 是对分组后的数据进行过滤，而不是对原始数据进行过滤。

1. HAVING 的基本语法：

SELECT column1, column2, ... , aggregate_function(column)
FROM table_name
WHERE condition
GROUP BY column1, column2, ...
HAVING condition
ORDER BY column1, column2, ...;

condition：过滤条件，可以使用聚合函数。

2. 举个栗子🌰：

还是上面的 orders 表，我们想找出销售总额超过 15000 的省份，就可以这样写：

SELECT province, SUM(amount) AS total_amount
FROM orders
GROUP BY province
HAVING SUM(amount) > 15000;

这条SQL语句会先按照 province 列进行分组，然后计算每个省份的销售总额，最后使用 HAVING SUM(amount) > 15000 过滤掉销售总额小于等于 15000 的省份。

执行结果如下：

province	total_amount
北京	17000

3. HAVING 和 WHERE 的区别：

WHERE 子句用于在分组之前过滤原始数据，而 HAVING 子句用于在分组之后过滤分组后的数据。
WHERE 子句不能使用聚合函数，而 HAVING 子句可以使用聚合函数。

你可以这样理解：WHERE 是在分组之前进行筛选，相当于先“瘦身”再分组；而 HAVING 是在分组之后进行筛选，相当于先分组再“瘦身”。

三、GROUP BY 和 HAVING 的高级用法：

多列分组：

有时候，我们需要按照多个列进行分组。比如，我们想统计每个省份、每个产品的销售总额，就可以这样写：
```
SELECT province, product_name, SUM(amount) AS total_amount
FROM orders
GROUP BY province, product_name;
```
这条SQL语句会先按照 province 列进行分组，然后在每个省份的分组内，再按照 product_name 列进行分组。
使用别名：

在 HAVING 子句中，可以使用在 SELECT 列表中定义的别名。比如，我们可以这样写：
```
SELECT province, SUM(amount) AS total_amount
FROM orders
GROUP BY province
HAVING total_amount > 15000;
```
这样写可以提高代码的可读性。
与 CASE WHEN 结合使用：

CASE WHEN 语句可以根据不同的条件，返回不同的值。我们可以将 CASE WHEN 语句与 GROUP BY 和 HAVING 结合使用，实现更复杂的统计分析。

比如，我们想统计每个省份的手机和电脑的销售总额，就可以这样写：
```
SELECT
    province,
    SUM(CASE WHEN product_name = '手机' THEN amount ELSE 0 END) AS mobile_amount,
    SUM(CASE WHEN product_name = '电脑' THEN amount ELSE 0 END) AS computer_amount
FROM orders
GROUP BY province;
```
这条SQL语句会先按照 province 列进行分组，然后使用 CASE WHEN 语句分别计算每个省份的手机和电脑的销售总额。
窗口函数辅助分组统计:

窗口函数可以在不使用GROUP BY的情况下进行分组统计。但有些场景下，两者结合使用可以发挥更强大的作用。例如，计算每个省份销售额占总销售额的比例，并筛选出比例大于10%的省份：
```
WITH ProvinceSales AS (
  SELECT
      province,
      SUM(amount) AS total_sales,
      SUM(SUM(amount)) OVER () AS overall_sales -- 计算总销售额
  FROM orders
  GROUP BY province
)
SELECT
  province,
  total_sales,
  total_sales / overall_sales AS sales_percentage
FROM ProvinceSales
WHERE total_sales / overall_sales > 0.1;
```
这里，窗口函数SUM(SUM(amount)) OVER () 计算的是总销售额，而外部查询则利用此值计算每个省份的销售额占比，最后通过WHERE筛选出占比大的省份。GROUP BY负责初步分组，窗口函数负责计算总额，WHERE负责筛选，分工明确。

四、GROUP BY 和 HAVING 的优化：

避免不必要的排序：

GROUP BY 操作可能会导致排序，如果不需要排序，可以使用 ORDER BY NULL 来避免排序，提高性能。
```
SELECT province, SUM(amount) AS total_amount
FROM orders
GROUP BY province
ORDER BY NULL;
```
使用索引：

如果 GROUP BY 子句中使用的列有索引，可以提高查询性能。
尽量在 WHERE 子句中过滤数据：

尽量在 WHERE 子句中过滤掉不需要的数据，减少 GROUP BY 操作的数据量。
预计算结果：

对于一些复杂的、重复使用的分组统计，可以考虑物化视图或汇总表，预先计算好结果，提高查询速度。例如，如果经常需要查询各省份的销售总额，可以创建一个物化视图，定期刷新数据。
避免隐式转换：

确保GROUP BY和HAVING子句中涉及的数据类型与对应列的数据类型一致，避免数据库进行隐式类型转换，这可能会导致索引失效。
合理选择聚合函数：

不同的聚合函数性能差异很大。例如，COUNT(*)通常比COUNT(column)快，因为前者只需要扫描所有行，而后者需要检查column是否为NULL。根据实际需求选择最合适的聚合函数。

五、案例分析：

假设我们是一家在线教育平台，有一张名为 courses 的表，记录了课程信息，包括 course_id（课程ID）、course_name（课程名称）、category（课程类别）、price（课程价格）和 enrollment（报名人数）。

course_id	course_name	category	price	enrollment
1	Python入门	编程	100	1000
2	Java高级	编程	200	500
3	英语口语	语言	150	800
4	日语入门	语言	120	600
5	数据分析	数据科学	180	700
6	机器学习	数据科学	250	400

现在，我们想分析一下平台的课程销售情况，可以提出以下问题：

每个类别的课程数量是多少？
每个类别的平均价格是多少？
哪个类别的总收入最高？
哪个类别的平均报名人数最多？
找出课程数量大于 1 的类别。

针对这些问题，我们可以使用 GROUP BY 和 HAVING 编写相应的SQL语句：

每个类别的课程数量是多少？

SELECT category, COUNT(*) AS course_count
FROM courses
GROUP BY category;

每个类别的平均价格是多少？

SELECT category, AVG(price) AS average_price
FROM courses
GROUP BY category;

哪个类别的总收入最高？

SELECT category, SUM(price * enrollment) AS total_revenue
FROM courses
GROUP BY category
ORDER BY total_revenue DESC
LIMIT 1;

哪个类别的平均报名人数最多？

SELECT category, AVG(enrollment) AS average_enrollment
FROM courses
GROUP BY category
ORDER BY average_enrollment DESC
LIMIT 1;

找出课程数量大于 1 的类别。

SELECT category, COUNT(*) AS course_count
FROM courses
GROUP BY category
HAVING COUNT(*) > 1;

六、总结：

GROUP BY 和 HAVING 是SQL中非常重要的两个子句，它们可以帮助我们对数据进行分组和筛选，实现复杂的统计分析。掌握 GROUP BY 和 HAVING 的用法，可以让你在数据分析的道路上更上一层楼。

记住，熟能生巧！多练习，多思考，你也能成为SQL高手！💪

好了，今天的分享就到这里。如果你觉得这篇文章对你有帮助，别忘了点赞、评论、分享三连哦！我们下期再见！ 😉

发表回复 取消回复

发表回复取消回复