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 语句。

今天的分析就到这里结束。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/115337.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

网络基础之重中之重

网络基础之重中之重

目录 IP协议 ​编辑 基本概念&#xff1a; 协议头格式&#xff1a; ​编辑 网段划分 DHCP &#xff1a; CIDR&#xff1a; 特殊的IP地址&#xff1a; IP地址的数量限制&#xff1a; 私有IP和公网IP 路由 路由的过程&#xff1a; 数据链路层 认识以太网&#x…
阅读更多...
GAN原理 代码解读

GAN原理 代码解读

模型架构 代码 数据准备 import os import time import matplotlib.pyplot as plt import numpy as np import torchvision.transforms as transforms from torch.utils.data import DataLoader from torchvision import datasets import torch.nn as nn import torch# 创建文…
阅读更多...
I IntelliJ IDEA 2023.2 最新解锁方式，支持java20

I IntelliJ IDEA 2023.2 最新解锁方式，支持java20

在 IntelliJ IDEA 2023.1 中&#xff0c;我们根据用户的宝贵反馈对新 UI 做出了大量改进。 我们还实现了性能增强&#xff0c;从而更快导入 Maven&#xff0c;以及在打开项目时更早提供 IDE 功能。 新版本通过后台提交检查提供了简化的提交流程。 IntelliJ IDEA Ultimate 现在支…
阅读更多...
C语言每日一练------------Day(7)

C语言每日一练------------Day(7)

本专栏为c语言练习专栏&#xff0c;适合刚刚学完c语言的初学者。本专栏每天会不定时更新&#xff0c;通过每天练习&#xff0c;进一步对c语言的重难点知识进行更深入的学习。 今日练习题关键字&#xff1a;两个数组的交集     双指针 &#x1f493;博主csdn个人主页&#xf…
阅读更多...
【MySQL】存储引擎

【MySQL】存储引擎

1.MySQL体系结构 1). 连接层 最上层是一些客户端和链接服务&#xff0c;包含本地 sock 通信和大多数基于客户端 / 服务端工具实现的类似 于TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入 了线程池的概念&#xff0c;为通过认证安全接…
阅读更多...
Ubuntu 启动出现grub rescue

Ubuntu 启动出现grub rescue

​ 一&#xff0c;原因 原因&#xff1a;出现 “grub rescue” 错误通常表示您的计算机无法正常引导到操作系统&#xff0c;而是进入了 GRUB&#xff08;Grand Unified Bootloader&#xff09;紧急模式。这可能是由于引导加载程序配置错误、硬盘驱动器损坏或其他引导问题引起…
阅读更多...
js定位到元素底部

js定位到元素底部

文字的一行一行添加的&#xff0c;每次添加要滚动条自动定位到元素底部 <div class"An">//要父元素包裹&#xff0c;父元素设置max-height&#xff0c;overflow啥的<div class"friendly_pW"></div></div>//添加文字时找子元素的高…
阅读更多...
Windows右键添加用 IDEA 打开

Windows右键添加用 IDEA 打开

1.安装IDEA时 安装时会有个选项来添加&#xff0c;如下&#xff1a; 勾选即可 2.修改注册表 安装时未勾选&#xff0c;可以把下面代码中程序路径改为自己的&#xff0c;保存为对应的 idea.reg文件&#xff0c;双击即可 Windows Registry Editor Version 5.00[HKEY_CLASSES…
阅读更多...
Screaming Frog SEO Spider，为您的网站提供全方位的优化解决方案

Screaming Frog SEO Spider，为您的网站提供全方位的优化解决方案

Screaming Frog SEO Spider是一款适用于Mac的软件&#xff0c;它可以帮助用户分析网站的优化信息。该软件可以模拟蜘蛛爬行的方式&#xff0c;抓取网站的各种信息&#xff0c;并将这些信息整理成易于理解的报告。这些报告可以帮助用户评估网站的优化情况&#xff0c;发现链接的…
阅读更多...
pyqt5-快捷键QShortcut

pyqt5-快捷键QShortcut

import sys from PyQt5.QtWidgets import * from PyQt5.QtCore import * from PyQt5.QtGui import *""" 下面示例揭示了&#xff0c;当关键字绑定的控件出现的时候&#xff0c;快捷键才管用&#xff0c; 绑定的控件没有出现的时候快捷键无效 """…
阅读更多...
微前沿 | 第1期：强可控视频生成；定制化样本检索器；用脑电重建视觉感知；大模型鲁棒性评测

微前沿 | 第1期：强可控视频生成；定制化样本检索器；用脑电重建视觉感知；大模型鲁棒性评测

