All Forums Analytics
dklasner 1 post Joined 03/06
06 Mar 2006
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

DaveC 5 posts Joined 03/06
30 Mar 2006

Maybe a little late, but how about something like thisSELECT 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) inner1I 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

You must sign in to leave a comment.