假设有如下表: SALE_AMOUNT
BU AMOUNT
=============
CRD 100
SHK 200
RSJ 500
运行如下 sql
SELECT DECODE (GROUPING_ID (BU), 1, 'Total', BU) as BU, SUM (AMOUNT) as AMOUNT, (ratio_to_report (SUM (AMOUNT)) OVER ()) * 2 as AMOUNT_RATIO FROM SALE_AMOUNTGROUP BY ROLLUP (BU)
ORDER BY BU
可得如下结果
BU AMOUNT AMOUNT_RATIO
================================
CRD 100 0.0125
RSJ 500 0.625
SHK 200 0.25
Total 800 1
当它们激情碰撞时, 有两点是需要注意的:
1) ratio_to_report (SUM (AMOUNT)) OVER () 中的SUM () 须写于内层而不能写于外层. 如SUM (ratio_to_report (AMOUNT) OVER ())
2) ratio_to_report 后须乘以 2, 因为ratio_to_report (SUM (AMOUNT)) OVER () 已除多一个 total 了. 即AMOUNT / (2 * TOTAL_ AMOUNT )