点击获取AI摘要

SQL 执行顺序与常用函数

🔄 SQL 语句执行顺序

  1. FROM
    从数据库复制原始表（生成临时表）
  2. WHERE
    在临时表中筛选符合条件的数据行
  3. GROUP BY
    按指定字段分组（类似Excel数据透视表的行标签）
  4. HAVING
    筛选满足条件的分组
  5. ORDER BY
    对结果进行排序
  6. LIMIT
    限制显示行数
  7. SELECT
    最终提取显示的字段

📝 注意：SELECT *实际在最后阶段执行

常用函数汇总

一、数学函数

CEIL(x) / CEILING(x)

  • 功能：向上取整，返回大于或等于x的最小整数

  • 参数：

    • x：要取整的数值

  • 示例：

    表达式 结果
    CEIL(3.14) 4
    CEIL(-3.14) -3
    CEILING(2.01) 3

FLOOR(x)

  • 功能：向下取整，返回小于或等于x的最大整数

  • 参数：

    • x：要取整的数值

  • 示例：

    表达式 结果
    FLOOR(3.97) 3
    FLOOR(-3.14) -4

ROUND(x, y)

  • 功能：四舍五入

  • 参数：

    • y>0：保留小数点后y位
    • y=0：取整数
    • y<0：小数点左侧指定位变0

  • 示例：

    表达式 结果
    ROUND(3.15, 1) 3.2
    ROUND(14.15, -1) 10

二、字符串函数

