Re: analytic function of percent of total
- From: "Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 21 Feb 2008 15:52:32 -0600 (CST)
And a practical DBA-type example of this:
SELECT
segment_name "Table",
total_mb "Total MB",
ROUND(rr,4)*100 "%/Report",
ROUND(SUM(rr) OVER (ORDER BY total_mb DESC),4)*100 "Cuml %",
RANK() OVER (ORDER BY total_mb DESC) "Rank"
FROM
(
SELECT
segment_name,
total_mb,
RATIO_TO_REPORT(total_mb) OVER () "RR"
FROM
(
SELECT
segment_name,
SUM(bytes)/1024/1024 "TOTAL_MB"
FROM dba_segments ds
WHERE ds.tablespace_name = :TABLESPACE
AND ds.owner = :OWNER
GROUP BY segment_name
)
);
Enjoy!
Rich
> RATIO_TO_REPORT() over (...)
>
>
> Quoting Jeffrey Beckstrom <JBECKSTROM@xxxxxxxxx>:
>
>> Isn't there an analytical function that computes each rows value as a
>> percent of the total of all rows?
>>
>>
>> Jeffrey Beckstrom
--
//www.freelists.org/webpage/oracle-l
Other related posts:
