Calculations across related rows.
WHAT THEY DO
Compute a value over a set of rows while keeping each row.
WHY THAT MATTERS
Grouping collapses rows; windows do not.
WHAT THEY ARE USED FOR
Running totals Ranking within a group Comparing a row to the previous or next Percentages of a group total
WHAT THE STRUCTURE IS
A function, then over, then a definition of the window.
WHAT PARTITIONING DOES
Restarts the calculation per group.
WHAT ORDERING DOES
Determines sequence, which running totals and ranking depend on.
WHAT THE COMMON RANKING FUNCTIONS DIFFER IN
How they handle ties: skipping numbers, or not.
WHAT LAG AND LEAD PROVIDE
The value from a previous or following row.
WHERE THAT HELPS
Calculating change between periods without joining a table to itself.
WHY THAT MATTERS
Self-joins for this purpose are slow and awkward.
WHAT VERSIONS SUPPORT THEM
Recent ones; older MySQL does not.
WHAT TO CHECK
Your version, before using them.
WHAT THEY REPLACE
Complex subqueries, and processing in application code.
WHY THAT IS AN IMPROVEMENT
The database does it once, efficiently, rather than transferring rows to be processed.
WHAT TO BE CAREFUL WITH
Very large windows, which consume memory.
