Rank rows with window functions
Ranking a vehicle against others of the same make is a daily analyst question. Window functions rank rows without collapsing them into groups.
How it works
fact_vehicle_summary has one row per vehicle. A window function looks at the rows in the same partition, here the same make, and gives each row a number based on its place in the order you choose. GROUP BY would leave one row per make. A window function keeps every vehicle.
- Write
RANK() OVER (PARTITION BY make ORDER BY defect_stats.total_defects DESC).PARTITION BYrestarts the numbering for each make andORDER BYsets the order inside it. This kind of partition is a group of rows inside one query and has nothing to do with how the table is stored. ROW_NUMBER()gives 1, 2, 3 with no repeats, even for ties.RANK()gives tied rows the same number and skips the numbers after them.DENSE_RANK()gives tied rows the same number and skips nothing.- Add a unique second sort key such as
vehicle_idto break ties. Then no two rows in a partition tie, andROW_NUMBERandRANKgive the same numbers. fact_vehicle_summaryis not partitioned, so it has no date filter to write. The estimate counts the whole of every column the query names. On a clustered table the bill can be lower.
Common mistake
Ranking by a value that has ties, with no tie-breaker, gives several rows the same rank. A later condition such as rank <= 3 can then return more than three rows. Add a unique key to the ORDER BY when you need exactly n rows.
Worked example
SELECT make, vehicle_id, mileage_stats.last_recorded_mileage AS last_mileage,
RANK() OVER (PARTITION BY make ORDER BY mileage_stats.last_recorded_mileage DESC) AS mileage_rank
FROM `learn.mot.fact_vehicle_summary`
WHERE make = 'SAAB' AND mileage_stats.last_recorded_mileage IS NOT NULLEvery SAAB vehicle with a recorded mileage stays in the result. The window only adds a column.
The vehicle with the highest last mileage gets mileage_rank 1. If two vehicles shared a mileage they would share a rank.