kettle数据抽取：企业信息进专调表（写了很久的SQL）

先说我这个SQL是在干嘛吧，抽取数据，把数据抽取到目标表中，这时候需要修改数据类型，调整数据格式，清洗异常数据类型。我就是在干这个啦

select 
'YWYCYD_' + PollutionsourceCode     PKID,
PollutionsourceCode     ENTERID,
PollutionsourceName     ENTERNAME,
PollutionsourceOldName     HISTORYENTERNAME,
PollutionsourceNick     SHORTNAME,
Province+'000'     CODE_REGION_SHENG,
County+'000'     CODE_REGION_XIAN,
RegionCode+'000'     CODE_REGION,
AttachedRelation     CODE_ENTERRELATION,
Nature     CODE_UNITTYPECODE,
Corporation     CORPNAME,
Telephone     TELEPHONE,
FaxNumber     FAX,
cast(Address  as varchar(255))   ENTERADDRESS,
Postalcode     POSTALCODE,
WebURL     WEBSITE,
case when isdate(SuspensionTime)=1 then cast(SuspensionTime  as  date) else null end STOPTIME,
case when isdate(InvestmentTime)=1 then cast(InvestmentTime  as  date)  else null end CREATETIME,
case 
when PATINDEX('%[^0-9|.|-|+]%', Longitude)=0  AND Longitude NOT LIKE '%.%.%'   
and cast(Longitude as decimal(18,6)) <1000
THEN cast(cast(ROUND(Longitude,6) as float) as  decimal(9,6))
ELSE NULL END LONGITUDE ,
case 
when PATINDEX('%[^0-9|.|-|+]%', Latitude)=0  AND Latitude NOT LIKE '%.%.%' 
and cast(Latitude as decimal(18,6)) <1000
THEN cast(cast(ROUND(Latitude,6)as float) as  decimal(9,6))
ELSE NULL END LATITUDE ,
ProductionState     PRODUCTIONSTATE,
cast(IsCancellation as numeric(10))    ENABLESTATE,
'一厂一档'     DATASOURCE,
GETDATE()  UPDATETIME
from 
DBO.PollutionSourceInfo

1、round()函数

round函数用于把数值字段舍入为指定的小数位数，ROUND(A,B) A 代表是要用到的字段 ，B是保留的小数

ROUND(Longitude,6)

2、GETDATE() 函数

返回当前的时间和日期。我们一般在创建表的时候会用到该函数，我们在查询的时候把函数添加到查询字段中可以显示我们查询时间,keittle在数据抽取时添加该字段可以显示进数据的时间

• 查询

SELECT PRIMARYPOLLUTANT, CITYNAME, ISO2_24H, IPM10_24H, CITYCODE,getdate() as "time"
FROM YWAIR.getCityAQI_DAY

image.png

3、isdate()函数

isdate()检查列是否为有效的日期，返回类型int(0,1)，也就是在判断某列的时候。isdate(字段) = 1为时间类型字段

isdate

4、PATINDEX('%[^0-9]%', 列名) 函数、ISNUMERIC()

image.png

• ISNUMERIC(SW) = 1确定表达式是否为一个有效的数字类型

• PATINDEX('%[^0-9]%', 列名)=0,如果返回值等于0，则是纯数字型（没有0~9之外的字符）

5、字符串的拼接

image.png

6、将float转换为数据类型numeric时出现算术溢出错误

我在数据类型转换为decimal(9,6)时也报数据溢出的问题，数据溢出肯定是因为设置的数据格式不满足数据格式。比如我在数据转换的时候虽然设置了round条件只保留了6为小数，但是可能回存在非小数位的数字大于三位数，这时我们可以设置条件然后在进行数据类型的转化。

cast(Longitude as decimal(18,6)) <1000

7、如何只获取年月日

• sqlserver很简单，只需要转换一下数据格式就好啦

cast(InvestmentTime  as  date) 

8、cast()、convert()数据类型的转换

CAST()函数和CONVERT()函数语法：

select cast（表达式 as 数据类型） 
select convert（数据类型[(长度)],表达式[,样式]）

• cast 数据类型转换,cast()函数不能执行四舍五入或截断操作

select cast(score as varchar(10)) from student

• Convert日期处理,一般在sql server 中对日期进行处理都会使用Convert 函数