Re: analytic function of percent of total

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


--
http://www.freelists.org/webpage/oracle-l


Other related posts: