MySQL 8 数据清洗总结
MySQL 8 数据清洗三要素:
- 库表拷贝和数据备份
- 数据清洗SQL
- 数据清洗必杀技-存储过程
前提:数据库关联库表初始化和基础数据初始化:
-- usc.t_project definitionCREATE TABLE `t_project` (`id` varchar(64) NOT NULL COMMENT '主键',`tid` varchar(64) NOT NULL COMMENT 'TID',`ptid` varchar(64) NOT NULL COMMENT 'PTID',`project_no` varchar(64) DEFAULT NULL COMMENT '项目编号',`project_name` varchar(128) NOT NULL COMMENT '项目名称',`project_address` varchar(128) NOT NULL COMMENT '项目地址',`is_delete` int NOT NULL DEFAULT '0' COMMENT '删除标识:0=未删除,1=已删除',PRIMARY KEY (`id`),UNIQUE KEY `t_project_id_IDX` (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- usc.t_arch definitionCREATE TABLE `t_arch` (`tid` varchar(64) NOT NULL COMMENT 'TID',`ptid` varchar(64) NOT NULL COMMENT 'PTID',`id` varchar(64) NOT NULL COMMENT '主键',`project_id` varchar(64) NOT NULL COMMENT '项目ID',`project_no` varchar(100) NOT NULL COMMENT '项目编号',`arch_name` varchar(128) NOT NULL COMMENT '案卷名称',`arch_no` varchar(128) NOT NULL COMMENT '案卷编号',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
上述库表的关系:1:N = 项目 :案卷
-- 项目基础数据
INSERT INTO usc.t_project (id,tid,ptid,project_no,project_name,project_address,is_delete) VALUES('1','430100','430000','1001','长沙国金中心','长沙市芙蓉区', 0),('2','430100','430000','1001','长沙国金中心','长沙市芙蓉区', 0);
-- 案卷基础数据
INSERT INTO usc.t_arch (tid,ptid,id,project_id,project_no,arch_name,arch_no) VALUES('430100','430000','1','1','1001','案卷一','案卷一'),('430100','430000','2','2','1002','案卷二','案卷二'),('430100','430000','3','2','1002','案卷三','案卷三');
库表拷贝和数据备份
在MySQL 8 客户端 执行如下命令:
-- 复制t_project 表结构
create table t_project_2023_08_29 like t_project;-- 拷贝t_project 表的数据至t_project_2023_08_29
insert into t_project_2023_08_29 select * from t_project-- t_arch 执行如下命令, 注意替换相关表名
create table t_arch_2023_08_29 like t_arch ;insert into t_arch_2023_08_29 select * from t_arch
数据清洗SQL
数据清洗的五要素:
- 确定数据清洗的筛选条件
- 确定数据清洗的数据记录
- 确定数据清洗的过滤条件
- 确定数据清洗的更新字段
- 数据清洗后的核验
实战:昨天晚上帮朋友写了一个Shell 脚本迁移******城建档案馆历史数据。今天跟我反馈迁移的历史项目信息存在重复情况,导致项目关联的案卷出现了缺失情况。
按照数据清洗的5要素一步步的来复盘,如何编写项目关联案卷的清洗SQL:
1、确定数据清洗的筛选条件:
select tp.tid, tp.ptid, tp.project_no from t_project tp group by tp.tid, tp.ptid, tp.project_no having(count(1)) > 1
此SQL功能含义:查询项目表以Tid\Ptid\Project_no 字段分组且数量大于1 的项目信息 。
上述截图标识:项目表存在重复记录的情况.
2、确定数据清洗的数据记录:
select * from t_arch ta
inner join (select tp.tid, tp.ptid, tp.project_no from t_project tp group by tp.tid, tp.ptid, tp.project_no having(count(1)) > 1) temp
on ta.tid = temp.tid and ta.ptid = temp.ptid and ta.project_no = temp.project_no
此SQL功能含义:使用内联模式查询案卷表和项目表【条件添加:数据清洗的筛选条件】 。
上述截图标识:案卷表需要进行数据清洗的记录数.
3、确定数据清洗的过滤条件
一般情况下过滤条件为:查询记录字段与关联从表关联字段。
select * from t_arch ta
inner join (select tp.tid, tp.ptid, tp.project_no from t_project tp group by tp.tid, tp.ptid, tp.project_no having(count(1)) > 1) temp
on ta.tid = temp.tid and ta.ptid = temp.ptid and ta.project_no = temp.project_no
where ta.project_id in (select tp.id from t_project tp where tp.tid = temp.tid and tp.ptid = temp.ptid and tp.project_no = temp.project_no
)
此SQL功能含义:使用查询字段temp.tid\temp.ptid\temp.project_no 关联从表t_project,查询满足主表t_arch 关联的project_id。
上述截图标识:案卷表需要进行数据清洗的记录数并添加了相关条件进行筛选。
4、 确定数据清洗的更新字段
案卷表需要更新project_id 字段,同时将Select 语句修改为Update 语句。
update t_arch ta
inner join (select tp.tid, tp.ptid, tp.project_no from t_project tp group by tp.tid, tp.ptid, tp.project_no having(count(1)) > 1) temp
on ta.tid = temp.tid and ta.ptid = temp.ptid and ta.project_no = temp.project_no
set ta.project_id = (select min(tp.id) from t_project tp where tp.tid = temp.tid and tp.temp.ptid and tp.project_no = temp.project_no group by tp.tid, tp.ptid, tp.project_no limit 1)
where ta.project_id in (select tp.id from t_project tp where tp.tid = temp.tid and tp.ptid = temp.ptid and tp.project_no = temp.project_no
)
重点:从表存在重复的情况,一般推荐使用:min/max函数 + group by +limit +筛选主表关联字段,查询出满足条件的从表字段进行Set。
(select min(tp.id) from t_project tp where tp.tid = temp.tid and tp.temp.ptid and tp.project_no = temp.project_no group by tp.tid, tp.ptid, tp.project_no limit 1)
5、数据清洗后的核验
select * from t_arch ta
inner join (select tp.tid, tp.ptid, tp.project_no from t_project tp group by tp.tid, tp.ptid, tp.project_no having(count(1)) > 1) temp
此SQL功能含义:核查数据的清洗记录情况。
上述截图标识:与数据清洗筛选记录截图,我们明显发现project_id 字段已经全部替换为 1,但是数据核查的清洗记录SQL 还能查询出相关数据,但是t_arch 表管理的project_id 字段又是正确的因为t_project 表的数据还没有进行清洗。
数据清洗拓展
以下SQL 主要涉及T_Project 表数据的清洗
update t_project tainner join (select tp.tid, tp.ptid, tp.project_no from t_project tp group by tp.tid, tp.ptid, tp.project_no having(count(1)) > 1) tempon ta.tid = temp.tid and ta.ptid = temp.ptid and ta.project_no = temp.project_noset ta.is_delete = 1where ta.id not in (select min_id from (select min(tp.id) as min_id from t_project tp where tp.is_delete = 0 group by tp.tid, tp.ptid, tp.project_no having(count(1)) > 1) temp)
重点:主表级联主表基于筛选条件构建的临时表。添加Where 条件为筛选主表重复记录的条件,并设置is_delete = 1.
温馨提示:
主表数据清理的条件为:主表重复记录条件
业务表级联主表数据清理条件为:查询满足条件记录的字段条件
主表数据清理SQL:
update t_project tainner join (select tp.tid, tp.ptid, tp.project_no from t_project tp group by tp.tid, tp.ptid, tp.project_no having(count(1)) > 1) tempon ta.tid = temp.tid and ta.ptid = temp.ptid and ta.project_no = temp.project_noset ta.is_delete = 1where ta.id not in (select min_id from (select min(tp.id) as min_id from t_project tp where tp.is_delete = 0 group by tp.tid, tp.ptid, tp.project_no having(count(1)) > 1) temp)
从表级联主表数据清理SQL:
update t_arch ta
inner join (select tp.tid, tp.ptid, tp.project_no from t_project tp group by tp.tid, tp.ptid, tp.project_no having(count(1)) > 1) temp
on ta.tid = temp.tid and ta.ptid = temp.ptid and ta.project_no = temp.project_no
set ta.project_id = (select min(tp.id) from t_project tp where tp.tid = temp.tid and tp.temp.ptid and tp.project_no = temp.project_no group by tp.tid, tp.ptid, tp.project_no limit 1)
where ta.project_id in (select tp.id from t_project tp where tp.tid = temp.tid and tp.ptid = temp.ptid and tp.project_no = temp.project_no
)
数据清洗必杀技-存储过程
如果数据清洗SQL 无法到达数据清洗的预期,那接下来我将使用存储过程实现数据清洗功能。
前提条件:
- 熟悉和了解MySQL 8 存储过程基本语法。
- 熟悉存储过程中的变量声明和赋值。
- 熟悉存储过程中的游标声明和遍历。
- 熟悉存储过程中的IF...ELSE 判断
- 熟悉存储过程中的运算符。
如果对于MySQL 8 存储过程的小白,建议参考学习:MySQL 8 一文读懂存储过程
项目和案卷清洗存储过程源码:
delimiter $
create procedure distanct_project()
begin-- 变量声明declare tid varchar(64);declare ptid varchar(64);declare project_no varchar(64);declare min_id varchar(64);-- 定义游标遍历标识符declare done int default 0;-- 游标定时declare project_cursor cursor for select tp.tid, tp.ptid, tp.project_no from t_project tp group by tp.tid, tp.ptid, tp.project_no having(count(1)) > 1;-- 游标全部遍历完成时,将游标遍历标识符设置为1declare continue handler for not found set done =1;-- 打开游标open project_cursor;-- 游标遍历read_project:LOOP-- 从游标中获取下一行数据FETCH project_cursor INTO tid, ptid, project_no;-- 判断是否已经遍历完所有行IF done THENLEAVE read_project;END IF;-- 查询select min(tp.id) into min_id from t_project tp where tp.tid = tid and tp.ptid = ptid and tp.project_no = project_no group by tp.tid, tp.ptid, tp.project_no limit 1;-- 从表更新update t_arch ta set ta.project_id = min_id where ta.tid =tid and ta.ptid =ptid and ta.project_no =project_no;-- 主表更新update t_project tp set tp.is_delete = 1where tp.tid =tid and tp.ptid = ptid and tp.project_no = project_no and tp.id <> min_id;END LOOP;-- 关闭游标CLOSE project_cursor;end $call distanct_project ();
温馨提示: 对于复杂的业务数据清洗,例如:商品房管理系统:项目-》楼栋-》房屋-》网签合同-》预售证 等多层级多维度的数据清洗,无非就是游标中嵌套游标,再进行select 查询插入最后执行IF...ELSE 判断执行insert/update 语句。
今天的分析就到这里结束。
相关文章:

MySQL 8 数据清洗总结
MySQL 8 数据清洗三要素: 库表拷贝和数据备份数据清洗SQL数据清洗必杀技-存储过程 前提:数据库关联库表初始化和基础数据初始化: -- usc.t_project definitionCREATE TABLE t_project (id varchar(64) NOT NULL COMMENT 主键,tid varchar(…...

设计模式第九讲:常见重构技巧 - 去除不必要的!=
设计模式第九讲:常见重构技巧 - 去除不必要的! 项目中会存在大量判空代码,多么丑陋繁冗!如何避免这种情况?我们是否滥用了判空呢?本文是设计模式第九讲,讲解常见重构技巧:去除不必要的! 文章目录…...
自动化安装系统问题记录
Cobbler 版本更新 https://github.com/cobbler/cobbler/releases Centos7/8 Cobbler 问题: 部署cobbler时,使用cobbler get-loaders从网络下载引导加载程序时提示命令未找到 解决: yum -y install syslinux Rockylinux9.2 Cobbler3.…...

centos7 docker安装记录
以下所有命令都在root用户下进行,若为普通用户,需要在所有命令前加上 sudo。 1、更新yum包 将yum包更新到最新 yum update2、安装需要的软件包 yum-util 提供yum-config-manager功能,另外两个是devicemapper驱动依赖的; yum …...
全网实时热点热榜事件API
全网热榜API 一、全网热榜二、使用步骤1、接口2、请求参数 三、 案例和demo 一、全网热榜 1个免费的帮助你获取全网热点事件的接口API 二、使用步骤 1、接口 重要提示:建议使用https协议,当https协议无法使用时再尝试使用http协议 请求方式: GET https://luckycola.com.cn…...

淘宝API接口:提高电商运营效率与用户体验的利器(淘宝API接口使用指南)
淘宝API接口:提高电商运营效率与用户体验的利器 随着电商行业的快速发展,淘宝作为国内最大的电商平台之一,不断探索和创新,以满足不断变化的用户需求和商家需求。其中,淘宝API接口便是其创新的一个重要方面。本文将深…...

智己 LS6 用实力和你卷,最强 800v ?
2023 成都车展期间,智己 LS6 正式公布预售价格,新车预售价为 23-30 万元。新车会在 10 月份进行上市,11 月正式交付。 此前我们对智己 LS6 做过非常详细的静态体验,感兴趣的可点击此链接了解。 造型方面,新车前脸相比…...

深入探索C语言自定义类型:打造你的编程世界
一、什么是自定义类型 C语言提供了丰富的内置类型,常见的有int, char, float, double, 以及各种指针。 除此之外,我们还能自己创建一些类型,这些类型称为自定义类型,如数组,结构体,枚举类型和联合体类型。 …...

Opencv基于文字检测去图片水印
做了一个简单的去水印功能,基于文字检测去图片水印。效果如下: 插件功能代码参考如下: using namespace cv::dnn; TextDetectionModel_DB *textDetector0; void getTextDetector() {if(textDetector)return;String modelPath "text_de…...

jdbc235
概念:java database connectivity java数据库连接 java语言操作数据库 定义了一套操作所有关系型数据库的规则(接口) 本质:其实是官方公司定义了一套操作所有关系型数据库的规则,即接口。各个数据库厂商去实现这套接…...

库仑定律和场强
1、库伦定律 两个电荷相互作用的力。 力是矢量,有大小和方向。 1.1、力的大小 1.2、力的方向 在两个电荷的连线上,同种电荷相互排斥,异种电荷相互吸引。 真空,不是必要条件,修改公式中介电常数的值仍然满足库伦定律。…...

Android安卓实战项目(13)---记账APP详细记录每天的收入和支出并且分类统计【生活助手类APP】强烈推荐自己也在用!!!(源码在文末)
Android安卓实战项目(13)—记账APP详细记录每天的收入和支出并且分类统计【生活助手类APP】强烈推荐自己也在用!!!(源码在文末🐕🐕🐕) 一.项目运行介绍 B站…...

嵌入式开发之syslog和rsyslog构建日志记录
1.syslogd作客户端 BusyBox v1.20.2 (2022-04-06 16:19:14 CST) multi-call binary.Usage: syslogd [OPTIONS]System logging utility-n Run in foreground-O FILE Log to FILE (default:/var/log/messages)-l N Log only messages more urge…...

Jaeger的经典BUG原创
前端,笔者在使用Jaeger进行Trace监控的时候,当数据量增大到一定数量级时,出现了一次CPU暴增导致节点服务器挂了的经典案例,这里对案例进行一个简单的抽象,供大家参考: 首先通过pprof对耗时的函数进行定位&…...

四款简洁好看 自适应的APP下载单页源码
分享四款简洁好看 自适应的APP下载单页源码,采用了底部自动获取ICP备案号,还有蓝奏云文件直链解析。不光可以做APP下载引导页,也可以随便改下按钮做网站引导页,自由发挥即可! 蓝奏云直链解析的好处:APP放在…...
【服务器】交换机带外管理和带内管理
一、交换机的带外管理是什么? 在带外管理模式中,网络的管理控制信息与用户网络的承载业务信息在不同的逻辑信道传送。 带外管理最大的优势在于,当网络出现故障中断时数据传输和管理都可以正常进行——不同的物理通道传送管理控制信息和数据…...
Kotlin的内置函数 apply、let、run、with、also
let 1.let函数返回类型,是根据匿名函数的最后一行变化而变化 2.let函数中的匿名函数里面持有的是it 集合自身 fun main() {var num1 1var num2 1var result:Intresult num1 num2var str result?.let {//传入本身,it指代本身即result,result不为空…...

2023年人工景点行业研究报告
第一章 行业概况 1.1 定义及分类 人工景点行业通常指的是设计和构建的为提供娱乐、教育或文化体验的景点。这些景点可能包括主题公园,博物馆,动物园,水族馆,科学中心,历史遗迹,艺术展览等。这个行业通常包…...

react轮播图
这里 我用的是组件: 网址:Collapse 折叠面板 - Ant Design Mobile 1.首先 先声明一个变量 2、把需要的数据存存进去 3、组件内容复制过来(这里用到的是map循环) 然后图片就出来了 就是这个简单 哈哈哈哈!!…...

Openlayers 叠加天地图-中国近海海洋等深面图层服务
Openlayers 叠加天地图-中国近海海洋等深面图层服务 核心代码完整代码:在线示例 偶然发现天地图有一个近海海洋图层,觉得不错,于是尝试叠加一下,花费了一些时间,叠加成功,这里分享一下。 本文包括核心代码…...

调用支付宝接口响应40004 SYSTEM_ERROR问题排查
在对接支付宝API的时候,遇到了一些问题,记录一下排查过程。 Body:{"datadigital_fincloud_generalsaas_face_certify_initialize_response":{"msg":"Business Failed","code":"40004","sub_msg…...
uni-app学习笔记二十二---使用vite.config.js全局导入常用依赖
在前面的练习中,每个页面需要使用ref,onShow等生命周期钩子函数时都需要像下面这样导入 import {onMounted, ref} from "vue" 如果不想每个页面都导入,需要使用node.js命令npm安装unplugin-auto-import npm install unplugin-au…...

无法与IP建立连接,未能下载VSCode服务器
如题,在远程连接服务器的时候突然遇到了这个提示。 查阅了一圈,发现是VSCode版本自动更新惹的祸!!! 在VSCode的帮助->关于这里发现前几天VSCode自动更新了,我的版本号变成了1.100.3 才导致了远程连接出…...

2.Vue编写一个app
1.src中重要的组成 1.1main.ts // 引入createApp用于创建应用 import { createApp } from "vue"; // 引用App根组件 import App from ./App.vue;createApp(App).mount(#app)1.2 App.vue 其中要写三种标签 <template> <!--html--> </template>…...
多模态商品数据接口:融合图像、语音与文字的下一代商品详情体验
一、多模态商品数据接口的技术架构 (一)多模态数据融合引擎 跨模态语义对齐 通过Transformer架构实现图像、语音、文字的语义关联。例如,当用户上传一张“蓝色连衣裙”的图片时,接口可自动提取图像中的颜色(RGB值&…...
数据链路层的主要功能是什么
数据链路层(OSI模型第2层)的核心功能是在相邻网络节点(如交换机、主机)间提供可靠的数据帧传输服务,主要职责包括: 🔑 核心功能详解: 帧封装与解封装 封装: 将网络层下发…...
在Ubuntu中设置开机自动运行(sudo)指令的指南
在Ubuntu系统中,有时需要在系统启动时自动执行某些命令,特别是需要 sudo权限的指令。为了实现这一功能,可以使用多种方法,包括编写Systemd服务、配置 rc.local文件或使用 cron任务计划。本文将详细介绍这些方法,并提供…...

C++ 求圆面积的程序(Program to find area of a circle)
给定半径r,求圆的面积。圆的面积应精确到小数点后5位。 例子: 输入:r 5 输出:78.53982 解释:由于面积 PI * r * r 3.14159265358979323846 * 5 * 5 78.53982,因为我们只保留小数点后 5 位数字。 输…...

自然语言处理——循环神经网络
自然语言处理——循环神经网络 循环神经网络应用到基于机器学习的自然语言处理任务序列到类别同步的序列到序列模式异步的序列到序列模式 参数学习和长程依赖问题基于门控的循环神经网络门控循环单元(GRU)长短期记忆神经网络(LSTM)…...
什么?连接服务器也能可视化显示界面?:基于X11 Forwarding + CentOS + MobaXterm实战指南
文章目录 什么是X11?环境准备实战步骤1️⃣ 服务器端配置(CentOS)2️⃣ 客户端配置(MobaXterm)3️⃣ 验证X11 Forwarding4️⃣ 运行自定义GUI程序(Python示例)5️⃣ 成功效果![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/55aefaea8a9f477e86d065227851fe3d.pn…...