MySQL中窗口函数的介绍和用法

**MySQL 中窗口函数的介绍和用法**

窗口函数（Window Function）是 MySQL8.0 版本引入的一种新型函数，它们可以在 SQL 查询中使用，用于处理数据集中的每一行，并返回一个值。与聚合函数不同，窗口函数不改变原始数据，而是返回一个新的列。

**什么是窗口函数？**

窗口函数的基本概念是：对于每一行数据，都会创建一个虚拟的“窗口”，这个窗口包含了当前行及其相邻的行。然后，窗口函数可以在这个窗口内进行计算和操作。

**窗口函数的类型**

MySQL 中支持以下几种窗口函数：

1. **ROW_NUMBER()**: 为每一行分配一个唯一的序号。
2. **RANK()**: 为每一行分配一个排名值，相同值的行会有相同的排名值，但下一个值的行会有不同的排名值。
3. **DENSE_RANK()**: 与 RANK() 类似，但不允许有空缺的排名值。
4. **NTILE()**: 将数据集分成 n 个组，每个组中的行都会有相同的值。
5. **LAG()** 和 **LEAD()**: 返回当前行前的或后的行的值。
6. **FIRST_VALUE()** 和 **LAST_VALUE()**: 返回当前行前的或后的行的值。

**窗口函数的用法**

下面是一些示例代码：

###1. ROW_NUMBER()

sqlCREATE TABLE scores (
 id INT,
 name VARCHAR(20),
 score DECIMAL(3,2)
);

INSERT INTO scores (id, name, score) VALUES(1, 'Alice',90.00),
(2, 'Bob',80.00),
(3, 'Charlie',70.00),
(4, 'David',60.00),
(5, 'Eve',50.00);

SELECT id, name, score,
 ROW_NUMBER() OVER (ORDER BY score DESC) AS row_numFROM scores;

结果：

| id | name | score | row_num |
|----|--------|----------|---------|
|1 | Alice |90.00 |1 |
|2 | Bob |80.00 |2 |
|3 | Charlie|70.00 |3 |
|4 | David |60.00 |4 |
|5 | Eve |50.00 |5 |

###2. RANK()

sqlSELECT id, name, score,
 RANK() OVER (ORDER BY score DESC) AS rankFROM scores;

结果：

| id | name | score | rank |
|----|--------|----------|------|
|1 | Alice |90.00 |1 |
|2 | Bob |80.00 |2 |
|3 | Charlie|70.00 |3 |
|4 | David |60.00 |4 |
|5 | Eve |50.00 |5 |

###3. DENSE_RANK()

sqlSELECT id, name, score,
 DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rankFROM scores;

结果：

| id | name | score | dense_rank |
|----|--------|----------|------------|
|1 | Alice |90.00 |1 |
|2 | Bob |80.00 |2 |
|3 | Charlie|70.00 |3 |
|4 | David |60.00 |4 |
|5 | Eve |50.00 |5 |

###4. NTILE()

sqlSELECT id, name, score,
 NTILE(2) OVER (ORDER BY score DESC) AS ntileFROM scores;

结果：

| id | name | score | ntile |
|----|--------|----------|-------|
|1 | Alice |90.00 |1 |
|2 | Bob |80.00 |1 |
|3 | Charlie|70.00 |2 |
|4 | David |60.00 |2 |
|5 | Eve |50.00 |2 |

###5. LAG() 和 LEAD()

sqlSELECT id, name, score,
 LAG(score) OVER (ORDER BY id) AS lag_score,
 LEAD(score) OVER (ORDER BY id) AS lead_scoreFROM scores;

结果：

| id | name | score | lag_score | lead_score |
|----|--------|----------|-----------|------------|
|1 | Alice |90.00 | NULL |80.00 |
|2 | Bob |80.00 |90.00 |70.00 |
|3 | Charlie|70.00 |80.00 |60.00 |
|4 | David |60.00 |70.00 |50.00 |
|5 | Eve |50.00 |60.00 | NULL |

###6. FIRST_VALUE() 和 LAST_VALUE()

sqlSELECT id, name, score,
 FIRST_VALUE(score) OVER (ORDER BY id) AS first_score,
 LAST_VALUE(score) OVER (ORDER BY id) AS last_scoreFROM scores;

结果：

| id | name | score | first_score | last_score |
|----|--------|----------|-------------|------------|
|1 | Alice |90.00 |90.00 |50.00 |
|2 | Bob |80.00 |90.00 |50.00 |
|3 | Charlie|70.00 |90.00 |50.00 |
|4 | David |60.00 |90.00 |50.00 |
|5 | Eve |50.00 |90.00 |50.00 |

以上是 MySQL 中窗口函数的基本介绍和用法示例。这些函数可以帮助你在 SQL 查询中进行数据分析和处理，提高你的编程效率和能力。