資料區間分群統計作法

本案例將說明如何分群以每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.2 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 -2  --小於-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)