SQL中如何使用窗口函数_SQL窗口函数的用法详解-SQL-PHP中文网

窗口函数通过OVER()子句在不聚合原始数据的前提下，实现分区、排序和帧范围内的计算，解决了传统SQL无法同时保留明细数据并进行组内分析的痛点，适用于排名、累计计算、移动平均等场景，且需注意索引优化与内存消耗。

sql中如何使用窗口函数_sql窗口函数的用法详解

SQL中的窗口函数，简单来说，就是一种能在查询结果集的一个“窗口”内执行计算的强大工具。它允许你在不聚合整个结果集（像GROUP BY那样）的情况下，对与当前行相关的多行数据进行计算。这就像你站在一扇窗户前，能看到窗外一片区域的风景，并基于这片区域的特定规则（比如，最高的那棵树、最近的那辆车）做出判断，而不是只能看到整个城市的鸟瞰图。

解决方案

窗口函数的核心在于OVER()子句，它定义了这个“窗口”的范围和行为。通过PARTITION BY，你可以将数据分成不同的组（分区），函数在每个分区内独立计算。而ORDER BY则决定了分区内数据的排序方式，这对于排名、累积计算或获取相邻行数据至关重要的。最后，ROWS或RANGE子句能进一步精确控制窗口帧，比如只看当前行之前或之后N行的数据。

想象一下，你有一张销售订单表，想知道每个员工的销售额在他们所在部门的排名，同时还想看到他们部门的总销售额，但又不想把每个员工的详细订单信息给“折叠”掉。传统SQL用GROUP BY就很难直接做到这一点，因为它会把员工行聚合成部门总销售额，失去了个体数据。窗口函数就是为这种场景而生的，它能在保留原始行数据的前提下，进行复杂的分析计算。

-- 假设我们有一个销售表 Sales，包含 EmployeeID, Department, SaleAmount
-- 目标：计算每个员工在部门内的销售额排名，并显示部门总销售额

SELECT
    EmployeeID,
    Department,
    SaleAmount,
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY SaleAmount DESC) AS DeptRank, -- 部门内销售额排名
    SUM(SaleAmount) OVER (PARTITION BY Department) AS DepartmentTotalSales -- 部门总销售额
FROM
    Sales;

登录后复制

这个例子清晰地展示了窗口函数的魅力：ROW_NUMBER()和SUM()都被赋予了OVER()子句，前者根据部门分区并按销售额降序排名，后者同样根据部门分区计算总和。最关键的是，原始的EmployeeID、Department、SaleAmount等行数据都还在，没有因为计算而被聚合掉。

为什么我们需要窗口函数？它们解决了哪些传统SQL的痛点？

说实话，刚接触窗口函数的时候，我总觉得GROUP BY和各种JOIN组合已经够用了。直到遇到那种“既要看整体，又要看个体”的需求，才发现窗口函数是多么巧妙。传统SQL在处理这类问题时，往往会显得笨拙甚至力不从心。

一个典型的痛点是，当你需要计算一个子集的聚合值，但又不想让整个结果集被聚合时。比如，你想找出每个产品类别中销售额最高的三个产品，并同时显示它们的具体销售额。如果用GROUP BY，你只能得到每个类别的总销售额，而无法直接获取到“前三”的具体产品。你可能需要子查询、临时表或者复杂的自连接来模拟，但这些方法不仅写起来复杂，性能也可能不尽如人意，尤其是在数据量大的时候，调试起来更是噩梦。

窗口函数完美地解决了这个矛盾。它允许你在一个“逻辑分区”内进行计算，比如按产品类别分区，然后在这个分区内对产品销售额进行排名。RANK()、DENSE_RANK()或ROW_NUMBER()这类函数能直接给出你想要的排名，然后你可以在外部查询中轻松筛选出前N名。它避免了多重JOIN可能带来的笛卡尔积风险，也减少了对临时表的需求，让SQL查询更加简洁、高效。

再比如，计算移动平均值或累计总和。如果没有窗口函数，你可能需要一个复杂的自连接，将每一行与它之前的所有相关行连接起来，这在性能上简直是灾难。但有了SUM() OVER (ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)，一行代码就能搞定，并且数据库引擎通常会对其进行优化，效率高得多。在我看来，窗口函数简直是SQL世界里的一把瑞士军刀，它填补了传统聚合和详细数据查询之间的鸿沟。

窗口函数的核心语法结构是怎样的？有哪些常见的函数类型？

理解窗口函数的语法结构是掌握它的关键。它的基本形式是：函数名(表达式) OVER ([PARTITION BY 列名1, 列名2...] [ORDER BY 列名3 [ASC|DESC], 列名4...] [ROWS/RANGE BETWEEN ...])。

函数名(表达式): 这里可以是任何聚合函数（如SUM, AVG, COUNT, MAX, MIN），也可以是专门的窗口函数（如ROW_NUMBER, RANK, LAG, LEAD）。
OVER(): 这是窗口函数的标志，它告诉数据库这个函数应该作为一个窗口函数来执行。
PARTITION BY: 这个子句是可选的，它将结果集分成独立的、不重叠的逻辑分区。函数会在每个分区内独立执行。如果省略，则整个结果集被视为一个分区。
ORDER BY: 这个子句也是可选的，它定义了每个分区内行的排序顺序。这对于排名函数和那些依赖于行顺序的函数（如LAG, LEAD, 累积求和）至关重要。
ROWS/RANGE BETWEEN ...: 这是最灵活也最复杂的子句，它定义了“窗口帧”——即在当前分区内，哪些行应该包含在当前行的计算中。
- UNBOUNDED PRECEDING: 从分区的第一行开始。
- N PRECEDING: 当前行之前的N行。
- CURRENT ROW: 当前行。
- N FOLLOWING: 当前行之后的N行。
- UNBOUNDED FOLLOWING: 到分区的最后一行。
- 例如：ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 表示从分区开始到当前行的所有行（常用于累计求和）。
- ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 表示当前行以及它前后各一行。

常见的窗口函数类型：

排名函数 (Ranking Functions):

ROW_NUMBER(): 为分区内的每一行分配一个唯一的、连续的整数。

SELECT EmployeeID, Department, SaleAmount,
       ROW_NUMBER() OVER (PARTITION BY Department ORDER BY SaleAmount DESC) AS RowNum
FROM Sales;

登录后复制

RANK(): 为分区内的每一行分配一个排名。如果有相同的值，它们会得到相同的排名，下一个不同的值会跳过排名。
```
SELECT EmployeeID, Department, SaleAmount,
       RANK() OVER (PARTITION BY Department ORDER BY SaleAmount DESC) AS RankNum
FROM Sales;
```
登录后复制

DENSE_RANK(): 类似于RANK()，但相同值后的排名不会跳过。

SELECT EmployeeID, Department, SaleAmount,
       DENSE_RANK() OVER (PARTITION BY Department ORDER BY SaleAmount DESC) AS DenseRankNum
FROM Sales;

登录后复制

NTILE(N): 将分区内的行分成N组，并为每行分配一个组号。

SELECT EmployeeID, Department, SaleAmount,
       NTILE(4) OVER (PARTITION BY Department ORDER BY SaleAmount DESC) AS Quartile
FROM Sales;

登录后复制

分析函数 (Analytic/Value Functions):

LAG(expression, offset, default): 获取当前行之前N行的expression值。

-- 获取每个员工上一次销售的金额
SELECT EmployeeID, SaleDate, SaleAmount,
       LAG(SaleAmount, 1, 0) OVER (PARTITION BY EmployeeID ORDER BY SaleDate) AS PreviousSaleAmount
FROM Sales;

登录后复制

LEAD(expression, offset, default): 获取当前行之后N行的expression值。

-- 获取每个员工下一次销售的金额
SELECT EmployeeID, SaleDate, SaleAmount,
       LEAD(SaleAmount, 1, 0) OVER (PARTITION BY EmployeeID ORDER BY SaleDate) AS NextSaleAmount
FROM Sales;

登录后复制

FIRST_VALUE(expression): 获取分区内第一行的expression值。

-- 获取每个部门销售额最高的员工的销售额
SELECT EmployeeID, Department, SaleAmount,
       FIRST_VALUE(SaleAmount) OVER (PARTITION BY Department ORDER BY SaleAmount DESC) AS TopDeptSale
FROM Sales;

登录后复制

LAST_VALUE(expression): 获取分区内最后一行的expression值。

