一、首先理解下面幾個函數

設置變量 length()函數 char_length() replace() 函數 max() 函數

1.1、設置變量 set @變量名=值

set @address='中國-山東省-聊城市-莘縣';select @address

1.2 、length()函數 char_length()函數區別

select length('a'),char_length('a'),length('中'),char_length('中')

1.3、 replace() 函數 和length()函數組合

set @address='中國-山東省-聊城市-莘縣';select @address,replace(@address,'-','') as address_1,length(@address) as len_add1,length(replace(@address,'-','')) as len_add2,length(@address)-length(replace(@address,'-','')) as _count

etl清洗字段時候有明顯分割符的如何確定新的數據表增加幾個分割出的字段

計算出com_industry中最多有幾個 - 符 以便確定增加幾個字段 最大值+1 為可以拆分成的字段數 此表為3 因此可以拆分出4個行業字段 也就是4個行業等級

select max(length(com_industry)-length(replace(com_industry,'-',''))) as _max_countfrom etl1_socom_data

1.4、設置變量 substring_index()字符串截取函數用法

set @address='中國-山東省-聊城市-莘縣';select substring_index(@address,'-',1) as china,substring_index(substring_index(@address,'-',2),'-',-1) as province,substring_index(substring_index(@address,'-',3),'-',-1) as city,substring_index(@address,'-',-1) as district

1.5、條件判斷函數 case when

case when then when then else 值 end as 字段名select case when 89>101 then '大于' else '小于' end as betl1_socom_data

二、kettle轉換etl1清洗

首先建表 步驟在視頻里

字段索引 沒有提 索引算法建議用BTREE算法增強查詢效率

2.1.kettle文件名：trans_etl1_socom_data

2.2.包括控件：表輸入>>>表輸出

2.3.數據流方向：s_socom_data>>>>etl1_socom_data

kettle轉換1截圖

2.4、表輸入2.4、SQL腳本 初步清洗com_district和com_industry字段

select a.*,case when com_district like '%業' or com_district like '%織' or com_district like '%育' then null else com_district end as com_district1,case when com_district like '%業' or com_district like '%織' or com_district like '%育' then concat(com_district,'-',com_industry) else com_industry end as com_industry_total,replace(com_addr,'地 址：','') as com_addr1,replace(com_phone,'電 話：','') as com_phone1,replace(com_fax,'傳 真：','') as com_fax1,replace(com_mobile,'手機：','') as com_mobile1,replace(com_url,'網址：','') as com_url1,replace(com_email,'郵箱：','') as com_email1,replace(com_contactor,'聯系人：','') as com_contactor1,replace(com_emploies_nums,'公司人數：','') as com_emploies_nums1,replace(com_reg_capital,'注冊資金：萬','') as com_reg_capital1,replace(com_type,'經濟類型：','') as com_type1,replace(com_product,'公司產品：','') as com_product1,replace(com_desc,'公司簡介：','') as com_desc1from s_socom_data as a