Computing Percentiles In BigQuery

google-bigquery google-cloud-platform percentile bigquery-standard-sql
Praangrammer picture Praangrammer · May 13, 2017 · Viewed 19.3k times · Source

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!

Answer

Mosha Pasumansky picture Mosha Pasumansky · May 13, 2017

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)

Related questions

What's the difference between BigQuery and BigTable?

Is there any reason why someone would use BigTable instead of BigQuery? Both seem to support Read and Write operations with the latter offering also advanced 'Query' operations. I need to develop an affiliate network (thus I need to track …

Read More
Random Sampling in Google BigQuery

I just discovered that the RAND() function, while undocumented, works in BigQuery. I was able to generate a (seemingly) random sample of 10 words from the Shakespeare dataset using: SELECT word FROM (SELECT rand() as random,word FROM [publicdata:samples.shakespeare] …

Read More
How do I identify the Google Cloud Storage URI from my Google Developers Console?

When I attempt load data into BigQuery from Google Cloud Storage it asks for the Google Cloud Storage URI (gs://). I have reviewed all of your online support as well as stackoverflow and cannot find a way to identify the …

Read More