-
turning columns into rows
Got this query (9.2.0.5)
Code:
select avg(cpu_used) from system_capacity_cpu
where to_char(metric_date,'MM/YYYY') = to_char(to_date('01/02/2007','DD/MM/YYYY'),'MM/YYYY') and hostname = 'xxx'
What that tables contains is a hostname, a month and cpu usage,
what I need to get is the output for each Month listed in the table (in the metric_date column) on the same line, so the output would be
Code:
hostname 01/2007 02/2007 03/2007
xxx 1.2 1.3 1.4
Anyone know how to do that?
-
You are talking about a pivot query. You can get it for all of the hostnames of you can limit to just one hostname.
Code:
SELECT *
FROM ( SELECT hostname, AVG(cpu_used) Jan2007
FROM system_capacity_cpu
WHERE TRUNC(metric_date,'MM') = TO_DATE('01/02/2007','DD/MM/YYYY')
GROUP BY hostname ) Jan2007
INNER JOIN
( SELECT hostname, AVG(cpu_used) Feb2007
FROM system_capacity_cpu
WHERE TRUNC(metric_date,'MM') = TO_DATE('01/02/2007','DD/MM/YYYY')
GROUP BY hostname ) Feb2007
ON Jan2007.Jan2007 = Feb2007.Feb2007
INNER JOIN
( SELECT hostname, AVG(cpu_used) Mar2007
FROM system_capacity_cpu
WHERE TRUNC(metric_date,'MM') = TO_DATE('01/02/2007','DD/MM/YYYY')
GROUP BY hostname ) Mar2007
ON Jan2007.Jan2007 = Mar2007.Mar2007
ORDER BY hostname;
-
thanks! will try that out tomorrow when i am back in work
-
Uhh.. i think you need to JOIN ON the hostname, NOT the month...
I never knew a universe where Jan 2007 was = to Feb 2007
But other than that.. It's okay.
Actually you can do this another way that only needs one table scan:
Code:
SELECT
hostname,
AVG(CASE WHEN TRUNC(metric_date, 'MM') = Jan THEN cpu_used ELSE null END) as jan_avg,
AVG(CASE WHEN TRUNC(metric_date, 'MM') = Feb THEN cpu_used ELSE null END) as feb_avg,
AVG(CASE WHEN TRUNC(metric_date, 'MM') = Mar THEN cpu_used ELSE null END) as mar_avg
FROM
system_capacity_cpu
CROSS JOIN
(
SELECT
TO_DATE('01012007','DDMMYYYY') as Jan,
TO_DATE('01022007','DDMMYYYY') as Feb,
TO_DATE('01032007','DDMMYYYY') as Mar
FROM dual
) date_constants
GROUP BY
hostname
also gandolf I think your wuery has a cut n paste error because you repeatedly say a date of '01/02/2007' for jan and mar also.. but '01/02/2007' is feb...
-
For an expl of how my query works:
The date_constants is simply 3 rows of date constants that are added on via cross join. it makes the CASE WHEN a bit less messy.
Whole table is scanned (note you could and should add a WHERE clause so only JAN FEB MAR are returned as they are the only ones that will give any meaningful values)
The table is widened out virtually to columns that are either filled with cpu, or null depending on the date::
hostname, metric_date, cpu_used
abc.com, 01-jan-2007, 1.0
abc.com, 03-jan-2007, 2.0
abc.com, 01-feb-2007, 1.0
abc.com, 03-mar-2007, 2.0
become:
hostname, metric_date, cpu_used, jan, feb, mar
abc.com, 01-jan-2007, 1.0, 1.0, null, null
abc.com, 03-jan-2007, 2.0, 2.0, null, null
abc.com, 01-feb-2007, 1.0, null, 1.0, null
abc.com, 03-mar-2007, 2.0, null, null, 2.0
when AVG is worked out, any nulls are discounted. the whole thing collapses to a single line. Date is gone because we arent grouping by it:
hostname, cpu_used, janavg, febavg, maravg
abc.com, 1.0, 1.5, 1.0, 2.0
This exploits the fact that the average of (1, null, 2) is 1.5, not 1. If nulls behaved like 0 then it would be average of 1, but nulls dont partake in averages at all, so its 2+1 / 2, not 2+0+1/3
Savvy?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|