Computing Percentiles In BigQuery
google-bigquery, google-cloud-platform, percentile
Solution
Check out APPROX_QUANTILES function in Standard SQL. If you ask for 100 quantiles - you get percentiles. So the query will look like following:
SELECT percentiles[offset(25)], percentiles[offset(50)], percentiles[offset(75)]
FROM (SELECT APPROX_QUANTILES(column, 100) percentiles FROM Table)
Problem
I am using BigQuery, and I need to compute the 25th, 50th, and 75th percentile of a column of a dataset. For example, how can I get the aforementioned numbers using BigQuery and STANDARD SQL. I have looked at the PERCENT_RANK, RANK, and NTILE functions but I can't seem to crack it. Here's some code that may guide me Appreciate the help!