Percentiles on a column
I would like to group a large data set by column (A) and calculate Percetiles on a separate numeric column (B).
I can only see the median, max, min, etc in grouping recipe. any idea?
Answers
-
you can achieve this by using a Window recipe, where the window partitions on A, orders by B, has a frame where no limit is set (ie no limit on preceding, no limit on following) and you ask for 100 quantiles.
-
thanks, It worked,
with the above, i get rather than the percentile value the percentile bucket for each original record which is halfway
then I have to add another step and group by the same Column A plus the newly created percentile (quantile) buckets and select the MAx value of the Column B to get to the value of each percentile...
eventually, i get the percentile values
If i use 4 ntiles i take Max value of ntlie 3 to get the value if the 3rd Quartile
