2018年2月26日 星期一

資料區間分群統計作法

本案例將說明如何分群以每0.1進行切割分群,但超過±0.2以上()將併入0.2群組,並計算出每個組出占整體之比例,如下:

資料

分群

Grp
Range
Rows
0.2
0.2()以上
2
0.1
[ 0.1, 0.2)
2
0.0
[ 0.0, 0.1)
2
-0.1
[-0.1, 0.0)
1
-0.2
-0.1()以下
3
SQL及執行結果如下:
MSSQL
SELECT CASE Grp
            WHEN  0.2 THEN '0.2()以上'
            WHEN -0.THEN '-0.1以下'
            ELSE '[' + RIGHT('    ' + CAST(Grp AS VARCHAR(10)), 4)
                     + ', '
                     + RIGHT('    ' + CAST((Grp+0.1) AS VARCHAR(10)),  4) 
                     +')'
       END Range --Step 02. 值域顯示轉換
     , Ratio
     , RowCnt
     , Total_RowCnt
FROM
(
--Step 01. 由於以0.1為分群並以值域中最小者為群組代碼
SELECT CASE WHEN FLOOR(DIFF / 0.1) >  2 THEN  2--重點1: 大於0.2,  併入0.2
              WHEN FLOOR(DIFF / 0.1) < -2 THEN -2--重點1: 小於-0.2, 併入-0.2
              ELSE FLOOR(DIFF / 0.1)
         END * 0.1 Grp
     , CAST(100.0* COUNT(*) / SUM(COUNT(*)) OVER() AS NUMERIC(5, 2)) Ratio--重點2:占
     , COUNT(*)                                                      RowCnt
, SUM(COUNT(*)) OVER()                                          Total_RowCnt
FROM #Meas       
GROUP BY CASE WHEN FLOOR(DIFF / 0.1) >  2 THEN  2  --大於0.2,  併入0.2
                WHEN FLOOR(DIFF / 0.1) < -2 THEN ---小於-0.2, 併入-0.2
                ELSE FLOOR(DIFF / 0.1)
           END
  ) A
WHERE 1=1
ORDER BY Grp DESC
SQL撰寫重點說明如下:
1.   資料分群
定義是以每0.1進行分群,並以此值域中最小值(包含此值)為群組代號,因此將利用數值運算基本概念,直接將資料除以0.1取得商數(具小數),再使用FLOOR函數取得下界整數值,即相對於基準值(0)之距離單位數,單位數相同者即同一群組。

2.   占比計算
每群組筆數除以總筆數即為占比(%)。首先用COUNT(*)計算每群組筆數,再以採用SUM() OVER()分析函數(2005支援之功能)將每群組筆數合計為總筆數,每群組筆數占總筆數之比率即為所求,但請特別注意,由於二者(筆數)資料型態為均整數,依MSSQL數值運算特性其運算結果也為應整數,請務必記得需轉型為具小數位之型態。

ORACLE
SELECT CASE Grp
            WHEN  0.2 THEN '0.2()以上'
            WHEN -0.2 THEN '-0.1以下'
            ELSE '[' || LPAD(Grp, 4, ' ')
                     || ', '
                     || LPAD(Grp-0.1, 4, ' '
                     || ')'
       END Range
     , Ratio
     , RowCnt
     , Total_RowCnt
FROM
   (
   SELECT CASE WHEN FLOOR(DIFF / 0.1) >2 THEN 2
               WHEN FLOOR(DIFF / 0.1) <-2 THEN -2
               ELSE FLOOR(DIFF / 0.1)
          END * 0.1                                                    Grp
        , CAST(100* COUNT(*) / SUM(COUNT(*)) OVER() AS NUMERIC(5, 2))  Ratio
        , COUNT(*)                                                     RowCnt
        , SUM(COUNT(*)) OVER()                                         Total_RowCnt
        --, MIN(DIFF), MAX(DIFF)
    FROM Meas       
    GROUP BY CASE WHEN FLOOR(DIFF / 0.1) >2 THEN 2
                  WHEN FLOOR(DIFF / 0.1) <-2 THEN -2
                  ELSE FLOOR(DIFF / 0.1)
             END
   ) A
WHERE 1=1
ORDER BY Grp DESC


建立測試資料 (ORACLE請將#剔除)
DROP TABLE #Meas

CREATE TABLE #Meas
(
 SEQ          INT,
 Param        VARCHAR(10),
 Diff         FLOAT
 )

SELECT *
FROM #Meas

INSERT INTO #Meas VALUES (8,  'Voltage',   0.31)
INSERT INTO #Meas VALUES (7,  'Voltage',   0.28)
INSERT INTO #Meas VALUES (1,  'Voltage',   0.19)
INSERT INTO #Meas VALUES (5,  'Voltage',   0.10)
INSERT INTO #Meas VALUES (10, 'Voltage',   0.02)
INSERT INTO #Meas VALUES (9,  'Voltage',   0.0)
INSERT INTO #Meas VALUES (3,  'Voltage',  -0.05)
INSERT INTO #Meas VALUES (2,  'Voltage',  -0.16)
INSERT INTO #Meas VALUES (4,  'Voltage',  -0.23)
INSERT INTO #Meas VALUES (6,  'Voltage',  -0.34)




沒有留言:

張貼留言