ウィンドウ関数(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' などのパイプライン集計を使用する。ソートと分割は terms 集計で行う。