Percentiles on a column

GiulioRossi

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?

fchataigner2

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.

GiulioRossi

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

@fchataigner2