函数名/用法 功能说明 示例 示例结果
CONCAT(s1, s2, ...) 连接字符串（含NULL则返回NULL） CONCAT(‘My’,’ ',‘SQL’) My SQL
CONCAT(‘My’, NULL, ‘SQL’) NULL
REPLACE(s, s1, s2) 全量替换字符串 REPLACE(‘MySQL’,‘SQL’,‘sql’) Mysql
LEFT(s, n) 截取左侧n字符 LEFT(‘abcdefg’, 3) abc
RIGHT(s, n) 截取右侧n字符 RIGHT(‘abcdefg’, 3) efg
SUBSTRING(s, n, len) 从位置n截取len长度（支持负数位置） SUBSTRING(‘abcdefg’, -2, 3) fg
SUBSTRING(‘abcdefg’, 2) bcdefg
LOWER(s) 将字母变为小写 LOWER(‘A’) a
UPPER(s) 将字母变为大写 UPPER(‘a’) A
CHAR_LENGTH(s) 返回字符串的字符数 CHAR_LENGTH(‘数’) 1
LENGTH(s) 返回字符串的字节数，对于 ASCII 字符，与CHAR_LENGTH的结果是相同的 LENGTH(‘数’) 3
LOCATE(substr, s, [pos]) 返回substr在s中首次出现的位置，pos为起始 LOCATE(‘b’, ‘abcde’) 2
LOCATE(‘b’, ‘abcdebcd’, 3) 6
POSITION(substr IN s) 返回substr在s中首次出现的位置 POSITION(‘b’ IN ‘abcde’) 2
SUBSTRING_INDEX(s, d, n) 以分隔符d分割，取第n段（正/负数） SUBSTRING_INDEX(‘a,b,c’, ‘,’, 2) a,b
SUBSTRING_INDEX(‘a,b,c’, ‘,’, -2) b,c
TRIM(s) 去除字符串两端的空格 TRIM(’ abc ') abc
TRIM([remstr FROM] s) 去除字符串两端的指定字符 TRIM(‘a’ FROM ‘aaabcaa’) bc
TRIM(LEADING remstr FROM s) 去除字符串左侧的指定字符 TRIM(LEADING ‘a’ FROM ‘aaabcaa’) bcaa
TRIM(TRAILING remstr FROM s) 去除字符串右侧的指定字符 TRIM(TRAILING ‘a’ FROM ‘aaabcaa’) aaabc

补充：GROUP_CONCAT 函数

功能说明
将分组内的多个字符串值合并为一个字符串，常用于多值拼接（需配合 GROUP BY 使用）。

1
2
3
4
5
GROUP_CONCAT(
    [DISTINCT] 字段名 
    [ORDER BY 排序字段 [ASC|DESC]] 
    [SEPARATOR '分隔符']
)

示例数据（表 students）：

class name
A 张三
A 李四
B 王五 
1
2
3
4
5
SELECT 
    class,
    GROUP_CONCAT(name) AS members
FROM students
GROUP BY class;

结果：

class members
A 张三,李四
B 王五 
1
2
3
4
5
6
7
8
9
10
-- 去重 + 自定义分隔符 + 排序
SELECT 
    class,
    GROUP_CONCAT(
        DISTINCT name
        ORDER BY name DESC
        SEPARATOR '|'
    ) AS members
FROM students
GROUP BY class;

注意事项

  1. 长度限制：受 group_concat_max_len 参数限制（默认1024字节），超长部分被截断
  2. NULL处理：自动忽略 NULL 值
  3. 跨数据库差异：
    • MySQL：GROUP_CONCAT
    • PostgreSQL：STRING_AGG
    • SQL Server：STRING_AGG（2017+版本）

典型应用场景

  1. 标签聚合

    1
    2
    3
    4
    5
    6
    7
    8
    -- 文章表+标签表联查，合并文章的所有标签
    SELECT 
        a.title,
        GROUP_CONCAT(t.tag_name) AS tags
    FROM articles a
    JOIN article_tags at ON a.id = at.article_id
    JOIN tags t ON at.tag_id = t.id
    GROUP BY a.id;

  2. 路径生成

    1
    2
    3
    4
    5
    6
    -- 生成层级路径（部门树结构）
    SELECT 
        dept_id,
        GROUP_CONCAT(parent_name SEPARATOR ' > ') AS full_path
    FROM department_hierarchy
    GROUP BY dept_id;

  3. 动态SQL拼接

    1
    2
    3
    4
    5
    6
    7
    8
    -- 生成批量更新语句（示例用途）
    SELECT 
        CONCAT(
            'UPDATE users SET status=1 WHERE id IN (',
            GROUP_CONCAT(id SEPARATOR ','),
            ');'
        ) AS sql_statement
    FROM temp_ids;

补充：正则表达式函数

1. 字符集（[] 内可用的符号）
符号 含义 示例 匹配示例
a-z 任意小写字母 [a-z] a, b, …, z
A-Z 任意大写字母 [A-Z] A, B, …, Z
0-9 任意数字 [0-9] 0, 1, …, 9
. 字面量点（需转义 \.） [a-z.] a, b, .
_ 下划线 [a-z_] a, _
% 百分号 [a-z%] a, %
+ 加号（在 [] 内是普通字符） [a-z+] a, +
- 减号（需放在开头或结尾，否则表示范围如 a-z） [-a-z] 或 [a-z-] -, a
^ 在 [] 内开头表示否定（如 [^a-z] 匹配非小写字母） [^0-9] A, !（不匹配 1）
\w 等价于 [a-zA-Z0-9_]（单词字符） [\w] a, 1, _
\d 等价于 [0-9]（数字） [\d] 0, 1
\s 空白字符（空格、制表符 \t、换行 \n 等） [\s] ``, \t
\b 匹配 单词和非单词字符之间的位置（如空格、标点符号、字符串开头/结尾等） \\bword
word\\b
\\bword\\b		 匹配单词开头、结尾和整个单词
2. 量词（控制出现次数，单个字符不用加）
量词 含义 示例 匹配示例
* 零次或多次 a* "", a, aa（允许空字符串）
+ 一次或多次 a+ a, aa（不匹配空字符串）
? 零次或一次（可选） a? "", a
{n} 恰好 n 次 a{2} aa
{n,} 至少 n 次 a{2,} aa, aaa
{n,m} n 到 m 次 a{2,4} aa, aaa, aaaa
3. 边界和转义
符号 含义 示例 作用
^ 匹配字符串开头 ^[a-z] 必须以小写字母开头
$ 匹配字符串结尾 [a-z]$ 必须以小写字母结尾
\ 转义特殊字符（如 \\. 匹配点） example\\.com 避免 . 被解释为"任意字符"
` ` 或逻辑（匹配左边或右边） `cat
4. 组合用法示例
正则表达式 含义 匹配示例
^[a-zA-Z0-9._%+-]+@ 邮箱本地部分（字母/数字/._%±，至少1字符） user, name+123
@[a-zA-Z0-9.-]+\\.[a-z]{2,}$ 域名部分（含点和至少2字母TLD） @example.com, @sub.domain.co

注意事项

  1. 减号 -：在 [] 内如果不是开头/结尾，表示范围（如 a-z）。
    • ✅ 正确：[a-z-] 或 [-a-z]
    • ❌ 错误：[a-z-0-9]（会被解析为 z 到 - 的范围）
  2. 点 .：在 [] 外是通配符（匹配任意字符），需转义 \. 才能匹配字面量点。
  3. 大小写敏感：默认区分大小写，添加 i 标志可忽略（如 /^[a-z]+$/i）。

1. 正则匹配检测

函数/操作符 功能说明 数据库支持 示例
REGEXP_LIKE(str, pattern) 检查字符串是否匹配正则模式 Oracle, MySQL 8.0+, SELECT * FROM users WHERE REGEXP_LIKE(email, '^[a-z0-9]+@[a-z]+\.com$')
str REGEXP pattern 简写匹配操作符 MySQL, MariaDB SELECT 'abc123' REGEXP '^[a-z]+[0-9]+$' → 1 (匹配)
~ 正则匹配操作符 PostgreSQL SELECT 'abc' ~ '^a' → true

2. 正则替换

函数 功能说明 示例
REGEXP_REPLACE(str, pattern, replacement) 替换匹配正则的内容 REGEXP_REPLACE('Tel: 010-12345678', '[^0-9]', '') → '01012345678'

3. 子串提取

函数 功能说明 示例
REGEXP_SUBSTR(str, pattern) 提取第一个匹配的子串 REGEXP_SUBSTR('2023Q4 Report', '[0-9]+Q[1-4]') → '2023Q4'

4. 常用正则模式

模式 说明 应用场景示例
^abc 以"abc"开头 验证身份证号开头
xyz$ 以"xyz"结尾 检测文件扩展名
[0-9]{4} 连续4位数字 提取年份信息
\d{3}-\d{8} 匹配电话号码格式 010-12345678
[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,} 邮箱格式验证 过滤无效邮箱地址

5. 跨数据库语法对比

操作 MySQL/MariaDB PostgreSQL Oracle SQL Server
匹配检测 REGEXP / RLIKE ~ REGEXP_LIKE LIKE（有限支持）
替换 REGEXP_REPLACE REGEXP_REPLACE REGEXP_REPLACE 无内置函数
提取子串 REGEXP_SUBSTR SUBSTRING(str FROM pattern) REGEXP_SUBSTR PATINDEX+SUBSTRING

6. 使用示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 验证手机号格式（11位数字）
SELECT *
FROM customers 
WHERE phone REGEXP '^1[3-9][0-9]{9}$';

-- 提取带区号的电话号码
SELECT 
  REGEXP_SUBSTR(contact_info, '\([0-9]{3}\) [0-9]{8}') AS phone 
FROM contacts;

-- 清理混合文本中的字母
SELECT REGEXP_REPLACE('a1b2c3', '[A-Za-z]', '') → '123';

-- 查找包含价格的描述
SELECT * 
FROM products 
WHERE description ~ '\$[0-9]+\.[0-9]{2}';
  1. 字符集敏感：注意数据库字符集设置（如中文匹配需用[一-龥]）
  2. 特殊字符转义：需用\\转义（如匹配点号需写\\.）

三、日期函数

时间计算

1
2
DATE_ADD(date, INTERVAL expr type)  -- 时间加法
DATE_SUB(date, INTERVAL expr type)  -- 时间减法
  • 示例：
1
2
SELECT DATE_ADD('2021-08-03 23:59:59', INTERVAL 1 SECOND) → '2021-08-04 00:00:00'
SELECT DATE_SUB('2021-08-03', INTERVAL 2 MONTH) → '2021-06-03'

时间差与格式化

函数 功能说明 示例
DATEDIFF(date1, date2) 计算日期差（忽略时间） DATEDIFF(‘2021-06-08’,‘2021-06-01’) →7
DATE_FORMAT(date, format) 日期格式化 DATE_FORMAT(NOW(),‘%Y-%m’) → 2023-12

📅 DATE_FORMAT 格式符对照表

格式符 描述 示例值
日期相关
%Y 四位年份 2023
%y 两位年份 23
%m 两位月份（01-12） 07
%c 月份（1-12，无前导零） 7
%M 月份英文全称 January, December
%b 月份缩写 Jan, Dec
%d 两位日期（01-31） 09
%e 日期（1-31，无前导零） 9
%D 英文后缀日期（1st, 2nd…） 1st, 22nd
时间相关
%H 24小时制小时（00-23） 15
%h 12小时制小时（01-12） 03
%i 分钟（00-59） 08
%S 秒（00-59） 45
%p AM/PM AM, PM
星期相关
%W 星期全称 Monday, Sunday
%a 星期缩写 Mon, Sun
%w 数字星期（0=周日, 1=周一…） 1 (周一), 0 (周日)
组合格式
%T 时间（HH:mm:ss） 23:59:58
%r 带AM/PM的时间（hh:mm:ss AM） 11:59:58 PM
%x 年份周编号（年份部分） 2023 (用于周计算)
%v 周编号（周一为周起始） 52

使用示例

1
2
3
4
-- 原始时间：2023-07-25 14:30:45
DATE_FORMAT(date, '%Y/%m/%d %H:%i')    → 2023/07/25 14:30
DATE_FORMAT(date, '%b %D %Y %h:%i %p') → Jul 25th 2023 02:30 PM
DATE_FORMAT(date, '%W, %M %e')         → Tuesday, July 25

四、条件判断函数

IF 函数

1
IF(expr, v1, v2)  -- expr为真返回v1，否则v2
  • 示例：IF(1>2, 'Y', 'N') → N

CASE 表达式

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 简单CASE
CASE expr 
    WHEN v1 THEN r1 
    WHEN v2 THEN r2 
    ELSE rn 
END

-- 搜索CASE
CASE 
    WHEN condition1 THEN r1 
    WHEN condition2 THEN r2 
    ELSE rn 
END
  • 示例：
1
CASE WHEN 1<0 THEN 'T' ELSE 'F' END → F

NULLIF vs. IFNULL

特性 NULLIF IFNULL
功能 比较两个值，相等时返回 NULL 检查一个值是否为 NULL，并提供替代值
返回值 返回 NULL 或 expression1 返回 expression1 或 expression2
常见用途 避免除零错误、处理重复值 提供默认值、数据清洗
示例 NULLIF(a, b)：如果 a = b，返回 NULL IFNULL(a, b)：如果 a 是 NULL，返回 b

COALESCE 函数

功能说明

返回参数列表中第一个非 NULL 的值，常用于处理缺失值替换。

语法

1
COALESCE(v1, v2, v3, ..., vn)

执行逻辑

  1. 从左到右依次检查参数
  2. 返回第一个不为 NULL 的值
  3. 如果所有参数均为 NULL，则返回 NULL

示例

1
2
3
4
-- 数据示例：name字段为NULL，nickname='小张'，default_name='匿名用户'
COALESCE(NULL, '小张', '匿名用户') → '小张'
COALESCE(NULL, NULL, '2023-01-01') → '2023-01-01'
COALESCE(NULL, NULL, NULL) → NULL

与 IFNULL 的对比

特性 COALESCE IFNULL
参数数量 支持多个参数 仅支持两个参数
功能范围 多条件NULL处理 简单双值替换
可读性 更适合多字段优先级选择 适合简单场景

等价写法

1
2
3
4
5
6
7
COALESCE(a, b, c) 
-- 等价于 
CASE 
    WHEN a IS NOT NULL THEN a 
    WHEN b IS NOT NULL THEN b 
    ELSE c 
END

常见使用场景

  1. 多级备用值选择

    1
    2
    -- 优先显示用户昵称，其次邮箱，最后显示'未知用户'
    SELECT COALESCE(nickname, email, '未知用户') AS display_name FROM users;

  2. NULL值数据清洗

    1
    2
    -- 将NULL金额转换为0计算总和
    SELECT SUM(COALESCE(amount, 0)) AS total FROM orders;

  3. 多字段优先级合并

    1
    2
    -- 合并地址信息（优先使用详细地址，没有时使用区域地址）
    SELECT COALESCE(detail_address, area_address) AS full_address FROM locations;

五、类型转换函数

CAST(x AS type)

  • 支持类型：CHAR(n), DATE, TIME,DATETIME,DECIMAL等
  • 示例：CAST('2023' AS DECIMAL) → 2023

六、表连接补充自连接

自连接介绍

自连接是一种特殊的连接查询，指的是同一个表自己与自己进行连接。常用于处理具有层级关系的数据，例如员工与经理、分类的父类与子类等场景。自连接实际上是将同一个表视为两个不同的实例，通过别名进行区分，并根据关联条件进行连接。

自连接语法

自连接可以使用内连接或外连接语法，核心是为同一表赋予不同的别名：

1
2
3
4
5
6
7
8
9
10
11
-- 内自连接（查询匹配条件的记录）
SELECT 字段 
FROM 表 AS 别名1 
INNER JOIN 表 AS 别名2 
ON 别名1.字段 = 别名2.关联字段;

-- 左自连接（以左表为主，查询右表可能不存在的记录）
SELECT 字段 
FROM 表 AS 别名1 
LEFT JOIN 表 AS 别名2 
ON 别名1.字段 = 别名2.关联字段;

自连接示例

场景：假设有员工表 employees，结构如下：

id name manager_id
1 张三 NULL
2 李四 1
3 王五 1
4 赵六 2

需求：查询每个员工及其对应经理的名字（包括没有经理的员工）。

SQL语句：

1
2
3
4
5
6
SELECT 
    e1.name AS employee_name,
    e2.name AS manager_name
FROM employees e1
LEFT JOIN employees e2 
ON e1.manager_id = e2.id;

查询结果：

employee_name manager_name
张三 NULL
李四 张三
王五 张三
赵六 李四

自连接应用场景

  1. 层级关系：如组织架构、分类层级（父类与子类）。
  2. 数据对称性分析：如社交网络中的用户关系（用户A和用户B互为好友）。
  3. 路径查询：如地铁站点之间的连接关系。

注意事项

  1. 别名必要性：必须为表指定不同的别名以区分左、右表。
  2. 性能优化：自连接可能引发较大的计算开销，尤其是大表操作时，建议在关联字段上创建索引。
  3. 连接类型选择：
    • 使用 内自连接 时，只返回满足条件的记录（如“有经理的员工”）。
    • 使用 左自连接 时，会包含左表所有记录，右表无匹配则填充 NULL（如“包括无经理的员工”）。

七、交叉连接 CROSS JOIN

功能说明

生成两个表的笛卡尔积（所有行的组合），无关联条件。
特点：

  • 结果集行数 = 表A行数 × 表B行数
  • 不需要连接条件（无ON子句）
  • 常用于生成组合数据、测试数据等场景

语法形式

1
2
3
4
5
6
7
8
-- 显式语法
SELECT * 
FROM table1 
CROSS JOIN table2;

-- 隐式语法（等同于CROSS JOIN）
SELECT * 
FROM table1, table2;

典型应用场景

  1. 生成组合数据
    如：颜色与尺寸组合、日期与产品组合
  2. 数据模拟测试
    快速生成大量测试数据
  3. 全量关联分析
    计算所有可能的组合关系

与INNER JOIN的区别

特性 CROSS JOIN INNER JOIN
连接条件 无需ON子句 必须使用ON子句
结果集逻辑 强制所有行组合 仅匹配关联条件的行
数据量 可能极大（慎用） 通常较小

八、集合操作符 UNION ALL

功能说明

用于合并多个查询结果，保留所有记录（包括重复行）

基本语法

1
2
3
4
SELECT 字段列表 FROM 表1
UNION ALL
SELECT 字段列表 FROM 表2
...

与UNION的区别

特性 UNION ALL UNION
去重处理 保留所有记录，包括重复行 自动去重
排序操作 不排序 默认进行排序
性能 更高（无去重、排序开销） 较低

使用场景

  1. 合并分表数据（如按年份拆分的订单表）
  2. 需要保留重复记录的统计场景
  3. 明确知道数据无重复时的性能优化选择

注意事项

  • 合并的SELECT语句必须包含相同数量的字段
  • 对应字段的数据类型必须兼容
  • 最终结果集的字段名以第一个SELECT语句为准

使用示例

1
2
3
4
5
6
7
8
9
-- 合并两个季度的销售数据（保留重复）
SELECT product_id, sales FROM Q1_sales
UNION ALL
SELECT product_id, sales FROM Q2_sales;

-- 组合不同表结构数据（使用别名统一字段）
SELECT id, name, 'employee' AS type FROM employees
UNION ALL
SELECT customer_id, company_name, 'customer' FROM customers;

小结

  • 自连接本质：同一表通过别名模拟两个表的连接操作，支持内连接或外连接语法。
  • 核心步骤：
    1. 为同一表赋予不同别名（如 e1, e2）。
    2. 指定连接条件（如 e1.manager_id = e2.id）。
  • 适用场景：处理数据内部的层级或对称关系。
  • 优化建议：合理使用索引，避免全表扫描带来的性能问题。

👀 窗口函数详解

一、什么是窗口函数？

窗口函数（Window Function）是一种对查询结果集进行逐行计算的特殊函数，能够：

  • 在保留原始数据行的同时进行聚合/排序等操作
  • 实现复杂的分组统计需求（如：累计值、移动平均值、排名等）
  • 不会像GROUP BY那样合并结果集

二、标准语法

1
2
3
4
函数名() OVER (
    [PARTITION BY 字段]  -- 定义分组窗口
    [ORDER BY 字段 [ASC|DESC]]  -- 定义排序规则
)

三、核心组件

1. PARTITION BY

  • 作用：将数据按指定字段分组，每组称为一个窗口
  • 类似GROUP BY但不合并结果集
  • 示例：PARTITION BY department 按部门分组

2. ORDER BY

  • 作用：在窗口内指定排序规则
  • 示例：ORDER BY sales DESC 按销售额降序排列

四、常用窗口函数

1. 排序函数

函数 特点 示例结果（相同值处理）
RANK() 出现并列时跳号（1,1,3） 1,1,3,4
DENSE_RANK() 出现并列时不跳号（1,1,2） 1,1,2,3
ROW_NUMBER() 强制生成唯一序号 1,2,3,4

📝 示例数据（按成绩排序）：

1
2
3
4
5
6
7
SELECT 
    name,
    score,
    RANK() OVER(ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER(ORDER BY score DESC) AS dense_rank,
    ROW_NUMBER() OVER(ORDER BY score DESC) AS row_num
FROM students;
name score rank dense_rank row_num
张三 95 1 1 1
李四 95 1 1 2
王五 90 3 2 3

2. 偏移分析函数

函数 功能说明 参数说明
LAG(字段, 偏移量[, 默认值]) 获取当前行向上偏移的值 默认偏移量=1，默认值=NULL
LEAD(字段, 偏移量[, 默认值]) 获取当前行向下偏移的值 默认偏移量=1，默认值=NULL

📝 示例（查看相邻订单金额）：

1
2
3
4
5
6
SELECT 
    order_date,
    amount,
    LAG(amount, 1) OVER(ORDER BY order_date) AS prev_amount,
    LEAD(amount, 1) OVER(ORDER BY order_date) AS next_amount
FROM orders;
order_date amount prev_amount next_amount
2023-01-01 100 NULL 150
2023-01-02 150 100 200
2023-01-03 200 150 NULL

3.滑动窗口（窗口框架）

核心概念

通过定义窗口框架（Window Frame），动态控制计算范围。支持两种模式：

  • 基于行数（ROWS）：物理行偏移
  • 基于数值范围（RANGE）：逻辑值偏移

标准语法

1
2
3
4
5
函数() OVER (
    PARTITION BY ... 
    ORDER BY ...
    [ROWS|RANGE BETWEEN 起始点 AND 结束点]
)

框架边界定义

关键词 说明
UNBOUNDED PRECEDING 窗口起始位置（第一行/最小值）
UNBOUNDED FOLLOWING 窗口结束位置（最后一行/最大值）
CURRENT ROW 当前行
n PRECEDING 当前行向前n行/n值（包含当前行）
n FOLLOWING 当前行向后n行/n值（包含当前行）

常用模式示例

1. 累计计算（默认模式）
1
2
3
4
5
6
SUM(sales) OVER (
    ORDER BY date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) 
-- 等价简写：
SUM(sales) OVER (ORDER BY date)
2. 移动平均（近3天）
1
2
3
4
AVG(temperature) OVER (
    ORDER BY date 
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
3. 对称窗口（前后各1行）
1
2
3
4
MAX(score) OVER (
    ORDER BY id 
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)

ROWS vs RANGE 对比

对比项 ROWS RANGE
计算逻辑 按物理行偏移 按ORDER BY字段的数值偏移
适用场景 明确行数移动（如近3行） 按数值区间计算（如3天范围）
性能 更高 较低（需排序计算）
数据要求 无特殊要求 ORDER BY字段需为数值/日期类型 
1
2
3
4
5
6
7
8
9
10
11
-- ROWS模式（物理行）
SUM(amount) OVER (
    ORDER BY date 
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) → 当前行+前1行

-- RANGE模式（逻辑值）
SUM(amount) OVER (
    ORDER BY date 
    RANGE BETWEEN INTERVAL 1 DAY PRECEDING AND CURRENT ROW
) → 当天+前一天的所有记录

典型应用场景

  1. 金融分析
    • 7日移动平均线
    • 滚动波动率计算
  2. 电商分析
    • 近30天消费趋势
    • 周环比增长率
  3. 运营监控
    • 每小时累计UV
    • 滑动窗口异常检测

高级用法示例

动态最近N条记录统计

1
2
3
4
5
6
7
8
SELECT 
    order_id,
    order_time,
    AVG(amount) OVER (
        ORDER BY order_time 
        ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
    ) AS last_10_avg
FROM orders;

时间段累计（日期类型）

1
2
3
4
5
6
7
SELECT 
    report_date,
    SUM(revenue) OVER (
        ORDER BY report_date 
        RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
    ) AS 7day_revenue 
FROM daily_stats;

注意事项

  1. 性能优化
    • 避免大范围窗口（如UNBOUNDED+大数据量表）
    • 优先使用ROWS模式
    • 配合合适的索引（ORDER BY字段）
  2. 边界处理
    • 窗口起始前的行返回NULL
    • 使用COALESCE处理空值
  3. 框架限制
    • RANGE模式不支持n FOLLOWING（MySQL限制）
    • 不同数据库实现细节可能有差异

五、综合应用场景

案例：部门薪资排名

1
2
3
4
5
6
SELECT 
    department,
    name,
    salary,
    RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;
department name salary dept_rank
技术部 张三 15000 1
技术部 李四 12000 2
市场部 王五 13000 1

六、注意事项

  1. 窗口函数执行顺序：在WHERE、GROUP BY之后，ORDER BY之前
  2. 可以组合使用多个窗口函数
  3. 不同数据库支持情况可能不同（MySQL 8.0+支持）
  4. 大数据量时注意性能优化