Оконные функции (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, а агрегаты с рамками и функции вроде LAG/LEAD — с 2012. 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 } } }
  } }
])

Этап $setWindowFields в MongoDB 5.0+ — эквивалент оконных функций 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'; для скользящих/накопительных вычислений — pipeline-агрегации 'derivative', 'cumulative_sum' и 'moving_fn'. Сортировка и разбиение выполняются агрегацией terms.