關(guān)于數(shù)倉(cāng)建模中的空值處理——以阿里云Dataworks語(yǔ)法為例

篇首語(yǔ)

大家好，我是唐三少，今天給大家?guī)頍嵝淖x者的投稿，歡迎大家品讀。廢話不多說，再次上號(hào)。

1 數(shù)據(jù)準(zhǔn)備

在日常建模過程中，我們會(huì)遇到不同的空值的情形，在不同的情況下需要有不同的處理方式。

首先我們先創(chuàng)建兩張表，分別是學(xué)生表和班級(jí)表

create table yht_dw_dev.t_student_info(

  id string COMMENT '學(xué)號(hào)',

  stu_name string COMMENT '學(xué)生姓名',

  class_id string COMMENT '班級(jí)id'

)TBLPROPERTIES('comment'='學(xué)生表');

create table yht_dw_dev.t_class_info(

  id string COMMENT '班級(jí)id',

  class_name string COMMENT '班級(jí)'

)TBLPROPERTIES('comment'='班級(jí)表');

然后我們構(gòu)造數(shù)據(jù)插入表中：

insert overwrite table yht_dw_dev.t_student_info
select
    *
from(
    select 's001' as id,'高啟 強(qiáng)' as stu_name,'c001' as class_id union all
    select 's002' as id,'安欣 ' as stu_name,'c003' as class_id union all
    select 's003' as id,' 孟鈺' as stu_name,'c002' as class_id union all
    select 's004' as id,'孟德海 ' as stu_name,'c005' as class_id union all
    select 's005' as id,'徐忠\n' as stu_name,'c001' as class_id union all
    select 's006' as id,'李\r響' as stu_name,'c002' as class_id union all
    select 's007' as id,'陳\t書婷' as stu_name,'c002' as class_id union all
    select 's008' as id,'楊  健' as stu_name,'c001' as class_id union all
    select 's009' as id,'吳剛\r' as stu_name,'c003' as class_id union all
    select 's010' as id,'\r張志\n\n堅(jiān)' as stu_name,'c004' as class_id union all
    select 's011' as id,'李健\t' as stu_name,'c003' as class_id union all
    select 's012' as id,'\t高）葉' as stu_name,'c004' as class_id union all
    select 's013' as id,'張\n頌（文 \t' as stu_name,'c005' as class_id union all
    select 's014' as id,'\n張\r\r譯' as stu_name,'c001' as class_id

) t1 ;


insert overwrite table yht_dw_dev.t_class_info
select
    id,
    class_name
from
(
    select 'c001' as id,'賣魚班' as class_name union all
    select 'c002' as id,'調(diào)查組班' as class_name union all
    select 'c003' as id,'狡猾班' as class_name union all
    select 'c004' as id,'演員班' as class_name

)tt
2 Join后得到的空值的處理方式
上文中t1和t2兩表關(guān)聯(lián)后，如下：

select
    t1.id as stu_id,
    stu_name,
    t1.class_id,
    t2.class_name
from yht_dw_dev.t_student_info t1
left join yht_dw_dev.t_class_info t2
on t1.class_id=t2.id
關(guān)聯(lián)后結(jié)果如下，我們發(fā)現(xiàn)在class_name列存在空值。



在某些實(shí)際建模過程中可能需要對(duì)空值進(jìn)行處理，我們可以用nvl() 或者coalesce() 函數(shù)進(jìn)行處理。

select
    t1.id as stu_id,
    stu_name,
    t1.class_id,
    t2.class_name,
    nvl(t2.class_name,'-') as nvl_class_name,
    coalesce(t2.class_name,'-') as coal_class_name
from yht_dw_dev.t_student_info t1
left join yht_dw_dev.t_class_info t2
on t1.class_id=t2.id
處理后的結(jié)果，如下：


3 值中存在空值處理方式
3.1 單元格存在空格——開頭或者結(jié)尾
Dataworks提供了三個(gè)函數(shù)針對(duì)這樣的情景：trim，rtrim和ltrim，我們來看最后代碼和結(jié)果：

