SQL窗口函数完全指南：从基础概念到实战应用

SQL窗口函数完全指南：从基础概念到实战应用

SQL窗口函数是SQL语言中一个非常强大但经常被忽视的功能。它允许在查询中进行复杂的计算，如累积求和、时间序列分析和数据填充等。本文将通过多个实际案例，详细介绍窗口函数的基本概念和具体应用，帮助读者掌握这一重要工具。

照片由Marcus Woodbridge拍摄。

什么是窗口函数？

传统的SQL查询基于关系代数理论，其中表中的行被视为无序的元组集合。这意味着在标准SQL中，无法直接基于其他行的值进行计算。而窗口函数则突破了这一限制，允许在查询中引用其他行的数据，从而实现更复杂的分析功能。

基础案例与基本结构

1. 聚合但无聚合过程

窗口函数最直观的应用之一是无需进行表压缩就能完成聚合计算。例如，要计算一个支出表的总和，传统方法会使用GROUP BY：

SELECT SUM(value) AS total FROM myTable

而使用窗口函数，可以在保留原始行结构的同时添加聚合结果：

SELECT *, SUM(value) OVER() FROM myTable

2. 时间和排序意识

窗口函数特别适用于需要基于时间序列或排序顺序进行计算的场景。例如，计算每年的身高增长：

SELECT 年份, 高度, LAG(高度) OVER (ORDER BY 年份) AS 上一年度高度 FROM myTable

3. 时间感知与整合

通过结合PARTITION BY和ORDER BY，窗口函数可以处理更复杂的分组和排序需求。例如，计算每个孩子的年度身高增长率：

SELECT 1 - 身高 / LAG(身高) OVER (PARTITION BY 名称 ORDER BY 年) ...

4. 排名与位置

窗口函数还包括专门用于排名的函数，如ROW_NUMBER()：

SELECT ROW_NUMBER() OVER(ORDER BY score) -- 计算行号并按分数排序

5. 窗口尺寸

窗口函数还支持定义计算范围，即只考虑当前行及其前后指定数量的行。例如，计算三天内的平均病例数：

SELECT AVG(n_cases) OVER (
    ORDER BY date_reference
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

实际应用场景

累积总和随时间变化

计算每月累计收入：

日志表中的事件时长

处理日志表中的事件持续时间：

填充缺失值

使用窗口函数填充数据表中的缺失值：

基于一组列的行去重

去除重复数据：

计算组或类在总行数中所占的百分比

计算各类别所占比例：

总结

窗口函数是SQL语言中一个强大但常被忽视的功能。通过本文的介绍，希望读者能够理解窗口函数的基本概念和应用场景，并能够在实际工作中灵活运用这一工具。虽然SQL可能看起来有些“古老”，但它仍然是数据处理领域不可或缺的工具，掌握其高级功能将为数据处理工作带来极大的便利。

参考文献

1. PostgreSQL窗口函数的数据处理任务. (n.d.). Timescale.链接.
2. Kho, J. (2022年6月5日). 高级SQL窗口函数的简易指南 — 数据科学.Medium.
3. Markingmyname. (2023年11月16日).Transact-SQL中的分析功能 — SQL Server.Microsoft Learn.
4. PostgreSQL教程. (2021年4月27日).PostgreSQL窗口函数完整指南.链接.
5. VanMSFT. (2023年5月23日).OVER子句 (Transact-SQL) — SQL Server.Microsoft Learn.
6. 窗口函数. (n.d.).SQLite官方文档.
7. 窗口函数. (2014年7月24日).PostgreSQL官方文档.

本文中的所有图片均由作者制作。