占总数的百分比:有比使用子选择更有效的方法吗？

Question

我有一个现有的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 |
+-----------------------+---------------+

我想按每一个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

然而，我意识到使用子选择是没有效率的。有更快的方法吗？

Answer 1

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

Answer 2

可以对整个结果集使用窗口聚合：

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

Answer 3

建议使用NTILE命令将数据划分为10组(视情况而定)，并接受所需的组以达到所需的百分比。