日常工作中，分析師會接到一些專項分析的需求，首先會搜索腦中的分析體悉，根據業務需求構建相應的分析模型（不只是機器學習模型），根據模型填充相應維度表，這些維度特征表能夠被使用的前提是假設已經清洗干凈了。

但真正的原始表是混亂且包含了很多無用的冗余特征，所以能夠根據原始數據清洗出相對干凈的特征表就很重要。

前兩天在Towards Data Science上看到一篇文章，講的是用Pandas做數據清洗，作者將常用的清洗邏輯封裝成了一個個的清洗函數。

而公司的業務數據一般存儲在數據倉庫里面，數據量很大，這時候用Pandas處理是不大方便的，更多時候用的是HiveSQL和MySql做處理。

基于此，我拓展了部分內容，寫了一個常用數據清洗的SQL對比版，腳本很簡單，重點是這些清洗場景和邏輯，大綱如圖：

01 刪除指定列、重命名列

場景：

多數情況并不是底表的所有特征（列）都對分析有用，這個時候就只需要抽取部分列，對于不用的那些列，可以刪除。

重命名列可以避免有些列的命名過于冗長（比如Case When 語句），且有時候會根據不同的業務指標需求來命名。

刪除列Python版：
df.drop(col_names,axis=1,inplace=True)

刪除列SQL版：
1、selectcol_namesfromTable_Name

2、altertabletableNamedropcolumncolumnName

重命名列Python版：
df.rename(index={'row1':'A'},columns={'col1':'B'})

重命名列SQL版：
selectcol_namesascol_name_BfromTable_Name

因為一般情況下是沒有刪除的權限（可以構建臨時表），反向思考，刪除的另一個邏輯是選定指定列（Select）。

02 重復值、缺失值處理

場景：比如某網站今天來了1000個人訪問，但一個人一天中可以訪問多次，那數據庫中會記錄用戶訪問的多條記錄，而這時候如果想要找到今天訪問這個網站的1000個人的ID并根據此做用戶調研，需要去掉重復值給業務方去回訪。

缺失值：NULL做運算邏輯時，返回的結果還是NULL，這可能就會出現一些腳本運行正確，但結果不對的BUG，此時需要將NULL值填充為指定值。

重復值處理Python版：
df.drop_duplicates()

重復值處理SQL版：
1、selectdistinctcol_namefromTable_Name

2、selectcol_namefromTable_Namegroupbycol_name

缺失值處理Python版：
df.fillna(value=0)

df1.combine_first(df2)

缺失值處理SQL版：
1、selectifnull(col_name,0)valuefromTable_Name

2、selectcoalesce(col_name,col_name_A,0)asvaluefromTable_Name

3、selectcasewhencol_nameisnullthen0elsecol_nameendfromTable_Name

03 替換字符串空格、清洗*%@等垃圾字符、字符串拼接、分隔等字符串處理

場景：理解用戶行為的重要一項是去假設用戶的心理，這會用到用戶的反饋意見或一些用研的文本數據，這些文本數據一般會以字符串的形式存儲在數據庫中，但用戶反饋的這些文本一般都會很亂，所以需要從這些臟亂的字符串中提取有用信息，就會需要用到文字符串處理函數。

字符串處理Python版：
##1、空格處理
df[col_name]=df[col_name].str.lstrip()

##2、*%d等垃圾符處理
df[col_name].replace('&#.*','',regex=True,inplace=True)

##3、字符串分割
df[col_name].str.split('分割符')

##4、字符串拼接
df[col_name].str.cat()

字符串處理SQL版：
##1、空格處理
selectltrim(col_name)fromTable_name

##2、*%d等垃圾符處理
selectregexp_replace(col_name,正則表達式)fromTable_name

##3、字符串分割
selectsplit(col_name,'分割符')fromTable_name

##4、字符串拼接
selectconcat_ws(col_name,'拼接符')fromTable_name

04 合并處理

場景：有時候你需要的特征存儲在不同的表里，為便于清洗理解和操作，需要按照某些字段對這些表的數據進行合并組合成一張新的表，這樣就會用到連接等方法。

合并處理Python版：

左右合并
1、pd.merge(left,right,how='inner',on=None,left_on=None,right_on=None,
left_index=False,right_index=False,sort=True,
suffixes=('_x','_y'),copy=True,indicator=False,
validate=None)
2、pd.concat([df1,df2])

上下合并
df1.append(df2,ignore_index=True,sort=False)

合并處理SQL版：

左右合并
selectA.*,B.*fromTable_aAjoinTable_bBonA.id=B.id

selectA.*fromTable_aAleftjoinTable_bBonA.id=B.id

上下合并
## Union：對兩個結果集進行并集操作，不包括重復行，同時進行默認規則的排序；
## Union All：對兩個結果集進行并集操作，包括重復行，不進行排序；

selectA.*fromTable_aA
union
selectB.*fromTable_bB

# Union 因為會將各查詢子集的記錄做比較，故比起Union All ，通常速度都會慢上許多。一般來說，如果使用Union All能滿足要求的話，務必使用Union All。

05、窗口函數的分組排序

場景：假如現在你是某寶的分析師，要分析今年不同店的不同品類銷售量情況，需要找到那些銷量較好的品類，并在第二年中加大曝光，這個時候你就需要將不同店里不同品類進行分組，并且按銷量進行排序，以便查找到每家店銷售較好的品類。



Demo數據如上，一共a,b,c三家店鋪，賣了不同品類商品，銷量對應如上，要找到每家店賣的最多的商品。

窗口分組Python版：

df['Rank']=df.groupby(by=['Sale_store'])['Sale_Num'].transform(lambdax:x.rank(ascending=False))

窗口分組SQL版：

select
*
from
(
Select
*，
row_number()over(partitionbySale_storeorderbySale_Numdesc)rk
from
table_name
)bwhereb.rk=1

可以很清晰的看到，a店鋪賣的最火的是蔬菜，c店鋪賣的最火的是雞肉，b店鋪？

嗯，b店鋪很不錯，賣了888份寶器狗。

總結，上面的內容核心是掌握這些數據清洗的應用場景，這些場景幾乎可以涵蓋90%的數據分析前數據清洗的內容。而對于分析模型來說，SQL和Python都是工具，如果熟悉SQL，是能夠更快速、方便的將特征清洗用SQL實現。

所以，請別張口閉口數據科學，你竟SQL都不會。

審核編輯：劉清