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