RATIO_TO_REPORT總數占比分析函數

以下需計算記憶體中每種規格(DDR2、DDR3及DDR4)所占銷售之比例，SQL及結果如下圖:

記憶體整體銷售總計為500，其中DDR2、DDR3及DDR4分別為40、160及300，ORACLE可使用RATIO_TO_REPORT分析函數直接即可得到總數占比，另外，也可使用SUM() OVER()分析函數計算總和，再據以計算各種規格之占比。

SELECT PROD

      , TYPE

      , AMT

      , RATIO_TO_REPORT(AMT) OVER() RTO

      -----

      , AMT / SUM(AMT) OVER()       "RTO-2"

FROM

    (

    SELECT 'DDR2' PROD, 'RAM' TYPE, 40 AMT

    FROM DUAL

    UNION ALL

    SELECT 'DDR3' PROD, 'RAM' TYPE, 160 AMT

    FROM DUAL

    UNION ALL

    SELECT 'DDR4' PROD, 'RAM' TYPE, 300 AMT

    FROM DUAL

    )

雖MSSQL目前中版本提供RATIO_TO_REPORT分析函數，但2005已提供不具累計功能之SUM() OVER()分析函數(無法使用ORDER BY子句)，因此仍可輕鬆達成。SQL及結果如下:

SELECT PROD

      , TYPE

      , AMT

      --, RATIO_TO_REPORT(AMT) OVER() RTO

      -----

      , AMT / SUM(AMT) OVER()                             "RTO-2"

       , 1.0 * AMT AS NUMERIC(6,2)) / SUM(AMT) OVER()  "RTO-2b"

FROM (VALUES('DDR2', 'RAM', 40)

          , ('DDR3', 'RAM', 160)

          , ('DDR4', 'RAM', 300)

      ) SALES(PROD, TYPE, AMT)

由前述結果，可發現比率為0，對於數值運算結果型態MSSQL將參考運算子之型態而定，由於分子/母均為整數，因此輸出型態亦同，而發生轉型(整數)捨位問題，可先乘上1.0進行隱喻轉型，詳細可參考SQL Server數學運算應注意事項所探討。

請注意，前述為簡化說明，函數中OVER()均未帶有任何參數，可自行測試帶參數之結果。

SELECT PROD

      , TYPE

      , AMT

      , RATIO_TO_REPORT(AMT) OVER(PARTITION BY TYPE) RTO

      -----

      , AMT / SUM(AMT) OVER(PARTITION BY TYPE)       "RTO-2"

FROM

    (

    SELECT 'DDR2' PROD, 'RAM' TYPE, 40 AMT

    FROM DUAL

    UNION ALL

    SELECT 'DDR3' PROD, 'RAM' TYPE, 160 AMT

    FROM DUAL

    UNION ALL

    SELECT 'DDR4' PROD, 'RAM' TYPE, 300 AMT

    FROM DUAL

    UNION ALL

    SELECT '22"' PROD, 'LCD' TYPE,  25 AMT   

    FROM DUAL

    UNION ALL

    SELECT '24"' PROD, 'LCD' TYPE,  75 AMT   

    FROM DUAL

    );