sql統計各種奇葩的資料庫表資料
sql統計各種奇葩的資料庫表資料
- 本文主要記錄由於各種不可描述的原因,需要統計一些奇葩的表設計的資料(比如十年前的表)的解決辦法和思路
- 主要有:
- 統計一個欄位中用某字元分割的詞出現的頻率
- 幾個欄位屬於同一型別資料,一條資料最多對應多少條該型別資料,取決於該型別欄位個數
- 以下僅針對sqlserver資料庫,但解決思路通用
奇葩的設計
一、資料間字元分隔統計
- 這種設計見的最多的就是樹結構資料(至少為了方便以前都是這麼幹的多),記錄子節點的方式,將子節點的id用逗號分隔,或者其它資料用別的字元隔開。這種一般是為了方便,不想建個僅僅記錄兩個欄位的中間表。舉個例子:
- 雖然跟常見的逗號分隔的資料比起不常見,但是第一眼見到還是懵了,這種都有的?要怎麼統計才好呢,下文分解。還有下面更奇葩的
二、同型別資料多欄位表示
- 這種設計更加說明偷懶了,上面的設計為了方便可能說的過去,這種設計的每個欄位可表示同一型別(可分出一個表)的不同狀態,字元分隔已經不能滿足了,於是:
- 這是一張房間表,C1-C4幾個欄位代表床位,0代表不存在的床位,1代表空閒,2代表使用中,這種結構用字元分隔放一個欄位,是不是滿足不了這麼多種狀態。如果問床位超過4個怎麼辦,那就再加欄位呀,多幾個欄位,不存在的就填0
重構
- 沒錯,先不說怎麼解決統計難題,先來看看如果是遵循正規化的設計應該是怎麼樣的,這有利於後面講解統計思路
一、字元分隔
- 首先,這個情況需要分一箇中間表,如果是有一張疾病表,那疾病名稱就換成疾病id
關聯id | 疾病名稱 |
---|---|
1 | 高血壓 |
1 | 糖尿病 |
2 | 腦梗 |
3 | 心臟病 |
- 以上結果,統計各類疾病數量就很簡單了,
group by
一下就行
二、同型別多欄位
- 這種情況也是中間表,略有不同
房間id | 床位 | 狀態 |
---|---|---|
1 | C1 | 2 |
1 | C2 | 1 |
1 | C3 | 1 |
1 | C4 | 1 |
2 | C1 | 2 |
2 | C2 | 0 |
2 | C3 | 0 |
2 | C4 | 0 |
- 以上結果,無論統計總床位數和各狀態的床位數都很簡單了
解決辦法
- 解決思路就是將各種表結構轉成上面易於統計的結構。問了很多人,大部分都推薦使用函式處理,將所要統計的資料轉成一個表格,即上面的表結構,實現是遍歷新增到新表或者臨時表。
- 參考幾個博文,整理下思路,得到以下解決方法:
一、字元分隔
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,將全部資料取出來在程式碼裡迴圈處理。 - 較大的收穫是堅持到最後,找到理想的解決方法,雖然費了不少功夫,但是得到了提升。如果一味的為了快速完成工作使用最簡單粗暴的方法,事後也不進行思考總結,我想這並不會有什麼提升。從長遠來看,多堅持那麼一下,換來的可能就是提升,還是值得的,即使事後花時間加班為研究時間買單。如果先快速完成,事後再研究,可能就沒有那種急迫感和動力了,因為事後你並不一定要研究出來,看情況選擇吧。
- 最後感謝各位群友提供各種建議,因為總是會有一兩個人把你點醒,堅持才可能有收穫,放棄了一定沒有收穫。總結成為了記錄博文最重要的一部分,寫到最後,越來越發現,很多問題,有一個清晰的解決思路,大部分問題都能進行轉換,然後被解決過問題的思路解決。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/3244/viewspace-2820552/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 奇葩需求記錄 各個系統取資料聯表展示
- 【SQL】18 SQL NULL 函式、SQL 通用資料型別、SQL 用於各種資料庫的資料型別SQLNull函式資料型別資料庫
- [資料庫][SQL]圖解各種連線join資料庫SQL圖解
- SQL Server資料庫檢視一個資料表各列的註釋SQLServer資料庫
- 各種資料庫連線資料庫
- SQL稽核 | 如何快速使用 SQLE 稽核各種型別的資料庫SQL型別資料庫
- JDBC連線各種資料庫的字串JDBC資料庫字串
- SQL Server統計資料庫中表大小SQLServer資料庫
- oracle 當前資料庫各種版本時間釋出表Oracle資料庫
- ORACLE資料庫裡表匯入SQL Server資料庫Oracle資料庫SQLServer
- SQL建立資料庫和表SQL資料庫
- 各種連線資料庫的連線字串資料庫字串
- Oracle 資料庫的各種狀態和模式Oracle資料庫模式
- 資料庫資料恢復-ORACLE資料庫的常見故障&各種故障下的資料恢復可能性資料庫資料恢復Oracle
- Oracle資料庫的資料統計(Analyze)Oracle資料庫
- 一次搞定各種資料庫SQL執行計劃:MySQL、Oracle、SQL Server、PostgreSQL以及SQLite資料庫MySqlOracleServerSQLite
- Golang 學習寶庫,各種資料收集Golang
- jdbc獲取各種資料庫連線JDBC資料庫
- .NET中各種資料庫連線大全資料庫
- 資料庫表設計資料庫
- [轉帖]達夢資料庫-統計資料表資料量及空間表大小資料庫
- 各種學習資料
- SQL Server 7.0資料庫的六種資料移動方法 (轉)SQLServer資料庫
- 【資料庫】sql連表查詢資料庫SQL
- 資料庫SQL調優的幾種方式資料庫SQL
- 常用3種資料庫的Sql分頁資料庫SQL
- .NET 資料庫事務的各種玩法進化資料庫
- JDBC連線各種資料庫的方法(經典)JDBC資料庫
- 【轉載】JDBC連線各種資料庫的字串JDBC資料庫字串
- jdbc獲取對各種資料庫的連線JDBC資料庫
- Java連線各種資料庫的例項 (轉)Java資料庫
- MySQL資料庫表索引取樣統計MySql資料庫索引
- 資料庫 根據出生日期計算年齡的sql幾種資料庫寫法資料庫SQL
- Sql Server 匯入另一個資料庫中的表資料SQLServer資料庫
- Oracle各種版本下“示例資料庫的建立”的建立Oracle資料庫
- 各種資料庫的resin 連線池的寫法資料庫
- 一張圖解釋各種資料庫型別圖解資料庫型別
- 如何選擇各種型別資料庫?- Raj型別資料庫