SQL 实战：聚合函数高级用法 – 多层分组与动态统计
在数据分析中，聚合函数如 COUNT()、SUM()、AVG()、MAX()、MIN() 等是最常用的工具之一。它们允许我们对大量数据进行汇总和统计。然而，随着业务需求的复杂化，我们常常需要进行更复杂的统计操作，例如多层次的分组统计、动态分组等。
本篇文章将深入讲解 聚合函数的高级用法，包括如何通过 多层分组 和 动态统计 来实现复杂的业务需求。
一、常见聚合函数回顾
| 函数 | 说明 | 示例 |
|---|---|---|
| COUNT() | 计算记录数 | COUNT(*) → 统计行数 |
| SUM() | 求和 | SUM(amount) → 求总和 |
| AVG() | 计算平均值 | AVG(price) → 计算平均价格 |
| MAX() | 获取最大值 | MAX(date) → 获取最新日期 |
| MIN() | 获取最小值 | MIN(salary) → 获取最低工资 |
| GROUP_CONCAT() | 合并分组内的元素（字符串） | GROUP_CONCAT(name) → 拼接所有名字 |
二、多层次分组与聚合
需求
在实际业务中，可能需要对数据进行多层次的分组统计，例如：按年份、月份和部门对销售额进行统计，或者按地区、产品类别和销售人员对销售数据进行分组。此时，嵌套的 GROUP BY 和聚合函数将帮助我们进行更精确的统计分析。
案例 1：按年份、月份、部门统计销售额
表结构 sales
| sale_id | sale_date | department | amount |
|---|---|---|---|
| 1 | 2024-12-01 10:00:00 | Sales | 1000 |
| 2 | 2024-12-02 14:30:00 | HR | 200 |
| 3 | 2024-11-01 09:00:00 | Sales | 1500 |
| 4 | 2024-12-03 11:15:00 | Marketing | 500 |
| 5 | 2024-11-15 16:30:00 | HR | 800 |
SQL 实现
SELECT
YEAR(sale_date) AS year,
MONTH(sale_date) AS month,
department,
SUM(amount) AS total_sales
FROM sales
GROUP BY year, month, department
ORDER BY year DESC, month DESC, department;
查询结果
| year | month | department | total_sales |
|---|---|---|---|
| 2024 | 12 | Sales | 1000 |
| 2024 | 12 | HR | 200 |
| 2024 | 12 | Marketing | 500 |
| 2024 | 11 | Sales | 1500 |
| 2024 | 11 | HR | 800 |
解释：
YEAR(sale_date)和MONTH(sale_date)提取了销售日期中的年份和月份。SUM(amount)用于计算每个分组（按年、月、部门）的销售总额。- 通过
GROUP BY year, month, department进行多层次的分组，按年、月、部门统计销售数据。
案例 2：按部门和产品类别统计销售总额与平均销售额
需求
我们希望统计每个部门在不同产品类别下的销售额总和和平均销售额。
表结构 sales
| sale_id | department | product_category | amount |
|---|---|---|---|
| 1 | Sales | Electronics | 1500 |
| 2 | Marketing | Furniture | 800 |
| 3 | Sales | Electronics | 1200 |
| 4 | HR | Furniture | 500 |
| 5 | Sales | Furniture | 900 |
SQL 实现
SELECT
department,
product_category,
SUM(amount) AS total_sales,
AVG(amount) AS avg_sales
FROM sales
GROUP BY department, product_category
ORDER BY department, product_category;
查询结果
| department | product_category | total_sales | avg_sales |
|---|---|---|---|
| Sales | Electronics | 2700 | 1350 |
| Sales | Furniture | 900 | 900 |
| Marketing | Furniture | 800 | 800 |
| HR | Furniture | 500 | 500 |
解释：
SUM(amount)计算每个部门和产品类别的销售总额。AVG(amount)计算每个部门和产品类别的平均销售额。- 通过
GROUP BY department, product_category对数据进行双重分组。
三、动态统计与条件聚合
需求
在一些业务场景下，我们可能需要根据某些条件动态地进行统计，例如：统计各个部门的销售总额和平均销售额，并且只有在销售额超过特定阈值时才进行统计。
案例 1：按部门统计销售总额，过滤销售额低于 1000 的部门
表结构 sales
| sale_id | department | amount |
|---|---|---|
| 1 | Sales | 1500 |
| 2 | Marketing | 800 |
| 3 | Sales | 1200 |
| 4 | HR | 500 |
| 5 | Sales | 900 |
SQL 实现
SELECT
department,
SUM(amount) AS total_sales,
AVG(amount) AS avg_sales
FROM sales
GROUP BY department
HAVING total_sales > 1000
ORDER BY total_sales DESC;
查询结果
| department | total_sales | avg_sales |
|---|---|---|
| Sales | 3700 | 1233.33 |
解释：
HAVING total_sales > 1000用于过滤销售总额低于 1000 的部门。- 通过
HAVING子句可以在聚合之后进行条件筛选，保证统计结果符合指定的条件。
案例 2：统计每个产品类别的销售总额，并对销售额进行排名
需求
我们希望统计每个产品类别的销售总额，并为每个产品类别打上销售排名标签。
表结构 sales
| sale_id | product_category | amount |
|---|---|---|
| 1 | Electronics | 1500 |
| 2 | Furniture | 800 |
| 3 | Electronics | 1200 |
| 4 | Furniture | 900 |
| 5 | Electronics | 1800 |
SQL 实现
SELECT
product_category,
SUM(amount) AS total_sales,
RANK() OVER (ORDER BY SUM(amount) DESC) AS sales_rank
FROM sales
GROUP BY product_category
ORDER BY sales_rank;
查询结果
| product_category | total_sales | sales_rank |
|---|---|---|
| Electronics | 4500 | 1 |
| Furniture | 1700 | 2 |
解释：
SUM(amount)计算每个产品类别的销售总额。RANK() OVER (ORDER BY SUM(amount) DESC)使用窗口函数为每个产品类别按照销售总额降序排序，并赋予一个排名。- 通过
GROUP BY product_category按产品类别进行分组，利用窗口函数进行动态排名。
四、总结
-
多层分组：通过嵌套使用
GROUP BY和聚合函数，能够对数据进行多层次的统计。例如，可以按年、月、部门对销售额进行统计，或者按部门、产品类别统计销售额和平均销售额。 -
动态统计：通过
HAVING子句，能够在聚合之后进行条件筛选，进行更精确的分析。例如，可以筛选销售总额高于特定值的部门或产品类别。 -
窗口函数：通过
RANK()等
原文地址：https://blog.csdn.net/u012561308/article/details/144779346
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！