简介面向城市规划研究者、数据分析人员与投资决策者的城市分级数据包围绕2020年城市商业魅力排行榜将全国城市按经济规模、人口吸引力、创新能力与交通便利性等维度划入一至五线城市可用于区域对比、趋势研究与市场机会判断。包内共2个文件含一份xlsx表格与一份sql数据库脚本压缩包约17KB表格便于直接筛选查看各等级城市名单sql文件以自增id、城市名称、城市等级三字段建表方便导入数据库后做查询与统计分析。已有10404人学习下载说明该数据在同类资料中关注度较高。读者可据此快速获得完整的城市等级对照清单并借助SQL按等级检索、聚合与横向比较为政策研究、企业选址或个人分析提供可直接复用的基础数据。1. 城市等级划分表一份被低估的“脏数据”治理样本拿到「2020中国城市等级划分一二三四五线城市及排名excel表格及sql文件」这个标题很多人第一反应是找一份现成的表导进数据库就完事。真做过的人都知道这类城市分级数据落地时最麻烦的从来不是“有没有表”而是同一座城市在 Excel 里叫“XX市”、在业务系统里叫“XX”、在物流面单上又是拼音缩写一线到五线的划分口径还随榜单年份漂移。它本质上是一份典型的维表dimension table要解决的是地址标准化、区域归属和分级标签三件事。适合谁做用户地域分析、门店选址、区域运营报表、CRM 地址清洗的从业者。下面按“先立数据模型、再复现导入、再排坑、最后做校验”的顺序讲透新手能照着跑熟手能直接对参数和边界。2. 先把城市分级维表的数据模型立住字段、粒度与分级口径动手建表之前得先想清楚这张表到底服务什么查询。城市等级划分表看着简单实际至少承载三类需求按城市查等级点查、按等级聚合统计分组、按城市名做模糊匹配清洗地址关联。这三类需求对字段和索引的要求完全不同模型立错了后面导入再多数据也是返工。2.1 一张城市维表最少要有哪些字段我一般会把字段拆成四组标识组、名称组、层级组、分级组。标识组是主键和行政编码名称组放全称/简称/拼音层级组放省、市、区县和行政级别分级组放一线到五线的标签和排名。下面这张表是我常用的最小字段集Excel 列名和 SQL 列名一一对应方便导入脚本直接映射。字段名类型含义是否必填说明city_idINT代理主键是自增避免用城市名做主键admin_codeVARCHAR(12)行政区划编码是唯一做跨表关联的稳定键city_nameVARCHAR(64)城市全称是带“市/州/盟”后缀short_nameVARCHAR(64)简称否去掉后缀用于模糊匹配pinyinVARCHAR(128)全拼否地址清洗时做拼音回退provinceVARCHAR(64)所属省级是直辖市填自身city_levelTINYINT行政级别是1直辖市 2省会 3地级市 4县级市tierTINYINT商业分级是1到5对应一线到五线tier_rankINT分级内排名否同一 tier 内的序号data_yearSMALLINT数据年份是固定 2020防止多版本混入关键点在于admin_code和city_name的分工行政编码稳定、城市名会变撤县设区、改名所以关联一律走编码展示才用名称。tier用 TINYINT 而不是字符串是为了让“一线到五线”能直接做数值比较和排序省掉一堆 CASE WHEN。2.2 分级口径为什么必须先定死再导数据一线到五线的划分不是官方行政标准而是商业机构按经济活力、人口、消费等维度打的标签不同榜单差异不小。落地时最容易翻车的地方是同一份 Excel 里一线城市 4 个、新一线 15 个而业务方嘴里的“一线”可能只认 4 个。我的做法是在建表阶段就把口径写进注释和一张配置表而不是散落在代码里。常见做法是tier只保留 1 到 5 五档把“新一线”并入 2 档但在tier_rank里保留原始排名另加一个tier_label字段存原始榜单文字如“新一线城市”。这样既满足“一二三四五线”的粗粒度聚合又不丢原始信息。如果业务只要五档查询时直接GROUP BY tier如果要还原榜单读tier_label。这一步想清楚后面导入脚本的字段映射就不用反复改。2.3 主键与唯一约束怎么设才不返工我见过太多人拿city_name当主键结果遇到“市辖区重名”“同名县级市跨省”直接崩。正确姿势是city_id自增主键 admin_code唯一索引 (city_name, province)普通联合索引。唯一索引放在admin_code上导入时用INSERT ... ON DUPLICATE KEY UPDATE做幂等重复跑脚本不会产生脏数据。联合索引服务于“按城市名省”这种最常见的地址清洗查询比单列索引命中率高。提示如果拿不到可靠的行政区划编码宁可留空并加is_verified标记位也不要用城市名拼一个假编码否则后面关联全是坑。3. 从 Excel 到 SQL导入脚本、建表语句与幂等重跑模型定完就进入动手环节。这一章给的是能直接抄的建表语句和导入脚本Excel 到数据库的路径用 Python 打通参数和失败排查都写清楚。假设 Excel 文件名为city_tier_2020.xlsxsheet 名cities列名与 2.1 的字段表一致。3.1 建表 SQL 与索引设计先建表。字符集统一 utf8mb4避免城市名里的生僻字或少数民族地名乱码。tier和city_level都加 CHECK 约束MySQL 8.0 支持把非法值挡在入库前。CREATE TABLE dim_city_tier ( city_id INT AUTO_INCREMENT PRIMARY KEY, admin_code VARCHAR(12) NOT NULL COMMENT 行政区划编码稳定关联键, city_name VARCHAR(64) NOT NULL COMMENT 城市全称带市/州/盟后缀, short_name VARCHAR(64) DEFAULT NULL COMMENT 去后缀简称用于模糊匹配, pinyin VARCHAR(128) DEFAULT NULL COMMENT 全拼地址清洗回退用, province VARCHAR(64) NOT NULL COMMENT 所属省级直辖市填自身, city_level TINYINT NOT NULL COMMENT 1直辖市 2省会 3地级市 4县级市, tier TINYINT NOT NULL COMMENT 商业分级 1-5 对应一线到五线, tier_rank INT DEFAULT NULL COMMENT 同 tier 内排名, tier_label VARCHAR(32) DEFAULT NULL COMMENT 原始榜单文字如新一线城市, data_year SMALLINT NOT NULL DEFAULT 2020 COMMENT 数据年份, is_verified TINYINT NOT NULL DEFAULT 0 COMMENT 编码是否可信, UNIQUE KEY uk_admin_code (admin_code), KEY idx_name_prov (city_name, province), KEY idx_tier (tier, tier_rank), CONSTRAINT chk_tier CHECK (tier BETWEEN 1 AND 5), CONSTRAINT chk_level CHECK (city_level BETWEEN 1 AND 4) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT城市等级划分维表;uk_admin_code是幂等导入的基础idx_tier让“按等级聚合”走索引而不是全表扫。CHECK 约束看着多余实际能拦住 Excel 里手滑填的“0”或“6”省掉后面排查半天才发现是脏值。3.2 用 Python 把 Excel 读进来并做字段清洗导入脚本的核心不是“读进来”而是“读进来之前把脏值处理掉”。下面这段脚本做了四件事列名映射、去空格、简称生成、空值兜底。依赖pandas和openpyxlsqlalchemy负责写库。import pandas as pd from sqlalchemy import create_engine, text # 1. 读 Excel指定 sheet 和列名映射 df pd.read_excel(city_tier_2020.xlsx, sheet_namecities) col_map { 行政编码: admin_code, 城市: city_name, 省份: province, 行政级别: city_level, 分级: tier, 排名: tier_rank, 榜单文字: tier_label, } df df.rename(columnscol_map) # 2. 清洗去空格、统一后缀、生成简称 df[city_name] df[city_name].astype(str).str.strip() df[province] df[province].astype(str).str.strip() df[short_name] df[city_name].str.replace(r(市|州|盟|地区)$, , regexTrue) # 3. 空值兜底编码缺失标记未核验分级缺失直接丢弃 df[admin_code] df[admin_code].fillna().astype(str).str.strip() df[is_verified] (df[admin_code] ! ).astype(int) df df[df[tier].notna()].copy() df[tier] df[tier].astype(int) # 4. 幂等写入按 admin_code 冲突则更新 engine create_engine(mysqlpymysql://user:pwd127.0.0.1:3306/demo?charsetutf8mb4) with engine.begin() as conn: for _, row in df.iterrows(): conn.execute(text( INSERT INTO dim_city_tier (admin_code, city_name, short_name, province, city_level, tier, tier_rank, tier_label, data_year, is_verified) VALUES (:admin_code, :city_name, :short_name, :province, :city_level, :tier, :tier_rank, :tier_label, 2020, :is_verified) ON DUPLICATE KEY UPDATE city_nameVALUES(city_name), short_nameVALUES(short_name), provinceVALUES(province), city_levelVALUES(city_level), tierVALUES(tier), tier_rankVALUES(tier_rank), tier_labelVALUES(tier_label), is_verifiedVALUES(is_verified) ), row.to_dict())逻辑说明col_map把中文列名映射成英文避免脚本里到处写中文键short_name用正则去掉行政后缀是地址模糊匹配的关键is_verified把“编码缺失”显式化而不是悄悄填个假值。ON DUPLICATE KEY UPDATE保证脚本可以反复跑第二次跑不会插重复行只会刷新字段。参数说明create_engine里的charsetutf8mb4必须带否则生僻地名会截断data_year在 SQL 里写死 2020是因为标题锁定了年份如果以后要导多版本把它改成从 DataFrame 取列即可。逐行execute在几千行量级完全够用上万行建议换executemany或to_sql(methodmulti)。3.3 导入后必做的三条校验查询导完不校验等于没导。我固定跑三条 SQL总数对不对、分级分布合不合理、有没有重复城市名跨省。-- 1. 总行数与分级分布和 Excel 原始透视表对得上 SELECT tier, COUNT(*) AS cnt FROM dim_city_tier GROUP BY tier ORDER BY tier; -- 2. 同一城市名出现在多个省多半是县级市重名需人工确认 SELECT city_name, COUNT(DISTINCT province) AS prov_cnt FROM dim_city_tier GROUP BY city_name HAVING prov_cnt 1; -- 3. 编码未核验的记录导入后要单独跟进 SELECT COUNT(*) FROM dim_city_tier WHERE is_verified 0;第一条对不上说明 Excel 里有被过滤掉的行比如 tier 为空回看 3.2 的df[df[tier].notna()]第二条结果多说明简称或全称没统一得回到清洗步骤补规则第三条数量大说明编码来源不可靠这批数据只能做展示不能做关联。4. 城市分级数据落地避坑五条血泪经验这一章全是踩过的坑每条按“现象→原因→解决”写照着排能省不少时间。4.1 现象导入报 Duplicate entry脚本中断原因Excel 里同一城市出现两行比如榜单把某地级市和它的某个区都列了而admin_code唯一索引拦住了第二行。解决导入前先df.drop_duplicates(subset[admin_code], keeplast)并在日志里打印被去重的行人工确认保留哪条。别图省事直接INSERT IGNORE那会把真正的数据冲突也吞掉。4.2 现象按城市名关联匹配率只有六成原因业务表里存的是“XX”维表里是“XX市”或者维表简称规则没覆盖“自治州”“地区”。解决关联时用short_name做一级匹配匹配不上再用pinyin做二级回退两级都失败才落人工。简称正则要覆盖市|州|盟|地区|自治州别只写市。4.3 现象分级聚合结果和业务方口径对不上原因把“新一线”当成了独立一档或者把县级市算进了地级市。解决tier只保留 1 到 5新一线并入 2 档但tier_label保留原文city_level单独表达行政级别两个维度不要混用。聚合前先和业务方对齐“一线到底几个”把结论写进表注释。4.4 现象中文城市名入库变问号原因连接串没带charsetutf8mb4或者表/列字符集是 utf8三字节遇到四字节生僻字截断。解决建表、连接串、客户端三处字符集统一 utf8mb4导入后用SELECT city_name FROM dim_city_tier WHERE city_name LIKE %?%抽查。4.5 现象重跑脚本后排名错乱原因tier_rank在 Excel 里是文本型数字astype(int)前没处理空串导致部分行写成 0 或 NULL排序全乱。解决清洗阶段pd.to_numeric(df[tier_rank], errorscoerce)空值保留 NULL 而不是填 0排序时ORDER BY tier_rank IS NULL, tier_rank把空值排到最后。5. 用分级维表做地址标准化一个可复用的匹配函数与验收方法维表建好只是开始真正体现价值的是拿它去清洗业务里的脏地址。这一章给一个可复用的匹配函数再讲怎么验收匹配质量最后落到一个我常用的技巧上。5.1 三级匹配函数全称、简称、拼音回退匹配策略按精确度从高到低先全称精确匹配再简称匹配最后拼音前缀匹配。每级命中都打上match_level标记方便后续按置信度分流。import pandas as pd def match_city(raw: str, dim: pd.DataFrame) - dict: raw 为业务侧原始城市串dim 为维表 DataFrame s str(raw).strip() # 一级全称精确 hit dim[dim[city_name] s] if len(hit) 1: return {city_id: hit.iloc[0][city_id], match_level: exact} # 二级简称精确 hit dim[dim[short_name] s] if len(hit) 1: return {city_id: hit.iloc[0][city_id], match_level: short} # 三级拼音前缀取最长命中 cand dim[dim[pinyin].fillna().str.startswith(s.lower())] if len(cand) 1: best cand.sort_values(pinyin, keylambda x: x.str.len(), ascendingFalse).iloc[0] return {city_id: best[city_id], match_level: pinyin} return {city_id: None, match_level: miss}逻辑说明每级只在唯一命中时才返回多命中一律降级到下一级或落 miss避免“同名跨省”被错误合并。match_level是后续验收的分组维度。参数上拼音匹配用startswith而不是contains因为地址里常带“XX新区”“XX开发区”contains会误命中。5.2 匹配质量怎么验收分层抽样看命中率别只看总体命中率要按match_level分层看。我的验收标准是exact 占比应超过七成short 占两成左右pinyin 和 miss 合计不超过一成。pinyin 命中里如果出现大量“新区/开发区”被误配到主城区说明维表缺新区条目得补数据而不是改函数。抽样时重点看 miss 样本往往能反推出维表缺哪些县级市。5.3 一个我常用的技巧把分级标签做成可配置映射分级口径会随榜单年份变硬编码在 SQL 里迟早返工。我的习惯是单独建一张cfg_tier_mapping配置表存tier到业务标签的映射和生效年份查询时 JOIN 取标签。这样换年份只改配置不动主表回滚也有后悔药。验收时跑一遍SELECT tier, COUNT(*) FROM dim_city_tier GROUP BY tier和配置表对账数字对得上才算这次导入收工。这套流程我跑过好几版城市数据最深的教训是别信“一份表导进去就完事”城市分级数据的价值全在清洗和校验那几步前面偷的懒后面都会以匹配率对不上的形式还回来。希望帮到你。本文还有配套的精品资源点击获取