MySQL窗口函数，让数据处理效率飙升！

数据分析与统计学之美 2025-04-16

161

什么是窗口函数？

窗口函数，也称为分析函数或OLAP函数，是SQL中一类特殊的函数，它们用于在数据集的某个子集（称为窗口）上进行计算，同时保持各行的独立性。与GROUP BY聚合不同，窗口函数不会减少行数，而是为每一行添加计算结果。这使得窗口函数在数据分析中具有广泛的应用场景，如排名、累计总和、移动平均等。

窗口函数的语法

窗口函数通常与OVER子句一起使用，OVER子句定义了窗口的范围，可以指定分区、排序规则等。窗口函数的语法如下：

<窗口函数> OVER ([PARTITION BY <分组列>] [ORDER BY <排序列>] [ROWS/RANGE <窗口范围>])

其中：

PARTITION BY：用于指定分区列，将数据分成不同的组，每组内独立进行计算。
ORDER BY：用于指定排序列，确定窗口内的计算顺序。
ROWS/RANGE：用于定义窗口的范围，ROWS表示按行数定义窗口，RANGE表示按值范围定义窗口。

示例数据集

为了更好地理解窗口函数的应用，我们构造一个销售数据表sales，包含以下字段：

id：销售记录的唯一标识
region：销售地区
amount：销售金额
sale_date：销售日期

-- MySQL示例
CREATE TABLE sales (
    id INT PRIMARY KEY,
    region VARCHAR(50),
    amount DECIMAL(10,2),
    sale_date DATE
);
 
INSERT INTO sales (id, region, amount, sale_date) VALUES
(1, 'North', 1000.50, '2025-04-01'),
(2, 'North', 1500.75, '2025-04-02'),
(3, 'South', 800.25, '2025-04-01'),
(4, 'West', 1200.00, '2025-04-02'),
(5, 'East', 2000.30, '2025-04-01'),
(6, 'North', 900.00, '2025-04-03'),
(7, 'South', 1100.00, '2025-04-02'),
(8, 'West', 1300.00, '2025-04-03'),
(9, 'East', 1800.00, '2025-04-02'),
(10, 'North', 1400.00, '2025-04-04');
 
-- Oracle示例（语法类似，只需调整数据类型和日期格式）
CREATE TABLE sales (
    id NUMBER PRIMARY KEY,
    region VARCHAR2(50),
    amount NUMBER(10,2),
    sale_date DATE
);
 
INSERT INTO sales (id, region, amount, sale_date) VALUES
(1, 'North', 1000.50, TO_DATE('2025-04-01', 'YYYY-MM-DD')),
(2, 'North', 1500.75, TO_DATE('2025-04-02', 'YYYY-MM-DD')),
...（其余数据插入类似）

窗口函数示例

1. 排名函数

排名函数用于为结果集中的每一行分配一个排名值。常见的排名函数有ROW_NUMBER、RANK、DENSE_RANK。

ROW_NUMBER：为每一行分配一个唯一的序号，序号连续且不重复。

-- MySQL
SELECT 
    id, region, amount, sale_date,
    ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num
FROM sales;
 
-- Oracle
SELECT 
    id, region, amount, sale_date,
    ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num
FROM sales;

RANK：为每一行分配一个排名值，相同值的行具有相同的排名，但排名不连续。

-- MySQL
SELECT 
    id, region, amount, sale_date,
    RANK() OVER (ORDER BY amount DESC) AS rank_num
FROM sales;
 
-- Oracle
SELECT 
    id, region, amount, sale_date,
    RANK() OVER (ORDER BY amount DESC) AS rank_num
FROM sales;

DENSE_RANK：为每一行分配一个排名值，相同值的行具有相同的排名，但排名连续。

-- MySQL
SELECT 
    id, region, amount, sale_date,
    DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank_num
FROM sales;
 
-- Oracle
SELECT 
    id, region, amount, sale_date,
    DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank_num
FROM sales;

2. 聚合函数

聚合函数用于对窗口内的数据进行统计计算。常见的聚合函数有SUM、AVG、COUNT、MAX、MIN。

SUM：计算窗口内指定列的总和。

-- MySQL
SELECT 
    id, region, amount, sale_date,
    SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales;
 
-- Oracle
SELECT 
    id, region, amount, sale_date,
    SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales;

AVG：计算窗口内指定列的平均值。

-- MySQL
SELECT 
    id, region, amount, sale_date,
    AVG(amount) OVER (PARTITION BY region) AS region_avg
FROM sales;
 
-- Oracle
SELECT 
    id, region, amount, sale_date,
    AVG(amount) OVER (PARTITION BY region) AS region_avg
FROM sales;

3. 跨行函数

跨行函数用于访问当前行之外的行数据。常见的跨行函数有LAG、LEAD。

LAG：访问当前行之前的第n行数据。

-- MySQL
SELECT 
    id, region, amount, sale_date,
    LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS prev_amount
FROM sales;
 
-- Oracle
SELECT 
    id, region, amount, sale_date,
    LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS prev_amount
FROM sales;

LEAD：访问当前行之后的第n行数据。

-- MySQL
SELECT 
    id, region, amount, sale_date,
    LEAD(amount, 1, 0) OVER (ORDER BY sale_date) AS next_amount
FROM sales;
 
-- Oracle
SELECT 
    id, region, amount, sale_date,
    LEAD(amount, 1, 0) OVER (ORDER BY sale_date) AS next_amount
FROM sales;

MySQL与Oracle窗口函数的差异

内置函数支持：Oracle提供了更多的内置窗口函数，如NTILE、FIRST_VALUE、LAST_VALUE等，而MySQL的窗口函数支持相对较少。
语法细节：在语法上，两者基本一致，但Oracle支持更复杂的窗口范围定义，如使用RANGE BETWEEN INTERVAL来定义时间范围。
性能优化：Oracle在窗口函数的性能优化方面可能更为成熟，尤其是在处理大数据量时。但具体性能还需根据数据库版本和硬件配置进行评估。

窗口函数的应用场景

窗口函数在数据分析中具有广泛的应用场景，如：

计算排名和百分比排名。
计算累计总和、平均值等统计指标。
访问前后行的数据，进行时间序列分析等。
计算分布函数，了解数据在窗口内的分布情况。

通过合理使用窗口函数，你可以更加灵活地处理复杂的数据分析任务，提高数据分析的效率和准确性。

总结

窗口函数是SQL中一类强大的工具，它能在不减少行数的情况下，对每一行数据进行复杂的计算。通过对比MySQL和Oracle数据库中的窗口函数用法和差异，我们可以看到两者在功能和支持上各有特点。在实际应用中，我们可以根据具体需求和数据库环境选择合适的窗口函数和数据库系统。

墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者