SQL窗口函数怎么使用

SQL窗口函数（Window Function）是一种强大的工具，允许在查询结果集中执行复杂的计算，而不会改变原始数据的行数。窗口函数通常用于计算累积值、排名、移动平均值等。本文将详细介绍窗口函数的基本概念、语法以及常见的使用场景。

1. 窗口函数的基本概念

窗口函数与普通的聚合函数（如SUM、AVG等）不同，它不会将多行数据合并为一行，而是在每一行上执行计算，同时保留原始数据的行数。窗口函数通过定义一个“窗口”（Window）来确定计算的范围，这个窗口可以是整个表、分组后的数据，或者是基于当前行的某个范围。

2. 窗口函数的语法

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

function_name (expression) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression]
    [frame_clause]
)

• function_name: 窗口函数的名称，如ROW_NUMBER()、RANK()、SUM()等。
• expression: 需要计算的表达式或列。
• OVER: 指定窗口函数的范围。
• PARTITION BY: 将数据分组，类似于GROUP BY，但不会合并行。
• ORDER BY: 指定窗口内的排序方式。
• frame_clause: 定义窗口的范围，通常用于计算移动平均值等。

3. 常见的窗口函数

3.1 排名函数

• ROW_NUMBER(): 为每一行分配一个唯一的序号，序号从1开始。
• RANK(): 为每一行分配一个排名，排名相同的行会得到相同的序号，后续的序号会跳过。
• DENSE_RANK(): 类似于RANK()，但不会跳过后续的序号。

SELECT 
    name, 
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK() OVER (ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;

3.2 聚合函数

• SUM(): 计算窗口内某列的总和。
• AVG(): 计算窗口内某列的平均值。
• MIN(): 计算窗口内某列的最小值。
• MAX(): 计算窗口内某列的最大值。

SELECT 
    name, 
    score,
    SUM(score) OVER (PARTITION BY class) AS total_score,
    AVG(score) OVER (PARTITION BY class) AS avg_score
FROM students;

3.3 分析函数

• LAG(): 获取当前行之前的某一行的值。
• LEAD(): 获取当前行之后的某一行的值。
• FIRST_VALUE(): 获取窗口内第一行的值。
• LAST_VALUE(): 获取窗口内最后一行的值。

SELECT 
    name, 
    score,
    LAG(score, 1) OVER (ORDER BY score) AS prev_score,
    LEAD(score, 1) OVER (ORDER BY score) AS next_score
FROM students;

4. 窗口函数的使用场景

4.1 计算累积值

窗口函数可以用于计算累积值，例如累积销售额、累积利润等。

SELECT 
    date, 
    sales,
    SUM(sales) OVER (ORDER BY date) AS cumulative_sales
FROM sales_data;

4.2 计算移动平均值

窗口函数可以用于计算移动平均值，例如过去7天的平均销售额。

SELECT 
    date, 
    sales,
    AVG(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales_data;

4.3 排名和分组

窗口函数可以用于对数据进行排名和分组，例如按班级分组后对学生的成绩进行排名。

SELECT 
    name, 
    class, 
    score,
    RANK() OVER (PARTITION BY class ORDER BY score DESC) AS class_rank
FROM students;

5. 总结

SQL窗口函数是一种非常强大的工具，能够在不改变原始数据行数的情况下执行复杂的计算。通过合理使用窗口函数，可以轻松实现累积值、排名、移动平均值等计算。掌握窗口函数的使用，能够大大提高SQL查询的灵活性和效率。