编程之外的智慧-Excel在数据处理中的妙用

经验

起因

接到一个修改了一个bug的任务，进行了分析,发现关系表中有重复的数据, 发现原来的表中 没有针对 2个 字段 进行唯一索引,导致出现了这个问题.于是 本着 为了 以后 避免 重复 数据 的问题, 针对 2个 字段 进行 唯一索引.但是 数据表中有重复数据,只能进行数据的清洗,清洗原则是:

  1. 清洗掉重复数据中 role_id 为0的数据
  2. 如果role_id 不为0 则清洗掉 get_time 比较大的数据

解决方案一

本着 一贯的思维 就是构造语句,然后删除重复的语句, 于是有了下面的语句:

  1. 查询 客户关系表 中 55 客户库的 warehouse_id,customer_id,extra_id,role_id, 然后按照 role_id 升序, get_time 降序 排序
1
2
3
4
5
6
7
    select warehouse_id, customer_id, extra_id,role_id,get_time from `customer_extra` where warehouse_id = 55 order by role_id asc, get_time desc

2. 按照要设定的唯一键 分组, 然后查询出对应的 要删除的 extra_id

```sql
select a.extra_id as extra_id from (select warehouse_id,customer_id,extra_id,role_id,get_time from `5k_customer_extra` where warehouse_id = 55 order by role_id asc,get_time desc) a  group by CONCAT(a.warehouse_id, '_', a.customer_id) having count(*) > 1

  1. 最后组合成删除的语句:
1
2
DELETE FROM `5k_customer_extra` WHERE extra_id in (select t.extra_id from (select a.extra_id as extra_id from (select warehouse_id,customer_id,extra_id,role_id,get_time from `5k_customer_extra` where warehouse_id = 55 order by role_id asc,get_time desc) a  group by CONCAT(a.warehouse_id, '_', a.customer_id) having count(*) > 1) t)

搞定,感觉 不错的样子,于是在测试服务器上运行一下子,完美执行. 于是对接线上服务器高高兴兴的执行了一下, 然后 线上 系统 瞬间 访问 卡死, 然后群里立马各种问候就来了,这就很尴尬滴一说.

解决方案二:

上面执行了SQL 发现系统卡成屎,所以得查一下原因, SQL语句没有问题 于是 查了一下数据库中的数据:

1
SELECT count(1) FROM customer_extra WHERE warehouse_id = 55;

查询的结果果然喜人: 120万

于是 上代码 慢慢按照 1000 条删除一次的原则进行:

1
2
3
4
5
6
7
8
9
10
11
# 创建一个游标
cursor = connection.cursor()
for i in range(10):
    delete_sql = "DELETE FROM `customer_extra` WHERE extra_id in (select t.extra_id from (select a.extra_id as extra_id from (select warehouse_id,customer_id,extra_id,role_id,get_time from `customer_extra` where warehouse_id = 55 order by role_id asc,get_time desc) a  group by CONCAT(a.warehouse_id, '_', a.customer_id) having count(*) > 1 LIMIT 1000) t)";
    cursor.execute(delete_sql)
    connection.commit()
    print(cursor.rowcount)
    time.sleep(2)
cursor.close()
connection.close()

运行后, 线上系统是不卡嘞, 但是 这个代码 处理的速度 有点 太喜人嘞, 一分钟 才删除几万条不到, 尴尬,这个方案也是不行

解决方案三

于是, 本着 试试看的心态, 把数据导出来成 excel 格式

1
2
3

select warehouse_id, customer_id, extra_id,role_id,get_time from `customer_extra` where warehouse_id = 55 order by role_id asc, get_time desc

导出EXCEL如下:

开始操作:

  1. 如图所示,查询公式 做出唯一键:

  1. 然后按照唯一键分组,如下图:

  1. 筛选,唯一键 拿到 为 1 的 就是重复的数据, 并且对 role_id 筛选 拿到 role_id 为 0的 先删除掉 一批

  2. 然后删按照 get_time 排序 删除掉 role_id 不为0 的数据.

OK 这样的操作比前面单纯跑 SQL 快多了,所以 方案三 可行. 但是 操作 Excel 不知道 其他熟练不, 反正我用起来 磕磕巴巴滴, 还得花点时间. 于是 就有嘞下面的操作

解决方案四

  1. 先运行 上面的查询SQL 然后 导出要删除的 主键ID
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
    SELECT t.extra_id
from ( select a.extra_id as extra_id from ( select
			warehouse_id,
			customer_id,
			extra_id,
			role_id,
			get_time
		from
			`5k_customer_extra`
		where
			warehouse_id = 55
		order by
			role_id asc,
			get_time desc) a
	group by
		CONCAT(a.warehouse_id, '_', a.customer_id)
	having
		count(*) > 1) t

导出来以后, 直接 整理 id 然后 放到代码里删除

1
DELETE FROM `customer_extra` WHERE extra_id IN (....)

速度比 操作 excel快多了, 然后一早上清理 完成, 然后果断执行 新建了一个唯一索引:

1
2
3

CREATE UNIQUE INDEX customer_IDX USING BTREE ON `customer_extra` (customer_id,warehouse_id);

完事,搞定,尴尬的是, 今天周一.上周清洗的数据 由于周六的阿里云故障 数据又又又被回复了, 然后 重新跑一遍 吧.