Window functions (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;

Window functions compute over a 'window' for each row without grouping rows (without reducing the row count). PARTITION BY splits the window, ORDER BY sorts it. Oracle pioneered these analytic functions and offers a rich set including ROW_NUMBER, RANK, DENSE_RANK, LAG/LEAD and 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 supports all standard window functions and lets you name a repeated window definition with WINDOW ... AS. With ROWS/RANGE/GROUPS frame definitions you can compute running totals, moving averages and similar.

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 (and MariaDB 10.2+) support window functions; before that they were emulated with user variables or subqueries. ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG/LEAD, FIRST_VALUE/LAST_VALUE and the OVER form of aggregate functions are available.

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 supports ranking window functions since 2005, and framed aggregates and functions such as LAG/LEAD since 2012. ROW_NUMBER is commonly used for paging (with OFFSET/FETCH) and for finding the top N rows per group.

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

The MongoDB 5.0+ $setWindowFields stage is the equivalent of SQL window functions: the window is defined with partitionBy and sortBy, producing outputs such as $rank/$denseRank/$documentNumber, $shift (LAG/LEAD) and running totals. In older versions this was solved with $group + code.

Redis

# Not applicable: Redis has no window functions.

Redis offers no analytic window functions. For ordered results and ranking, use the Sorted Set (ZSET) structure: assign scores with ZADD, read the rank with ZRANK/ZREVRANK and ranges with ZRANGE. Complex analytics are done in the application.

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 has no direct window functions. For the top N records per group use a 'top_hits' sub-aggregation; for moving/cumulative calculations use pipeline aggregations such as 'derivative', 'cumulative_sum' and 'moving_fn'. Sorting and partitioning are done with a terms aggregation.