WITH sample AS ( SELECT d FROM UNNEST(ARRAY[1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31,33,35,37,39]) AS d ) SELECT `justfunctions.eu.percentiles_approx`(d, 50) FROM sample
/*--Output--
20
*/
CREATE OR REPLACE AGGREGATE FUNCTION `your_project_id.your_dataset_id.percentiles_approx`(`col` float64, `percentile` int64 NOT AGGREGATE)
RETURNS FLOAT64 AS ((APPROX_QUANTILES(col, 100))[OFFSET(percentile)])
OPTIONS ( description = '''Finds the percentile <percentile> of the specified column <column> by calculating approximate quantiles (1% granularity approximation). This approach provides an efficient and fast estimate of percentiles.''')
SQL User Defined Aggregate Function (SQL UDAF)
See something wrong? Contact us or report an issue on Github.