0、概述

本文以班主任筛选班级成绩排名为背景，对比select嵌套查询与开窗函数效率，介绍其语法、类型，最后附三道sql练习题以及题解。

1、背景

你是一个班主任，现在有一个年级的成绩单，你需要把你们班的成绩筛选出来并排名。

姓名	班级	成绩
豆小包	1	85
迪普西克	2	73
克劳德	2	83
元大宝	3	75
···	···	···

你现在立刻就想到了，需要先利用班级列筛选出你班级的同学，再在班内通过计数法得出成绩排名，两步走。但天才如你，写出了select嵌套。

SELECT t1.姓名, t1.成绩, (  
  SELECT COUNT(DISTINCT t2.成绩)  
  FROM 成绩单表 t2  
  WHERE t2.班级=1 AND t2.成绩 >= t1.成绩  
) AS 排名  
FROM 成绩单表 t1 WHERE t1.班级=1;

你盯着电脑屏幕半天，隔壁班主任已经开完家长会了，你还没查出成绩。

2、为什么开窗

隔壁班主任提议你开窗，但你此时并不理解开着空调开窗的操作。经过他的一番指导，你引入了over()

为什么慢？执行过程是什么？

我们分析一下select嵌套的执行流程：

找出1班的学生（一次扫描）；
对每条1班学生的成绩，执行内层子查询：
再次扫描 1 班数据，筛选成绩≥当前成绩的数据行（两次扫描）；
统计上面找到的不同成绩值的个数，也即是该成绩的对应排名（COUNT(DISTINCT)）。

很明显，上述过程中出现了两次对指定数据的扫描操作，如此时间复杂度会得到指数级增加。

如果开窗呢？

SELECT 姓名, 成绩, RANK() OVER (ORDER BY 成绩 DESC)  
FROM 成绩单表 WHERE 班级=1;

执行流程：

找出1班的学生（一次扫描）；
对结果集排序后直接计算排名（内存中一次性处理）。

显而易见，这是更高效的方式

3、什么是开窗

现在我们回到sql发出提问，什么是开窗函数，为什么叫开窗函数。

是什么？

核心定义：
开窗函数（WindowFunction）是一种在数据子集（窗口）上执行计算，同时保留原始的特殊函数。通过OVER()子句定义窗口范围，支持分区、排序及动态范围控制。同时它也叫OLAP函数（OnlineAnallyticalProcessing，联机分析处理），可以对数据库数据进行进行复杂分析。

听着很抽象，我们不妨再回到一开始的select嵌套。select嵌套在干什么？无非是根据一开始两步走的思路，将查询分为了两次，第一次查出需要的数据子集，第二次在进行需求查询排名。而这里找到数据子集的过程，我们就可以理解为开窗。

对于查成绩的场景，我们并不需要直白的执行两次查询，通过开窗函数，我们可以一次查询出需要的成绩，后将其作为一个独立的结果集在内部进行排序工作即可。

为什么？

这么看来，select嵌套和over()好像在干一件事，为什么效率差别这大呢？

SELECT嵌套像查字典：每查一个词就要翻一遍整本词典（成绩表），100个词翻100遍；OVER()像老师排座次：只看一次成绩单，按分数高低从头排到尾（一次搞定全班的排名）。

开窗函数：数据库内核级优化支持

依赖数据库的向量化执行引擎（VectorizedExecution），将数据按列批量处理，避免逐行解析；排序、排名等操作可直接利用索引有序性（如B + 树索引），或通过内存中的高效排序算法（如归并排序）完成。

典型流程：

扫描表并筛选数据（如班级 = 1）；
按窗口条件（如成绩排序）对结果集排序；
遍历排序后的结果，按顺序生成排名（如 RANK ()）。

SELECT 嵌套：基于 SQL 语法的语义解析

遵循 SQL 的 “嵌套查询语义”，内层查询被视为外层每行的"子任务"，由查询优化器拆解为嵌套循环（Nested Loop Join）；缺乏内核级优化，每次子查询需重新解析SQL、生成执行计划、访问存储引擎。

典型流程：

扫描表并筛选外层数据（如班级 = 1）；
对每行外层数据，生成内层查询条件（如t2.成绩>= t1.成绩）；
执行内层查询，扫描表、筛选数据、计算聚合，返回结果给外层。

4、怎么开窗

怎么做？

开窗函数（Window Function）在 DM数据库（达梦数据库）中的实现基于其查询优化器和执行引擎的特性，通过特定的语法结构和执行流程来完成数据处理。

语法：

OVER ( \[ PARTITION BY column \] \[ ORDER BY culumn \] )

拆成两块看，其中PARTITION BY 子句负责进行分组；ORDER BY
子句负责进行排序。OVER()指定一组行，开窗函数计算从窗口函数输出的结果集中各行的值。

开窗函数不需要使用GROUP BY就可以对数据进行分组，还可以同时返回基础行的列和聚合列。

用法：

支持的开窗函数类型：

排名函数：RANK()、DENSE_RANK()、ROW_NUMBER()、NTILE(n)
分析函数：SUM()、AVG()、MAX()、MIN()、COUNT()（聚合函数作为开窗函数）
偏移函数：LEAD()、LAG()、FIRST_VALUE()、LAST_VALUE()

回到最开始的班级成绩表：

SELECT 姓名, 成绩, RANK() OVER (ORDER BY 成绩 DESC)  
FROM 成绩单表 WHERE 班级=1;

再拆解：

FROM 成绩单表
定位数据源表。
WHERE 班级=1
筛选出班级 1 的所有学生记录（例如 10 条记录）。
ORDER BY 成绩 DESC（窗口函数内）
**将筛选后的结果按成绩从高到低排序（例如：95, 90, 90, 85…）。
RANK() 计算排名

