Fonctions de fenêtrage (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 a été pionnier des fonctions analytiques (de fenêtrage) ; ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM/AVG OVER, NTILE et FIRST_VALUE sont entièrement pris en charge. Dans OVER, PARTITION BY définit les groupes, ORDER BY l'ordre, et le cadre (ROWS/RANGE BETWEEN) la fenêtre glissante.

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 prend entièrement en charge les fonctions de fenêtrage avec la syntaxe SQL standard. ROW_NUMBER/RANK/DENSE_RANK, LAG/LEAD, les agrégats avec OVER, NTILE et FIRST_VALUE/LAST_VALUE sont disponibles. Les définitions de fenêtre répétées peuvent être nommées et partagées avec la clause 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 a ajouté les fonctions de fenêtrage en version 8.0 (MariaDB 10.2+ les prend aussi en charge). ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE et les agrégats avec OVER sont disponibles. Les versions antérieures à 8.0 n'ont pas ces fonctions ; on les émulait avec des variables utilisateur.

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 prend en charge les fonctions de fenêtrage depuis 2005 (ROW_NUMBER/RANK) et 2012 (LAG/LEAD, cadres, agrégats avec OVER). PARTITION BY, ORDER BY et le cadre ROWS/RANGE sont entièrement disponibles. OFFSET/FETCH ou ROW_NUMBER est courant pour la pagination.

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

L'étape d'agrégation $setWindowFields, introduite dans MongoDB 5.0, fournit les fonctions de fenêtrage : $documentNumber (ROW_NUMBER), $rank/$denseRank, $shift (LAG/LEAD), les fonctions d'agrégat et les fenêtres glissantes (window: { documents/range }). partitionBy et sortBy correspondent à PARTITION BY/ORDER BY en SQL.

Redis

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

Redis est un magasin clé-valeur ; il n'a pas de fonctions analytiques de fenêtrage. Pour des résultats ordonnés, un Sorted Set peut être utilisé (accès de type rang avec ZADD/ZRANGE) ; les opérations de fenêtrage relationnelles comme PARTITION BY/LAG se font toutefois dans la couche applicative.

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 n'offre pas de fonctions de fenêtrage classiques (ROW_NUMBER/RANK/LAG). Le regroupement et le tri se font avec le framework d'agrégation : une agrégation terms se rapproche de PARTITION BY, top_hits des premiers enregistrements, et derivative/serial_diff (pipeline aggregations) des différences successives de type LAG.