SQL Window Functions
debt(d7/e5/b3/t5)
Closest to 'only careful code review or runtime testing' (d7). The detection_hints.tools list includes mysql-workbench, pgadmin, and laravel-debugbar, but these are query browsers/debuggers, not static analyzers that flag 'you should use a window function here.' The code_pattern describes PHP loops computing running totals or self-JOINs — recognizing these as window-function candidates requires manual review or runtime performance analysis. No automated tool flags the absence of window functions.
Closest to 'touches multiple files / significant refactor in one component' (e5). The quick_fix describes replacing 'multiple self-joins or PHP post-processing loops' with window functions. This typically means rewriting SQL queries and removing corresponding PHP code that did the computation — touching both the query layer and the application logic. Not a one-liner, but contained within the data-access component.
Closest to 'localised tax' (b3). Window functions are a query-level technique that applies to specific analytics scenarios (rankings, running totals). The choice to use or not use them affects the queries where they're relevant, not the entire codebase architecture. Other parts of the system remain unaffected by this choice.
Closest to 'notable trap' (t5). The misconception field states 'Window functions replace GROUP BY' — a documented gotcha that confuses aggregation (row collapse) with windowing (row preservation). Common_mistakes reinforce this: ROW_NUMBER vs RANK confusion, forgetting PARTITION BY, filtering in WHERE instead of CTE. These are learnable but catch most developers initially.
Also Known As
TL;DR
Explanation
Window functions use the OVER() clause to define a window (partition + ordering). They enable ranking (ROW_NUMBER, RANK, DENSE_RANK), running totals (SUM OVER), moving averages, lead/lag access to adjacent rows, and percentile calculations — all without GROUP BY collapsing rows. They replace complex self-joins and subqueries with readable, performant SQL. Available in MySQL 8+, PostgreSQL, SQL Server, and SQLite 3.25+.
Common Misconception
Why It Matters
Common Mistakes
- Using ROW_NUMBER() when RANK() or DENSE_RANK() is needed — ROW_NUMBER gives unique ranks even for ties.
- Not using PARTITION BY — the window spans the entire result set instead of logical groups.
- Filtering on window function results in WHERE — use a CTE or subquery since window functions execute after WHERE.
- Applying window functions in MySQL 5.7 or earlier — they require MySQL 8.0+.
Code Examples
-- Application-level ranking — loads all rows, ranks in PHP:
$users = $db->query('SELECT id, name, score FROM users ORDER BY score DESC')->fetchAll();
foreach ($users as $i => $user) {
$user['rank'] = $i + 1; // O(n) in PHP memory
}-- ROW_NUMBER() — rank in the database:
SELECT id, name, score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
FROM users;
-- Running total per category:
SELECT category, amount,
SUM(amount) OVER (PARTITION BY category ORDER BY created_at) AS running_total
FROM transactions;