Fonctions de fenêtrage (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;

Les fonctions de fenêtrage calculent sur une « fenêtre » pour chaque ligne sans regrouper les lignes (sans réduire leur nombre). PARTITION BY découpe la fenêtre, ORDER BY la trie. Oracle a été pionnier de ces fonctions analytiques et en offre un riche ensemble, dont ROW_NUMBER, RANK, DENSE_RANK, LAG/LEAD et 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 prend en charge toutes les fonctions de fenêtrage standard et permet de nommer une définition de fenêtre répétée avec WINDOW ... AS. Avec les définitions de cadre ROWS/RANGE/GROUPS, vous pouvez calculer des cumuls, des moyennes mobiles, etc.

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 (et MariaDB 10.2+) prennent en charge les fonctions de fenêtrage ; auparavant, on les émulait avec des variables utilisateur ou des sous-requêtes. ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG/LEAD, FIRST_VALUE/LAST_VALUE et la forme OVER des fonctions d'agrégat sont disponibles.

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 prend en charge les fonctions de fenêtrage de classement depuis 2005, et les agrégats avec cadre et des fonctions comme LAG/LEAD depuis 2012. ROW_NUMBER est couramment utilisé pour la pagination (avec OFFSET/FETCH) et pour trouver les N premières lignes par groupe.

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

L'étape $setWindowFields de MongoDB 5.0+ est l'équivalent des fonctions de fenêtrage SQL : la fenêtre est définie avec partitionBy et sortBy, et produit des sorties comme $rank/$denseRank/$documentNumber, $shift (LAG/LEAD) et des cumuls. Dans les anciennes versions, on résolvait cela avec $group + du code.

Redis

# Not applicable: Redis has no window functions.

Redis n'offre pas de fonctions analytiques de fenêtrage. Pour des résultats ordonnés et le classement, utilisez la structure Sorted Set (ZSET) : attribuez des scores avec ZADD, lisez le rang avec ZRANK/ZREVRANK et les plages avec ZRANGE. L'analytique complexe se fait dans l'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 n'a pas de fonctions de fenêtrage directes. Pour les N premiers enregistrements par groupe, utilisez une sous-agrégation 'top_hits' ; pour les calculs mobiles/cumulés, utilisez des pipeline aggregations comme 'derivative', 'cumulative_sum' et 'moving_fn'. Le tri et le partitionnement se font avec une agrégation terms.