聚合函数与窗口函数的结合使用

在 SQL 中，聚合函数（如 SUM、AVG、MIN、MAX、COUNT）通常用于对一组数据进行计算，返回一个单一的结果。然而，通过与窗口函数（OVER 子句）结合使用，可以在不减少结果集行数的情况下，对每一行应用聚合计算。这种方法非常适用于需要同时保留原始数据和聚合结果的场景。

聚合函数

这些函数用于在一组行上执行聚合计算，同时保留每一行的细节。

• SUM(expression)：

  • 计算指定列的总和。
  • 语法：

    SUM(expression) OVER (
        [PARTITION BY column1, column2, ...]
        ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...
    )
    

• AVG(expression)：

  • 计算指定列的平均值。
  • 语法：

    AVG(expression) OVER (
        [PARTITION BY column1, column2, ...]
        ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...
    )
    

• MIN(expression)：

  • 计算指定列的最小值。
  • 语法：

    MIN(expression) OVER (
        [PARTITION BY column1, column2, ...]
        ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...
    )
    

• MAX(expression)：

  • 计算指定列的最大值。
  • 语法：

    MAX(expression) OVER (
        [PARTITION BY column1, column2, ...]
        ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...
    )
    

• COUNT(expression)：

  • 计算指定列的非 NULL 值的数量。
  • 语法：

    COUNT(expression) OVER (
        [PARTITION BY column1, column2, ...]
        ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...
    )
    

示例数据

假设有一个 sales 表，包含以下数据：

1. 计算每个产品的总销售额

使用 SUM 函数计算每个产品的总销售额：

SELECT 
    product_id, 
    sale_amount,
    SUM(sale_amount) OVER (PARTITION BY product_id) AS total_sales
FROM 
    sales;

结果：

2. 计算每个产品的平均销售额

使用 AVG 函数计算每个产品的平均销售额：

SELECT 
    product_id, 
    sale_amount,
    AVG(sale_amount) OVER (PARTITION BY product_id) AS average_sales
FROM 
    sales;

结果：

3. 计算每个产品的最小销售额

使用 MIN 函数计算每个产品的最小销售额：

SELECT 
    product_id, 
    sale_amount,
    MIN(sale_amount) OVER (PARTITION BY product_id) AS min_sales
FROM 
    sales;

结果：

4. 计算每个产品的最大销售额

使用 MAX 函数计算每个产品的最大销售额：

SELECT 
    product_id, 
    sale_amount,
    MAX(sale_amount) OVER (PARTITION BY product_id) AS max_sales
FROM 
    sales;

结果：

5. 计算每个产品的销售记录数

使用 COUNT 函数计算每个产品的销售记录数：

SELECT 
    product_id, 
    sale_amount,
    COUNT(sale_amount) OVER (PARTITION BY product_id) AS sale_count
FROM 
    sales;

结果：

其他窗口函数（真题中较少使用）

值函数

这些函数用于获取特定行的值。

• LEAD(column, n, default)：

  • 获取当前行之后第 n 行的值，如果没有足够的行，则返回默认值。
  • 语法：

    LEAD(column, n, default) OVER (
        [PARTITION BY column1, column2, ...]
        ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...
    )
    

• LAG(column, n, default)：

  • 获取当前行之前第 n 行的值，如果没有足够的行，则返回默认值。
  • 语法：

    LAG(column, n, default) OVER (
        [PARTITION BY column1, column2, ...]
        ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...
    )
    

• FIRST_VALUE(column)：

  • 获取当前分组中的第一个值。
  • 语法：

    FIRST_VALUE(column) OVER (
        [PARTITION BY column1, column2, ...]
        ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...
    )
    

• LAST_VALUE(column)：

  • 获取当前分组中的最后一个值。
  • 语法：

    LAST_VALUE(column) OVER (
        [PARTITION BY column1, column2, ...]
        ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...
    )
    

分布函数

这些函数用于计算数据的分布情况。

• CUME_DIST()：

  • 计算当前行的累积分布，即当前行的值在分组中的相对位置。
  • 语法：

    CUME_DIST() OVER (
        [PARTITION BY column1, column2, ...]
        ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...
    )
    

• PERCENT_RANK()：

  • 计算当前行的百分比排名，即当前行的值在分组中的相对位置。
  • 语法：

    PERCENT_RANK() OVER (
        [PARTITION BY column1, column2, ...]
        ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...
    )