【实习笔记04】SQL中的数据清洗—字符串数据中无用字段去除、数据类型格式的转换

处理目标：将下图初始表（下图1）处理后变为目标表（下图2）
解决方法：
1、replace()函数：替换或去掉字段中的某些字符。
2.1、CAST(expr AS type) ：是将某种数据类型的表达式显式转换为另一种数据类型
2.2、Decimal（）：为SQL Server、MySql等数据库的一种数据类型，不属于浮点数类型，可以在定义时划定整数部分以及小数部分的位数。

一、字符串数据中清洗无用字段-replace()函数

1.1 replace()函数的使用

应用场景：字段中含有*，/等无用字段。
replace()使用：
1.replace(name,‘C’,’’)——去掉name列中的C
2.replace(name,substring(name,1,4),’’) ——name列字段中改’C1 16号平台’为’16号平台’
(注意：在replace()函数中使用substring()函数时，里面的函数不能带单引号)
参考链接：https://blog.csdn.net/rosejeck/article/details/84102957

1.2 在SQL中实现处理目标代码如下：

select 
		设备编码
		,replace(replace(`名称`,"?","")," ","") as 设备名称
		,replace(replace(`CPU使用率`,"?","")," ","") as CPU使用率
		,replace(replace(`内存使用率`,"?","")," ","") as 内存使用率
		,replace(replace(`可用内存`,"?","")," ","") as 可用内存
		,replace(replace(`系统运行时间`,"?","")," ","") as 系统运行时间
	from 表

二、数据类型或格式的转换

2.1 cast（）的使用

语法：CAST (expression AS data_type)

expression：任何有效的SQServer表达式。
AS：用于分隔两个参数，在AS之前的是要处理的数据，在AS之后是要转换的数据类型。
data_type：目标系统所提供的数据类型，包括bigint和sql_variant，不能使用用户定义的数据类型。

可以转换的类型是有限制的。这个类型可以是以下值其中的一个：

二进制，同带binary前缀的效果 : BINARY
字符型，可带参数 : CHAR()
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL
整数 : SIGNED
无符号整数 : UNSIGNED
参考链接：https://blog.csdn.net/qq_21101587/article/details/78642423

2.2 decimal()的使用

decimal(10,2)中的“2”表示小数部分的位数，如果插入的值未指定小数部分或者小数部分不足两位则会自动补到2位小数，若插入的值小数部分超过了2为则会发生截断，截取前2位小数。
“10”指的是整数部分加小数部分的总长度，也即插入的数字整数部分不能超过“10-2”位，否则不能成功插入，会报超出范围的错误。
decimal一般用于对精度要求高的，比如与金钱相关的，用整型和浮点型都不行了，才会用到decimal，或者用于sql查询中也可以

     decimal(a,b)

     a:指的是整数和小数所能存的最多的位数

     b:指的是小数数字的位数，即a-b就是整数的位数

参考链接：https://www.cnblogs.com/Helloxxm/articles/11400923.html

2.3 在SQL中实现处理目标代码如下：

,if(length(CPU使用率)>0,concat(cast(CPU使用率 as decimal(32,2)),"%"),CPU使用率) as CPU使用率
	,if(length(内存使用率)>0,concat(cast(内存使用率 as decimal(32,2)),"%"),内存使用率) as 内存使用率
	,if(length(可用内存)>0,concat(round(cast(可用内存 as decimal(32,2))/1024/1024,2),"M"),可用内存) as 可用内存
	,if(length(系统运行时间)>0,concat(round(cast(系统运行时间 as decimal(32,2))/3600,2),"小时"),系统运行时间) as 系统运行时间

三、完整代码

select 
	row_number() over(order by 设备编码 asc) as 序号
	,设备名称
	,if(length(CPU使用率)>0,concat(cast(CPU使用率 as decimal(32,2)),"%"),CPU使用率) as CPU使用率
	,if(length(内存使用率)>0,concat(cast(内存使用率 as decimal(32,2)),"%"),内存使用率) as 内存使用率
	,if(length(可用内存)>0,concat(round(cast(可用内存 as decimal(32,2))/1024/1024,2),"M"),可用内存) as 可用内存
	,if(length(系统运行时间)>0,concat(round(cast(系统运行时间 as decimal(32,2))/3600,2),"小时"),系统运行时间) as 系统运行时间
from (
	select 
		设备编码
		,replace(replace(`设备名称`,"?","")," ","") as 设备名称
		,replace(replace(`CPU使用率`,"?","")," ","") as CPU使用率
		,replace(replace(`内存使用率`,"?","")," ","") as 内存使用率
		,replace(replace(`可用内存`,"?","")," ","") as 可用内存
		,replace(replace(`系统运行时间`,"?","")," ","") as 系统运行时间
	from 表
) t