SQL的数据清洗方法有哪些
这篇文章主要讲解了“SQL的数据清洗方法有哪些”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“SQL的数据清洗方法有哪些”吧!
大纲如图:
01 删除指定列、重命名列
场景:
多数情况并不是底表的所有特征(列)都对分析有用,这个时候就只需要抽取部分列,对于不用的那些列,可以删除。
重命名列可以避免有些列的命名过于冗长(比如Case When 语句),且有时候会根据不同的业务指标需求来命名。
删除列Python版:
df.drop(col_names,axis=1,inplace=True)
删除列SQL版:
selectcol_namesfromTable_NamealtertabletableNamedropcolumncolumnName
重命名列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版:
selectdistinctcol_namefromTable_Nameselectcol_namefromTable_Namegroupbycol_name
缺失值处理Python版:
df.fillna(value=0)df1.combine_first(df2)
缺失值处理SQL版:
selectifnull(col_name,0)valuefromTable_Nameselectcoalesce(col_name,col_name_A,0)asvaluefromTable_Nameselectcasewhencol_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版:
左右合并
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版:
左右合并
selectA.*,B.*fromTable_aAjoinTable_bBonA.id=B.idselectA.*fromTable_aAleftjoinTable_bBonA.id=B.id
上下合并
##Union:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序;##UnionAll:对两个结果集进行并集操作,包括重复行,不进行排序;selectA.*fromTable_aAunionselectB.*fromTable_bB#Union因为会将各查询子集的记录做比较,故比起UnionAll,通常速度都会慢上许多。一般来说,如果使用UnionAll能满足要求的话,务必使用UnionAll。
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)rkfromtable_name)bwhereb.rk=1
可以很清晰的看到,a店铺卖的最火的是蔬菜,c店铺卖的最火的是鸡肉,b店铺?
嗯,b店铺很不错,卖了888份宝器狗。
感谢各位的阅读,以上就是“SQL的数据清洗方法有哪些”的内容了,经过本文的学习后,相信大家对SQL的数据清洗方法有哪些这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是亿速云,小编将为大家推送更多相关知识点的文章,欢迎关注!
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。