2018年1月25日 星期四

RATIO_TO_REPORT總數占比分析函數

以下需計算記憶體中每種規格(DDR2DDR3DDR4)所占銷售之比例,SQL及結果如下圖:
記憶體整體銷售總計為500,其中DDR2DDR3DDR4分別為40160300ORACLE可使用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' TYPE25 AMT   
    FROM DUAL
    UNION ALL
    SELECT '24"' PROD, 'LCD' TYPE75 AMT   
    FROM DUAL
    );


沒有留言:

張貼留言