select
    t1.id as stu_id,
    stu_name,
    trim(stu_name) as trim_stu_name,
    case when length(stu_name)>length(trim(stu_name)) then '空格剔除' else '空格未剔除' end as is_trim,
    -- trim函數(shù)是否有效
    rtrim(stu_name) as rtrim_stu_name,
    case when length(stu_name)>length(rtrim(stu_name)) then '空格剔除' else '空格未剔除' end as is_rtrim,
    -- rtrim函數(shù)是否有效
    ltrim(stu_name) as ltrim_stu_name,
    case when length(stu_name)>length(ltrim(stu_name)) then '空格剔除' else '空格未剔除' end as is_ltrim
    -- ltrim函數(shù)是否有效
from yht_dw_dev.t_student_info t1
where regexp_count(stu_name,'\\s')>=1
-- 篩選stu_name字符串中含有空白字符的項(xiàng)
*需要注意的一點(diǎn)是 \s 指空格，換行，tab縮進(jìn)等空白字符，時(shí)間長(zhǎng)了，大家很容易誤認(rèn)為其僅代表空格 ，下面的例子很容易說明這個(gè)問題。


根據(jù)上述的結(jié)果中，我們發(fā)現(xiàn)，trim是可以將字符串開始和字符串結(jié)尾的連續(xù)空格去除（比如上圖數(shù)字1部分），對(duì)\n 、\r 、\t 等空白無法做剔除，對(duì)字符串內(nèi)部空格不起作用；rtrim是可以將字符串結(jié)尾的連續(xù)空格去除（比如上圖數(shù)字2部分），對(duì)\n 、\r 、\t 等空白無法做剔除，對(duì)字符串內(nèi)部空格不起作用；ltrim是可以將字符串開始的連續(xù)空格去除（比如上圖數(shù)字3部分），對(duì)\n 、\r 、\t 等空白無法做剔除，對(duì)字符串內(nèi)部空格不起作用。
當(dāng)然了，此處你也可以用replace 函數(shù)，不過前提是你需要知道字符串中空白字符具體是什么，并且不支持多種空白字符同時(shí)使用或者正則表達(dá)式的使用，除非你愿意嵌套，這是可以的。

綜上所述，不論是trim，rtrim和ltrim三個(gè)函數(shù)，是存在局限性的。那問題就來了，對(duì)于非空格的其他空白字符怎么處理呢？對(duì)于字符串內(nèi)部的空白字符怎么處理呢？對(duì)于同時(shí)有多種空白字符怎么處理呢？






3.2 單元格字符串空格通用處理
此處，我們引入需要使用 正則表達(dá)式 的函數(shù) regexp_replace(待處理的值,匹配值,替換的值) ，腳本和結(jié)果如下：

select
    t1.id as stu_id,
    stu_name,
    regexp_replace(stu_name,'\\s+','') as trim_stu_name,
    case when length(stu_name)>length(regexp_replace(stu_name,'\\s+','')) then '空格剔除' else '空格未剔除' end as is_trim
from yht_dw_dev.t_student_info t1
where regexp_count(stu_name,'\\s')>=1

從上圖中我們可以看到結(jié)果，對(duì)于字符串中的開始，結(jié)果或者中間存在的空白字符（空格，\n，\r，\t)，都被剔除了。

簡(jiǎn)單說明一下其中的正則參數(shù)：'\s'  表示空白字符，雙斜杠 '\\' 是表示對(duì)斜杠的轉(zhuǎn)義，+表示前述一次或者多次，這樣既能匹配單個(gè)空白字符也能匹配連續(xù)多個(gè)空白字符。具體想了解正則表達(dá)式的寫法，可以參考菜鳥學(xué)院 正則表達(dá)式教程 。

如果同時(shí)要剔除多種符號(hào)，空白字符，有應(yīng)該如何處理呢？regexp_replace 也提供了同時(shí)替換多種字符串的功能，可以用“|”隔開不同匹配模式，如下：

select
    t1.id as stu_id,
    stu_name,
    regexp_replace(stu_name,'\\s+|\）|\（','') as trim_stu_name,
    case when length(stu_name)>length(regexp_replace(stu_name,'\\s+','')) then '空格剔除' else '空格未剔除' end as is_trim
