資料分群統計(SQL Server)

範例資料如下：

CREATE TABLE #tKSArea(District VARCHAR(10),Area FLOAT);
INSERT #tKSArea SELECT '楠梓區',25.8276;
INSERT #tKSArea SELECT '左營區',19.3888;
INSERT #tKSArea SELECT '鼓山區',14.7458;
INSERT #tKSArea SELECT '三民區',19.7866;
INSERT #tKSArea SELECT '苓雅區',8.1522;
INSERT #tKSArea SELECT '新興區',1.9764;
INSERT #tKSArea SELECT '前金區',1.8573;
INSERT #tKSArea SELECT '鹽埕區',1.4161;
INSERT #tKSArea SELECT '前鎮區',19.1309;
INSERT #tKSArea SELECT '旗津區',1.4639;
INSERT #tKSArea SELECT '小港區',39.8753;

目的是將這11個行政區的面積以5平方公里為一個區間作資料分群(大於20平方公里者自成一群)。

程式主體以及執行結果如下：

SELECT CASE A.Grp
		WHEN 20 THEN '20(含)以上'
		WHEN 0 THEN '不足5'
		ELSE CAST(A.Grp AS VARCHAR(10))+' ~ '+CAST((A.Grp+5.0) AS VARCHAR(10))
	END '面積(平方公里)' --Step 02. 值域顯示轉換
	,A.RowCnt '行政區數'
	,A.TotalRowCnt '行政區總數'
	,A.Ratio '占比'
FROM
(
	--Step 01. 以5為單位分群並以值域中最小者為群組代碼
	SELECT CASE WHEN FLOOR(Area/5.0)>4 THEN 4 --大於20，併入20
			ELSE FLOOR(Area/5.0)
		END*5.0 Grp
		,CAST(100.0*COUNT(*)/SUM(COUNT(*)) OVER() AS NUMERIC(5,2)) Ratio --占比
		,COUNT(*) RowCnt
		,SUM(COUNT(*)) OVER() TotalRowCnt
	FROM #tKSArea       
	GROUP BY CASE WHEN FLOOR(Area/5.0)>4 THEN 4 --大於20，併入20
				ELSE FLOOR(Area/5.0)
			END
) A
ORDER BY A.Grp DESC;

本案例的重點在於第11~21行的子查詢，將它抓出來獨立執行的話，結果如下：

如同註解所描述，以5為一個值域，並以最小值當作群組名稱，

意思就是Grp 0表示0 ~ 5這個值域，Grp 5表示5 ~ 10這個值域，依此類推。

因為設定區間為5，所以GROUP BY的分群規則就是面積除以5，取最小整數後分群。

	GROUP BY CASE WHEN FLOOR(Area/5.0)>4 THEN 4 --大於20，併入20
				ELSE FLOOR(Area/5.0)
			END

如果區間改成10那就將Area/5.0改寫成Area/10.0就可以了。

這邊要注意多了一個CASE WHEN判斷，前面有說過20平方公里以上就不再細分了，直接自成一群，

所以Area/5.0>4的話，就併入「20」這個群組。

既然已經群組化，那每個群組內的資料筆數可以用COUNT(*)取得。

		,COUNT(*) RowCnt

另外本案例有每個群組所占的百分比，所以必須知道行政區總數之後，當作分母去計算出來。

		,SUM(COUNT(*)) OVER() TotalRowCnt

SUM() OVER() 這個分析函數是由MS SQL 2012或ORACLE 8i以上版本所提供，有點類似累計的功能。

礙於篇幅以及主題相關性，這邊不多作介紹。如果需要知道SUM() OVER()用法的朋友可以參考這篇。

內部的子查詢已經將行政區面積資料群組化完成了，接下來就是交給最外層去作顯示。

SELECT CASE A.Grp
		WHEN 20 THEN '20(含)以上'
		WHEN 0 THEN '不足5'
		ELSE CAST(A.Grp AS VARCHAR(10))+' ~ '+CAST((A.Grp+5.0) AS VARCHAR(10))
	END '面積(平方公里)' --Step 02. 值域顯示轉換
	,A.RowCnt '行政區數'
	,A.TotalRowCnt '行政區總數'
	,A.Ratio '占比'

可以清楚看到，若是FLOOR(Area/5.0)的值大於4，就代表超過20平方公里。

這邊額外寫了一個0的判斷，只是為了顯示「不足5」這筆資料，如果不要這個判斷也行，那結果就會顯示「0 ~ 5」這個樣子。

介紹到這邊突然想到可能會有顯示每個群組中的行政區名稱這個需求，類似像以下這個執行結果：

雖然麻煩了一點，但也不是很難的問題。

SQL Server 2017版以前的使用者可以參考FOR XML PATH的用法。

SQL Server 2017版開始，可以使用更為簡便的STRING_AGG()函數。

最後附上本範例所有的程式碼：

CREATE TABLE #tKSArea(District VARCHAR(10),Area FLOAT);
INSERT #tKSArea SELECT '楠梓區',25.8276;
INSERT #tKSArea SELECT '左營區',19.3888;
INSERT #tKSArea SELECT '鼓山區',14.7458;
INSERT #tKSArea SELECT '三民區',19.7866;
INSERT #tKSArea SELECT '苓雅區',8.1522;
INSERT #tKSArea SELECT '新興區',1.9764;
INSERT #tKSArea SELECT '前金區',1.8573;
INSERT #tKSArea SELECT '鹽埕區',1.4161;
INSERT #tKSArea SELECT '前鎮區',19.1309;
INSERT #tKSArea SELECT '旗津區',1.4639;
INSERT #tKSArea SELECT '小港區',39.8753;

SELECT CASE A.Grp
		WHEN 20 THEN '20(含)以上'
		WHEN 0 THEN '不足5'
		ELSE CAST(A.Grp AS VARCHAR(10))+' ~ '+CAST((A.Grp+5.0) AS VARCHAR(10))
	END '面積(平方公里)' --Step 02. 值域顯示轉換
	,A.RowCnt '行政區數'
	,A.TotalRowCnt '行政區總數'
	,A.Ratio '占比'
FROM
(
	--Step 01. 以5為單位分群並以值域中最小者為群組代碼
	SELECT CASE WHEN FLOOR(Area/5.0)>4 THEN 4 --大於20，併入20
			ELSE FLOOR(Area/5.0)
		END*5.0 Grp
		,CAST(100.0*COUNT(*)/SUM(COUNT(*)) OVER() AS NUMERIC(5,2)) Ratio --占比
		,COUNT(*) RowCnt
		,SUM(COUNT(*)) OVER() TotalRowCnt
	FROM #tKSArea       
	GROUP BY CASE WHEN FLOOR(Area/5.0)>4 THEN 4 --大於20，併入20
				ELSE FLOOR(Area/5.0)
			END
) A
ORDER BY A.Grp DESC;
			
DROP TABLE #tKSArea;