欢迎阅读我们的新栏目——“微前沿”&#xff01; “微前沿”汇聚了微软亚洲研究院最新的创新成果与科研动态。在这里&#xff0c;你可以快速浏览研究院的亮点资讯&#xff0c;保持对前沿领域的敏锐嗅觉&#xff0c;同时也能找到先进实用的开源工具。 本期内容速览 01. 强可…
阅读更多...
Vue2里监听localstorage里值的变化

Vue2里监听localstorage里值的变化

有的时候，我们需要根据本地缓存在localstorage里值的变化做出相应的操作，这就需要我们监听localstorage： 首先，我们在src下的libs文件夹下新建一个stroage.js用于重写setItem事件，当使用setItem的时候，触发，window.dispatchEvent派发事件 const Stroage = {// 重写set…
阅读更多...
python基础之miniConda管理器

python基础之miniConda管理器

一、介绍 MiniConda 是一个轻量级的 Conda 版本&#xff0c;它是 Conda 的精简版&#xff0c;专注于提供基本的环境管理功能。Conda 是一个流行的开源包管理系统和环境管理器&#xff0c;用于在不同的操作系统上安装、管理和运行软件包。 与完整版的 Anaconda 相比&#xff0c…
阅读更多...
安卓版yolo-fastest

安卓版yolo-fastest

安卓版本yolofastest效果测试 安卓配置OPENCV4ANDROID&#xff0c;见我的博客一篇文章opencv4dandroid配置 这个不需要使用JNI&#xff0c;十分简单的配置 说真的&#xff0c;其实只调用OPENCV的函数&#xff0c;自己写的代码不多&#xff0c;使用OPENCV4ANDROID和JNI的时间差…
阅读更多...
SPSS教程：如何绘制带误差的折线图

SPSS教程：如何绘制带误差的折线图

SPSS教程&#xff1a;如何绘制带误差的折线图 1、问题与数据 研究者想研究45-65岁健康男性中&#xff0c;静坐时长和血胆固醇水平的关系&#xff0c;故招募100名研究对象询问其每天静坐时长&#xff08;time&#xff09;&#xff0c;并检测其血液中胆固醇水平&#xff08;cho…
阅读更多...
2023开学礼《乡村振兴战略下传统村落文化旅游设计》北农馆藏许少辉八一新书

2023开学礼《乡村振兴战略下传统村落文化旅游设计》北农馆藏许少辉八一新书

2023开学礼《乡村振兴战略下传统村落文化旅游设计》北京农学院图书馆许少辉八一新书
阅读更多...
ModaHub魔搭社区——决胜大模型时代，算力、网络、向量数据库缺一不可

ModaHub魔搭社区——决胜大模型时代，算力、网络、向量数据库缺一不可

大模型应用场景日趋多样，需求也随着增加，进而倒逼着多元算力方面的创新，为满足AI工作负载的需求，采用GPU、FPGA、ASIC等加速卡的服务器越来越多。 根据IDC数据统计，2022年，中国加速服务器市场相比2019年增长44.0亿美元，服务器市场增量的一半更是来自加速服务器。 这意味…
阅读更多...
已解决 Python FileNotFoundError 的报错问题

已解决 Python FileNotFoundError 的报错问题

本文摘要&#xff1a;本文已解决 Python FileNotFoundError 的相关报错问题&#xff0c;并总结提出了几种可用解决方案。同时结合人工智能GPT排除可能得隐患及错误。 &#x1f60e; 作者介绍&#xff1a;我是程序员洲洲&#xff0c;一个热爱写作的非著名程序员。CSDN全栈优质领…
阅读更多...
SQL Server开启变更数据捕获（CDC）

SQL Server开启变更数据捕获（CDC）

一、CDC简介 变更数据捕获&#xff08;Change Data Capture &#xff0c;简称 CDC&#xff09;&#xff1a;记录 SQL Server 表的插入、更新和删除操作。开启cdc的源表在插入、更新和删除操作时会插入数据到日志表中。cdc通过捕获进程将变更数据捕获到变更表中&#xff0c;通过…
阅读更多...
即时物流进入盈利期，为什么说顺丰同城才是“头雁”？

即时物流进入盈利期，为什么说顺丰同城才是“头雁”？

从餐饮店、便利店老板们扮演跑腿角色给顾客送商品算起&#xff0c;即时配送&#xff08;简称“即配”&#xff09;行业跌跌撞撞好几年&#xff0c;规模壮大、秩序提升&#xff0c;但盈亏平衡的及格线&#xff0c;始终让人望洋兴叹。直到这个夏天&#xff0c;平均分终于被拉上去…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023