sql統計各種奇葩的資料庫表資料

• 本文主要記錄由於各種不可描述的原因，需要統計一些奇葩的表設計的資料（比如十年前的表）的解決辦法和思路
• 主要有：
  • 統計一個欄位中用某字元分割的詞出現的頻率
  • 幾個欄位屬於同一型別資料，一條資料最多對應多少條該型別資料，取決於該型別欄位個數
  • 以下僅針對sqlserver資料庫，但解決思路通用

奇葩的設計

一、資料間字元分隔統計

• 這種設計見的最多的就是樹結構資料（至少為了方便以前都是這麼幹的多），記錄子節點的方式，將子節點的id用逗號分隔，或者其它資料用別的字元隔開。這種一般是為了方便，不想建個僅僅記錄兩個欄位的中間表。舉個例子：
  
• 雖然跟常見的逗號分隔的資料比起不常見，但是第一眼見到還是懵了，這種都有的？要怎麼統計才好呢，下文分解。還有下面更奇葩的

二、同型別資料多欄位表示

• 這種設計更加說明偷懶了，上面的設計為了方便可能說的過去，這種設計的每個欄位可表示同一型別（可分出一個表）的不同狀態，字元分隔已經不能滿足了，於是：
  
• 這是一張房間表，C1-C4幾個欄位代表床位，0代表不存在的床位，1代表空閒，2代表使用中，這種結構用字元分隔放一個欄位，是不是滿足不了這麼多種狀態。如果問床位超過4個怎麼辦，那就再加欄位呀，多幾個欄位，不存在的就填0

重構

• 沒錯，先不說怎麼解決統計難題，先來看看如果是遵循正規化的設計應該是怎麼樣的，這有利於後面講解統計思路

一、字元分隔

• 首先，這個情況需要分一箇中間表，如果是有一張疾病表，那疾病名稱就換成疾病id

• 以上結果，統計各類疾病數量就很簡單了，group by一下就行

二、同型別多欄位

• 這種情況也是中間表，略有不同

• 以上結果，無論統計總床位數和各狀態的床位數都很簡單了

解決辦法

• 解決思路就是將各種表結構轉成上面易於統計的結構。問了很多人，大部分都推薦使用函式處理，將所要統計的資料轉成一個表格，即上面的表結構，實現是遍歷新增到新表或者臨時表。
• 參考幾個博文，整理下思路，得到以下解決方法：

一、字元分隔

SELECT COUNT(a.DiseaseType) AS Num, a.DiseaseType as jk_zyjb FROM 
(
Select 
a.Id, 
a.jk_zyjb 疾病,
b.number, 
substring(a.jk_zyjb,b.number, charindex('+',a.jk_zyjb+'+',b.number)-b.number) as DiseaseType

From Dict_Oldie a WITH (NOLOCK), master..spt_values b WITH (NOLOCK)

WHERE 
jk_zyjb IS NOT NULL AND 
b.type='p'AND b.number > 0  AND 
b.number <= LEN(a.jk_zyjb) AND
substring('+'+a.jk_zyjb, b.number,1) = '+'
GROUP BY a.jk_zyjb,a.Id,b.number
) as a

GROUP BY a.DiseaseType

• 以上子查詢的結果是
  

• 最終結果
  

• 上面程式碼主要是利用master..spt_values表的p型別資料，0-2047，與目標表進行聯合查詢，遍歷每個number，擷取字元分隔的詞。

• 查詢中的substring函式用於擷取疾病名稱，其中的charindex函式用於查詢疾病名稱的長度，where中的substring用於確定number是否是開始擷取的位置，每一處都很關鍵，這裡不過多解釋，多試幾遍就理解了

二、同型別多欄位

SELECT a.BedStatus,COUNT(a.BedStatus) Num,
BedStatusStr=(CASE a.BedStatus
	WHEN 1 THEN '空閒'
	WHEN 2 THEN '使用'
	WHEN 3 THEN '外出'
	ELSE '試住'
END) 
FROM
(SELECT Id, BedStatus=(CASE b.number
	WHEN 0 THEN C1
	WHEN 1 THEN C2
	WHEN 2 THEN C3
	ELSE C4
END)
FROM Room a WITH (NOLOCK), master..spt_values b WITH (NOLOCK)
WHERE 
b.type = 'p' AND b.number < 4) a
WHERE a.BedStatus!=0
GROUP BY a.BedStatus

• 其中子查詢結果

• 最終結果
  

• 以上，相當於聯合一張只有[1,2,3,4]四個資料的表，用迴圈，分支判斷將四個欄位轉換成一列，得到上述中間表結構，再按需求進行統計

三、說明

• 上述解決方法用到了master..spt_values、WITH (NOLOCK)。
• master..spt_values是個存有各種常用資料的表，也可以自己構造，個人感覺使用還是挺廣泛的，這個表也是第一次用，給解決問題提供了不錯的思路。
• WITH (NOLOCK)，字面意思就是不加鎖嘛，由於我只是讀取資料統計，就算髒讀也無所謂，加上這個據說效能更好，沒驗證過

總結

• 第一次遇到這麼麻煩的統計，總的來說，堅持到最後，還是找到了不錯的解決方法，特別是master..spt_values這個表，應用挺多的，但是在不知道它之前真的沒有思路，差點就放棄了用SQL，將全部資料取出來在程式碼裡迴圈處理。
• 較大的收穫是堅持到最後，找到理想的解決方法，雖然費了不少功夫，但是得到了提升。如果一味的為了快速完成工作使用最簡單粗暴的方法，事後也不進行思考總結，我想這並不會有什麼提升。從長遠來看，多堅持那麼一下，換來的可能就是提升，還是值得的，即使事後花時間加班為研究時間買單。如果先快速完成，事後再研究，可能就沒有那種急迫感和動力了，因為事後你並不一定要研究出來，看情況選擇吧。
• 最後感謝各位群友提供各種建議，因為總是會有一兩個人把你點醒，堅持才可能有收穫，放棄了一定沒有收穫。總結成為了記錄博文最重要的一部分，寫到最後，越來越發現，很多問題，有一個清晰的解決思路，大部分問題都能進行轉換，然後被解決過問題的思路解決。