常用数据清洗的SQL对比版
日常工作中，分析师会接到一些专项分析的需求，首先会搜索脑中的分析体悉，根据业务需求构建相应的分析模型（不只是机器学习模型），根据模型填充相应维度表，这些维度特征表能够被使用的前提是假设已经清洗干净了。
但真正的原始表是混乱且包含了很多无用的冗余特征，所以能够根据原始数据清洗出相对干净的特征表就很重要。
前两天在towards data science上看到一篇文章，讲的是用pandas做数据清洗，作者将常用的清洗逻辑封装成了一个个的清洗函数。
而公司的业务数据一般存储在数据仓库里面，数据量很大，这时候用pandas处理是不大方便的，更多时候用的是hivesql和mysql做处理。
基于此，我拓展了部分内容，写了一个常用数据清洗的sql对比版，脚本很简单，重点是这些清洗场景和逻辑，大纲如图：
01 删除指定列、重命名列
场景：
多数情况并不是底表的所有特征（列）都对分析有用，这个时候就只需要抽取部分列，对于不用的那些列，可以删除。
重命名列可以避免有些列的命名过于冗长（比如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）。
02 重复值、缺失值处理
场景：比如某网站今天来了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
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、空格处理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
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版：左右合并select a.*,b.* from table_a a join table_b b on a.id = b.idselect a.* from table_a a left join table_b b on a.id = b.id上下合并## union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；## union all：对两个结果集进行并集操作，包括重复行，不进行排序；select a.* from table_a a unionselect b.* from table_b b # union 因为会将各查询子集的记录做比较，故比起union all ，通常速度都会慢上许多。一般来说，如果使用union all能满足要求的话，务必使用union all。 05、窗口函数的分组排序
场景：假如现在你是某宝的分析师，要分析今年不同店的不同品类销售量情况，需要找到那些销量较好的品类，并在第二年中加大曝光，这个时候你就需要将不同店里不同品类进行分组，并且按销量进行排序，以便查找到每家店销售较好的品类。
demo数据如上，一共a,b,c三家店铺，卖了不同品类商品，销量对应如上，要找到每家店卖的最多的商品。窗口分组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份宝器狗。
总结，上面的内容核心是掌握这些数据清洗的应用场景，这些场景几乎可以涵盖90%的数据分析前数据清洗的内容。而对于分析模型来说，sql和python都是工具，如果熟悉sql，是能够更快速、方便的将特征清洗用sql实现。
所以，请别张口闭口数据科学，你竟sql都不会。
基于AT90S2313单片机和ATF1500A芯片实现自动图像报警系统的设计
云计算能否解决归档存储的问题
怎样为台灯添加一个USB端口
为什么区块链行业的增长已经容不下原教旨主义了
Apollo“ACE交通引擎”推动着技术强国、交通强国向前发展
常用数据清洗的SQL对比版
低成本高性能2.4GHz无线收发芯片的性能详解
紫光虎贲T710芯片领先骁龙855 Plus，暂居世界第一
如何基于分布式存储来构建大数据时代的云服务
宇凡微2.4g芯片合封单片机,高集成更具性价比
世界最大3D打印建筑完成 总建筑面积为640平方米
LED芯片的发光原理与分类
华为花粉年会：荣耀Magic华为总销量还有高管能否再现超人装
iPhoneX定价太高，或推出廉价版，配置依旧只要5000了，你买吗？
V23行云设计：崭新视界，全面升级！
机器人及核心部件国产化正在加速到来
登临科技加入飞桨硬件生态共创计划
酷开电视Max 86" 巨幕出击潮玩到底
MAX8532低噪声、低压差、200mA线性稳压器,UCSP封装
一文详解匹配网络拓扑设计
但真正的原始表是混乱且包含了很多无用的冗余特征，所以能够根据原始数据清洗出相对干净的特征表就很重要。
前两天在towards data science上看到一篇文章，讲的是用pandas做数据清洗，作者将常用的清洗逻辑封装成了一个个的清洗函数。
而公司的业务数据一般存储在数据仓库里面，数据量很大，这时候用pandas处理是不大方便的，更多时候用的是hivesql和mysql做处理。
基于此，我拓展了部分内容，写了一个常用数据清洗的sql对比版，脚本很简单，重点是这些清洗场景和逻辑，大纲如图：
01 删除指定列、重命名列
场景：
多数情况并不是底表的所有特征（列）都对分析有用，这个时候就只需要抽取部分列，对于不用的那些列，可以删除。
重命名列可以避免有些列的命名过于冗长（比如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）。
02 重复值、缺失值处理
场景：比如某网站今天来了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
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、空格处理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
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版：左右合并select a.*,b.* from table_a a join table_b b on a.id = b.idselect a.* from table_a a left join table_b b on a.id = b.id上下合并## union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；## union all：对两个结果集进行并集操作，包括重复行，不进行排序；select a.* from table_a a unionselect b.* from table_b b # union 因为会将各查询子集的记录做比较，故比起union all ，通常速度都会慢上许多。一般来说，如果使用union all能满足要求的话，务必使用union all。 05、窗口函数的分组排序
场景：假如现在你是某宝的分析师，要分析今年不同店的不同品类销售量情况，需要找到那些销量较好的品类，并在第二年中加大曝光，这个时候你就需要将不同店里不同品类进行分组，并且按销量进行排序，以便查找到每家店销售较好的品类。
demo数据如上，一共a,b,c三家店铺，卖了不同品类商品，销量对应如上，要找到每家店卖的最多的商品。窗口分组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份宝器狗。
总结，上面的内容核心是掌握这些数据清洗的应用场景，这些场景几乎可以涵盖90%的数据分析前数据清洗的内容。而对于分析模型来说，sql和python都是工具，如果熟悉sql，是能够更快速、方便的将特征清洗用sql实现。
所以，请别张口闭口数据科学，你竟sql都不会。
基于AT90S2313单片机和ATF1500A芯片实现自动图像报警系统的设计
云计算能否解决归档存储的问题
怎样为台灯添加一个USB端口
为什么区块链行业的增长已经容不下原教旨主义了
Apollo“ACE交通引擎”推动着技术强国、交通强国向前发展
常用数据清洗的SQL对比版
低成本高性能2.4GHz无线收发芯片的性能详解
紫光虎贲T710芯片领先骁龙855 Plus，暂居世界第一
如何基于分布式存储来构建大数据时代的云服务
宇凡微2.4g芯片合封单片机,高集成更具性价比
世界最大3D打印建筑完成 总建筑面积为640平方米
LED芯片的发光原理与分类
华为花粉年会：荣耀Magic华为总销量还有高管能否再现超人装
iPhoneX定价太高，或推出廉价版，配置依旧只要5000了，你买吗？
V23行云设计：崭新视界，全面升级！
机器人及核心部件国产化正在加速到来
登临科技加入飞桨硬件生态共创计划
酷开电视Max 86" 巨幕出击潮玩到底
MAX8532低噪声、低压差、200mA线性稳压器,UCSP封装
一文详解匹配网络拓扑设计