濟行業(yè)分類SQL文件：導入、跨版本映射與ETL清洗實戰(zhàn))
簡介本資源面向從事大數(shù)據(jù)清洗與行業(yè)維度標準化的技術(shù)人員提供2002、2011、2017三個年度的國民經(jīng)濟行業(yè)分類與代碼MySQL數(shù)據(jù)文件對應GB/T4754-2002、GB/T4754-2011、GB/T4754-2017三版國家標準。每個代碼均按“門類·大類·中類·小類”四級結(jié)構(gòu)組織例如“A0111”對應“農(nóng)、林、牧、漁業(yè)·農(nóng)業(yè)·谷物及其他作物的種植·谷物的種植”便于直接入庫做行業(yè)映射與口徑統(tǒng)一。壓縮包共3個文件均為sql腳本整體約44KB體積輕便導入后即可用于數(shù)據(jù)倉庫維表構(gòu)建、行業(yè)代碼轉(zhuǎn)換與歷史版本對照。已有3034人學習下載適合需要跨年度行業(yè)分類對齊、做數(shù)據(jù)標準化與清洗的工程師參考使用。1. 三套國標行業(yè)代碼躺在三個 SQL 文件里到底怎么用做數(shù)據(jù)清洗的人多半遇到過這種場面業(yè)務方丟來一張企業(yè)表行業(yè)字段寫的是「農(nóng)、林、牧、漁業(yè)·農(nóng)業(yè)·谷物及其他作物的種植·谷物的種植」這種長串中文或者干脆是A0111這種帶字母前綴的編碼讓你按行業(yè)維度做聚合。你手上沒有權(quán)威的對照表只能靠LIKE硬匹配結(jié)果「農(nóng)業(yè)」和「農(nóng)、林、牧、漁業(yè)」混在一起統(tǒng)計口徑直接崩掉。這份2002_2011_2017國民經(jīng)濟行業(yè)分類與代碼mysql數(shù)據(jù)四級分類文件.rar解決的就是這件事——它把 GB/T4754 三個版本2002、2011、2017的國民經(jīng)濟行業(yè)分類與代碼整理成了可直接導入 MySQL 的 SQL 文件每個版本一個std_code_xxxx.sql代碼按「門類·大類·中類·小類」四級結(jié)構(gòu)組織第一列是形如A0111的編碼第二列是對應的四級中文路徑。適合做行業(yè)維度清洗、標準化、映射對齊的大數(shù)據(jù)開發(fā)和數(shù)據(jù)治理人員尤其是需要跨年份做口徑統(tǒng)一的場景。2. 拆開壓縮包三個 SQL 文件的結(jié)構(gòu)與字段設計2.1 四級分類的編碼邏輯國民經(jīng)濟行業(yè)分類采用層級編碼GB/T4754 的編碼規(guī)則是「門類用一位字母大類用兩位數(shù)字中類用三位數(shù)字小類用四位數(shù)字」。以摘要里給的例子拆解2002 0111 A0111 農(nóng)、林、牧、漁業(yè)·農(nóng)業(yè)·谷物及其他作物的種植·谷物的種植2002版本年份標識這條記錄屬于哪一版國標0111純數(shù)字編碼四位對應小類層級A0111帶門類字母前綴的完整編碼A是門類農(nóng)、林、牧、漁業(yè)0111是往下三級的數(shù)字最后一列用·分隔的四級中文路徑依次是門類、大類、中類、小類這種設計的好處是你既能用A0111做精確匹配也能用0111做跨版本的數(shù)字對齊還能把中文路徑SPLIT開分別拿到門類名、大類名、中類名、小類名。三個文件結(jié)構(gòu)一致意味著你可以把三張表UNION起來做跨年份分析。2.2 導入前的表結(jié)構(gòu)確認SQL 文件是純INSERT還是帶CREATE TABLE直接決定你怎么導入。常見做法是先看文件頭部# 看 SQL 文件開頭 20 行確認有沒有建表語句 head -n 20 std_code_2017.sql # 統(tǒng)計 INSERT 行數(shù)估算數(shù)據(jù)量 grep -c INSERT INTO std_code_2017.sql如果文件里已經(jīng)帶了CREATE TABLE直接source導入即可如果只有INSERT你得先自己建表。我一般會按下面這個結(jié)構(gòu)建字段類型留足余量CREATE TABLE std_code_2017 ( id INT AUTO_INCREMENT PRIMARY KEY, year SMALLINT NOT NULL COMMENT 版本年份, code_num VARCHAR(8) NOT NULL COMMENT 純數(shù)字編碼如0111, code_full VARCHAR(16) NOT NULL COMMENT 帶門類前綴編碼如A0111, name_path VARCHAR(255) NOT NULL COMMENT 四級中文路徑·分隔, KEY idx_code_full (code_full), KEY idx_code_num (code_num) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT2017版國民經(jīng)濟行業(yè)分類;code_full和code_num都建索引因為實際查詢里這兩種匹配方式都會用到。name_path給到 255 是因為四級中文路徑最長可能超過 100 字符留足空間避免截斷。字符集用utf8mb4中文路徑里偶爾會有生僻字utf8三字節(jié)可能不夠。2.3 導入命令與驗證建好表之后導入mysql -u root -p your_database std_code_2017.sql導入完必須驗證行數(shù)和層級完整性不然數(shù)據(jù)缺了你都不知道-- 看總行數(shù) SELECT COUNT(*) FROM std_code_2017; -- 看門類有幾個正常應該是 A~T 共 20 個左右 SELECT LEFT(code_full, 1) AS menlei, COUNT(*) FROM std_code_2017 GROUP BY menlei ORDER BY menlei; -- 抽查一條四級路徑是否完整按·分隔應該有4段 SELECT code_full, name_path, LENGTH(name_path) - LENGTH(REPLACE(name_path, ·, )) 1 AS seg_count FROM std_code_2017 WHERE LENGTH(name_path) - LENGTH(REPLACE(name_path, ·, )) 1 4 LIMIT 10;最后那條查詢是關(guān)鍵如果seg_count不等于 4說明這條記錄的中文路徑層級不完整可能是數(shù)據(jù)本身的問題也可能是導入時被截斷了。正常情況應該返回空結(jié)果。這一步能幫你提前發(fā)現(xiàn)臟數(shù)據(jù)別等到業(yè)務查詢出錯了才回頭查。3. 跨版本映射2002 到 2017 的行業(yè)代碼怎么對齊3.1 為什么不能直接 JOIN三個版本的行業(yè)分類不是簡單的一對一關(guān)系。2011 版對 2002 版做了大量拆分和合并2017 版又對 2011 版做了調(diào)整。比如某個行業(yè)在 2002 版是一個小類到 2011 版被拆成兩個到 2017 版又合并回來。直接JOIN ON code_num會丟數(shù)據(jù)或者產(chǎn)生笛卡爾積。常見做法是建一張映射表人工維護版本間的對應關(guān)系。但在這份資源的基礎上你可以先用中文路徑做模糊對齊把能自動匹配的先匹配上剩下的再人工處理-- 用中文路徑精確匹配找出三個版本都存在的行業(yè) SELECT a.code_full AS code_2002, b.code_full AS code_2011, c.code_full AS code_2017, a.name_path FROM std_code_2002 a JOIN std_code_2011 b ON a.name_path b.name_path JOIN std_code_2017 c ON a.name_path c.name_path;這條查詢能跑出多少條取決于三個版本之間有多少行業(yè)名稱完全沒變。實際跑下來通常只有一部分能精確匹配剩下的需要按門類或大類做降級匹配。3.2 降級匹配策略當小類對不上時退到大類或中類層級做匹配。用SUBSTRING截取編碼前綴-- 按大類前3位數(shù)字匹配找出2002和2017在同一大類下的行業(yè) SELECT a.code_full AS code_2002, a.name_path AS name_2002, c.code_full AS code_2017, c.name_path AS name_2017 FROM std_code_2002 a JOIN std_code_2017 c ON SUBSTRING(a.code_num, 1, 3) SUBSTRING(c.code_num, 1, 3) WHERE a.name_path c.name_path LIMIT 50;SUBSTRING(code_num, 1, 3)取的是中類層級。這樣匹配出來的結(jié)果會有多對多的情況需要業(yè)務方確認口徑。我一般會把匹配結(jié)果導出成 CSV讓業(yè)務方標注哪些是可以接受的映射哪些需要人工指定。3.3 用臨時表固化映射關(guān)系匹配結(jié)果確認后建一張映射表存下來后續(xù) ETL 直接查這張表CREATE TABLE industry_mapping ( id INT AUTO_INCREMENT PRIMARY KEY, code_2002 VARCHAR(16), code_2011 VARCHAR(16), code_2017 VARCHAR(16), match_level TINYINT COMMENT 1小類精確 2中類 3大類, remark VARCHAR(255) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 把精確匹配的結(jié)果灌進去 INSERT INTO industry_mapping (code_2002, code_2011, code_2017, match_level) SELECT a.code_full, b.code_full, c.code_full, 1 FROM std_code_2002 a JOIN std_code_2011 b ON a.name_path b.name_path JOIN std_code_2017 c ON a.name_path c.name_path;match_level字段很重要它告訴你這條映射的可靠程度。做報表時如果只想要高置信度的數(shù)據(jù)加個WHERE match_level 1就行。remark留給人工補充說明比如「2011版拆分自2002版XX行業(yè)」這種備注。注意跨版本映射沒有百分之百自動化的方案國標調(diào)整本身就有業(yè)務判斷成分。這份資源提供的是標準代碼底座映射邏輯得結(jié)合你的業(yè)務場景來定。4. 避坑與排查導入和查詢時最容易翻車的幾個點4.1 中文亂碼現(xiàn)象是查詢出來全是問號現(xiàn)象導入后SELECT出來的中文路徑顯示為????或者亂碼字符。原因SQL 文件的編碼和數(shù)據(jù)庫、連接字符集不一致。常見情況是文件是utf8編碼但 MySQL 服務端默認latin1或者客戶端連接沒指定字符集。解決先確認文件編碼file std_code_2017.sql看輸出。如果是 UTF-8導入時顯式指定mysql -u root -p --default-character-setutf8mb4 your_database std_code_2017.sql建表時也要確認DEFAULT CHARSETutf8mb4。如果已經(jīng)導入錯了TRUNCATE TABLE重來比修數(shù)據(jù)快。4.2 編碼前綴字母大小寫不一致現(xiàn)象WHERE code_full a0111查不到數(shù)據(jù)但WHERE code_full A0111能查到。原因MySQL 默認排序規(guī)則utf8mb4_general_ci是大小寫不敏感的但如果你建表時用了utf8mb4_bin或者查詢時用了BINARY關(guān)鍵字就會變成大小寫敏感。另外有些 SQL 文件里門類字母可能混用了大小寫。解決統(tǒng)一用大寫。建表時排序規(guī)則用utf8mb4_general_ci查詢時用UPPER(code_full) UPPER(a0111)做兼容。更穩(wěn)妥的做法是在 ETL 層就把編碼統(tǒng)一轉(zhuǎn)成大寫再入庫。4.3 四級路徑分隔符不統(tǒng)一現(xiàn)象按·分隔取第四級時有些記錄取出來是空或者多了一段。原因不同年份的文件可能用了不同的分隔符比如 2002 版用·2017 版混用了·和-?；蛘吣承┬袠I(yè)名稱本身包含·字符。解決導入前先檢查分隔符一致性SELECT DISTINCT SUBSTRING(name_path, LOCATE(·, name_path) - 1, 1) AS sep_check FROM std_code_2017 LIMIT 5;如果發(fā)現(xiàn)混用在 ETL 層統(tǒng)一替換。行業(yè)名稱本身含·的情況比較少見但一旦出現(xiàn)就會導致SPLIT結(jié)果錯位需要人工修正那幾條記錄。4.4 導入大文件時超時或內(nèi)存溢出現(xiàn)象source導入時卡住或者報MySQL server has gone away。原因SQL 文件太大單條INSERT語句過長超過了max_allowed_packet限制。解決臨時調(diào)大參數(shù)再導入SET GLOBAL max_allowed_packet 256 * 1024 * 1024; SET GLOBAL net_read_timeout 600;如果文件里是逐條INSERT也可以考慮用mysqlimport或者先把 SQL 拆成多個小文件分批導入。導入完成后把參數(shù)改回去別一直開著。4.5 版本年份字段缺失或錯誤現(xiàn)象三張表UNION之后發(fā)現(xiàn)某些記錄的年份對不上或者年份字段是空的。原因SQL 文件里可能沒有顯式的年份列年份信息只體現(xiàn)在文件名上。如果你建表時沒加year字段或者導入時沒填就會丟版本信息。解決導入時手動補年份。如果文件里沒有年份列可以在導入后UPDATEALTER TABLE std_code_2002 ADD COLUMN year SMALLINT DEFAULT 2002; ALTER TABLE std_code_2011 ADD COLUMN year SMALLINT DEFAULT 2011; ALTER TABLE std_code_2017 ADD COLUMN year SMALLINT DEFAULT 2017;或者建表時就帶上year字段導入時用LOAD DATA指定。別依賴文件名來區(qū)分版本數(shù)據(jù)落到表里之后文件名就沒意義了。5. 把行業(yè)代碼接進 ETL一個可復用的清洗函數(shù)5.1 用存儲過程做行業(yè)名稱標準化實際清洗時業(yè)務表里的行業(yè)字段往往是自由文本需要映射到標準代碼。寫一個存儲過程輸入中文關(guān)鍵詞返回最匹配的標準編碼DELIMITER // CREATE PROCEDURE match_industry_code( IN input_text VARCHAR(255), IN ver_year SMALLINT, OUT out_code VARCHAR(16), OUT out_path VARCHAR(255) ) BEGIN DECLARE tbl_name VARCHAR(32); SET tbl_name CONCAT(std_code_, ver_year); -- 優(yōu)先精確匹配完整路徑 SELECT code_full, name_path INTO out_code, out_path FROM std_code_2017 WHERE name_path input_text LIMIT 1; -- 精確匹配不到用 LIKE 做模糊匹配取最長匹配 IF out_code IS NULL THEN SELECT code_full, name_path INTO out_code, out_path FROM std_code_2017 WHERE name_path LIKE CONCAT(%, input_text, %) ORDER BY LENGTH(name_path) DESC LIMIT 1; END IF; END // DELIMITER ;調(diào)用方式CALL match_industry_code(谷物的種植, 2017, code, path); SELECT code, path;這個存儲過程的核心邏輯是「先精確后模糊模糊匹配取最長路徑」。ORDER BY LENGTH(name_path) DESC保證匹配到的是最具體的那個行業(yè)而不是籠統(tǒng)的門類。ver_year參數(shù)目前只用來拼表名實際查詢里我寫死了std_code_2017你可以根據(jù)需求改成動態(tài) SQL。5.2 批量清洗的 SQL 寫法如果業(yè)務表有幾千條記錄要清洗逐條調(diào)存儲過程太慢。用JOIN批量做-- 假設 biz_company 是業(yè)務表industry_name 是自由文本行業(yè)字段 UPDATE biz_company b JOIN std_code_2017 s ON b.industry_name s.name_path SET b.industry_code s.code_full, b.industry_std s.name_path WHERE b.industry_code IS NULL;先跑精確匹配把能對上的更新掉。剩下的用模糊匹配UPDATE biz_company b JOIN ( SELECT b2.id, s.code_full, s.name_path FROM biz_company b2 JOIN std_code_2017 s ON b2.industry_name LIKE CONCAT(%, SUBSTRING_INDEX(s.name_path, ·, -1), %) WHERE b2.industry_code IS NULL ORDER BY LENGTH(s.name_path) DESC ) m ON b.id m.id SET b.industry_code m.code_full, b.industry_std m.name_path;SUBSTRING_INDEX(name_path, ·, -1)取的是四級路徑的最后一段也就是小類名稱。用業(yè)務字段去LIKE匹配小類名比匹配完整路徑的命中率高。ORDER BY LENGTH DESC保證優(yōu)先匹配更具體的小類。5.3 驗證清洗覆蓋率清洗完必須看覆蓋率不然你以為洗完了其實漏了一大半SELECT COUNT(*) AS total, SUM(CASE WHEN industry_code IS NOT NULL THEN 1 ELSE 0 END) AS matched, ROUND(SUM(CASE WHEN industry_code IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS match_rate FROM biz_company;match_rate低于 80% 就說明匹配邏輯有問題要么是業(yè)務字段太臟要么是標準表里沒有對應行業(yè)。把沒匹配上的記錄導出來看看SELECT DISTINCT industry_name FROM biz_company WHERE industry_code IS NULL LIMIT 100;這些就是需要人工處理的硬骨頭。我一般會把它們丟給業(yè)務方確認而不是自己硬猜。提示清洗函數(shù)和批量 SQL 建議先在測試庫跑一遍確認匹配率和數(shù)據(jù)質(zhì)量后再上生產(chǎn)。行業(yè)代碼一旦寫錯下游的報表和模型全跟著錯。6. 版本差異比對與增量更新技巧三個版本之間的差異不只是代碼變了分類的粒度也在變。2017 版相比 2011 版小類數(shù)量有增減部分行業(yè)被重新歸類。做跨年分析時如果直接用 2017 版的分類去套 2002 年的數(shù)據(jù)口徑會對不上。我一般會先跑一個版本差異統(tǒng)計看看三個版本各有多少個門類、大類、中類、小類SELECT 2002 AS ver, COUNT(DISTINCT LEFT(code_full,1)) AS menlei, COUNT(DISTINCT SUBSTRING(code_num,1,2)) AS dalei, COUNT(DISTINCT SUBSTRING(code_num,1,3)) AS zhonglei, COUNT(*) AS xiaolei FROM std_code_2002 UNION ALL SELECT 2011, COUNT(DISTINCT LEFT(code_full,1)), COUNT(DISTINCT SUBSTRING(code_num,1,2)), COUNT(DISTINCT SUBSTRING(code_num,1,3)), COUNT(*) FROM std_code_2011 UNION ALL SELECT 2017, COUNT(DISTINCT LEFT(code_full,1)), COUNT(DISTINCT SUBSTRING(code_num,1,2)), COUNT(DISTINCT SUBSTRING(code_num,1,3)), COUNT(*) FROM std_code_2017;這個查詢能讓你一眼看出三個版本的分類粒度變化。如果 2017 版的小類數(shù)量比 2002 版多了不少說明行業(yè)細分程度提高了跨年對比時要注意口徑膨脹的問題。另一個實用技巧是找出「只在某一個版本里存在」的行業(yè)代碼-- 找出2017版有但2002版沒有的行業(yè) SELECT code_full, name_path FROM std_code_2017 WHERE code_full NOT IN (SELECT code_full FROM std_code_2002) ORDER BY code_full;這些就是新增行業(yè)做歷史數(shù)據(jù)回溯時得特別標注。反過來2002 版有但 2017 版沒有的就是被合并或刪除的行業(yè)。把這兩個列表導出來就是一份版本遷移的差異清單。增量更新時如果國標出了新版本你只需要按同樣的表結(jié)構(gòu)建一張新表導入新 SQL 文件然后在映射表里補充新版本的對應關(guān)系。已有的映射記錄不用動只加新版本列就行。我習慣在映射表里加一個update_time字段記錄每條映射最后確認的時間方便追溯。從那以后我每次拿到新的國標代碼文件都會先跑一遍層級完整性校驗和版本差異統(tǒng)計確認數(shù)據(jù)沒問題再往生產(chǎn)庫導。這個習慣幫我擋掉過好幾次因為文件本身缺行導致的口徑錯誤。希望幫到你。本文還有配套的精品資源點擊獲取 三版國民經(jīng)濟行業(yè)分類SQL文件：導入、跨版本映射與ETL清洗實戰(zhàn)
簡介本資源面向從事大數(shù)據(jù)清洗與行業(yè)維度標準化的技術(shù)人員提供2002、2011、2017三個年度的國民經(jīng)濟行業(yè)分類與代碼MySQL數(shù)據(jù)文件對應GB/T4754-2002、GB/T4754-2011、GB/T4754-2017三版國家標準。每個代碼均按“門類·大類·中類·小類”四級結(jié)構(gòu)組織例如“A0111”對應“農(nóng)、林、牧、漁業(yè)·農(nóng)業(yè)·谷物及其他作物的種植·谷物的種植”便于直接入庫做行業(yè)映射與口徑統(tǒng)一。壓縮包共3個文件均為sql腳本整體約44KB體積輕便導入后即可用于數(shù)據(jù)倉庫維表構(gòu)建、行業(yè)代碼轉(zhuǎn)換與歷史版本對照。已有3034人學習下載適合需要跨年度行業(yè)分類對齊、做數(shù)據(jù)標準化與清洗的工程師參考使用。1. 三套國標行業(yè)代碼躺在三個 SQL 文件里到底怎么用做數(shù)據(jù)清洗的人多半遇到過這種場面業(yè)務方丟來一張企業(yè)表行業(yè)字段寫的是「農(nóng)、林、牧、漁業(yè)·農(nóng)業(yè)·谷物及其他作物的種植·谷物的種植」這種長串中文或者干脆是A0111這種帶字母前綴的編碼讓你按行業(yè)維度做聚合。你手上沒有權(quán)威的對照表只能靠LIKE硬匹配結(jié)果「農(nóng)業(yè)」和「農(nóng)、林、牧、漁業(yè)」混在一起統(tǒng)計口徑直接崩掉。這份2002_2011_2017國民經(jīng)濟行業(yè)分類與代碼mysql數(shù)據(jù)四級分類文件.rar解決的就是這件事——它把 GB/T4754 三個版本2002、2011、2017的國民經(jīng)濟行業(yè)分類與代碼整理成了可直接導入 MySQL 的 SQL 文件每個版本一個std_code_xxxx.sql代碼按「門類·大類·中類·小類」四級結(jié)構(gòu)組織第一列是形如A0111的編碼第二列是對應的四級中文路徑。適合做行業(yè)維度清洗、標準化、映射對齊的大數(shù)據(jù)開發(fā)和數(shù)據(jù)治理人員尤其是需要跨年份做口徑統(tǒng)一的場景。2. 拆開壓縮包三個 SQL 文件的結(jié)構(gòu)與字段設計2.1 四級分類的編碼邏輯國民經(jīng)濟行業(yè)分類采用層級編碼GB/T4754 的編碼規(guī)則是「門類用一位字母大類用兩位數(shù)字中類用三位數(shù)字小類用四位數(shù)字」。以摘要里給的例子拆解2002 0111 A0111 農(nóng)、林、牧、漁業(yè)·農(nóng)業(yè)·谷物及其他作物的種植·谷物的種植2002版本年份標識這條記錄屬于哪一版國標0111純數(shù)字編碼四位對應小類層級A0111帶門類字母前綴的完整編碼A是門類農(nóng)、林、牧、漁業(yè)0111是往下三級的數(shù)字最后一列用·分隔的四級中文路徑依次是門類、大類、中類、小類這種設計的好處是你既能用A0111做精確匹配也能用0111做跨版本的數(shù)字對齊還能把中文路徑SPLIT開分別拿到門類名、大類名、中類名、小類名。三個文件結(jié)構(gòu)一致意味著你可以把三張表UNION起來做跨年份分析。2.2 導入前的表結(jié)構(gòu)確認SQL 文件是純INSERT還是帶CREATE TABLE直接決定你怎么導入。常見做法是先看文件頭部# 看 SQL 文件開頭 20 行確認有沒有建表語句 head -n 20 std_code_2017.sql # 統(tǒng)計 INSERT 行數(shù)估算數(shù)據(jù)量 grep -c INSERT INTO std_code_2017.sql如果文件里已經(jīng)帶了CREATE TABLE直接source導入即可如果只有INSERT你得先自己建表。我一般會按下面這個結(jié)構(gòu)建字段類型留足余量CREATE TABLE std_code_2017 ( id INT AUTO_INCREMENT PRIMARY KEY, year SMALLINT NOT NULL COMMENT 版本年份, code_num VARCHAR(8) NOT NULL COMMENT 純數(shù)字編碼如0111, code_full VARCHAR(16) NOT NULL COMMENT 帶門類前綴編碼如A0111, name_path VARCHAR(255) NOT NULL COMMENT 四級中文路徑·分隔, KEY idx_code_full (code_full), KEY idx_code_num (code_num) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT2017版國民經(jīng)濟行業(yè)分類;code_full和code_num都建索引因為實際查詢里這兩種匹配方式都會用到。name_path給到 255 是因為四級中文路徑最長可能超過 100 字符留足空間避免截斷。字符集用utf8mb4中文路徑里偶爾會有生僻字utf8三字節(jié)可能不夠。2.3 導入命令與驗證建好表之后導入mysql -u root -p your_database std_code_2017.sql導入完必須驗證行數(shù)和層級完整性不然數(shù)據(jù)缺了你都不知道-- 看總行數(shù) SELECT COUNT(*) FROM std_code_2017; -- 看門類有幾個正常應該是 A~T 共 20 個左右 SELECT LEFT(code_full, 1) AS menlei, COUNT(*) FROM std_code_2017 GROUP BY menlei ORDER BY menlei; -- 抽查一條四級路徑是否完整按·分隔應該有4段 SELECT code_full, name_path, LENGTH(name_path) - LENGTH(REPLACE(name_path, ·, )) 1 AS seg_count FROM std_code_2017 WHERE LENGTH(name_path) - LENGTH(REPLACE(name_path, ·, )) 1 4 LIMIT 10;最后那條查詢是關(guān)鍵如果seg_count不等于 4說明這條記錄的中文路徑層級不完整可能是數(shù)據(jù)本身的問題也可能是導入時被截斷了。正常情況應該返回空結(jié)果。這一步能幫你提前發(fā)現(xiàn)臟數(shù)據(jù)別等到業(yè)務查詢出錯了才回頭查。3. 跨版本映射2002 到 2017 的行業(yè)代碼怎么對齊3.1 為什么不能直接 JOIN三個版本的行業(yè)分類不是簡單的一對一關(guān)系。2011 版對 2002 版做了大量拆分和合并2017 版又對 2011 版做了調(diào)整。比如某個行業(yè)在 2002 版是一個小類到 2011 版被拆成兩個到 2017 版又合并回來。直接JOIN ON code_num會丟數(shù)據(jù)或者產(chǎn)生笛卡爾積。常見做法是建一張映射表人工維護版本間的對應關(guān)系。但在這份資源的基礎上你可以先用中文路徑做模糊對齊把能自動匹配的先匹配上剩下的再人工處理-- 用中文路徑精確匹配找出三個版本都存在的行業(yè) SELECT a.code_full AS code_2002, b.code_full AS code_2011, c.code_full AS code_2017, a.name_path FROM std_code_2002 a JOIN std_code_2011 b ON a.name_path b.name_path JOIN std_code_2017 c ON a.name_path c.name_path;這條查詢能跑出多少條取決于三個版本之間有多少行業(yè)名稱完全沒變。實際跑下來通常只有一部分能精確匹配剩下的需要按門類或大類做降級匹配。3.2 降級匹配策略當小類對不上時退到大類或中類層級做匹配。用SUBSTRING截取編碼前綴-- 按大類前3位數(shù)字匹配找出2002和2017在同一大類下的行業(yè) SELECT a.code_full AS code_2002, a.name_path AS name_2002, c.code_full AS code_2017, c.name_path AS name_2017 FROM std_code_2002 a JOIN std_code_2017 c ON SUBSTRING(a.code_num, 1, 3) SUBSTRING(c.code_num, 1, 3) WHERE a.name_path c.name_path LIMIT 50;SUBSTRING(code_num, 1, 3)取的是中類層級。這樣匹配出來的結(jié)果會有多對多的情況需要業(yè)務方確認口徑。我一般會把匹配結(jié)果導出成 CSV讓業(yè)務方標注哪些是可以接受的映射哪些需要人工指定。3.3 用臨時表固化映射關(guān)系匹配結(jié)果確認后建一張映射表存下來后續(xù) ETL 直接查這張表CREATE TABLE industry_mapping ( id INT AUTO_INCREMENT PRIMARY KEY, code_2002 VARCHAR(16), code_2011 VARCHAR(16), code_2017 VARCHAR(16), match_level TINYINT COMMENT 1小類精確 2中類 3大類, remark VARCHAR(255) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 把精確匹配的結(jié)果灌進去 INSERT INTO industry_mapping (code_2002, code_2011, code_2017, match_level) SELECT a.code_full, b.code_full, c.code_full, 1 FROM std_code_2002 a JOIN std_code_2011 b ON a.name_path b.name_path JOIN std_code_2017 c ON a.name_path c.name_path;match_level字段很重要它告訴你這條映射的可靠程度。做報表時如果只想要高置信度的數(shù)據(jù)加個WHERE match_level 1就行。remark留給人工補充說明比如「2011版拆分自2002版XX行業(yè)」這種備注。注意跨版本映射沒有百分之百自動化的方案國標調(diào)整本身就有業(yè)務判斷成分。這份資源提供的是標準代碼底座映射邏輯得結(jié)合你的業(yè)務場景來定。4. 避坑與排查導入和查詢時最容易翻車的幾個點4.1 中文亂碼現(xiàn)象是查詢出來全是問號現(xiàn)象導入后SELECT出來的中文路徑顯示為????或者亂碼字符。原因SQL 文件的編碼和數(shù)據(jù)庫、連接字符集不一致。常見情況是文件是utf8編碼但 MySQL 服務端默認latin1或者客戶端連接沒指定字符集。解決先確認文件編碼file std_code_2017.sql看輸出。如果是 UTF-8導入時顯式指定mysql -u root -p --default-character-setutf8mb4 your_database std_code_2017.sql建表時也要確認DEFAULT CHARSETutf8mb4。如果已經(jīng)導入錯了TRUNCATE TABLE重來比修數(shù)據(jù)快。4.2 編碼前綴字母大小寫不一致現(xiàn)象WHERE code_full a0111查不到數(shù)據(jù)但WHERE code_full A0111能查到。原因MySQL 默認排序規(guī)則utf8mb4_general_ci是大小寫不敏感的但如果你建表時用了utf8mb4_bin或者查詢時用了BINARY關(guān)鍵字就會變成大小寫敏感。另外有些 SQL 文件里門類字母可能混用了大小寫。解決統(tǒng)一用大寫。建表時排序規(guī)則用utf8mb4_general_ci查詢時用UPPER(code_full) UPPER(a0111)做兼容。更穩(wěn)妥的做法是在 ETL 層就把編碼統(tǒng)一轉(zhuǎn)成大寫再入庫。4.3 四級路徑分隔符不統(tǒng)一現(xiàn)象按·分隔取第四級時有些記錄取出來是空或者多了一段。原因不同年份的文件可能用了不同的分隔符比如 2002 版用·2017 版混用了·和-?；蛘吣承┬袠I(yè)名稱本身包含·字符。解決導入前先檢查分隔符一致性SELECT DISTINCT SUBSTRING(name_path, LOCATE(·, name_path) - 1, 1) AS sep_check FROM std_code_2017 LIMIT 5;如果發(fā)現(xiàn)混用在 ETL 層統(tǒng)一替換。行業(yè)名稱本身含·的情況比較少見但一旦出現(xiàn)就會導致SPLIT結(jié)果錯位需要人工修正那幾條記錄。4.4 導入大文件時超時或內(nèi)存溢出現(xiàn)象source導入時卡住或者報MySQL server has gone away。原因SQL 文件太大單條INSERT語句過長超過了max_allowed_packet限制。解決臨時調(diào)大參數(shù)再導入SET GLOBAL max_allowed_packet 256 * 1024 * 1024; SET GLOBAL net_read_timeout 600;如果文件里是逐條INSERT也可以考慮用mysqlimport或者先把 SQL 拆成多個小文件分批導入。導入完成后把參數(shù)改回去別一直開著。4.5 版本年份字段缺失或錯誤現(xiàn)象三張表UNION之后發(fā)現(xiàn)某些記錄的年份對不上或者年份字段是空的。原因SQL 文件里可能沒有顯式的年份列年份信息只體現(xiàn)在文件名上。如果你建表時沒加year字段或者導入時沒填就會丟版本信息。解決導入時手動補年份。如果文件里沒有年份列可以在導入后UPDATEALTER TABLE std_code_2002 ADD COLUMN year SMALLINT DEFAULT 2002; ALTER TABLE std_code_2011 ADD COLUMN year SMALLINT DEFAULT 2011; ALTER TABLE std_code_2017 ADD COLUMN year SMALLINT DEFAULT 2017;或者建表時就帶上year字段導入時用LOAD DATA指定。別依賴文件名來區(qū)分版本數(shù)據(jù)落到表里之后文件名就沒意義了。5. 把行業(yè)代碼接進 ETL一個可復用的清洗函數(shù)5.1 用存儲過程做行業(yè)名稱標準化實際清洗時業(yè)務表里的行業(yè)字段往往是自由文本需要映射到標準代碼。寫一個存儲過程輸入中文關(guān)鍵詞返回最匹配的標準編碼DELIMITER // CREATE PROCEDURE match_industry_code( IN input_text VARCHAR(255), IN ver_year SMALLINT, OUT out_code VARCHAR(16), OUT out_path VARCHAR(255) ) BEGIN DECLARE tbl_name VARCHAR(32); SET tbl_name CONCAT(std_code_, ver_year); -- 優(yōu)先精確匹配完整路徑 SELECT code_full, name_path INTO out_code, out_path FROM std_code_2017 WHERE name_path input_text LIMIT 1; -- 精確匹配不到用 LIKE 做模糊匹配取最長匹配 IF out_code IS NULL THEN SELECT code_full, name_path INTO out_code, out_path FROM std_code_2017 WHERE name_path LIKE CONCAT(%, input_text, %) ORDER BY LENGTH(name_path) DESC LIMIT 1; END IF; END // DELIMITER ;調(diào)用方式CALL match_industry_code(谷物的種植, 2017, code, path); SELECT code, path;這個存儲過程的核心邏輯是「先精確后模糊模糊匹配取最長路徑」。ORDER BY LENGTH(name_path) DESC保證匹配到的是最具體的那個行業(yè)而不是籠統(tǒng)的門類。ver_year參數(shù)目前只用來拼表名實際查詢里我寫死了std_code_2017你可以根據(jù)需求改成動態(tài) SQL。5.2 批量清洗的 SQL 寫法如果業(yè)務表有幾千條記錄要清洗逐條調(diào)存儲過程太慢。用JOIN批量做-- 假設 biz_company 是業(yè)務表industry_name 是自由文本行業(yè)字段 UPDATE biz_company b JOIN std_code_2017 s ON b.industry_name s.name_path SET b.industry_code s.code_full, b.industry_std s.name_path WHERE b.industry_code IS NULL;先跑精確匹配把能對上的更新掉。剩下的用模糊匹配UPDATE biz_company b JOIN ( SELECT b2.id, s.code_full, s.name_path FROM biz_company b2 JOIN std_code_2017 s ON b2.industry_name LIKE CONCAT(%, SUBSTRING_INDEX(s.name_path, ·, -1), %) WHERE b2.industry_code IS NULL ORDER BY LENGTH(s.name_path) DESC ) m ON b.id m.id SET b.industry_code m.code_full, b.industry_std m.name_path;SUBSTRING_INDEX(name_path, ·, -1)取的是四級路徑的最后一段也就是小類名稱。用業(yè)務字段去LIKE匹配小類名比匹配完整路徑的命中率高。ORDER BY LENGTH DESC保證優(yōu)先匹配更具體的小類。5.3 驗證清洗覆蓋率清洗完必須看覆蓋率不然你以為洗完了其實漏了一大半SELECT COUNT(*) AS total, SUM(CASE WHEN industry_code IS NOT NULL THEN 1 ELSE 0 END) AS matched, ROUND(SUM(CASE WHEN industry_code IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS match_rate FROM biz_company;match_rate低于 80% 就說明匹配邏輯有問題要么是業(yè)務字段太臟要么是標準表里沒有對應行業(yè)。把沒匹配上的記錄導出來看看SELECT DISTINCT industry_name FROM biz_company WHERE industry_code IS NULL LIMIT 100;這些就是需要人工處理的硬骨頭。我一般會把它們丟給業(yè)務方確認而不是自己硬猜。提示清洗函數(shù)和批量 SQL 建議先在測試庫跑一遍確認匹配率和數(shù)據(jù)質(zhì)量后再上生產(chǎn)。行業(yè)代碼一旦寫錯下游的報表和模型全跟著錯。6. 版本差異比對與增量更新技巧三個版本之間的差異不只是代碼變了分類的粒度也在變。2017 版相比 2011 版小類數(shù)量有增減部分行業(yè)被重新歸類。做跨年分析時如果直接用 2017 版的分類去套 2002 年的數(shù)據(jù)口徑會對不上。我一般會先跑一個版本差異統(tǒng)計看看三個版本各有多少個門類、大類、中類、小類SELECT 2002 AS ver, COUNT(DISTINCT LEFT(code_full,1)) AS menlei, COUNT(DISTINCT SUBSTRING(code_num,1,2)) AS dalei, COUNT(DISTINCT SUBSTRING(code_num,1,3)) AS zhonglei, COUNT(*) AS xiaolei FROM std_code_2002 UNION ALL SELECT 2011, COUNT(DISTINCT LEFT(code_full,1)), COUNT(DISTINCT SUBSTRING(code_num,1,2)), COUNT(DISTINCT SUBSTRING(code_num,1,3)), COUNT(*) FROM std_code_2011 UNION ALL SELECT 2017, COUNT(DISTINCT LEFT(code_full,1)), COUNT(DISTINCT SUBSTRING(code_num,1,2)), COUNT(DISTINCT SUBSTRING(code_num,1,3)), COUNT(*) FROM std_code_2017;這個查詢能讓你一眼看出三個版本的分類粒度變化。如果 2017 版的小類數(shù)量比 2002 版多了不少說明行業(yè)細分程度提高了跨年對比時要注意口徑膨脹的問題。另一個實用技巧是找出「只在某一個版本里存在」的行業(yè)代碼-- 找出2017版有但2002版沒有的行業(yè) SELECT code_full, name_path FROM std_code_2017 WHERE code_full NOT IN (SELECT code_full FROM std_code_2002) ORDER BY code_full;這些就是新增行業(yè)做歷史數(shù)據(jù)回溯時得特別標注。反過來2002 版有但 2017 版沒有的就是被合并或刪除的行業(yè)。把這兩個列表導出來就是一份版本遷移的差異清單。增量更新時如果國標出了新版本你只需要按同樣的表結(jié)構(gòu)建一張新表導入新 SQL 文件然后在映射表里補充新版本的對應關(guān)系。已有的映射記錄不用動只加新版本列就行。我習慣在映射表里加一個update_time字段記錄每條映射最后確認的時間方便追溯。從那以后我每次拿到新的國標代碼文件都會先跑一遍層級完整性校驗和版本差異統(tǒng)計確認數(shù)據(jù)沒問題再往生產(chǎn)庫導。這個習慣幫我擋掉過好幾次因為文件本身缺行導致的口徑錯誤。希望幫到你。本文還有配套的精品資源點擊獲取