????优质资源分享????
| ???? Python实战微信订餐小程序 ???? | 进阶级 | 本课程是python flask+微信小程序的完美结合，从项目搭建到腾讯云部署上线，打造一个全栈订餐系统。 |
| ????Python量化交易实战???? | 入门级 | 手把手带你打造一个易扩展、更安全、效率更高的量化交易系统 |
前几天因工作需要，组长给我安排了一个数据清洗的任务。
任务：把A表的数据洗到B表。
我的第一反应，**什么是「洗」？洗数据是什么？**洗钱我倒是知道。
不过我不能慌啊，于是问了问组长。
我：组长，把A表的数据洗到B表是什么意思？
组长一脸无奈，手捂住脸，恨铁不成钢，然后调整过来，还是很耐心地跟我讲的，大概意思就是我们现在B表需要A表的数据，A表中和B表中字段含义一样，但是值可能不一样，这就需要我们进行处理，在将A表数据搞到B表的过程中，把数据搞正确。
基于我理解能力有限，当时并不是很懂所谓的「洗数据」，而且这个A表的字段也和B表的字段没怎么对上，A的字段明显多于B的字段，某些字段命名也和B不一样，但是表达的意思是一样的，该如何洗？
于是疯狂搜索如何洗数据！
我这里就举个例子来说明，分别给出A表和B表，当然我只列出了一部分字段，现在假设就这么多字段。
A表字段：name,province_id,city_id,area_id,tech_id,crop_id,field_id,create_time,update_time,xxx,yyy,zzz,...
A表的字段是多于B表的，我需要将A表的数据洗到B表，只处理我需要的字段，不需要的就不用理。
A表中有2万多条记录，B表我自己插入的有200多条记录。
给出A表后，还给了个实体模型
public class A {
private String name;
private Long provinceId;
private Long cityId;
private Long areaId;
private Long techId;
private Long cropId;
private Date createTime;
private Date updateTime;
...
}
B表字段：name,province_id,city_id,area_id,mature_id,crop_id,create_time,update_time
还原现场
下面我模拟测试环境中数据库的那两张表，上面是A表，下面的B表。
洗数据思路
我是先找出A表中能和B表对上意思的字段，然后将A表数据全部插入到B表中。
于是，我便找出了如下这些字段：
name,province_id,city_id,area_id,tech_id,crop_id,update_time
之后在B表中对新插入的数据进行处理，即洗数据。
写SQL操作
主要的SQL语句是：
INSERT INTO 目标表(字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM 来源表 WHERE 条件;
于是，便这样操作：
INSERT INTO b(name, province_id, city_id, area_id, mature_id, crop_id, update_time)
SELECT name, province_id, city_id, area_id, tech_id, crop_id, update_time FROM a;
操作是正常的，成功将A中2万多条记录全部插入到了B中。
但是！我漏了一个字段，就是create_time。
于是，想着对这个字段进行更新，将A中这个字段更新到B中。
于是写了一条SQL语句。
UPDATE b(create_time) SET create_time = (SELECT create_time FROM a);
> 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "(create\_time) SET create\_time = (SELECT create\_time FROM a)" at line 1
噢，看来不能这样更新啊！
换种写法：
UPDATE b AS tb, (SELECT create_time FROM a) AS ta SET tb.create_time = ta.create_time;
可以，噩梦开始了！
更新了非常久，看着十几秒的SQL执行到六七百秒还没执行完，心里着实很慌！眼看着数据库可能会崩，我不得不向我的组长求救了！
这时问题出现了，有人数据库连接不上了，可见这严重性，已经影响到其他人的使用了！
于是我组长来帮我处理了，想着kill掉我的navicat，但是kill掉还是没效果，毕竟这个SQL已经在执行了。
我：能不能重启这个MySQL服务？
组长一波连环炮过来了
组长：重启？你知道这个MySQL有多少人在用吗？又不只是我们在用，你重启其他人怎么搞？
我哑口无言，心里非常忐忑，想着闯祸了，GG，就看着他操作。没过多久，经过他的一顿操作，终于解决了这个问题。我的心里的一块悬着的大石终于放下了，还好解决了。组长牛逼，救世主！
我：如何解决的？
组长：将这个事务回滚解决的，你更新的SQL怎么写的？
于是写出了上面写的SQL：
UPDATE b AS tb, (SELECT create_time FROM a) AS ta SET tb.create_time = ta.create_time;
组长：你为什么这样写？不应该把子查询写在SETtb.create_time后面吗？
我：对啊，我一开始就是把这个子查询写在它后面的，但是提示我语法错误，我就换了一种写法。
组长：那你写写你说提示错误的SQL。
于是我又丢出来一个SQL：
UPDATE b SET create_time = (SELECT create_time FROM a);
实际上，这条SQL也是不行的，子查询返回的结果不止一行，而当前SET是更新某一行的。
正确的写法是：
UPDATE b AS tb
SET create_time = (SELECT create_time FROM a AS ta WHERE tb.id = ta.id AND tb.name = ta.name)
最后组长深思，你B表已经有2万多条记录了，A表也有2万多条记录，你这样更新，每一次都需要子查询查出A表的2万多条记录，B也有2万多条记录，这样成笛卡尔积了，你知道什么是笛卡尔积吧？2万×2万=4亿的记录行了，难怪这么久。
让我重新操作，那么现在我会在原先的SQL加上WHERE条件，这样写：
UPDATE b AS tb, (SELECT create_time, name FROM a) AS ta
SET tb.create_time = ta.create_time
WHERE tb.id = ta.id AND tb.name = ta.name;
情况：漏了某一个字段X，需要将A表的这个字段列值更新到B表
条件：A中的id字段的值等于B表中的id字段的值且A中的name字段的值等于B中name字段的值。
条件这样写主要是因为表和表之间的关联关系可能有多个字段，此处只选二个字段，多个依此类推。
操作：
一张表的数据插入到另一张表，可以这样写：
INSERT INTO 目标表(字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM 来源表 WHERE 条件;
批量更新一张表的某个字段到另一张表，那么SQL可以类似这样写：
# 写法一
UPDATE b AS tb
SET create_time = (SELECT create_time FROM a AS ta WHERE tb.id = ta.id AND tb.name = ta.name)
# 写法二
UPDATE b AS tb, (SELECT create_time, name FROM a) AS ta
SET tb.create_time = ta.create_time
WHERE tb.id = ta.id AND tb.name = ta.name;
所谓洗数据：在我的理解中，就是把旧数据，按照新数据的规则把旧数据不正确的值修改正确，同时把这些旧数据插入到新数据中，成为新数据。举个例子，A表中的province_id，值为10代表广东，而B表中的province_id，值为19代表广东，把A表中的数据插入到B表的过程中，把值为10修改为1这样插入的数据才能在B表中正确表示广东，这个过程就是「洗数据」，也可以在插入后再修改，不管过程是怎样，最终能把数据的值修改正确，就是洗数据！
教训：
数据量大的表，少在测试环境操作，要操作尽量保证写的SQL是正确的，能在本地环境操作就现在本地环境操作！能用Java代码进行操作，优先写Java代码操作！
最后的最后
由本人水平所限，难免有错误以及不足之处，屏幕前的靓仔靓女们如有发现，恳请指出！
你轻轻地点了个赞，那将在我的心里世界增添一颗明亮而耀眼的星！
文章为作者独立观点，不代表 股票程序化软件自动交易接口观点
股民评论