【數(shù)據(jù)分析】5大SQL數(shù)據(jù)清洗方法！
日常工作中，分析師會接到一些專項(xiàng)分析的需求，首先會搜索腦中的分析體悉，根據(jù)業(yè)務(wù)需求構(gòu)建相應(yīng)的分析模型（不只是機(jī)器學(xué)習(xí)模型），根據(jù)模型填充相應(yīng)維度表，這些維度特征表能夠被使用的前提是假設(shè)已經(jīng)清洗干凈了。
但真正的原始表是混亂且包含了很多無用的冗余特征，所以能夠根據(jù)原始數(shù)據(jù)清洗出相對干凈的特征表就很重要。
前兩天在Towards Data Science上看到一篇文章，講的是用Pandas做數(shù)據(jù)清洗，作者將常用的清洗邏輯封裝成了一個(gè)個(gè)的清洗函數(shù)。
https://towardsdatascience.com/the-simple-yet-practical-data-cleaning-codes-ad27c4ce0a38
而公司的業(yè)務(wù)數(shù)據(jù)一般存儲在數(shù)據(jù)倉庫里面，數(shù)據(jù)量很大，這時(shí)候用Pandas處理是不大方便的，更多時(shí)候用的是HiveSQL和MySql做處理。
基于此，我拓展了部分內(nèi)容，寫了一個(gè)常用數(shù)據(jù)清洗的SQL對比版，腳本很簡單，重點(diǎn)是這些清洗場景和邏輯，大綱如圖：
01 刪除指定列、重命名列
場景：
多數(shù)情況并不是底表的所有特征（列）都對分析有用，這個(gè)時(shí)候就只需要抽取部分列，對于不用的那些列，可以刪除。
重命名列可以避免有些列的命名過于冗長（比如Case When 語句），且有時(shí)候會根據(jù)不同的業(yè)務(wù)指標(biāo)需求來命名。
刪除列Python版：
df.drop(col_names,?axis=1,?inplace=True)
刪除列SQL版：
1、select?col_names?from?Table_Name
2、alter?table?tableName?drop?column?columnName
重命名列Python版：
df.rename(index={'row1':'A'},columns?={'col1':'B'})
重命名列SQL版：
select?col_names?as?col_name_B?from?Table_Name
因?yàn)橐话闱闆r下是沒有刪除的權(quán)限（可以構(gòu)建臨時(shí)表），反向思考，刪除的另一個(gè)邏輯是選定指定列（Select）。
02 重復(fù)值、缺失值處理
場景：比如某網(wǎng)站今天來了1000個(gè)人訪問，但一個(gè)人一天中可以訪問多次，那數(shù)據(jù)庫中會記錄用戶訪問的多條記錄，而這時(shí)候如果想要找到今天訪問這個(gè)網(wǎng)站的1000個(gè)人的ID并根據(jù)此做用戶調(diào)研，需要去掉重復(fù)值給業(yè)務(wù)方去回訪。
缺失值：NULL做運(yùn)算邏輯時(shí)，返回的結(jié)果還是NULL，這可能就會出現(xiàn)一些腳本運(yùn)行正確，但結(jié)果不對的BUG，此時(shí)需要將NULL值填充為指定值。
重復(fù)值處理Python版：
df.drop_duplicates()
重復(fù)值處理SQL版：
1、select?distinct?col_name?from?Table_Name
2、select?col_name?from?Table_Name?group?bycol_name
缺失值處理Python版：
df.fillna(value?=?0)
df1.combine_first(df2)
缺失值處理SQL版：
1、select?ifnull(col_name,0)?value?from?Table_Name
2、select?coalesce(col_name,col_name_A,0)?as?value?from?Table_Name
3、select?case?when?col_name?is?null?then?0?else?col_name?end?from?Table_Name
03 替換字符串空格、清洗*%@等垃圾字符、字符串拼接、分隔等字符串處理
場景：理解用戶行為的重要一項(xiàng)是去假設(shè)用戶的心理，這會用到用戶的反饋意見或一些用研的文本數(shù)據(jù)，這些文本數(shù)據(jù)一般會以字符串的形式存儲在數(shù)據(jù)庫中，但用戶反饋的這些文本一般都會很亂，所以需要從這些臟亂的字符串中提取有用信息，就會需要用到文字符串處理函數(shù)。
字符串處理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、空格處理
select?ltrim(col_name)?from?Table_name?
##?2、*%d等垃圾符處理
select?regexp_replace(col_name,正則表達(dá)式)?from?Table_name?
##?3、字符串分割
select?split(col_name,'分割符')?from?Table_name?
##?4、字符串拼接
select?concat_ws(col_name,'拼接符')?from?Table_name?
04? 合并處理
場景：有時(shí)候你需要的特征存儲在不同的表里，為便于清洗理解和操作，需要按照某些字段對這些表的數(shù)據(jù)進(jìn)行合并組合成一張新的表，這樣就會用到連接等方法。
合并處理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版：
左右合并
select?A.*,B.*?from?Table_a?A?join?Table_b?B?on?A.id?=?B.id
select?A.*?from?Table_a?A?left?join?Table_b?B?on?A.id?=?B.id
上下合并
## Union：對兩個(gè)結(jié)果集進(jìn)行并集操作，不包括重復(fù)行，同時(shí)進(jìn)行默認(rèn)規(guī)則的排序；
## Union All：對兩個(gè)結(jié)果集進(jìn)行并集操作，包括重復(fù)行，不進(jìn)行排序；
select?A.*?from?Table_a?A?
union
select?B.*?from?Table_b?B?
# Union 因?yàn)闀⒏鞑樵冏蛹挠涗涀霰容^，故比起Union All ，通常速度都會慢上許多。一般來說，如果使用Union All能滿足要求的話，務(wù)必使用Union All。?
窗口分組Python版：
df['Rank']?=?df.groupby(by=['Sale_store'])['Sale_Num'].transform(lambda?x:?x.rank(ascending=False))
窗口分組SQL版：
select?
??*?
from
??(
??Select?
????*，
????row_number()?over(partition?by?Sale_store?order?by?Sale_Num?desc)?rk
??from?
????table_name
??)?b?where?b.rk?=?1
可以很清晰的看到，a店鋪賣的最火的是蔬菜，c店鋪賣的最火的是雞肉，b店鋪？
嗯，b店鋪很不錯，賣了888份寶器狗。
總結(jié)，上面的內(nèi)容核心是掌握這些數(shù)據(jù)清洗的應(yīng)用場景，這些場景幾乎可以涵蓋90%的數(shù)據(jù)分析前數(shù)據(jù)清洗的內(nèi)容。而對于分析模型來說，SQL和Python都是工具，如果熟悉SQL，是能夠更快速、方便的將特征清洗用SQL實(shí)現(xiàn)。
所以，請別張口閉口數(shù)據(jù)科學(xué)，你竟SQL都不會。
往期精彩回顧
獲取一折本站知識星球優(yōu)惠券，復(fù)制鏈接直接打開：
https://t.zsxq.com/y7uvZF6
本站qq群704220115。
加入微信群請掃碼：