佔總數的百分比：有沒有比使用子選擇更有效的方法？

我有一個現有的ASSET_SUMMARY_VW觀點：

+-----------------------+---------------+
|      ASSET_TYPE       |     COST      |
+-----------------------+---------------+
| TRANSPORTATION        | 1,000,000,000 |
| TRANSPORTATION        |   500,000,000 |
| TRANSPORTATION        |   500,000,000 |
| ACTIVE TRANSPORTATION |   100,000,000 |
| ACTIVE TRANSPORTATION |    50,000,000 |
| STORMWATER MANAGEMENT |   500,000,000 |
+-----------------------+---------------+

我想 GROUP BY 併計算每個ASSET_TYPE與總成本相比的百分比：

+-----------------------+---------------+------------------+
|      ASSET_TYPE       |     COST      | PERCENT_OF_TOTAL |
+-----------------------+---------------+------------------+
| TRANSPORTATION        | 2,000,000,000 |             75.4 |
| ACTIVE TRANSPORTATION |   150,000,000 |              5.6 |
| STORMWATER MANAGEMENT |   500,000,000 |             18.8 |
+-----------------------+---------------+------------------+

我可以除以COST得到總成本的子選擇：

SELECT 
   ASSET_TYPE
   ,SUM(COST) AS COST
   ,SUM(COST/(
              SELECT 
                  SUM(COST) 
              FROM 
                  USER.ASSET_SUMMARY_VW)
              ) AS PERCENT_OF_TOTAL
FROM 
   USER.ASSET_SUMMARY_VW
GROUP BY 
   ASSET_TYPE

但是，我發現使用子選擇效率低下。有沒有更快的方法來做到這一點？

with data as
(
 select 'TRANSPORTATION' as asset_type, 2000000000 as cost from dual union all
 select 'ACTIVE TRANSPORTATION' as asset_type, 150000000 as cost from dual union all
 select 'STORMWATER MANAGEMENT' as asset_type, 500000000 as cost from dual
 )
select
 data.asset_type, data.cost, 
 trunc(ratio_to_report(data.cost) over () * 100, 1) as percent_of_total
from data;

ASSET_TYPE                  COST PERCENT_OF_TOTAL
--------------------- ---------- ----------------
TRANSPORTATION        2000000000             75.4
ACTIVE TRANSPORTATION  150000000              5.6
STORMWATER MANAGEMENT  500000000             18.8

RATIO_TO_REPORT仍然適用於您更新的數據：

with data as
(
 select 'TRANSPORTATION' as asset_type, 1000000000 as cost from dual union all
 select 'TRANSPORTATION' as asset_type, 500000000 as cost from dual union all
 select 'TRANSPORTATION' as asset_type, 500000000 as cost from dual union all
 select 'ACTIVE TRANSPORTATION' as asset_type, 100000000 as cost from dual union all
 select 'ACTIVE TRANSPORTATION' as asset_type, 50000000 as cost from dual union all
 select 'STORMWATER MANAGEMENT' as asset_type, 500000000 as cost from dual
 )
select
 data.asset_type, sum(data.cost), 
 trunc(ratio_to_report(sum(data.cost)) over () * 100, 1) as percent_of_total
from data
 group by data.asset_type
;

ASSET_TYPE            SUM(DATA.COST) PERCENT_OF_TOTAL
--------------------- -------------- ----------------
TRANSPORTATION            2000000000             75.4
ACTIVE TRANSPORTATION      150000000              5.6
STORMWATER MANAGEMENT      500000000             18.8

引用自：https://dba.stackexchange.com/questions/170852