-- 获取每个部门销售额最低的员工的销售额
SELECT EmployeeID, Department, SaleAmount,
       LAST_VALUE(SaleAmount) OVER (PARTITION BY Department ORDER BY SaleAmount ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS BottomDeptSale
FROM Sales;

登录后复制

聚合函数作为窗口函数 (Aggregate Functions as Window Functions):

SpeakingPass-打造你的专属雅思口语语料
使用chatGPT帮你快速备考雅思口语，提升分数

25

查看详情
- SUM() OVER(...), AVG() OVER(...), COUNT() OVER(...), MAX() OVER(...), MIN() OVER(...)
```
-- 计算每个员工的累计销售额和部门平均销售额
SELECT EmployeeID, SaleDate, SaleAmount,
       SUM(SaleAmount) OVER (PARTITION BY EmployeeID ORDER BY SaleDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeSales,
       AVG(SaleAmount) OVER (PARTITION BY Department) AS DepartmentAvgSales
FROM Sales;
```
  登录后复制
  这些函数在OVER()子句的加持下，不再将行聚合成单个结果，而是在每个窗口内进行聚合计算，并为窗口中的每一行返回结果。

实际场景中，窗口函数有哪些高级应用和性能考量？

窗口函数远不止排名和简单的聚合，它们在很多高级分析场景中都表现出色。

高级应用：

滑动平均/累计求和 (Moving Averages/Cumulative Sums)：这是最经典的应用之一。通过ROWS BETWEEN子句，你可以轻松计算出过去N天、N周或N个月的平均值，或者从某个时间点开始的累计值。这在金融分析、销售趋势分析中非常常见。
```
-- 计算过去3天的销售额滑动平均值
SELECT SaleDate, DailySales,
       AVG(DailySales) OVER (ORDER BY SaleDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ThreeDayMovingAvg
FROM DailySalesSummary;
```
登录后复制
百分比计算 (Percentage of Total)：想知道每个产品类别在总销售额中的占比？或者每个员工销售额占部门总销售额的比例？窗口函数可以优雅地完成。
```
-- 计算每个产品在总销售额中的百分比
SELECT ProductID, SaleAmount,
       (SaleAmount * 100.0 / SUM(SaleAmount) OVER ()) AS PercentageOfTotalSales
FROM Sales;
```
登录后复制
这里SUM(SaleAmount) OVER ()表示对整个结果集进行求和，作为分母。

去重 (Deduplication)：如果你有一张表，其中包含重复的行（比如基于某些列），但你只想保留每组重复行中的一条，窗口函数是比DISTINCT更灵活的选择，因为你可以控制保留哪一条（比如最新或最早的）。

-- 假设 LogTable 有重复的 UserID 和 EventType，我们想保留每个 UserID-EventType 组合的最新一条记录
WITH RankedLogs AS (
    SELECT
        LogID, UserID, EventType, LogTimestamp,
        ROW_NUMBER() OVER (PARTITION BY UserID, EventType ORDER BY LogTimestamp DESC) AS rn
    FROM LogTable
)
SELECT LogID, UserID, EventType, LogTimestamp
FROM RankedLogs
WHERE rn = 1;

登录后复制

填充缺失值 (Filling Missing Values)：在时间序列数据中，如果某个时间点的数据缺失，你可能想用前一个有效值来填充。LAG()配合IGNORE NULLS（如果你的数据库支持）可以做到这一点，或者通过更复杂的CTE和LAST_VALUE()实现。

性能考量：

窗口函数虽然强大，但并非没有代价。它们通常涉及对数据进行排序和分区，这在处理大量数据时可能会成为性能瓶颈。

排序开销：ORDER BY子句在窗口函数中是常见的，它意味着数据库需要对数据进行一次或多次排序操作。如果PARTITION BY和ORDER BY的列上没有合适的索引，这会非常耗时，可能导致全表扫描和内存溢出到磁盘。
内存消耗：数据库需要为每个窗口维护状态，尤其是在ROWS BETWEEN定义了较大窗口帧时，可能需要将大量数据加载到内存中。
PARTITION BY列的基数：如果PARTITION BY的列具有非常高的基数（即有很多不同的值），那么数据库需要创建和管理很多小的分区，这会增加开销。反之，如果基数很低，分区少但每个分区大，也可能导致单个分区处理时间过长。
优化器行为：不同的数据库系统对窗口函数的优化程度不同。了解你所使用的数据库（如PostgreSQL, SQL Server, MySQL 8+, Oracle）如何处理窗口函数，以及它们是否支持并行处理，可以帮助你更好地设计查询。

建议：

创建索引：确保PARTITION BY和ORDER BY子句中使用的列都有合适的索引。这是提高窗口函数性能最直接有效的方法。
简化ORDER BY：只在必要时使用ORDER BY。例如，COUNT(*) OVER (PARTITION BY Department)就不需要ORDER BY，因为它不依赖于行顺序。
限制窗口帧：如果可能，尽量限制ROWS BETWEEN的范围，避免使用UNBOUNDED PRECEDING或UNBOUNDED FOLLOWING，除非确实需要。
测试和分析：始终在实际数据量下测试你的查询，并使用数据库的执行计划分析工具来理解查询的性能瓶颈。有时，将一个复杂的窗口函数分解成多个步骤（例如，通过CTE）可能会帮助优化器更好地工作。
权衡：在某些极端情况下，如果窗口函数导致性能问题，可能需要重新审视是否可以通过其他传统SQL方法（如子查询、临时表）来替代，即使代码看起来更复杂。但通常，窗口函数在可读性和维护性上带来的优势是巨大的。