GB/T 4754标准演进与MySQL数据清洗：行业代码映射全攻略

简介这份数据包面向需要处理行业维度数据清洗与标准化的大数据技术人员完整汇集了2002、2011、2017三个年度发布的国民经济行业分类国家标准GB/T 4754-2002、GB/T 4754-2011、GB/T 4754-2017并统一为“门类·大类·中类·小类”的四级结构。每个行业代码都按这一结构拆分并提供跨版本的汇总统计例如A0111对应“农、林、牧、渔业·农业·谷物及其他作物的种植·谷物的种植”可直接核对不同版本间行业代码的变化降低手工整理国标的时间成本。压缩包共3个文件均为SQL格式分别存放三个年份的建表与数据总大小仅44KB适合导入MySQL数据库快速用于数据治理、数据仓库建模或统计分析。压缩包内文件组织清晰便于单独导入或对比使用。资源已有3038人学习或下载尤其适合需要建立历史行业口径映射表、解决不同版本分类冲突的数据分析与开发人员。1. 国民经济行业分类与代码三年版本MySQL打包行业数据清洗的后悔药做行业维度数据清洗的人大概率都被“年份魔咒”折磨过业务库里的行业代码是2008年的可最新标准已经换到2017版销售表里存着旧门类代码财务系统却按新代码出报表两套对齐时只能靠人工翻标准。这份资源把2002、2011、2017三个版本的国民经济行业分类与代码做成了MySQL数据文件每一行都是带完整四级路径的代码和名称。国民经济行业分类采用“门类·大类·中类·小类”四级结构代码规则在三个版本中各有差异直接跨年比对必然翻车。资源内的std_code_2002.sql、std_code_2011.sql、std_code_2017.sql三个文件表结构一致查询和清洗逻辑可以复用适合数据仓库建模、指标口径统一、跨年度对比分析的技术人员直接导入使用。2. GB/T 4754 标准演进从2002到2017四级分类体系怎么变在动手导入SQL之前先花点时间搞清三个版本之间的关系。行业分类代码不是随便编的它是按照门类字母A-T、大类两位数、中类三位数、小类四位数逐级展开的。比如A0111这个代码拆开看就是门类A农、林、牧、渔业、大类01农业、中类011谷物及其他作物的种植、小类0111谷物的种植。四条路径合起来就是摘要里那句“农、林、牧、渔业·农业·谷物及其他作物的种植·谷物的种植”。版本差异的核心不是编码规则变了而是类目本身在变。2002版、2011版、2017版分别对应GB/T 4754-2002、GB/T 4754-2011、GB/T 4754-2017每一次标准更新都会新增一些当时的新兴行业同时合并或删除一些过时的小类。如果你手里的数据跨越多个年份做行业维度标准化之前必须先把标准版本差异理清否则映射工作会很痛苦。2.1 门类·大类·中类·小类四位编码怎么拆国民经济行业分类的编码规则是“一个字母加四位数字”。字母代表门类从A到T一共20个门类覆盖农、林、牧、渔业采矿业制造业电力、热力、燃气及水生产和供应业建筑业批发和零售业交通运输、仓储和邮政业住宿和餐饮业信息传输、软件和信息技术服务业金融业房地产业租赁和商务服务业科学研究和技术服务业水利、环境和公共设施管理业居民服务、修理和其他服务业教育卫生和社会工作文化、体育和娱乐业公共管理、社会保障和社会组织以及国际组织。四位数字中的前两位是大类代码前三位是中类代码四位全取是小类代码。举个例子C门类是制造业C13是农副食品加工业大类C131是谷物磨制中类C1311是稻谷磨制小类。在表里C1311这个代码对应的名称就是“制造业·农副食品加工业·谷物磨制·稻谷磨制”。名称的层级关系用“·”分隔每一段分别对应一个层级。关键点在于表中的“代码”列存的是完整的“字母四位数字”而“名称”列存的是用“·”拼接的完整层级路径。两者是对应的但查询时不能只靠名称列模糊匹配应该优先用代码列做精确计算。因为名称在三个版本之间可能有微调代码如果没变关联关系就还在名称变化不影响JOIN结果。2.2 2002到2011产业结构调整带来的类目增删2002版是入世后的第一版行业分类标准整体框架沿袭了之前的四级体系但编排上更贴近当时的统计口径。2011版发布时服务业占比已经明显上升于是新增了“金属制品、机械和设备修理业”这个大类同时把“开采辅助活动”从采矿业里单独拆出放在B门类下作为大类存在。类似这种“大类级别”的变动在代码映射时最麻烦因为旧版里没有对应的整段代码范围。具体到小类层面2011版删掉了一些产能过剩相关的类目比如部分矿产开采的小类被合并进相邻代码新增了诸如“其他电信服务”“互联网信息服务”等细分项。如果业务数据产生于2005年左右用2002版代码入库后来要转成2011版口径就必须逐条核对这类变动。2.3 2011到2017合并与拆分的关键变动2017版GB/T 4754-2017是当前最常用的标准。这一版在2011版基础上新增了不少“互联网”相关的行业类别比如“互联网零售”“互联网生活服务平台”“互联网游戏服务”这些小类反映出数字经济在国民经济统计里的权重。同时一些传统制造业类目做了细化拆分比如“汽车零部件及配件制造”在2011版下只有一个中类2017版则按功能进一步分成几个小类。另一个值得注意的变动是部分类目的“归属调整”。例如原属于某些门类的辅助性活动在新版里被整体平移到了“科学研究和技术服务业”或者“租赁和商务服务业”。这类调整不会被写进小类名称里如果你只比对代码和名称很容易漏掉必须对照官方修订说明才能确认。所以做映射时我一般不会只看三个SQL文件还会额外找一下官方发布的修订对照表。SQL文件提供的是最终代码和名称修订对照表才详细说明每个代码变动的原因和对应关系。资源里虽然没有附带修订说明但你可以用三版代码表交叉比对先找出代码完全相同、代码不同但名称相似、代码和名称都完全不同的三类记录再针对后两类做人工核对。3. 数据表结构与SQL文件std_code_2002.sql 怎么用压缩包解压后是三个SQL文件std_code_2002.sql、std_code_2011.sql、std_code_2017.sql。文件命名很直白年份后缀对应的就是标准的发布年份。三个文件内部结构一致都是两列代码列和名称列。代码列存储“字母四位数字”的完整编码名称列存储“·”连接的完整四级名称路径。我先按2017版文件举例2002和2011的用法完全一样只是数据内容不同。3.1 表结构解析代码列与名称列的对应关系导入SQL后表里每一行的形态是这样的代码名称A0111农、林、牧、渔业·农业·谷物及其他作物的种植·谷物的种植C1311制造业·农副食品加工业·谷物磨制·稻谷磨制注意这里有个隐含信息每一行存的都是小类级别。所谓四级结构是指名称里包含四个层级的名称而不是表里有四列。所以如果你想做“门类大类”的粒度分析不能直接在这张表里GROUP BY必须先对代码做字符串处理拆出对应的层再单独建一张维度表。我一般会在导入后立刻建一张拆分视图把每个小类代码的父级代码段都算出来。SQL如下CREATE OR REPLACE VIEW v_industry_2017 AS SELECT 代码, 名称, SUBSTRING(代码, 1, 1) AS 门类代码, SUBSTRING(代码, 2, 2) AS 大类代码, SUBSTRING(代码, 2, 3) AS 中类代码, SUBSTRING(代码, 2, 4) AS 小类代码 FROM std_code_2017;逻辑说明SUBSTRING(代码, 2, 2) 从第2个字符起取两位拿到的就是大类代码。因为第1位是门类字母从第2位开始才是数字段。中类代码取三位小类代码取四位。门类代码单独截取第一位。视图建好之后每一次查询都不用重复写SUBSTRING逻辑。参数说明如果你的代码列不是字母开头而是纯数字SUBSTRING的起始位置要从2改成1不要直接复制这段SQL。另外SUBSTRING截取的结果是字符串类型如果你需要跟业务表里的数字字段JOIN记得用CAST转换比如CAST(SUBSTRING(代码, 2, 2) AS UNSIGNED)。3.2 导入MySQL命令行三步走第一步建库。如果目标库还不存在先建一个专门放维度表的库mysql -uroot -p -e CREATE DATABASE IF NOT EXISTS dim DEFAULT CHARSET utf8mb4;第二步导入SQL文件。以2017版为例mysql -uroot -p dim --default-character-setutf8mb4 std_code_2017.sql这里把std_code_2017.sql导入到dim库。文件名如果和表名不一致也没关系MySQL导入的是文件里的CREATE TABLE语句和INSERT语句文件名只影响你在命令行里敲的路径。第三步验证行数SELECT COUNT(*) AS 小类总数 FROM std_code_2017; SELECT COUNT(*) AS 中类去重 FROM ( SELECT DISTINCT SUBSTRING(代码, 2, 3) FROM std_code_2017 ) t;第一步里的-e参数表示执行完命令就退出适合在自动化脚本里用。第二步如果你用的是高版本MySQL客户端8.0可能会遇到认证插件兼容问题那个不在资源范围内但可以换用source命令从mysql客户端内部导入source /path/to/std_code_2017.sql;3.3 常用查询按层级筛选业务数据表导入好后最常见的用法是关联业务表把存的行业代码翻译成名称。如果业务表里存的是A0111这种完整代码直接JOIN即可SELECT b.订单号, s.名称 FROM 业务表 b LEFT JOIN std_code_2017 s ON b.行业代码 s.代码;如果业务表里只存四位数字0111没有门类字母那要先把两边对齐SELECT b.订单号, s.名称 FROM 业务表 b LEFT JOIN std_code_2017 s ON CONCAT(A, b.行业代码) s.代码;注意这里的CONCAT只能在你知道所有代码都属于同一个门类时才能用。实际业务中业务表里的四位数字往往省略的是当前默认门类的字母如果门类不确定需要先反查行业名称来确认。这里就是很多人掉坑的地方第五章会展开说。3.4 文件的字符集兼容性解压后的SQL文件如果直接双击打开在Windows记事本里可能看到中文乱码那不是文件坏了是记事本默认按ANSI解析内容。导入MySQL时只要指定了--default-character-setutf8mb4乱码问题不会出现。如果你是在Linux环境用vim查看需要用:set fileencodingutf-8重新加载。关于表的命名三个SQL文件导入后会生成三张独立的表std_code_2002、std_code_2011、std_code_2017。它们彼此不关联字段结构相同。如果你需要跨年比对可以之后把它们UNION ALL到一起或者建一个带年份字段的汇总表。第4章会给出更完整的做法。4. 三年数据对比把业务代码映射到统一口径资源的核心价值在于三年的代码表都有了剩下的问题是“怎么用”。大多数数据仓库项目的行业维度标准化都是把业务表中杂七杂八的行业代码统一到最新口径2017版并给每一行打上门类、大类、中类、小类的标签。下面我按实际项目里最常见的流程来拆解。4.1 建一张带年份的汇总字典表首先把三个版本的代码合成一张表加一个年份字段这样后续映射筛选才方便CREATE TABLE dim_industry_all AS SELECT 2002 AS 版本, 代码, 名称 FROM std_code_2002 UNION ALL SELECT 2011 AS 版本, 代码, 名称 FROM std_code_2011 UNION ALL SELECT 2017 AS 版本, 代码, 名称 FROM std_code_2017;这条SQL把三张表纵向堆到一起。UNION ALL保留重复行不需要去重因为同一个代码在不同版本里可能对应不同的名称去重反而会丢数据。加完索引之后查询效率会更好ALTER TABLE dim_industry_all ADD INDEX idx_code (代码);逻辑说明版本字段用字符串而不是数字是因为2002、2011、2017三个值不是等差数列字符串的辨识度更高。代码字段建索引是为了后续按代码JOIN业务表时能用上索引避免全表扫描。参数说明如果你希望查询结果更易读可以把版本字段改成“GB/T 4754-2002”这种带标准号的写法比如用CASE WHEN替换。但这会影响后续JOIN的SQL长度建议保留简短值。4.2 跨年映射一对多、多对一的处理原则现在回到最核心的问题业务表里存的是2011版代码怎么映射到2017版第一步找出两个版本中都存在的代码SELECT a.代码, a.名称 AS 名称_2011, b.名称 AS 名称_2017 FROM std_code_2011 a LEFT JOIN std_code_2017 b ON a.代码 b.代码 WHERE b.代码 IS NOT NULL;如果名称相同说明这个代码没变直接沿用。如果名称不同说明代码虽然没变但叫法改了还是可以直接沿用代码但名称要用2017版替换。第二步找出只有旧版才有、新版没有的代码SELECT a.代码, a.名称 AS 名称_2011 FROM std_code_2011 a LEFT JOIN std_code_2017 b ON a.代码 b.代码 WHERE b.代码 IS NULL;对这些代码不能简单抛弃而是要根据业务去判断它在新版里是被合并了还是被拆分了还是真的被删除了。实际操作中我一般会先用名称相似度做初筛SELECT a.代码 AS 旧代码, a.名称 AS 旧名称, b.代码 AS 新代码, b.名称 AS 新名称 FROM std_code_2011 a LEFT JOIN std_code_2017 b ON REPLACE(a.名称, ·, ) LIKE CONCAT(%, SUBSTRING_INDEX(b.名称, ·, 3), %);这条SQL把旧名称里的“·”去掉拿新名称的前三段做模糊匹配。注意LIKE两侧的字符串比较耗时只适合几万行以内的小表如果代码量很大建议把名称拆列后做索引再匹配。一对多和多对一没有标准答案。常见的做法是如果一个旧代码对应多个新代码按业务体量选一个主映射其他映射写成辅助记录如果多个旧代码对应一个新代码直接统一到新代码。映射表结构建议做成这样CREATE TABLE dim_industry_mapping ( 旧版本 VARCHAR(4), 旧代码 VARCHAR(5), 新代码 VARCHAR(5), 映射类型 ENUM(不变,改名,合并,拆分,新增), 备注 VARCHAR(255) );映射类型字段很有用。以后做指标同比环比时如果发现某个行业的数据出现异常波动可以先看映射类型判断是代码变动引起的还是业务真实变动。4.3 做行业标签标准化的完整流程整理完映射关系后在业务表上打标签的流程是这样的第一步给业务表加几个冗余字段ALTER TABLE 业务表 ADD COLUMN 门类代码 VARCHAR(1), ADD COLUMN 大类代码 VARCHAR(2), ADD COLUMN 中类代码 VARCHAR(3), ADD COLUMN 小类代码 VARCHAR(4), ADD COLUMN 行业名称_标准 VARCHAR(200);第二步用映射关系更新业务表UPDATE 业务表 b LEFT JOIN dim_industry_mapping m ON b.行业代码 m.旧代码 AND m.旧版本 2011 LEFT JOIN std_code_2017 s ON m.新代码 s.代码 SET b.门类代码 SUBSTRING(s.代码, 1, 1), b.大类代码 SUBSTRING(s.代码, 2, 2), b.中类代码 SUBSTRING(s.代码, 2, 3), b.小类代码 SUBSTRING(s.代码, 2, 4), b.行业名称_标准 s.名称 WHERE b.数据年份 BETWEEN 2012 AND 2017;这里的UPDATE语句会把业务表的2011版代码先映射到2017版。如果你的业务表数据跨度超过十年建议先统一到2011版再映射到2017版分两步走避免跳过中间版本导致映射偏差。第三步验证数据质量看看有没有仍然没关联上的代码SELECT b.行业代码, b.数据年份, COUNT(*) FROM 业务表 b LEFT JOIN dim_industry_mapping m ON b.行业代码 m.旧代码 WHERE m.旧代码 IS NULL GROUP BY b.行业代码, b.数据年份;如果这个查询有结果说明业务表里存在三个版本标准以外的代码很可能已经是2017版无需映射也可能是录入错误。把结果导出逐条人工确认。5. 避坑与常见问题导入失败、编码错乱、代码缺失的排错清单接下来这部分全是实战中踩过的坑按“现象→原因→解决”的方式整理。第3章里那几步看起来简单真跑起来你会发现坑一个接一个。5.1 SQL文件导入报“Unknown database”现象mysql std_code_2017.sql 命令执行后终端报错 ERROR 1049 (42000): Unknown database。原因这条命令隐含的语义是“把SQL文件应用到当前连接的默认数据库”但如果你没在命令里指定库名而当前连接又没有默认库MySQL不知道把表建到哪里。大部分图形化客户端会自动选中当前操作的库所以平时没遇到这个问题一旦换命令行就翻车。解决导入命令里显式指定库名并且先确保库已存在mysql -uroot -p dim --default-character-setutf8mb4 std_code_2017.sql5.2 导入后中文全部变成问号现象执行SELECT查询名称列显示一堆“???”比如“农、林、牧、渔业”变成“?????????”。原因SQL文件里的中文以UTF8编码存储但导入时客户端和服务器之间的连接字符集不是UTF8导致中文字节被当成单字节字符解析。还有一种可能是表结构里字符集被建成了latin1而不是utf8mb4。解决导入时增加--default-character-setutf8mb4同时确认目标表字符集是utf8mb4SHOW CREATE TABLE std_code_2017;如果建表语句里没有DEFAULT CHARSETutf8mb4用下面的语句转换ALTER TABLE std_code_2017 CONVERT TO CHARACTER SET utf8mb4;5.3 三个表行数差异巨大怀疑文件是不是缺数据现象std_code_2002.sql导入后只有一千多行std_code_2017.sql有两千多行有人会觉得2002版数据不全。原因这是标准本身的变化不是文件问题。2002版的小类数量本来就少于2017版。每一版新增行业的数量与当时的统计覆盖范围相关比如2002版里没有“互联网零售”这类类目自然少一行。解决不要用行数多少判断文件完整性。正确的验证方式是抽几个已知代码出来查比如A0111在三个版本里都存在查出来都有就是对的再抽几个2017版新增的行业代码在2002版里查不到属于正常。5.4 业务表里的四位代码JOIN不上现象业务表里存的是“0111”这种四位数字代码表里存的是“A0111”两边直接JOIN结果为空或者错位。原因行业分类标准的完整代码必须是“字母四位数字”业务表里存的是省略门类字母的简写。如果业务表本身没有额外字段记录门类你无法确定“0111”到底属于哪个门类——有可能是A0111也可能是其他门类下的0111比如C0111。解决不要盲目用CONCAT(A, 代码)去JOIN。先看业务表里有没有门类字段如果有就用完整代码JOIN如果没有先统计一下业务表里出现的四位代码在哪个门类下唯一存在SELECT SUBSTRING(代码, 2, 4) AS 四位代码, COUNT(DISTINCT SUBSTRING(代码, 1, 1)) AS 门类数 FROM std_code_2017 GROUP BY SUBSTRING(代码, 2, 4) HAVING 门类数 1;如果门类数大于1的代码很少可以对这些代码单独人工确认门类如果很多说明业务表的存储约定就是“默认门类四位代码”你只能找业务方要门类对照说明。5.5 名称里的间隔符号不一致导致匹配失败现象用名称列做相似度匹配时明明两个版本的名称看着一模一样但REPLACE或LIKE就是匹配不上。原因肉眼看到的间隔符可能是同一个中点符号但有的文件里用的是中文全角符号“·”U00B7有的是西文句号甚至可能是两个不同Unicode码位的点。SQL文件统一后一般没有这个问题但如果你从其他渠道拿过补充数据很容易混入不同的符号。解决做名称清洗时先把所有间隔符统一成一种UPDATE 业务表 SET 行业名称 REPLACE(REPLACE(REPLACE(行业名称, ·, |), ., |), 、, |);把中文的点、西文的点、顿号都替换成管道符再做匹配。这个操作要在副本表上做避免污染原始数据。6. 进阶用法把三版数据做成常驻映射视图半年省下两天清洗时间如果这个行业代码表只是临时用一次前面的内容已经够用。但如果你所在的数据仓库每个季度都要做行业维度统计我建议把三版代码做成常驻视图顺便把映射关系固化下来。半年之后回头再看这个初始搭建成本会帮你省掉大把手工对码时间。6.1 建一张长期维护的行业维度宽表宽表的结构建议包含标准代码、标准名称、门类名称、大类名称、中类名称、小类名称。这样查询时不用每次都做字符串截取CREATE TABLE dim_industry_2017 AS SELECT s.代码 AS 标准代码, s.名称 AS 标准名称, SUBSTRING_INDEX(s.名称, ·, 1) AS 门类名称, SUBSTRING_INDEX(SUBSTRING_INDEX(s.名称, ·, 2), ·, -1) AS 大类名称, SUBSTRING_INDEX(SUBSTRING_INDEX(s.名称, ·, 3), ·, -1) AS 中类名称, SUBSTRING_INDEX(SUBSTRING_INDEX(s.名称, ·, 4), ·, -1) AS 小类名称 FROM std_code_2017 s;逻辑说明SUBSTRING_INDEX(字符串, 分隔符, n) 取第n个分隔符之前的部分再配合-1取倒数第一个就能逐层拆出名称的每一段。这种方法比SUBSTRING按位置截取更稳因为门类名称的字节长度不固定比如“农、林、牧、渔业”是7个字符而“制造业”只有3个字符按位置截取容易错位。参数说明如果你要拆2011版或2002版只需要把FROM后面的表名换成std_code_2011或std_code_2002其他逻辑不变。这就是三个表结构一致带来的好处。6.2 每日增量任务里加一个自动校验步骤宽表建好之后把第5章里的“未匹配代码查询”做成定时任务每天扫描业务表里新增的行业代码发现陌生代码就自动写入一张待人工确认表INSERT INTO dim_industry_unknown (行业代码, 数据年份, 出现次数, 发现时间) SELECT b.行业代码, b.数据年份, COUNT(*), NOW() FROM 业务表 b LEFT JOIN dim_industry_2017 s ON b.行业代码 s.标准代码 WHERE s.标准代码 IS NULL GROUP BY b.行业代码, b.数据年份 ON DUPLICATE KEY UPDATE 出现次数 VALUES(出现次数);这个INSERT会不断累积新的未知代码运维人员只需要每周看一次待确认表把新的映射关系补进dim_industry_mapping而不是每次发现问题都临时翻标准。从那以后我每次搭建行业维度数据都会先建好这张校验表再开始业务清洗省下的手工对码时间远超过建表那半小时。希望这篇文章的整理过程能帮到你少走我当年踩过的那些坑。本文还有配套的精品资源点击获取