窗口函数（OVER / PARTITION BY）

Oracle

SELECT name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_no,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank_no,
  LAG(salary)    OVER (PARTITION BY department ORDER BY salary)      AS prev_salary
FROM employee;

窗口函数在不对行分组（不减少行数）的情况下，为每一行在一个“窗口”上进行计算。PARTITION BY 划分窗口，ORDER BY 对其排序。Oracle 是这些分析函数的先驱，提供包括 ROW_NUMBER、RANK、DENSE_RANK、LAG/LEAD 和 SUM() OVER 在内的丰富函数集。

PostgreSQL

SELECT name, department, salary,
  ROW_NUMBER() OVER w AS row_no,
  RANK()       OVER w AS rank_no,
  LEAD(salary)   OVER w AS next_salary
FROM employee
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

PostgreSQL 支持所有标准窗口函数，并允许用 WINDOW ... AS 为重复的窗口定义命名。借助 ROWS/RANGE/GROUPS 框架定义，可计算累计总和、移动平均等。

MySQL

SELECT name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_no,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_no,
  LAG(salary)    OVER (PARTITION BY department ORDER BY salary)      AS prev_salary
FROM employee;

MySQL 8.0（以及 MariaDB 10.2+）支持窗口函数；此前通过用户变量或子查询模拟。提供 ROW_NUMBER、RANK、DENSE_RANK、NTILE、LAG/LEAD、FIRST_VALUE/LAST_VALUE 以及聚合函数的 OVER 形式。

SQL Server

SELECT name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_no,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank_no,
  SUM(salary)    OVER (PARTITION BY department) AS dept_total
FROM employee;

SQL Server 自 2005 起支持排名窗口函数，自 2012 起支持带框架的聚合以及 LAG/LEAD 等函数。ROW_NUMBER 常用于分页（配合 OFFSET/FETCH）以及查找每组前 N 行。

MongoDB

// Closest equivalent: $setWindowFields (MongoDB 5.0+)
db.employee.aggregate([
  { $setWindowFields: {
      partitionBy: "$department",
      sortBy: { salary: -1 },
      output: { row_no: { $rank: {} },
                prev_salary: { $shift: { output: "$salary", by: -1 } } }
  } }
])

MongoDB 5.0+ 的 $setWindowFields 阶段等价于 SQL 窗口函数：通过 partitionBy 和 sortBy 定义窗口，生成 $rank/$denseRank/$documentNumber、$shift（LAG/LEAD）和累计总和等输出。在旧版本中，需通过 $group + 代码解决。

Redis

# Not applicable: Redis has no window functions.

Redis 不提供分析型窗口函数。对于有序结果和排名，使用 Sorted Set（ZSET）结构：用 ZADD 设置分数，用 ZRANK/ZREVRANK 读取排名，用 ZRANGE 读取范围。复杂分析在应用中完成。

Elasticsearch

# Closest equivalent: top_hits and pipeline aggregations
GET employee/_search
{
  "size": 0,
  "aggs": { "dept": { "terms": { "field": "department" },
    "aggs": { "top_paid": { "top_hits": {
      "sort": [ { "salary": "desc" } ], "size": 3 } } } } }
}

Elasticsearch 没有直接的窗口函数。每组前 N 条记录使用 'top_hits' 子聚合；移动/累计计算使用 'derivative'、'cumulative_sum' 和 'moving_fn' 等 pipeline 聚合。排序和分区通过 terms 聚合完成。