专用窗口函数的种类-SQL基础教程(下)

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

专用窗口函数的种类

在SQL的广阔领域中，窗口函数（Window Functions）是一种强大的工具，它们允许我们对一组行执行计算，而不需要将这些行分组为单独的输出行。窗口函数在数据分析、报表生成以及复杂查询优化中扮演着至关重要的角色。特别是专用窗口函数，它们针对特定的计算需求设计，如排名、累计总和、移动平均等，极大地丰富了SQL的数据处理能力。本章将深入探讨几种常见的专用窗口函数种类，包括排名函数、分布函数、聚合窗口函数以及分析函数，并辅以实例说明其用法。

一、排名函数

排名函数是窗口函数中最为直观的一类，它们用于为结果集中的每一行分配一个唯一的排名。这类函数在处理排名、等级或顺序相关的查询时非常有用。

1.1 ROW_NUMBER()

ROW_NUMBER()函数为结果集中的每一行分配一个唯一的连续整数。这个整数是根据OVER子句中指定的排序顺序来分配的。如果两行或多行具有相同的排序值，则它们会被赋予不同的行号，但具体哪行获得较小的行号是不确定的。

SELECT 
    employee_id, 
    salary, 
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_rank
FROM 
    employees;

此查询按薪资降序为员工分配了一个薪资排名。

1.2 RANK()

与ROW_NUMBER()不同，RANK()函数在遇到相同排序值的行时，会给予这些行相同的排名，并且跳过随后的排名数字以维持排名的唯一性。

SELECT 
    employee_id, 
    salary, 
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM 
    employees;

如果两名员工薪资相同，则他们会有相同的排名，并且下一名员工的排名会跳过该排名数字。

1.3 DENSE_RANK()

DENSE_RANK()函数与RANK()类似，也是为相同排序值的行分配相同的排名，但不同之处在于它不会跳过任何排名数字。这意味着，如果有两行并列第一，则下一行的排名将是第二，而不是第三。

SELECT 
    employee_id, 
    salary, 
    DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM 
    employees;

二、分布函数

分布函数用于计算数据在整体中的位置或分布，如百分位数、累计分布等。

2.1 PERCENT_RANK()

PERCENT_RANK()函数计算每行在其分区内的相对位置，以百分比形式表示。这个百分比是基于行值在分区内排序后的位置计算的。

SELECT 
    employee_id, 
    salary, 
    PERCENT_RANK() OVER (ORDER BY salary DESC) AS salary_percent_rank
FROM 
    employees;

此查询显示了每位员工薪资在其部门内的百分比排名。

2.2 CUME_DIST()

CUME_DIST()函数计算一行在其分区内的累计分布。对于分区内的每一行，它返回小于或等于当前行值的行数占分区内总行数的比例。

SELECT 
    employee_id, 
    salary, 
    CUME_DIST() OVER (ORDER BY salary DESC) AS salary_cume_dist
FROM 
    employees;

此查询显示了薪资高于或等于当前员工薪资的员工所占的比例。

三、聚合窗口函数

聚合窗口函数允许我们在不分组数据的情况下执行聚合计算，如求和、平均值等。

3.1 SUM()

SUM()作为窗口函数时，可以计算分区内某列值的总和，而不需要将数据分组为单独的记录。

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

此查询显示了每个员工的薪资以及他们所在部门的总薪资。

3.2 AVG()

AVG()窗口函数计算分区内某列值的平均值。

SELECT 
    department_id, 
    employee_id, 
    salary, 
    AVG(salary) OVER (PARTITION BY department_id) AS avg_department_salary
FROM 
    employees;

此查询为每个员工提供了他们所在部门的平均薪资。

四、分析函数

分析函数是一类特殊的窗口函数，它们执行更复杂的计算，如移动平均、领先和滞后值等。

4.1 LEAD() 和 LAG()

LEAD()和LAG()函数分别用于访问结果集中当前行的下一行和上一行的数据。这对于计算差异、增长率或填充缺失值非常有用。

SELECT 
    employee_id, 
    salary, 
    LAG(salary, 1) OVER (ORDER BY employee_id) AS previous_salary,
    LEAD(salary, 1) OVER (ORDER BY employee_id) AS next_salary
FROM 
    employees;

此查询显示了每位员工的薪资以及他们前一位和后一位员工的薪资。

4.2 FIRST_VALUE() 和 LAST_VALUE()

FIRST_VALUE()和LAST_VALUE()函数分别返回分区内第一行和最后一行的值。这对于获取分区内的最大值或最小值（当排序顺序已知时）很有用。

SELECT 
    department_id, 
    employee_id, 
    salary, 
    FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS max_salary_in_dept,
    LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary ASC) AS min_salary_in_dept
FROM 
    employees;

此查询显示了每位员工的薪资以及他们所在部门的最高和最低薪资。

结语

专用窗口函数是SQL中处理复杂数据分析和报表生成任务时不可或缺的工具。通过排名函数、分布函数、聚合窗口函数以及分析函数的灵活应用，我们可以轻松实现数据的排序、排名、累计计算、移动平均等多种高级分析功能。掌握这些函数的使用，将极大地提升SQL查询的效率和深度，为数据分析和业务决策提供有力支持。