ウィンドウ関数(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(パイプライン集計)は LAG のような連続した差分に近い。

DBAtlas で開く →