Data Summarisation

[alfie002]

Dear all,

I have started working in more detail with Oracle and would like some pointers on data summarisation. I have two tables, a rate based table that holds hourly data. The second table is to hold the summarised data. In the rate table there are many entries for multiple sources. Each source will have a 24 entries, one per hour and there can be up to 300 sources of this information. Each source is uniquely identifed together with it's data.

I want to be able to summarise with the following metrics

min value
max value
median value
90 % quartile
10 % quartile

I know there are statistical functions within Oracle which can provide these calculations.

Can someone give me a starter to summarise this data.

Thanks

Alf

 

[Dagon]

It would be easier if you gave us the table definitions (or at least as much as is required to see the requirements) plus some example data to show what's in them and what you want to see.

 

[LKBrwnDBA]

And while you are at it, take a look at the "Analytical Functions" section in the fine Oracle SQL Reference manual.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Here are samples of the functions that it sounds as though you want:

select min(ht) min
      ,max(ht) max
      ,median(ht) median
  from ca_ht;

       MIN        MAX     MEDIAN
---------- ---------- ----------
        55       81.5       65.5

I presume that your "quartile" inquiries have to do with Standard Deviations, which Oracle can handle, as well, (with its STDDEV() function), but I flunked statistics and, as a result, don't know how to produce meaningful data for the 10% and 90% quartiles.

Let us know if this is "in the ballpark" for what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
Deuparth gwaith yw ei ddechrau.

 

[SantaMufasa]

Oops...I just recognised that you are posting in the "Oracle 9i" forum...MEDIAN is available in 10g and above. Sorry to be the (possible) bearer of bad news (if you have only 9i available).

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
Deuparth gwaith yw ei ddechrau.

 

[alfie002]

Dear all,

Thanks for the responses. Please find more information as requested.

time,unique_key,data1,data2,data3.......

So, in this case, I need to be able to summarise all the data for a give unique_key for the given time frame, in this case the data is held in rate (5 minutes) and hourly format (separate table). In the case of the rate data, for each unique_key, I want to have summarised quartile values. These values would then be populated into a new hourly table, one value (row) for each hour to reflect the rate data summaried over the hour. The new data created by the summarisation would reflect the statistical processing of the data. I can almost see the fix for this but I am not quite there and hence the posting on the forum.

Any advice would be greatly appreciated.

Thanks and regards

Alf