Quantile with partition

Database

Quantile with partition

Hi,

 

I have a column called Parm_Id and i would like to get the quantiles for each value (Amt Column)

Is it possible to use the function with partition ? There is any other way ?

 

Thanks

 

QUANTILE(100,Amt) OVER(PARTITION BY Param_Id)

 

 

1 REPLY
Junior Contributor

Re: Quantile with partition

QUANTILE is an old deprecated Teradata function, PARTITON was added later to Windowed Aggregates.

 

Those old funtions use GROUP BY instead of PARTITON:

SELECT ...
   QUANTILE(100,Amt)
FROM ...
GROUP BY Param_Id

But as you can't mix old & new style functions in the same Select you better rewrite it:

 (RANK() OVER (PARTITION BY Param_id ORDER BY Amt) - 1) * 100 / COUNT(*) OVER()

 

See Using ANSI Window Functions Instead of QUANTILE