from yht_dw_dev.t_student_info t1
where regexp_count(stu_name,'\\s')>=1

同時(shí)剔除多種模式
按照上述模式，是不是就能處理所有的空白字符以及指定字符呢？我們來看例子：

select
 id as stu_id,
 stu_name,
 regexp_replace(stu_name,'\\s+|\）|\（','') as trim_stu_name,
    case when length(stu_name)>length(regexp_replace(stu_name,'\\s+','')) then '空格剔除' else '空格未剔除'
from yht_dw_dev.t_student_info
where stu_name rlike '孟德海'

結(jié)果如下：
stu_id stu_name replace_stu_name is_replace
s004   孟德海   孟德海           空格未剔除
我們發(fā)現(xiàn)上面的刪除模式也失敗了，并沒有被剔除，這種應(yīng)該如何處理呢？

3.3 單元格字符串特殊空格處理
上述處理方式有兩種，但是第一步都是一樣的：

一、復(fù)制字符串到 在線編碼轉(zhuǎn)換器 網(wǎng)站，查看空白字符的編碼，如下圖：


二、如圖所示，如果 符號(hào)2 中的數(shù)字范圍在0~255之間，我們可以直接將空白符轉(zhuǎn)換為chr(數(shù)字)，這樣替換是沒有問題的；如果符號(hào)2中的數(shù)字范圍大于255，那么可以直接用1中的編碼（如\u00A0)。具體腳本如下：
select
 id as stu_id,
 stu_name,
 regexp_replace(regexp_replace(stu_name,'\\s+|\）|\（',''),chr(160),'') as trim_stu_name,
    case when length(stu_name)>length(regexp_replace(regexp_replace(stu_name,'\\s+|\）|\（',''),chr(160),'')) then '空格剔除' else '空格未剔除'
from yht_dw_dev.t_student_info
where stu_name rlike '孟德海'
結(jié)果如下：


備注1：關(guān)于編碼的替換

但是有一點(diǎn)需要說明：在dataworks上，使用 regexp_replace 函數(shù)使用上文提到的編碼作為匹配模式，是不會(huì)成功的，至少在\u00A0編碼上是替換不成功的，但是有的編碼卻是可以成功的。

如果有讀者了解這里的細(xì)節(jié)，還希望在評(píng)論中進(jìn)行交流。

備注2 ：對(duì)常見空格的編碼的說明

1.不間斷空格\u00A0,主要用在office中,讓一個(gè)單詞在結(jié)尾處不會(huì)換行顯示,快捷鍵ctrl+shift+space ;
2.半角空格(英文符號(hào))\u0020,代碼中常用的;
3.全角空格(中文符號(hào))\u3000,中文文章中使用;
4.水平制表符\t
5.垂直制表符\v
6.換頁(yè)符\f
7.不間斷空白符\xa0
8.空格&nbsp，HTML 中的空格表示形式
4 小結(jié)
本文灑灑兩千字，其實(shí)重點(diǎn)要介紹的經(jīng)驗(yàn)就是3.3小節(jié)中的對(duì)于一些特殊字符處理的經(jīng)驗(yàn)。第一，對(duì)于字符串中出現(xiàn)的特殊空白字符可以通過線上轉(zhuǎn)碼網(wǎng)站來進(jìn)行剖析其“廬山真面目”；第二，對(duì)于數(shù)倉(cāng)建模過程中，想要清洗數(shù)據(jù)，對(duì)于正則表達(dá)式的使用是需要引起讀者重視的，尤其是對(duì)于regexp_** 系列函數(shù)的熟練使用，可以有效簡(jiǎn)化語(yǔ)句，提升模型的運(yùn)行效率。

個(gè)人水平有限，僅記錄自身工作中的經(jīng)驗(yàn)，以饗讀者，還望多多指正。




作者： 教你學(xué)懂大數(shù)據(jù)


歡迎關(guān)注微信公眾號(hào) ：教你學(xué)懂大數(shù)據(jù)