【SQL学习笔记3】深入理解窗口函数的用法

SQL中的窗口函数（Window Functions）是一种特殊的函数，它允许在查询的结果集的行之间进行计算。与聚合函数不同的是，窗口函数不会将多行数据聚合成一行；相反，它们可以为结果集中的每一行执行计算，并返回一个值。这使得窗口函数非常适合用于需要保持原始行信息的同时进行复杂分析的场景。

常见的窗口函数包括：

1.ROW_NUMBER(): 为分区内的每一行分配一个唯一的行号。

2.RANK(): 根据ORDER BY子句指定的顺序对结果集进行排名。如果有相同的值，则它们会得到相同的排名，但会影响后续排名的编号。 

3.DENSE_RANK(): 类似于RANK()，但是当有相同排名时，DENSE_RANK()不会跳过后续的排名编号

4.NTILE(n): 将结果集划分为n个大致相等的部分，为每一行分配桶编号。

5.LAG() / LEAD(): 分别访问当前行之前或之后的指定行的数据。这对于比较当前行和前一行或后一行的数据非常有用。

6.SUM(), AVG(), MIN(), MAX() 等聚合函数也可以作为窗口函数使用，提供累计、移动平均等高级分析能力。

目录

一、创建表并插入数据

二、演示几种不同的窗口函数用法

1. ROW_NUMBER() OVER() 

2. RANK() over()

3. DENSE_RANK()

4. NTILE（n）

5.LAG() 和 LEAD() 

我们可以从零开始，创建一个简单的数据表，并插入一些示例数据，然后通过窗口函数进行演练。

一、创建表并插入数据

CREATE TABLE sales (
    id INT PRIMARY KEY,
    employee_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO sales (id, employee_id, sale_date, amount) VALUES
(1, 101, '2025-06-01', 234.56),
(2, 102, '2025-06-02', 123.45),
(3, 101, '2025-06-03', 345.67),
(4, 103, '2025-06-04', 456.78),
(5, 102, '2025-06-05', 567.89),
(6, 101, '2025-06-06', 678.90);

二、演示几种不同的窗口函数用法

1. ROW_NUMBER() OVER() 

为每位员工的销售记录按日期排序分配一个唯一的行号。

SELECT 
    id,
    employee_id,
    sale_date,
    amount,
    ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY sale_date) AS row_num
FROM sales;

输出示例如下：

2. RANK() over()

根据销售金额对每位员工的销售记录进行排名。如果有相同的销售金额，它们会得到相同的排名，但后续排名会跳过。

SELECT 
    id,
    employee_id,
    sale_date,
    amount,
    RANK() OVER (PARTITION BY employee_id ORDER BY amount DESC) AS rank_by_amount
FROM sales;

3. DENSE_RANK()

与RANK()类似，但如果存在相同的销售金额，它不会跳过后续的排名编号。

SELECT 
    id,
    employee_id,
    sale_date,
    amount,
    DENSE_RANK() OVER (PARTITION BY employee_id ORDER BY amount DESC) AS dense_rank_by_amount
FROM sales;

4. NTILE（n）

将结果集划分为n个大致相等的部分，为每一行分配桶编号。这里我们将每个员工的销售记录分成两部分（即NTILE(2)）。

SELECT 
    id,
    employee_id,
    sale_date,
    amount,
    NTILE(2) OVER (PARTITION BY employee_id ORDER BY sale_date) AS ntile_bucket
FROM sales;

5.LAG() 和 LEAD() 

分别访问当前行之前或之后的指定行的数据。这对于比较当前行与前一行或后一行的数据非常有用。

使用 LAG() 查看每位员工上一笔销售的金额:

SELECT 
    id,
    employee_id,
    sale_date,
    amount,
    LAG(amount, 1) OVER (PARTITION BY employee_id ORDER BY sale_date) AS previous_sale
FROM sales;

使用 LEAD() 查看每位员工下一笔销售的金额：

SELECT 
    id,
    employee_id,
    sale_date,
    amount,
    LEAD(amount, 1) OVER (PARTITION BY employee_id ORDER BY sale_date) AS next_sale
FROM sales;