SQL入门：窗口函数全解析-数据分析必备技能

原创于 2025-09-27 22:11:34 发布 · 1.1k 阅读

9 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #大数据 #sql #etl工程师 #数据库开发 #数据仓库 #etl

SQL基础专栏收录该内容

31 篇文章

订阅专栏

窗口函数（Window Functions），也称为分析函数或OLAP函数，是 SQL 中一种强大的数据分析工具，它能够在不将数据分组的情况下对记录集进行聚合计算，同时保留每条记录的独立信息。对于数据分析、报表生成等场景非常有用。

一、窗口函数的基本概念

窗口函数可以理解为 "在一个数据窗口内进行计算的函数"。这里的 "窗口" 指的是记录的集合，函数会对这个集合中的数据进行计算。

与GROUP BY不同，窗口函数不会减少结果集中的行数，而是为每一行添加一个基于指定 "窗口" 计算的结果。

基本语法结构

函数名(字段) OVER (
    [PARTITION BY 分组字段]
    [ORDER BY 排序字段 [ASC|DESC]]
    [ROWS/RANGE 窗口范围]
)

OVER()：关键字，表明这是一个窗口函数，而非普通聚合函数
PARTITION BY（分区）：

作用：可选，将数据表按照指定的列分成多个更小的“窗口”或“组”。窗口函数会在每个分区内独立进行计算。

类比：类似于 GROUP BY，但不会合并行。如果你不指定 PARTITION BY，整个结果集将被视为一个大的分区。

ORDER BY：可选，指定窗口内数据的排序方式

作用：在每个分区内，按照指定的列对行进行排序。这个排序决定了窗口函数计算的逻辑顺序（例如，累计求和、排名等）。

注意：对于某些窗口函数（如 ROW_NUMBER, RANK），ORDER BY 是必需的。对于其他函数（如聚合窗口函数），它是可选的，但会严重影响结果。

ROWS/RANGE：可选，定义窗口的具体范围（如前 N 行到后 N 行）

二、窗口函数的分类

窗口函数主要分为以下几类：

1. 聚合类窗口函数

将普通聚合函数用作窗口函数，如SUM()、COUNT()、AVG()、MAX()、MIN()等。

示例：计算每个用户的累计消费金额

SELECT 
  order_id,
  user_id,
  amount,
  create_time,
  SUM(amount) OVER (
    PARTITION BY user_id 
    ORDER BY create_time
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_spending
FROM orders;

这个查询会为每个订单添加一列cumulative_spending，显示该用户到当前订单为止的累计消费金额。

2. 排序类窗口函数

专门用于排序和排名的函数，常用的有：

RANK()：排名，存在相同排名时会跳过后续排名（如 1,2,2,4...）
DENSE_RANK()：密集排名，存在相同排名时不会跳过后续排名（如 1,2,2,3...）
ROW_NUMBER()：为每行分配唯一序号，即使值相同也不会重复

示例：按金额对每个用户的订单进行排名

SELECT 
  order_id,
  user_id,
  amount,
  RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rank,
  DENSE_RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS dense_rank,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS row_num
FROM orders;

3. 分析类窗口函数

提供特定分析功能的函数，如：

LAG(字段, n)：获取当前行的前 n 行记录的指定字段值
LEAD(字段, n)：获取当前行的后 n 行记录的指定字段值
FIRST_VALUE(字段)：获取窗口内第一行的指定字段值
LAST_VALUE(字段)：获取窗口内最后一行的指定字段值
NTILE(n)：将窗口内的数据分成 n 个桶，为每行分配桶编号

示例：获取每个用户的上一笔订单金额和下一笔订单金额

SELECT 
  order_id,
  user_id,
  create_time,
  amount,
  LAG(amount, 1) OVER (PARTITION BY user_id ORDER BY create_time) AS prev_amount,
  LEAD(amount, 1) OVER (PARTITION BY user_id ORDER BY create_time) AS next_amount
FROM orders;

三、窗口范围的定义

通过ROWS或RANGE可以精确控制窗口的范围：

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：从窗口第一行到当前行
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING：从当前行到窗口最后一行
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW：从前 3 行到当前行
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING：从前 1 行到后 1 行（共 3 行）
RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW：从当前时间前 1 天到当前行

示例：计算每个用户最近 3 笔订单的平均金额

SELECT 
  order_id,
  user_id,
  amount,
  create_time,
  AVG(amount) OVER (
    PARTITION BY user_id 
    ORDER BY create_time
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS avg_last_3_orders
FROM orders;

四、窗口函数与普通聚合函数的区别

特性	窗口函数	普通聚合函数
结果集行数	与原表相同	减少为分组数
计算范围	可指定窗口范围	整个分组
分组方式	PARTITION BY（每个分组单独计算）	GROUP BY（将分组合并为一行）
用途	保留明细数据的同时显示聚合结果	仅显示聚合结果

五、实际应用场景

累计计算：如累计销售额、累计用户数

SELECT 
  date,
  sales,
  SUM(sales) OVER (ORDER BY date) AS cumulative_sales
FROM daily_sales;

2.排名分析：如销售业绩排名、用户活跃度排名

SELECT 
  user_id,
  total_spending,
  RANK() OVER (ORDER BY total_spending DESC) AS spending_rank
FROM user_spending;

3.同比环比分析：与上期数据比较

SELECT 
  month,
  sales,
  LAG(sales, 1) OVER (ORDER BY month) AS prev_month_sales,
  (sales - LAG(sales, 1) OVER (ORDER BY month)) / LAG(sales, 1) OVER (ORDER BY month) * 100 AS growth_rate
FROM monthly_sales;

4.移动平均值：如最近 7 天平均销量

SELECT 
  date,
  sales,
  AVG(sales) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_sales;

六、使用注意事项

窗口函数只能在SELECT子句和ORDER BY子句中使用，不能在WHERE、GROUP BY或HAVING中使用。
不同数据库对窗口函数的支持程度不同，基本功能在 PostgreSQL、MySQL 8.0+、SQL Server、Oracle 中都有支持。
对于大型数据集，复杂的窗口函数可能影响性能，需要合理使用索引优化。
PARTITION BY和GROUP BY可以同时使用，GROUP BY先对数据进行分组，窗口函数再在每个分组内进行计算。