在本实验中，你将探索 MySQL 窗口函数 (Window Functions) 的强大功能。你将学习如何对与当前行相关的表行集执行计算。
你将从创建一个示例数据集开始，然后使用 ROW_NUMBER(), SUM(), AVG(), 和 LAG() 等函数，并结合 OVER() 子句来执行高级数据分析。本实验提供了实用的、分步的示例，帮助你理解和应用这些强大的函数。
在使用窗口函数之前，你需要一个包含示例数据的数据库和表。在此步骤中，你将创建一个名为 company 的数据库和一个名为 employees 的表。
首先，从你的桌面打开终端。
以 root 用户连接到 MySQL 服务器。由于这是一个实验环境，你可以使用 sudo 在不输入密码的情况下连接。
sudo mysql -u root连接成功后，你将看到 MySQL 提示符 (mysql>)。
现在，创建 company 数据库并切换到该数据库。IF NOT EXISTS 子句可以防止在数据库已存在时出错。
CREATE DATABASE IF NOT EXISTS company;
USE company;接下来，创建 employees 表。该表将存储员工 ID、姓名、部门和薪资。
CREATE TABLE IF NOT EXISTS employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2)
);现在，向 employees 表插入一些示例数据。
INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
(1, 'Alice', 'Sales', 60000.00),
(2, 'Bob', 'Marketing', 55000.00),
(3, 'Charlie', 'Sales', 65000.00),
(4, 'David', 'IT', 70000.00),
(5, 'Eve', 'Marketing', 62000.00)
ON DUPLICATE KEY UPDATE
employee_name = VALUES(employee_name),
department = VALUES(department),
salary = VALUES(salary);为了验证数据是否正确插入，你可以查看 employees 表中的所有行。
SELECT * FROM employees;输出应显示你插入的五条记录：
+-------------+---------------+------------+----------+
| employee_id | employee_name | department | salary |
+-------------+---------------+------------+----------+
| 1 | Alice | Sales | 60000.00 |
| 2 | Bob | Marketing | 55000.00 |
| 3 | Charlie | Sales | 65000.00 |
| 4 | David | IT | 70000.00 |
| 5 | Eve | Marketing | 62000.00 |
+-------------+---------------+------------+----------+
5 rows in set (0.00 sec)数据库和表准备就绪后，你可以继续下一步学习窗口函数。
ROW_NUMBER() 函数为结果集分区中的每一行分配一个唯一的连续整数。它常用于排名和分页。
基本语法是：
ROW_NUMBER() OVER (ORDER BY column_name [ASC|DESC])
OVER(): 此子句定义了函数的窗口（行集）。ORDER BY: 此子句位于 OVER() 内部，指定了行号分配的顺序。在 MySQL shell 中继续操作，你现在将使用 ROW_NUMBER() 按薪资降序对员工进行排名。
SELECT
employee_name,
department,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_rank
FROM
employees;此查询添加了一个 salary_rank 列，根据薪资从高到低为员工编号。
+---------------+------------+----------+-------------+
| employee_name | department | salary | salary_rank |
+---------------+------------+----------+-------------+
| David | IT | 70000.00 | 1 |
| Charlie | Sales | 65000.00 | 2 |
| Eve | Marketing | 62000.00 | 3 |
| Alice | Sales | 60000.00 | 4 |
| Bob | Marketing | 55000.00 | 5 |
+---------------+------------+----------+-------------+
5 rows in set (0.00 sec)如你所见，David 的排名是 1，因为他的薪资最高。这展示了 ROW_NUMBER() 如何用于创建简单的排名。
累计总计（或累积和）是指一个数字序列的和，该和在每次添加新数字时都会更新。在 SQL 中，你可以使用 SUM() OVER() 来计算它。
语法是：
SUM(column_name) OVER (ORDER BY column_name [ASC|DESC])
此函数对 ORDER BY 子句指定的顺序中的列值进行求和。
现在，让我们按 employee_id 对薪资进行排序，计算累计总计。
SELECT
employee_name,
salary,
SUM(salary) OVER (ORDER BY employee_id) AS running_total
FROM
employees;结果将显示每位员工的薪资以及截至该行的累积总计。
+---------------+----------+---------------+
| employee_name | salary | running_total |
+---------------+----------+---------------+
| Alice | 60000.00 | 60000.00 |
| Bob | 55000.00 | 115000.00 |
| Charlie | 65000.00 | 180000.00 |
| David | 70000.00 | 250000.00 |
| Eve | 62000.00 | 312000.00 |
+---------------+----------+---------------+
5 rows in set (0.00 sec)例如，Bob 的 running_total 是他的薪资加上 Alice 的薪资的总和（60000.00 + 55000.00 = 115000.00）。这对于跟踪随时间变化的累计指标（如销售额或费用）非常有用。
PARTITION BY 子句将结果集划分为多个分区（组），并独立地将窗口函数应用于每个分区。这对于在特定类别内执行计算非常有用。
语法是：
function() OVER (PARTITION BY column_name ORDER BY ...)
让我们使用 PARTITION BY 根据薪资在每个部门内对员工进行排名。
SELECT
employee_name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM
employees;此查询按 department 对数据进行分区，然后按薪资对每个部门内的员工进行排名。
+---------------+------------+----------+--------------+
| employee_name | department | salary | rank_in_dept |
+---------------+------------+----------+--------------+
| David | IT | 70000.00 | 1 |
| Eve | Marketing | 62000.00 | 1 |
| Bob | Marketing | 55000.00 | 2 |
| Charlie | Sales | 65000.00 | 1 |
| Alice | Sales | 60000.00 | 2 |
+---------------+------------+----------+--------------+
5 rows in set (0.00 sec)请注意，排名在每个部门内都会重新开始。例如，Eve 和 Charlie 的排名都是 1，但分别是在他们各自的“Marketing”和“Sales”部门内。与全局排名相比，这允许进行更细粒度的分析。
LAG() 函数可以访问当前行之前的、指定物理偏移量的行。它对于将当前行中的值与前一行中的值进行比较非常有用。
语法是：
LAG(expression, offset, default_value) OVER (ORDER BY ...)
expression: 要检索的列或表达式。offset: 要回溯的行数（默认为 1）。default_value: 如果偏移量超出范围（例如，对于第一行）要返回的值。让我们查找列表中前一个员工的薪资，按 employee_id 排序。
SELECT
employee_name,
salary,
LAG(salary) OVER (ORDER BY employee_id) AS previous_salary
FROM
employees;此查询检索前一行的薪资。对于第一行，由于没有前一行，它将返回 NULL。
+---------------+----------+-----------------+
| employee_name | salary | previous_salary |
+---------------+----------+-----------------+
| Alice | 60000.00 | NULL |
| Bob | 55000.00 | 60000.00 |
| Charlie | 65000.00 | 55000.00 |
| David | 70000.00 | 65000.00 |
| Eve | 62000.00 | 70000.00 |
+---------------+----------+-----------------+
5 rows in set (0.00 sec)你可以使用此功能计算连续薪资之间的差异。当前一个薪资为 NULL 时（对于第一行），结果也将是 NULL。
SELECT
employee_name,
salary,
salary - LAG(salary) OVER (ORDER BY employee_id) AS salary_diff
FROM
employees;此查询计算当前员工薪资与前一个员工薪资之间的差异。
+---------------+----------+-------------+
| employee_name | salary | salary_diff |
+---------------+----------+-------------+
| Alice | 60000.00 | NULL |
| Bob | 55000.00 | -5000.00 |
| Charlie | 65000.00 | 10000.00 |
| David | 70000.00 | 5000.00 |
| Eve | 62000.00 | -8000.00 |
+---------------+----------+-------------+
5 rows in set (0.00 sec)你现在已经练习了几个关键的窗口函数。你可以退出 MySQL shell。
exit;在此次实验中，你探索了 MySQL 窗口函数的使用。你学习了如何使用 ROW_NUMBER() 分配行号，使用 SUM() OVER() 计算累计总和，使用 PARTITION BY 对特定组执行计算，以及使用 LAG() 访问前一行的数据。
通过将这些函数应用于示例数据集，你获得了在 SQL 查询中直接执行高级数据分析的实践经验。这些技能对于从数据中生成复杂的报告和洞察非常有价值。
