窗口函数（ROW_NUMBER/RANK/LAG）

Oracle

-- Rank by amount within each city and fetch the previous value:
SELECT city, name, amount,
  ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) AS row_num,
  RANK()       OVER (PARTITION BY city ORDER BY amount DESC) AS rnk,
  LAG(amount)   OVER (PARTITION BY city ORDER BY created_at)      AS prev_amount
FROM sale;

Oracle 是分析（窗口）函数的先驱；完整支持 ROW_NUMBER、RANK、DENSE_RANK、LAG、LEAD、SUM/AVG OVER、NTILE 和 FIRST_VALUE。在 OVER 内，PARTITION BY 定义分组，ORDER BY 定义排序，窗口框架（ROWS/RANGE BETWEEN）定义滑动窗口。

PostgreSQL

-- Window functions with OVER():
SELECT city, name, amount,
  ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) AS row_num,
  RANK()       OVER (PARTITION BY city ORDER BY amount DESC) AS rnk,
  LAG(amount)   OVER (PARTITION BY city ORDER BY created_at)      AS prev_amount
FROM sale;

PostgreSQL 以标准 SQL 语法完整支持窗口函数。提供 ROW_NUMBER/RANK/DENSE_RANK、LAG/LEAD、带 OVER 的聚合、NTILE 和 FIRST_VALUE/LAST_VALUE。重复的窗口定义可通过 WINDOW 子句命名并共享。

MySQL

-- MySQL 8.0+ window functions:
SELECT city, name, amount,
  ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) AS row_num,
  RANK()       OVER (PARTITION BY city ORDER BY amount DESC) AS rnk,
  LAG(amount)   OVER (PARTITION BY city ORDER BY created_at)      AS prev_amount
FROM sale;

MySQL 在 8.0 版中加入了窗口函数（MariaDB 10.2+ 也支持）。提供 ROW_NUMBER、RANK、DENSE_RANK、LAG、LEAD、NTILE 和带 OVER 的聚合。8.0 之前的版本没有这些函数；过去通过用户变量模拟。

SQL Server

-- Window functions with OVER():
SELECT city, name, amount,
  ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) AS row_num,
  RANK()       OVER (PARTITION BY city ORDER BY amount DESC) AS rnk,
  LAG(amount)   OVER (PARTITION BY city ORDER BY created_at)      AS prev_amount
FROM dbo.sale;

SQL Server 自 2005 起支持窗口函数（ROW_NUMBER/RANK），自 2012 起支持 LAG/LEAD、窗口框架和带 OVER 的聚合。PARTITION BY、ORDER BY 和 ROWS/RANGE 框架均完整可用。分页常用 OFFSET/FETCH 或 ROW_NUMBER。

MongoDB

// Window calculations with $setWindowFields:
db.sale.aggregate([
  { $setWindowFields: {
      partitionBy: "$city",
      sortBy: { amount: -1 },
      output: {
        row_num: { $documentNumber: {} },
        rnk: { $rank: {} },
        prev_amount: { $shift: { output: "$amount", by: -1 } }
      }
  } }
])

MongoDB 5.0 引入的 $setWindowFields 聚合阶段提供窗口函数：$documentNumber（ROW_NUMBER）、$rank/$denseRank、$shift（LAG/LEAD）、聚合函数和滑动窗口（window: { documents/range }）。partitionBy 和 sortBy 对应 SQL 中的 PARTITION BY/ORDER BY。

Redis

# Not applicable: there are no SQL-like window functions.

Redis 是键值存储；没有分析型窗口函数。有序结果可使用 Sorted Set（通过 ZADD/ZRANGE 实现类似排名的访问）；但 PARTITION BY/LAG 等关系型窗口操作需在应用层完成。

Elasticsearch

# Not applicable: there are no SQL-like window functions.
# Closest equivalent: bucket aggregations.
GET sale/_search
{ "size": 0, "aggs": { "by_city": {
  "terms": { "field": "city" },
  "aggs": { "total": { "sum": { "field": "amount" } } } } } }

Elasticsearch 不提供传统的窗口函数（ROW_NUMBER/RANK/LAG）。分组和排序通过聚合框架完成：terms 聚合接近 PARTITION BY，top_hits 对应头部记录，derivative/serial_diff（pipeline 聚合）对应类似 LAG 的相邻差值。