CREATE TABLE sales(
id INT PRIMARY KEY AUTO_INCREMENT,
city VARCHAR(15),
county VARCHAR(15),
sales_value DECIMAL
);
INSERT INTO sales(city,county,sales_value)
VALUES
('北京','海淀',10.00),
('北京','朝阳',20.00),
('上海','黄埔',30.00),
('上海','长宁',10.00);

-- 窗口函数DEMO
SELECT CITY AS 城市,COUNTY AS 区,SALES_VALUE AS 区销售额,
 SUM(SALES_VALUE) OVER(PARTITION BY CITY) AS 市销售额, -- 计算市销售额
 SALES_VALUE/SUM(SALES_VALUE) OVER(PARTITION BY CITY) AS 市比率,
 SUM(SALES_VALUE) OVER() AS 总销售额, -- 计算总销售额
 SALES_VALUE/SUM(SALES_VALUE) OVER() AS 总比率
 FROM sales ORDER BY CITY,COUNTY;

1. 窗口函数的概念

窗口函数与聚合函数的主要区别在于：

• 聚合函数（如 SUM、AVG）会对一组数据进行聚合并返回单一结果。
• 窗口函数 在计算过程中不会压缩结果集，而是为每一行提供附加信息，同时它的计算可以基于某些行的上下文（即 "窗口"）。

窗口函数的语法通常如下：

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

其中：

• PARTITION BY：定义分组（类似 GROUP BY，但不影响结果集的行数），窗口函数只在这些分组内计算。
• ORDER BY：定义窗口内的排序，用于计算的顺序。
• OVER：指定窗口函数的计算范围。

---

2. 常见的窗口函数

以下是一些常见的窗口函数和它们的功能：

窗口函数	描述
ROW_NUMBER()	为每一行生成唯一的序号，从 1 开始（基于窗口的排序）。
RANK()	类似于 ROW_NUMBER()，但如果存在并列值，则会跳过排名。
DENSE_RANK()	类似于 RANK()，但不会跳过排名。
NTILE(N)	将数据划分为 N 个桶，并为每行返回桶编号。
SUM()	返回当前行及窗口范围内的累积总和。
AVG()	返回当前行及窗口范围内的平均值。
MIN() / MAX()	返回窗口范围内的最小值/最大值。
LEAD() / LAG()	返回当前行的下一行/上一行的值。
FIRST_VALUE() / LAST_VALUE()	返回窗口内的第一行/最后一行的值。

---

3. 窗口函数的使用示例

3.1 使用 ROW_NUMBER() 实现行号

在一个销售表中为每个客户按照销售额降序编号：

SQL 示例（MySQL 和 Oracle 都支持）：

SELECT
    customer_id,
    order_id,
    total_amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS rank
FROM orders;

解释：

• PARTITION BY customer_id：按照每个客户分组。
• ORDER BY total_amount DESC：每个分组内按照 total_amount 降序排列。
• ROW_NUMBER()：为每组内的行分配唯一的行号。

---

3.2 使用 RANK() 和 DENSE_RANK()

计算每个客户的销售额排名，允许并列排名：

SQL 示例：

SELECT
    customer_id,
    order_id,
    total_amount,
    RANK() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS `rank`,
    DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS `dense_rank`
FROM orders;

区别：

• RANK()：如果两行的销售额相同，它们会有相同的排名，但排名会跳过。
• DENSE_RANK()：如果两行的销售额相同，它们会有相同的排名，但不会跳过排名。

---

3.3 使用 LEAD() 和 LAG()

比较每个订单与上一笔订单的销售额差异：

SQL 示例：

SELECT
    order_id,
    total_amount,
    LAG(total_amount) OVER (ORDER BY order_date) AS previous_order,
    LEAD(total_amount) OVER (ORDER BY order_date) AS next_order
FROM orders;

解释：

• LAG(total_amount)：返回当前行的上一行的 total_amount。
• LEAD(total_amount)：返回当前行的下一行的 total_amount。

---

3.4 累积和计算 (SUM())

计算每个客户的累积销售额：

SQL 示例：

SELECT
    customer_id,
    order_id,
    total_amount,
    SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS cumulative_total
FROM orders;

解释：

• SUM(total_amount)：计算当前行及之前行的累计总和。
• PARTITION BY customer_id：按客户分组。
• ORDER BY order_date：按订单日期排序。

---

3.5 找到窗口范围的最大值 (MAX() 和 MIN())

获取每个客户的订单中最大值和最小值：

SQL 示例：

SELECT
    customer_id,
    order_id,
    total_amount,
    MAX(total_amount) OVER (PARTITION BY customer_id) AS max_order,
    MIN(total_amount) OVER (PARTITION BY customer_id) AS min_order
FROM orders;

---

4. 窗口函数 vs 聚合函数

窗口函数的一个显著优势是它可以在计算的同时保留行的原始数据。

对比示例

假设我们有一张销售表 orders，计算每个客户的总销售额：

• 使用聚合函数：

SELECT customer_id, SUM(total_amount) AS total_sales
FROM orders
GROUP BY customer_id;

此时，只返回每个客户的聚合结果，订单的详细信息丢失。

• 使用窗口函数：

SELECT customer_id, order_id, total_amount,
       SUM(total_amount) OVER (PARTITION BY customer_id) AS total_sales
FROM orders;

窗口函数不仅保留了每行的详细信息（如 order_id 和 total_amount），还为每行附加了分组后的总销售额。