ROW_NUMBER vs RANK vs DENSE_RANK?

ROW_NUMBER: unique sequential number (1, 2, 3, 4). RANK: same rank for ties, gaps after (1, 2, 2, 4). DENSE_RANK: same rank for ties, no gaps (1, 2, 2, 3). Use ROW_NUMBER for unique row identification, RANK for competitive ranking with gaps, DENSE_RANK for sequential ranking without gaps.

Window functions vs GROUP BY?

GROUP BY collapses rows into groups (one row per group). Window functions keep all rows and add a calculated column. Use GROUP BY for aggregation (total per category); use window functions for per-row calculations with context (running total, rank within category, previous value).

Utiliser SQL window fonctions

Window fonctions (OVER) perform calculations across lignes sans grouping. Apprendre ROW_NUMBER, RANK, LAG, LEAD, et aggregate windows.

1

Window functions operate on a "window" of rows related to the current row, without collapsing them like GROUP BY. Syntax: function() OVER (PARTITION BY ... ORDER BY ...). Common functions: ROW_NUMBER (sequential), RANK (with gaps), DENSE_RANK (no gaps), LAG/LEAD (previous/next row), SUM/AVG (running totals).
2

Formateur SQL →
Formater queries with window functions to verify the PARTITION BY and ORDER BY clauses. The window specification determines which rows are included in the calculation. Format makes the window frame explicit and helps debug incorrect results.

Questions fréquentes

ROW_NUMBER vs RANK vs DENSE_RANK?: ROW_NUMBER: unique sequential number (1, 2, 3, 4). RANK: same rank for ties, gaps after (1, 2, 2, 4). DENSE_RANK: same rank for ties, no gaps (1, 2, 2, 3). Use ROW_NUMBER for unique row identification, RANK for competitive ranking with gaps, DENSE_RANK for sequential ranking without gaps.
Window functions vs GROUP BY?: GROUP BY collapses rows into groups (one row per group). Window functions keep all rows and add a calculated column. Use GROUP BY for aggregation (total per category); use window functions for per-row calculations with context (running total, rank within category, previous value).