【MySQL】窗口函数详解（概念+练习+实战）

窗口函数（Window Function）是SQL中一种强大的数据处理功能，它可以在查询结果的行集上进行计算，并且能够将计算应用于数据的子集，该子集被称为窗口。窗口函数可以在SELECT语句的SELECT列表中，也可以在ORDER BY子句中使用。

窗口函数的一般语法如下：

<窗口函数> OVER ([PARTITION BY <列名>] ORDER BY <列名>)

窗口函数类型：

1. 聚合窗口函数：如ROW\_NUMBER, RANK, DENSE\_RANK, COUNT, MIN, MAX, SUM, AVG等。
2. 排序窗口函数：如RANK, DENSE\_RANK, NTILE。
3. 分布式窗口函数：如PERCENT\_RANK, CUME\_DIST, NTH\_VALUE, LAG, LEAD。

练习和实战：

假设有一个销售数据表sales，包含字段year, product\_id, amount（销售额）。

1. 计算每一行的累计销售额（使用窗口函数SUM）。

SELECT year, product_id, amount,
SUM(amount) OVER (PARTITION BY product_id ORDER BY year) AS running_total
FROM sales;

2. 计算每个产品每年的销售额占该年度总销售额的比例。

SELECT year, product_id, amount,
amount / SUM(amount) OVER (PARTITION BY year) AS yearly_percentage
FROM sales;

3. 计算每个产品每年的销售额与之前年份的销售额之差。

SELECT year, product_id, amount,
amount - LAG(amount) OVER (PARTITION BY product_id ORDER BY year) AS year_over_year_change
FROM sales;

4. 计算每个产品每年的销售额与前两年的销售额之差。

SELECT year, product_id, amount,
amount - LAG(amount, 2) OVER (PARTITION BY product_id ORDER BY year) AS year_over_year_change
FROM sales;

5. 计算每个产品每年的销售额与同一年上一个季度的销售额之差。

SELECT year, product_id, amount,
amount - LAG(amount) OVER (PARTITION BY product_id, QUARTER(date) ORDER BY year, QUARTER(date)) AS quarterly_change
FROM sales;

6. 计算每个产品每年的销售额与同一年上一个季度的销售额之差，并且只显示那些有增长的记录。

SELECT year, product_id, amount,
quarterly_change
FROM (
    SELECT year, product_id, amount,
    amount - LAG(amount) OVER (PARTITION BY product_id, QUARTER(date) ORDER BY year, QUARTER(date)) AS quarterly_change
    FROM sales
) AS subquery
WHERE quarterly_change > 0;

这些练习和实战旨在帮助开发者理解和应用MySQL窗口函数，它们涵盖了不同的应用场景，包括分区、排序、聚合以及使用窗口函数进行更复杂计算。