SQL 实战：聚合函数高级用法 – 多层分组与动态统计

在数据分析中，聚合函数如 COUNT()、SUM()、AVG()、MAX()、MIN() 等是最常用的工具之一。它们允许我们对大量数据进行汇总和统计。然而，随着业务需求的复杂化，我们常常需要进行更复杂的统计操作，例如多层次的分组统计、动态分组等。

本篇文章将深入讲解 聚合函数的高级用法，包括如何通过 多层分组 和 动态统计 来实现复杂的业务需求。

一、常见聚合函数回顾

二、多层次分组与聚合

需求

在实际业务中，可能需要对数据进行多层次的分组统计，例如：按年份、月份和部门对销售额进行统计，或者按地区、产品类别和销售人员对销售数据进行分组。此时，嵌套的 GROUP BY 和聚合函数将帮助我们进行更精确的统计分析。

案例 1：按年份、月份、部门统计销售额

表结构 sales

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(sale_date) 和 MONTH(sale_date) 提取了销售日期中的年份和月份。
• SUM(amount) 用于计算每个分组（按年、月、部门）的销售总额。
• 通过 GROUP BY year, month, department 进行多层次的分组，按年、月、部门统计销售数据。

案例 2：按部门和产品类别统计销售总额与平均销售额

需求

我们希望统计每个部门在不同产品类别下的销售额总和和平均销售额。

表结构 sales

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;

查询结果

解释：

• SUM(amount) 计算每个部门和产品类别的销售总额。
• AVG(amount) 计算每个部门和产品类别的平均销售额。
• 通过 GROUP BY department, product_category 对数据进行双重分组。

三、动态统计与条件聚合

需求

在一些业务场景下，我们可能需要根据某些条件动态地进行统计，例如：统计各个部门的销售总额和平均销售额，并且只有在销售额超过特定阈值时才进行统计。

案例 1：按部门统计销售总额，过滤销售额低于 1000 的部门

表结构 sales

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;

查询结果

解释：

• HAVING total_sales > 1000 用于过滤销售总额低于 1000 的部门。
• 通过 HAVING 子句可以在聚合之后进行条件筛选，保证统计结果符合指定的条件。

案例 2：统计每个产品类别的销售总额，并对销售额进行排名

需求

我们希望统计每个产品类别的销售总额，并为每个产品类别打上销售排名标签。

表结构 sales

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;

查询结果

解释：

• SUM(amount) 计算每个产品类别的销售总额。
• RANK() OVER (ORDER BY SUM(amount) DESC) 使用窗口函数为每个产品类别按照销售总额降序排序，并赋予一个排名。
• 通过 GROUP BY product_category 按产品类别进行分组，利用窗口函数进行动态排名。

四、总结

1. 多层分组：通过嵌套使用 GROUP BY 和聚合函数，能够对数据进行多层次的统计。例如，可以按年、月、部门对销售额进行统计，或者按部门、产品类别统计销售额和平均销售额。

2. 动态统计：通过 HAVING 子句，能够在聚合之后进行条件筛选，进行更精确的分析。例如，可以筛选销售总额高于特定值的部门或产品类别。

3. 窗口函数：通过 RANK() 等