超實用!做資料分析應該掌握的5個SQL資料清洗方法

日常工作中,分析師經常會接到一些專項分析的需求,首先會搜尋腦中的分析體系,根據業務需求構建相應的分析模型(不只是機器學習模型),根據模型填充相應維度表,這些維度特徵表能夠被使用的前提是假設已經清洗乾淨了。

但真正的原始表是混亂且包含了很多無用的冗餘特徵,所以能夠根據原始資料清洗出相對乾淨的特徵表就很重要。

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

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

基於此,我拓展了部分內容,寫了一個常用資料清洗的SQL對比版,指令碼很簡單,重點是這些清洗場景和邏輯,大綱如圖:

刪除指定行、重新命名行

場景

多數情況並不是底表的所有特徵都對分析有用,這個時候就只需要抽取部分行,對於不用的那些行,可以刪除。

重新命名很難過可以避免有些行的命名過於冗長(比如Case When 語句),且有時候會根據不同的業務指標需求來命名。

刪除行Python版df.drop(col_names, axis=1, inplace=True)刪除行SQL版1、select col_names from Table_Name2、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

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

重複值、缺失值處理

場景

比如某網站今天來了1000個人訪問,但一個人一天中可以訪問多次,那資料庫中會記錄使用者訪問的多條記錄,而這時候如果想要找到今天訪問這個網站的1000個人的ID並根據此做使用者調研,需要去掉重複值給業務方去回訪。

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

重複值處理Python版:
df.drop_duplicates()

重複值處理SQL版1、select distinct col_name from Table_Name2、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_Name2、select coalesce(col_name,col_name_A,0) as value from Table_Name3、select case when col_name is null then 0 else col_name end from Table_Name

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

場景

理解使用者行為的重要一項是去假設使用者的心理,這會用到使用者的反饋意見或一些用研的文字資料,這些文字資料一般會以字串的形式儲存在資料庫中,但使用者反饋的這些文字一般都會很亂,所以需要從這些髒亂的字串中提取有用資訊,就會需要用到文字串處理函式。

字串處理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,正則表示式) from Table_name
## 3、字串分割
select split(col_name,’分割符’) from Table_name
## 4、字串拼接
select concat_ws(col_name,’拼接符’) from Table_name

合併處理

場景

有時候你需要的特徵儲存在不同的表裡,為便於清洗理解和操作,需要按照某些欄位對這些表的資料進行合併組合成一張新的表,這樣就會用到連線等方法。

合併處理Python版

左右合併

上下合併
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:對兩個結果集進行並集操作,不包括重複行,同時進行預設規則的排序;
## Union All:對兩個結果集進行並集操作,包括重複行,不進行排序;
select A.* from Table_a A
union
elect B.* from Table_b B
# Union 因為會將各查詢子集的記錄做比較,故比起Union All ,通常速度都會慢上許多。一般來說,如果使用Union All能滿足要求的話,務必使用Union All。

視窗函式的分組排序

場景

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

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

視窗分組Python版

視窗分組SQL版

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

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

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

文源:數據管道 作者:寶器

關注數據君的臉書:

我是「數據分析那些事」。常年分享數據分析乾貨,不定期分享好用的職場技能工具。按贊我的臉書私訊「10」,會有豐富資料包贈送唷!

這是一個專注於數據分析職場的內容部落格,聚焦一批數據分析愛好者,在這裡,我會分享數據分析相關知識點推送、(工具/書籍)等推薦、職場心得、熱點資訊剖析以及資源大盤點,希望同樣熱愛數據的我們一同進步! 臉書會有更多互動喔:https://www.facebook.com/shujvfenxi/