作为窗口函数使用的聚合函数-SQL基础教程(下)

当前位置:　首页>> 技术小册>> SQL基础教程(下)

章节标题：作为窗口函数使用的聚合函数

在SQL的广阔领域中，聚合函数是处理数据汇总的强大工具，它们能够计算一组值并返回一个单一的结果，如总和（SUM）、平均值（AVG）、最大值（MAX）、最小值（MIN）以及计数（COUNT）等。然而，当这些聚合函数被用作窗口函数时，它们的用途和灵活性得到了极大的扩展。窗口函数允许我们在结果集的每一行上执行聚合计算，同时保持与原始数据行的关联，这为数据分析提供了前所未有的深度和洞察力。

一、窗口函数基础

在深入探讨作为窗口函数使用的聚合函数之前，有必要先了解窗口函数的基本概念。窗口函数，也称为分析函数，是在SQL 2003标准中引入的，用于在结果集的每一行上执行计算，这些计算基于一组行（称为窗口）而不是整个结果集。窗口函数不会减少结果集中的行数，而是为每行添加新的列，这些列包含了基于窗口内行的聚合计算结果。

窗口函数的基本语法如下：

<窗口函数> OVER (
    [PARTITION BY <列名>]
    [ORDER BY <列名> [ASC|DESC]]
    [ROWS|RANGE BETWEEN <起点> AND <终点>]
)

PARTITION BY 子句（可选）：用于将结果集分成多个分区，窗口函数在每个分区内独立计算。
ORDER BY 子句（对于某些窗口函数是必需的，如ROW_NUMBER()）：定义窗口内行的排序顺序。
ROWS|RANGE BETWEEN 子句（可选）：定义窗口的框架，即窗口函数将考虑哪些行进行计算。

二、聚合函数作为窗口函数

当聚合函数（如SUM、AVG、MAX、MIN、COUNT）被用作窗口函数时，它们不再返回整个结果集的汇总值，而是基于每个窗口（或分区）内的行来计算值。这使得我们可以在不改变结果集行数的情况下，获得每行相对于其周围行的聚合信息。

2.1 SUM() 作为窗口函数

SUM() 函数作为窗口函数时，可以计算每个窗口内值的总和。这在计算累计总和或移动平均时特别有用。

SELECT
    department_id,
    employee_id,
    salary,
    SUM(salary) OVER (PARTITION BY department_id ORDER BY employee_id) AS cumulative_salary
FROM
    employees;

此查询按部门分区，并计算每个员工及其之前所有员工的累计薪资总和。

2.2 AVG() 作为窗口函数

AVG() 函数作为窗口函数时，计算窗口内值的平均值。这有助于理解每行数据相对于其窗口内其他数据的平均水平。

SELECT
    product_category,
    product_id,
    price,
    AVG(price) OVER (PARTITION BY product_category) AS category_average_price
FROM
    products;

此查询按产品类别分区，并计算每个产品相对于其类别的平均价格。

2.3 MAX() 和 MIN() 作为窗口函数

MAX() 和 MIN() 函数作为窗口函数时，分别用于找到窗口内的最大值和最小值。这在比较每行数据与其窗口内极端值的关系时非常有用。

SELECT
    order_date,
    product_id,
    quantity,
    MAX(quantity) OVER (PARTITION BY order_date) AS max_quantity_per_day
FROM
    order_details;

此查询按订单日期分区，并找出每天每个订单详情行的最大数量。

2.4 COUNT() 作为窗口函数

COUNT() 函数作为窗口函数时，计算窗口内的行数。这在需要了解每行数据相对于其窗口内其他数据的数量信息时非常有用。

SELECT
    employee_id,
    department_id,
    hire_date,
    COUNT(*) OVER (PARTITION BY department_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rank_in_department
FROM
    employees;

此查询按部门分区，并计算每个员工按入职日期在其部门内的排名（即入职日期之前的员工数加1）。

三、窗口函数的进阶应用

除了基本的聚合函数作为窗口函数外，还可以结合使用不同的窗口定义（如ROWS与RANGE的区别）、多个窗口函数以及与其他SQL特性的结合（如JOIN、子查询等），来实现更复杂的数据分析需求。

3.1 窗口框架的精细控制

通过调整窗口框架（ROWS或RANGE），可以精确控制窗口函数考虑的行的范围。例如，使用ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING可以计算当前行及其前后各一行的聚合值。

3.2 多个窗口函数组合使用

在同一个SELECT语句中，可以对同一组行应用多个窗口函数，以获取多个维度的聚合信息。

3.3 与其他SQL特性的结合

窗口函数可以与其他SQL特性（如子查询、JOIN、条件表达式等）结合使用，以实现更复杂的查询逻辑。

四、结论

作为窗口函数使用的聚合函数是SQL中一个极其强大的特性，它们允许我们在不改变结果集行数的情况下，对每行数据进行深入的聚合分析。通过灵活运用窗口函数，我们可以揭示数据中的隐藏模式和趋势，为决策制定提供有力的支持。无论是计算累计总和、移动平均、排名还是其他复杂的聚合需求，窗口函数都是不可或缺的工具。随着对SQL的深入理解和实践经验的积累，你将能够更加熟练地运用这些功能强大的工具来解锁数据的无限潜力。