sql统计各种奇葩的数据库表数据

• 本文主要记录由于各种不可描述的原因，需要统计一些奇葩的表设计的数据（比如十年前的表）的解决办法和思路
• 主要有：
  • 统计一个字段中用某字符分割的词出现的频率
  • 几个字段属于同一类型数据，一条数据最多对应多少条该类型数据，取决于该类型字段个数
  • 以下仅针对sqlserver数据库，但解决思路通用

奇葩的设计

一、数据间字符分隔统计

• 这种设计见的最多的就是树结构数据（至少为了方便以前都是这么干的多），记录子节点的方式，将子节点的id用逗号分隔，或者其它数据用别的字符隔开。这种一般是为了方便，不想建个仅仅记录两个字段的中间表。举个栗子：
  
• 虽然跟常见的逗号分隔的数据比起不常见，但是第一眼见到还是懵了，这种都有的？要怎么统计才好呢，下文分解。还有下面更奇葩的

二、同类型数据多字段表示

• 这种设计更加说明偷懒了，上面的设计为了方便可能说的过去，这种设计的每个字段可表示同一类型（可分出一个表）的不同状态，字符分隔已经不能满足了，于是：
  
• 这是一张房间表，C1-C4几个字段代表床位，0代表不存在的床位，1代表空闲，2代表使用中，这种结构用字符分隔放一个字段，是不是满足不了这么多种状态。如果问床位超过4个怎么办，那就再加字段呀，多几个字段，不存在的就填0

重构

• 没错，先不说怎么解决统计难题，先来看看如果是遵循范式的设计应该是怎么样的，这有利于后面讲解统计思路

一、字符分隔

• 首先，这个情况需要分一个中间表，如果是有一张疾病表，那疾病名称就换成疾病id

• 以上结果，统计各类疾病数量就很简单了，group by一下就行

二、同类型多字段

• 这种情况也是中间表，略有不同

• 以上结果，无论统计总床位数和各状态的床位数都很简单了

解决办法

• 解决思路就是将各种表结构转成上面易于统计的结构。问了很多人，大部分都推荐使用函数处理，将所要统计的数据转成一个表格，即上面的表结构，实现是遍历添加到新表或者临时表。
• sql分割字符串并分组统计数量
• SQL Server 中master…spt_values的应用
• SQL：将字符串以特定字符分割并返回Table
• 参考几个博文，整理下思路，得到以下解决方法：

一、字符分隔

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，将全部数据取出来在代码里循环处理。
• 较大的收获是坚持到最后，找到理想的解决方法，虽然费了不少功夫，但是得到了提升。如果一味的为了快速完成工作使用最简单粗暴的方法，事后也不进行思考总结，我想这并不会有什么提升。从长远来看，多坚持那么一下，换来的可能就是提升，还是值得的，即使事后花时间加班为研究时间买单。如果先快速完成，事后再研究，可能就没有那种急迫感和动力了，因为事后你并不一定要研究出来，看情况选择吧。
• 最后感谢各位群友提供各种建议，因为总是会有一两个人把你点醒，坚持才可能有收获，放弃了一定没有收获。总结成为了记录博文最重要的一部分，写到最后，越来越发现，很多问题，有一个清晰的解决思路，大部分问题都能进行转换，然后被解决过问题的思路解决。