Window functions (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 pioneered analytic (window) functions; ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM/AVG OVER, NTILE and FIRST_VALUE are fully supported. Inside OVER, PARTITION BY defines the groups, ORDER BY the ordering, and the frame (ROWS/RANGE BETWEEN) the sliding window.

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 fully supports window functions with standard SQL syntax. ROW_NUMBER/RANK/DENSE_RANK, LAG/LEAD, aggregates with OVER, NTILE and FIRST_VALUE/LAST_VALUE are available. Repeated window definitions can be named and shared with the WINDOW clause.

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 added window functions in version 8.0 (MariaDB 10.2+ also supports them). ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE and aggregates with OVER are available. Versions before 8.0 lack these functions; they used to be emulated with user variables.

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 supports window functions since 2005 (ROW_NUMBER/RANK) and 2012 (LAG/LEAD, framing, aggregates with OVER). PARTITION BY, ORDER BY and the ROWS/RANGE frame are fully available. OFFSET/FETCH or ROW_NUMBER is common for paging.

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 } }
      }
  } }
])

The $setWindowFields aggregation stage, introduced in MongoDB 5.0, provides window functions: $documentNumber (ROW_NUMBER), $rank/$denseRank, $shift (LAG/LEAD), aggregate functions and sliding windows (window: { documents/range }). partitionBy and sortBy correspond to PARTITION BY/ORDER BY in SQL.

Redis

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

Redis is a key-value store; it has no analytic window functions. For ordered results a Sorted Set can be used (rank-like access with ZADD/ZRANGE); however, relational window operations such as PARTITION BY/LAG are done in the application layer.

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 does not offer classic window functions (ROW_NUMBER/RANK/LAG). Grouping and ordering are done with the aggregation framework: a terms aggregation is close to PARTITION BY, top_hits to the top records, and derivative/serial_diff (pipeline aggregations) to LAG-like successive differences.