Getting average and max values in PL/SQL 1

[toddyl]

Hi,

I have a table, category_rate, where I am collecting stats per process as follows:

Server Process Category Day Time Value
================================================
serv1 a1 Update_FL 15/9 09:00 100
serv1 a2 Update_FL 15/9 09:00 95
serv2 b1 Downrate_X 15/9 09:00 14
serv2 b2 Update_FL 15/9 09:00 1200
serv1 a1 Update_FL 15/9 09:01 25
serv1 a2 Update_FL 15/9 09:01 195
serv2 b1 Downrate_X 15/9 09:01 87
serv2 b2 Update_FL 15/9 09:01 200

I want to summarise this table into a category_rate_daily table where I am interested in the columns: Category, Day, Avg Value and Max Value

If I summarise this manually I would add up the similar categories for the same time to get their overall values for each time interval:

Category Day Time Value
=============================
Update_FL 15/9 09:00 1395
Downrate_X 15/9 09:00 14
Update_FL 15/9 09:01 420
Downrate_X 15/9 09:01 87

and then would get the average for the day by adding the values for each time interval and dividing by the number of intervals (in this case 1395 + 420 divided by 2 for Update_FL) and get the max as the highest value for a single time entry (in this case 1395 for Update_FL)

I'm wondering how do I do this in PL/SQL without physically creating the intermediate table to get the totals per time interval.

If I create a Cursor to create the intermediate table in memory do I need to loop through this cursor to get the average and max values? I need this to be performant and in a day there are 1440 minutes so I would have to loop this many times per Category and there could be 100 categories. Is there a better solution?

Thanks for your help,

Toddyl

 

[SantaMufasa]

Toddyl,

It would be very helpful if you post:[ul][li]Same output for your final step: then would get the average for the day by adding the values for each time interval and dividing by the number of intervals (in this case 1395 + 420 divided by 2 for Update_FL) and get the max as the highest value for a single time entry (in this case 1395 for Update_FL)

[/li][li]CREATE TABLE... and INSERT TABLE... lines of code so that we can simulate, and code for, your need.[/li][/ul]Thanks,


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

Too slow, Toddyl...<grin>

Frankly, I don't know why you are resorting to PL/SQL...you can do it all in plain SQL:

select Server,Process,Category,to_char(Day_Time,'dd/mm hh24:mi')Day_time ,Value
  from category_rate;

SERVE PR CATEGORY   DAY_TIME         VALUE
----- -- ---------- ----------- ----------
serv1 a1 Update_FL  15/09 09:00        100
serv1 a2 Update_FL  15/09 09:00         95
serv2 b1 Downrate_X 15/09 09:00         14
serv2 b2 Update_FL  15/09 09:00       1200
serv1 a1 Update_FL  15/09 09:01         25
serv1 a2 Update_FL  15/09 09:01        195
serv2 b1 Downrate_X 15/09 09:01         87
serv2 b2 Update_FL  15/09 09:01        200

8 rows selected.

col a heading "Category's|Daily|Avg Value" format 999,999.99
col m heading "Category's|Daily|Max Value" format 999,999.99
select category, avg(value) a, max(value) m
  from (select category, to_char(day_time,'dd/mm hh24:mi') Day_Time, sum(value)value
          from category_rate
         group by category, to_char(day_time,'dd/mm hh24:mi'))
 group by category
/

            Category's  Category's
                 Daily       Daily
CATEGORY     Avg Value   Max Value
---------- ----------- -----------
Downrate_X       50.50       87.00
Update_FL       907.50    1,395.00

2 rows selected.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”