Oracle Function percentile_disc

Analytics

Oracle Function percentile_disc

I am looking for a way to perform the same functionality in Teradata as the Oracle Function percentile_disc performs.

If anyone has logic or a udf that performs the logic in Teradata I would truly appreciate it being shared.

Thanks,
David
1 REPLY
N/A

Re: Oracle Function percentile_disc

Maybe a little late, but how about something like this

SELECT inner1.*,
MIN(CASE WHEN inner1.q = 49 THEN day_of_year ELSE NULL END) over()
FROM (SELECT calendar_date,
day_of_year,
quantile(100, day_of_year) AS q
FROM dbc.calendar
WHERE year_val = 2006) inner1

I think you'd use q=49 to get what Oracle would call the 50'th percentile, but I'm not sure off the top of my head and I don't have any data in common on each platform to test.

According the Oracle documentation, percentile_disc gets the minimum value in the specified percentile.

Just change the "49" to something else if you need a different percentile. (But test that 49-or-50 thing first!)

(I've just started using Teradata, so take this with a grain of salt....)

Dave