Percentiles and buckets: NTILE, PERCENTILE_CONT, PERCENT_RANK
Is a vehicle in the top quarter for mileage, and is its defect count ordinary for its make? Three window functions place a row among its peers.
How it works
Line the TESLA vehicles up by last recorded mileage. Here NTILE(4) cuts the line into four groups of nearly equal size and numbers them 1 to 4. Next, PERCENT_RANK gives each vehicle its place in the line as a number from 0 to 1. Last, PERCENTILE_CONT works the other way round. You give it a place such as 0.5 and it returns the value found there. A place of 0.5 is the median, the middle value. All three are window functions, so every vehicle stays in the result.
NTILE(4) OVER (ORDER BY defect_stats.total_defects, vehicle_id)numbers the rows in order from bucket 1 to bucket 4, and bucket 1 holds the rows that sort first. TheORDER BYinsideOVERis required, and a frame clause is refused. When the rows do not divide evenly, the larger buckets come first and sizes never differ by more than one row.- Rows that tie on the
ORDER BYcome out in any order, and two runs or two engines can order them differently. End theORDER BYwith a unique key such asvehicle_id, so that every row gets the same bucket every time. PERCENTILE_CONT(x, 0.5) OVER (PARTITION BY make)returns the median ofxfor each make. The value comes first and the place, from 0 to 1, comes second. It interpolates, so for an even number of rows the median is the average of the two middle values.PERCENTILE_DISCreturns a value that exists in the data instead.- In an ordinary query
PERCENTILE_CONThas no aggregate form, soGROUP BY makewith it is refused. TheOVERclause is the only way, it cannot have its ownORDER BY, and it puts the same median on every row of the make. AddSELECT DISTINCT, or wrap the query in aGROUP BY, to get one row per make.OVER ()makes the whole table one partition, as the worked example shows. PERCENT_RANK()is the rank minus 1, divided by the number of rows minus 1. The lowest value gets 0. Rows that tie share one value, so when the highest value ties, no row reaches 1.CUME_DIST()is a close relative that counts the rows at or below the current one, so its smallest value is above 0.
Common mistake
A bucket number only says where a row falls in the order. Without a unique key at the end of the ORDER BY, tied rows can change buckets from one run to the next. Also, PERCENTILE_CONT with OVER (PARTITION BY make) returns one row per vehicle. Without DISTINCT the result has a row per vehicle where you wanted one per make.
Worked example
SELECT vehicle_id, mileage_stats.last_recorded_mileage AS last_mileage,
NTILE(4) OVER (ORDER BY mileage_stats.last_recorded_mileage, vehicle_id) AS quartile,
PERCENTILE_CONT(mileage_stats.last_recorded_mileage, 0.5) OVER () AS median_mileage
FROM `learn.mot.fact_vehicle_summary`
WHERE make = 'SAAB'
ORDER BY last_mileageSAAB has 8 vehicles, so each quartile holds 2 of them. The two lowest mileages are in quartile 1.
median_mileage is the same on every row, because OVER () makes all 8 vehicles one partition. With 8 vehicles it is the average of the 4th and 5th mileage.