为每条记录分配排名：最高分排名=1，相同成绩者并列（如两个 90 分均排名
2），后续名次跳过并列数（下一位 85 分排名 4）。
因无 PARTITION BY，排名在全班范围内计算。

SELECT 返回结果
输出姓名、成绩、排名三列。

graph TD
    A[FROM 成绩单表] --> B[WHERE 班级=1]
    B --> C[按成绩 DESC 排序]
    C --> D[RANK 计算排名]
    D --> E[SELECT 姓名 成绩 排名]

至此我们依据2W1H的思路完成了对开窗函数的初步了解学习。

5、数据对比

多说无益，数据说话，我们分别在MySQL中用两种写法进行速度对比。这里我们选择阿里天池中的经典sql题目之红酒厂。该题需要在一张表中找出ph值等于3.03的酒后，对他们进行中式排名并输出。首先，我们打开mysql的profiling，帮助我们获取更精确的sql执行时间。

set profiling=1;

再分别执行一下两种写法：

select嵌套

SELECT 
    t1.pH,
    t1.`citric acid`,
    (SELECT COUNT(DISTINCT t2.`citric acid`) 
     FROM `winequality-red` t2 
     WHERE t2.`citric acid` &lt;= t1.`citric acid`
    ) AS rankn
FROM `winequality-red` t1
WHERE t1.pH = '3.03';

开窗函数

SELECT 
    pH, 
    `citric acid`,
    DENSE_RANK() OVER (ORDER BY `citric acid`) AS rankn  -- 中式排名（并列不跳过）
FROM `winequality-red`
WHERE pH = '3.03';  -- 注意pH为字符串类型[6](@ref)

查看执行明细

show profiles;

分别定位到执行的两条指令时间。

两者相差约8倍。

6、总结

开窗函数的核心优势：开窗函数通过OVER()子句定义数据窗口，能在不使用GROUP
BY的情况下对数据进行分组、排序及复杂计算，相比传统嵌套查询，其依赖数据库内核级优化（如向量化执行引擎、索引利用），避免重复扫描和子查询嵌套，大幅提升查询效率。
关键语法与函数

语法结构：函数名() OVER([PARTITION BY 分组列][ORDER BY
排序列])，其中PARTITION BY用于分组，ORDER BY用于排序。
常见函数
- 排名函数：RANK()（英式排名，并列跳过后续名次）、DENSE_RANK()（中式排名，并列不跳过）、ROW_NUMBER()（连续编号）、NTILE(n)（将数据分n组）。
- 分析函数：SUM()、AVG()等聚合函数作为开窗函数时，可计算窗口内的累计或移动聚合值。
- 偏移函数：LEAD()、LAG()用于获取上下行数据，FIRST_VALUE()、LAST_VALUE()获取窗口内首尾值。

典型应用场景

排名计算：如按成绩对学生排名，RANK() OVER(ORDER BY 成绩 DESC)。
分组聚合分析：结合PARTITION BY按班级分组后计算各组成绩均值。

附录：三道Sql练习题题解（Mysql）

1、winequality-red

数据示例：

题解：

SELECT 
    pH, 
    `citric acid`,
    DENSE_RANK() OVER (ORDER BY `citric acid`) AS rankn  -- 中式排名（并列不跳过）
FROM `winequality-red`
WHERE pH = '3.03';  -- 注意pH为字符串类型[6](@ref)

2、Macro&Industry

数据示例：

题解：

-- 步骤1：获取2015年用电最高峰月份
WITH Peak2015 AS (
    SELECT 
        PERIOD_DATE,
        MAX(DATA_VALUE) AS MaxValue2015
    FROM `macro industry`
    WHERE 
        INDIC_ID = '2020101522'  -- 第一产业用电量指标ID[4](@ref)
        AND YEAR(PERIOD_DATE) = 2015
    GROUP BY PERIOD_DATE
    ORDER BY MaxValue2015 DESC
    LIMIT 1
),
-- 步骤2：获取2014年同期数据
YoY2014 AS (
    SELECT 
        PERIOD_DATE,
        DATA_VALUE AS Value2014
    FROM `macro industry`
    WHERE 
        INDIC_ID = '2020101522'
        AND YEAR(PERIOD_DATE) = 2014
        AND MONTH(PERIOD_DATE) = (SELECT MONTH(PERIOD_DATE) FROM Peak2015)  -- 匹配2015高峰月份
)
-- 步骤3：计算增长率
SELECT 
    Peak2015.PERIOD_DATE AS PeakMonth2015,
    Peak2015.MaxValue2015,
    YoY2014.Value2014,
    (Peak2015.MaxValue2015 - YoY2014.Value2014) / YoY2014.Value2014 * 100 AS GrowthRate
FROM Peak2015
JOIN YoY2014 
    ON MONTH(Peak2015.PERIOD_DATE) = MONTH(YoY2014.PERIOD_DATE);

3、winequality-white

数据示例：

题解：

SELECT 
    pH, 
    `residual sugar`,
    RANK() OVER (ORDER BY `residual sugar`) AS rankn  -- 英式排名（并列跳过）
FROM `winequality-white`
WHERE pH = '3.63';  -- pH作为字符串处理[4](@ref)

SQL学习之开窗函数以及三道sql题

0、概述

1、背景

2、为什么开窗

3、什么是开窗

是什么？

为什么？

4、怎么开窗

怎么做？

语法：

用法：

再拆解：

5、数据对比

6、总结

附录：三道Sql练习题题解（Mysql）

1、winequality-red

2、Macro&Industry

3、winequality-white

作者