SQL的數(shù)據(jù)清洗方法有哪些

這篇文章主要講解了“SQL的數(shù)據(jù)清洗方法有哪些”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“SQL的數(shù)據(jù)清洗方法有哪些”吧！

大綱如圖：

01 刪除指定列、重命名列

場景：

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

重命名列可以避免有些列的命名過于冗長(比如Case When 語句)，且有時候會根據(jù)不同的業(yè)務(wù)指標(biāo)需求來命名。

刪除列Python版：

df.drop(col_names, axis=1, inplace=True)

刪除列SQL版：

select col_names from Table_Name 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)建臨時表)，反向思考，刪除的另一個邏輯是選定指定列(Select)。

02 重復(fù)值、缺失值處理

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

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

重復(fù)值處理Python版：

df.drop_duplicates()

重復(fù)值處理SQL版：

select distinct col_name from Table_Name select col_name from Table_Name group bycol_name

缺失值處理Python版：

df.fillna(value = 0)df1.combine_first(df2)

缺失值處理SQL版：

select ifnull(col_name,0) value from Table_Name  select coalesce(col_name,col_name_A,0) as value from Table_Name  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ù)據(jù)進(jìn)行合并組合成一張新的表，這樣就會用到連接等方法。

合并處理Python版：

左右合并

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)  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：對兩個結(jié)果集進(jìn)行并集操作，不包括重復(fù)行，同時進(jìn)行默認(rèn)規(guī)則的排序;  ##Union All：對兩個結(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。

05、窗口函數(shù)的分組排序

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

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

窗口分組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份寶器狗。

感謝各位的閱讀，以上就是“SQL的數(shù)據(jù)清洗方法有哪些”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對SQL的數(shù)據(jù)清洗方法有哪些這一問題有了更深刻的體會，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識點(diǎn)的文章，歡迎關(guān)注！