Oracle 91 Percentage_cont

[alfie002]

Dear all,

I have a need to create the 20/80% percentiles from data contained within a database. I have just started this and think it will work for me. The data that the table contains is a device_name, period, responsetime. The table holds 60 days worth of data. I need to be able to calculate the 20/80 percentiles for each row of data within this table.

I have inserted a sample of the code that I am using.

select time_period,
percentile_cont(0.2) WITHIN GROUP (Order by responsetime DESC) "80 P'Tile"
percentile_cont(0.8) WITHIN GROUP (Order by responsetime DESC) "20 P'Tile"
from table_name where test <> 0 group by ta_period.


This code works in the sense it will provide the 20/80 percentiles for all data with the same time_period. However, I need to be able to work out the 20/80 percentiles for all rows of data (for all devices and all times ). Essentially,the query should list as its output the same number of rows as are contained in the table. I will continue to investigate myself, should anyone have any ideas, I'd appreciate it if you would drop me a response.

Thanks

Alf

 

[alfie002]

Hello all,

I have a bit of a mistake here. What is want to do is using date from one table, work out the 20/80 percentiles and then move these into a second table. In this second table, I will have summarised the data from table one (hourly) using the sum function. At the same time I want to be able to insert the 20/80 percentiles into the second table.

Thanks


Alf