简介这份资源面向城市规划研究者、数据分析人员和投资决策者提供2020年中国一二三四五线城市等级划分的完整数据可用于人口流动、产业结构、教育资源分布等横向对比分析以及区域市场调研与选址评估。压缩包内共2个文件包含1个xlsx表格和1个sql数据库文件包体仅17KBExcel表依据2020城市商业魅力排行榜综合经济规模、人口吸引力、创新能力、交通便利性等维度给出五级分类SQL文件以自增id、城市名称、城市等级三字段建表便于直接导入数据库做条件查询与批量统计。已有10403人学习下载说明该数据在城市研究与商业分析场景中较受认可。读者可直接获得一份可检索、可二次加工的城市等级基础数据省去逐城搜集与清洗的环节快速搭建分析底表为政策研究、企业区域布局和个人投资判断提供参照。1. 一份城市等级表真正难的是口径对齐上周有个做投放的同行来问为什么同一批城市后台按二线打标签报表按三线算两边差了小两成预算。翻到源头问题出在他们用了两份不同年份、不同编制方的分级结果——一份是随手下的图片转表格一份是业务同学手敲的 Excel。这类事在圈子里太常见了。这份2020 中国城市等级划分资源交付形态很直白一份.xlsx表格加一份.sql文件内容是把城市按一二三四五线城市分了档并带排名。它解决的不是哪个城市更发达的学术问题而是让运营分档、产品配置、BI 切片、渠道报价这四个场景共用同一份底表。适合谁用要做地域分层的运营和增长、要给城市打标签的后端、要出区域报表的数据分析以及只想拿个干净字典表塞进配置中心的开发者。它不适合当权威榜单引用也不适合做城市人口、GDP 之类的经济分析——那是另一类数据。2. 数据字典一张城市表该有哪些字段才算能用拿到 .sql 文件的第一件事不是急着 source 进去而是先看它的建表语句判断这份数据是能查还是能用。能查的意思是字段够把它自己读出来能用的意思是字段够支撑业务侧的过滤、排序、关联和版本切换。城市等级这种维度表字段设计上的偷懒会在三个月后集中爆发想按省份统计时发现没有省份列想按年份对比时发现没有年份列想跟订单表 join 时发现城市编码是中文名。2.1 等级分层与排名口径拆解一二三四五线城市这个分法落到表里至少要拆成四个概念混在一起就会出问题。第一个是tier也就是线级本身。它是分类变量取值从 1 到 5某些版本里还会单列新一线这一档。这里有个绕不开的选择新一线到底编码成 1 还是 2我一般会把它单独给一个枚举值比如 1一线、2新一线、3二线……这样做的代价是所有按tier 2写的筛选逻辑都得改收益是原始信息不丢失下游可以自由决定要不要把一线和新一线合并。如果直接压成五档等于在入库那一刻替所有人做了决定后面想拆回来只能重新导一份。第二个是tier_label中文标签。别嫌冗余业务方在 SQL 客户端里看结果集时看到2和新一线的认知成本完全不同而且前端下拉框、导出报表的枚举文案都能直接复用这列。冗余一列 10 个字节省掉的是一个枚举字典接口。注意tier_label必须和tier严格一一对应导出前用GROUP BY tier跑一遍确认每个 tier 只对应一个 label。我见过把三线和3线混着写进同一列的表前端筛选直接失灵。第三个是排名。排名这个词最坑因为排名至少有两种口径线内排名在二线里排第几和全量排名在所有城市里排第几还有一种更少见的省内排名。三者在表结构上不能共用一个字段。实操里我会保留rank_in_tier和rank_overall两列前者用于取二线城市前 10 个这类需求后者用于全国前 50 强城市这类需求。如果原始 Excel 只给了一列排名先看它的最大值最大值等于该线级城市数就是线内排名最大值接近城市总数就是全量排名两者都不像说明它是按别的维度排的得回去看表头注释。第四个是城市标识。中文城市名做主键迟早翻车。原因很朴素重名、简称、曾用名、后缀市地区自治州写法不统一。合格的底表应该带一列六位行政区划代码用CHAR(6)存而不是INT——前导零在整型里会消失这是新手最容易踩的一脚。2.2 单表还是多表表结构设计与索引选型资源包给的 .sql 可能是单表平铺也可能是省市区三表关联。单表的好处是导入快、查询不用 join坏处是省份字段重复存储改动省份名要全表更新。我的倾向是城市等级这种几十到几百行的维度表单表平铺完全够用不必为了范式硬拆但要把省份单独拿出来做一列province_name并在需要省维度聚合时直接GROUP BY province_name。如果有更高要求比如要按省做层级下钻、要挂大区华东、华南之类那就建一张省份维表城市表只留省份代码。判断标准很简单如果大区这个字段未来一年内会变比如业务把某几个省从华南划到华中就必须拆表如果不会变平铺更省事。建表语句我一般写成下面这样字段顺序按标识 → 归属 → 分类 → 排名 → 版本排读起来顺CREATE TABLE dim_city_tier ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 自增主键仅作物理主键, city_code CHAR(6) NOT NULL COMMENT 六位行政区划代码字符串存防止前导零丢失, city_name VARCHAR(64) NOT NULL COMMENT 城市全称含“市/地区/自治州”后缀, province_name VARCHAR(64) NOT NULL COMMENT 省级名称用于省级聚合, tier TINYINT UNSIGNED NOT NULL COMMENT 线级1一线 2新一线 3二线 4三线 5四线 6五线 0未分级, tier_label VARCHAR(16) NOT NULL COMMENT 线级中文标签与 tier 一一对应, rank_in_tier SMALLINT UNSIGNED DEFAULT NULL COMMENT 线内排名同线级内从 1 开始, rank_overall INT UNSIGNED DEFAULT NULL COMMENT 全量排名, data_year SMALLINT NOT NULL COMMENT 数据年份多版本共存靠这列区分, PRIMARY KEY (id), UNIQUE KEY uk_code_year (city_code, data_year), KEY idx_tier_rank (data_year, tier, rank_in_tier), KEY idx_province (province_name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT城市等级维度表;几个参数值得单独说。city_code用CHAR(6)而不是VARCHAR(6)因为定长且必定六位定长在索引里更紧凑配套的坑是应用层读出来要 trim某些驱动会补空格。uk_code_year这个联合唯一键是整个表结构的核心它保证同一城市在同一年份只有一条记录重复导入时直接报错而不是静默产生双份数据——这比事后写 DELETE 去重靠谱得多。idx_tier_rank的列顺序是(data_year, tier, rank_in_tier)因为最高频的查询是某年、某线级、按排名排序取前 N这个顺序能一路走索引到排序省掉一次 filesort。如果你的场景是按省份取城市列表那idx_province就够但记得把data_year也加进去否则跨年查询会扫全表。字符集用utf8mb4。这条没什么可商量的城市名里虽然大概率没有生僻字但行政区划名称里偶尔会出现四字节字符用utf8三字节会在插入时报Incorrect string value。MySQL 8.0 默认排序规则是utf8mb4_0900_ai_ci5.7 上退回utf8mb4_general_ci两边行为差异主要在大小写和重音敏感度上城市名场景无所谓但迁移时要留意。data_year这一列在多版本共存时价值最大。有人图省事直接覆盖旧表结果第二年业务方拿新表跑去年的同比数字全错。正确的做法是唯一键带上年份查询时永远显式写WHERE data_year 2020把它当成一个强制习惯而不是可选项。3. 把 .xlsx 变成可入库的干净数据Excel 是给人看的数据库是给机器看的两者之间隔着一堆历史包袱合并单元格、颜色标记、手工加的空行、被 Excel 自作主张转成日期的编码。直接拿 Navicat 之类的工具导入 Excel功能硬怼十次里能成三次剩下七次会在你没注意的地方留下脏数据。稳妥的路子是先读进来做体检再清洗最后导出成 CSV 或 INSERT 语句。3.1 用 pandas 给 .xlsx 做体检第一步永远是全量读、不猜结构。用sheet_nameNone把所有工作表读成字典因为这类资源包经常把一线到五线拆成多个 sheet或者把说明放在第一个 sheet。import pandas as pd # dtypestr 是关键强制所有列按文本读避免编码列的前导零被吃掉 sheets pd.read_excel(city_tier_2020.xlsx, sheet_nameNone, dtypestr) for name, df in sheets.items(): print(f--- sheet: {name} | shape: {df.shape}) print(df.columns.tolist()) # 列名先看有没有表头错位 print(df.head(3).to_string()) # 前三行确认数据起始位置 print(空值统计:\n, df.isna().sum()) # 每列空值数这段代码的重点不在语法在dtypestr。不加这个参数pandas 会自动推断类型六位行政区划代码010001会被读成整数10001前导零没了而且你从结果里完全看不出它曾经有个零。同理1会被读成整数1.0会被读成浮点等到导出 CSV 时变成1.0塞进TINYINT列导入直接报错。isna().sum()这一行是在做初筛如果某个 sheet 的城市名列有 3 个空值大概率是中间夹了分页标题或者手工加的空行而不是真的有城市没名字。体检还要看三件事。一是列名是否错位很多 Excel 第一行是大标题2020 中国城市等级划分表第二行才是字段名read_excel默认拿第一行当表头这时候需要传header1。二是是否存在合并单元格合并单元格在读进来后除首行外全是 NaN表现为省份列只有第一行有值。三是行尾是否有汇总行比如合计 337 个城市这种行必须剔除否则它会以city_code为空的形式混进库。3.2 清洗脚本补编码、拉平层级、去重体检完就知道要动哪些刀了。下面这段是通用骨架参数按实际列名改import re import pandas as pd SRC city_tier_2020.xlsx SHEET 2020 # 换成体检时看到的真实 sheet 名 YEAR 2020 df pd.read_excel(SRC, sheet_nameSHEET, dtypestr, header0) df.columns [str(c).strip() for c in df.columns] # 列名去空格 # 1) 列名映射把 Excel 里的中文表头映射到库表字段 df df.rename(columns{ 城市: city_name, 省份: province_name, 行政编码: city_code, 等级: tier_label, 排名: rank_in_tier, }) # 2) 文本列统一去空格去掉不可见字符 for col in [city_name, province_name, tier_label]: df[col] df[col].astype(str).str.strip() df[col] df[col].str.replace(r[\u200b\s], , regexTrue) # 3) 线级标签转枚举一张映射表别用 if-else 堆 TIER_MAP {一线: 1, 新一线: 2, 二线: 3, 三线: 4, 四线: 5, 五线: 6} df[tier] df[tier_label].map(TIER_MAP) missing df[df[tier].isna()][tier_label].unique() assert len(missing) 0, f存在未覆盖的线级标签: {missing} # 4) 编码补齐保留六位非六位的标记出来人工处理 def norm_code(v): s re.sub(r\D, , str(v)) # 只留数字 return s.zfill(6) if 0 len(s) 6 else None df[city_code] df[city_code].apply(norm_code) # 5) 排名转整型非数字置空 df[rank_in_tier] pd.to_numeric(df[rank_in_tier], errorscoerce).astype(Int64) # 6) 去重同编码同城市保留第一条其余打印出来人工确认 dup df[df.duplicated(subset[city_code], keepFalse) df[city_code].notna()] if len(dup): print(重复编码:\n, dup[[city_code, city_name, tier_label]].to_string()) df df.drop_duplicates(subset[city_code], keepfirst) df[data_year] YEAR # 7) 导出 out_cols [city_code, city_name, province_name, tier, tier_label, rank_in_tier, data_year] df[out_cols].to_csv(city_tier_2020_clean.csv, indexFalse, encodingutf-8-sig)逐段说逻辑。第 1 步的列名映射必须显式写不要依赖位置索引——Excel 里插一列就能让后面全错位而按名字映射错了会直接 KeyError属于响的失败比静默错位好得多。第 2 步里的正则[\u200b\s]同时干掉零宽空格和所有空白。零宽空格是网页复制粘贴的典型残留肉眼看不出GROUP BY时却会分裂成两个组属于玄学级 bug。第 3 步用映射字典加断言而不是一大堆if/elif。断言这行是整个脚本的安全阀只要表里出现强二线这种非标标签脚本立刻停而不是把它变成 NaN 悄悄混进去。tier用 TINYINT 存但映射值从 1 开始0 留给未分级这个约定要写进注释。第 4 步的zfill(6)是补前导零的核心。0 len(s) 6这个条件挡住两种情况空值长度为 0和明显异常的超长值。返回 None 的行走人工通道通常这类问题要么是 Excel 里手敲错了一位要么是原表本身用的不是标准编码。第 5 步用Int64大写 I而不是int64因为 pandas 的可空整型才能容纳 NULL普通 int64 遇到空值会强制转 float导出时变成10.0塞进SMALLINT列直接报错。第 6 步的重复检测要用keepFalse把所有重复项都捞出来看看到底是同一城市录了两遍还是不同城市撞了同一个编码。前者直接去重后者是数据错误必须回原表核对。注意导出用utf-8-sig带 BOM。给 MySQL 的LOAD DATA用不带 BOM 的utf-8也行但如果你要把这份 CSV 再发给业务同学用 Excel 打开没有 BOM 中文必乱码。这一步的取舍取决于下游是谁。3.3 导出一份可回滚的 SQL 脚本有些环境不让用LOAD DATA LOCAL INFILE客户端没开local_infile或者云数据库禁了本地文件那就得靠 INSERT 语句。几万行以内用批量 INSERT 完全没问题几百行更是秒进。关键是把可回滚写进脚本里先删同年份旧数据再插入整体包在一个事务里。def to_insert_sql(df, year): def esc(v): if pd.isna(v): return NULL if isinstance(v, (int,)) or str(v).isdigit(): return str(v) return str(v).replace(, ) rows [] for _, r in df.iterrows(): rows.append( f({esc(r.city_code)},{esc(r.city_name)},{esc(r.province_name)}, f{esc(r.tier)},{esc(r.tier_label)},{esc(r.rank_in_tier)},{year}) ) head (INSERT INTO dim_city_tier (city_code,city_name,province_name,tier,tier_label,rank_in_tier,data_year) VALUES\n) body ,\n.join(rows) ; return -- 回滚保护先清同年份\n \ fSTART TRANSACTION;\nDELETE FROM dim_city_tier WHERE data_year {year};\n \ head body \nCOMMIT;\n with open(city_tier_2020_insert.sql, w, encodingutf-8) as f: f.write(to_insert_sql(df, 2020))esc函数处理三件事空值输出NULL而不是字符串nan数字不加引号加引号 MySQL 会隐式转换严格模式下可能告警字符串里的单引号转义成两个单引号这是 SQL 标准的转义写法比反斜杠兼容性好。事务包住 DELETE 和 INSERT 是必须的。中途失败时自动回滚表还是导入前的样子不包事务的话删完插入失败你就得到一张空表加一份报错日志还得重新导一遍。4. .sql 文件落地建表、导入与对账资源包自带的 .sql 文件不一定适配你的库。它可能用了utf8字符集、可能没建唯一键、可能把年份写死在表名里city_tier_2020。我的习惯是把它当参考不直接 source而是按第 2 章的结构自己建表再把数据灌进去。这样表结构、索引、字符集全在掌控内。4.1 导入方式的选择与实操小数据量几百到几千行用source或客户端执行 INSERT 脚本最省事# 命令行执行注意先建库并指定字符集 mysql -h 127.0.0.1 -u app_user -p \ --default-character-setutf8mb4 \ demo_db city_tier_2020_insert.sql--default-character-setutf8mb4这个参数不能省。客户端的默认字符集如果在某些系统上是latin1中文城市名会在传输阶段就被转坏表现为入库后全是问号——而且这个过程不可逆转坏了就是坏了只能重导。数据量大或者要频繁重导时走LOAD DATA速度快一个数量级LOAD DATA LOCAL INFILE /data/city_tier_2020_clean.csv INTO TABLE dim_city_tier CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (city_code, city_name, province_name, tier, tier_label, rank_in_tier, y) SET data_year 2020;参数逐个解释LOCAL表示文件在客户端机器上去掉它则表示文件在数据库服务器上两者路径含义完全不同搞混了会报File not found。CHARACTER SET utf8mb4指文件本身的编码必须和导出时一致。OPTIONALLY ENCLOSED BY 处理城市名里可能出现的逗号比如某些带括号注释的名称。IGNORE 1 LINES跳过表头。最后两行的y加SET是读进来但不直接用的技巧用来给年份列赋常量也能顺手做SET city_name TRIM(city_name)之类的行内清洗。注意LOAD DATA LOCAL需要服务端local_infileON且客户端也开启云数据库普遍禁用它。禁用时就老老实实用批量 INSERT别浪费时间找绕路方案。4.2 导入后的对账三张检查表导完不核对等于没导。我会跑固定的一组对账语句把结果贴到变更记录里。-- 1) 总量与分档分布先确认总行数和你预期一致 SELECT tier, tier_label, COUNT(*) AS cnt, MIN(rank_in_tier) AS min_rank, MAX(rank_in_tier) AS max_rank FROM dim_city_tier WHERE data_year 2020 GROUP BY tier, tier_label ORDER BY tier; -- 2) 排名连续性线内排名应等于该档城市数且无重复 SELECT tier, COUNT(*) AS total, COUNT(DISTINCT rank_in_tier) AS distinct_rank, MAX(rank_in_tier) AS max_rank FROM dim_city_tier WHERE data_year 2020 GROUP BY tier HAVING total distinct_rank OR max_rank total; -- 3) 编码与名称完整性 SELECT SUM(city_code IS NULL) AS null_code, SUM(city_name IS NULL OR city_name ) AS empty_name, SUM(city_code NOT REGEXP ^[0-9]{6}$) AS bad_code FROM dim_city_tier WHERE data_year 2020;第一条查分布重点看max_rank是否合理如果某个档max_rank是 300 多而该档只有几十个城市说明排名列其实是全量排名列映射搞错了。第二条是排名连续性检查HAVING条件同时抓两种异常有并列排名total distinct_rank和排名不连续max_rank total。并列是真实存在的现象不一定是错误但必须知道它存在否则前端按排名取前 10时会多取。不连续则基本可以确定是缺数据。第三条用正则校验编码格式。NOT REGEXP ^[0-9]{6}$命中数不为 0 就说明有脏编码进来了通常是zfill之前混进了字母或者位数不对。提示把这三条固化成一个.sql文件放进仓库每次导数据后执行一遍。人力核对靠不住脚本靠得住。如果资源包自带的 .sql 是建表加数据一体的文件导入前先grep -i create table看一眼它的字符集和主键定义。我见过用utf8建表、数据里带四字节字符的包导入直接崩在第一条记录上报错还特别难懂。遇到这种情况别在原表上改按自己的结构重建一份再灌数据省时间。5. 避坑五条踩过的记录这一节写的都是真实发生过、且每次换人接手都会重演的问题。按现象 → 原因 → 解决的格式记方便直接对照排查。5.1 格式与编码类翻车现象一导入后所有城市名变成问号。原因是字符集链路断了一环。从 Excel 到 CSV 到客户端到表全程要统一utf8mb4任何一环用latin1或gbk都会在转换时丢字符而且是静默丢不报错。解决方式是把链路逐段验证先用file -i xxx.csv看文件编码再在 MySQL 里SHOW VARIABLES LIKE character_set%看连接字符集最后SELECT city_name, HEX(city_name) FROM dim_city_tier LIMIT 1;看实际存储的字节。三步定位到具体哪一环改配置重导。现象二行政区划代码少了前导零010001变成10001。原因是中间某一环把该列当数字处理了。Excel 打开 CSV 时会自动把纯数字列转成数值pandas 读 Excel 时不加dtypestr也会。解决方式是源头掐死pandas 全程dtypestr导出 CSV 时该列前置一个不可见的制表符或者干脆导出成.txt用LOAD DATA读。如果数据已经入库且零丢了没法从库里补回来——六位编码里哪一位是零你是猜的只能回原始文件重导。这一条属于典型的事后没有后悔药。现象三Excel 里看着一行读进来变成两行。原因是单元格内有换行符AltEnter 敲的pandas 会把它当普通字符读进字段导出 CSV 时那一行就断成两行LOAD DATA按行切分直接错位后面的数据全跟着串。解决方式是在清洗脚本里加一句df[col] df[col].str.replace(r[\r\n], , regexTrue)把单元格内换行统一替换成空格。更稳妥的做法是先统计每列的最大长度异常长的值单独打印出来看。5.2 数据口径类翻车现象四同一城市在表里出现两次一次在一线一次在新一线。原因是原表本身就有并列或跨档标注编制方可能把某个城市同时列进了两个档或者在表格里做了备选标记但没单独列。drop_duplicates按编码去重会随机保留一条结果就是你不知道留下了哪个。解决方式是先去重前把重复项打印出来人工判断判断依据看排名列和备注列如果原表真的没有裁决依据就按高档优先的规则写死在脚本里并在注释中注明这条规则是谁定的、什么时候定的。规则写进代码比写在文档里靠谱。现象五排名列有并列前端取前 10取了 12 条。原因是排名列存在重复值WHERE rank_in_tier 10自然返回多行。这不是数据错误是分档方法的固有特性。解决方式是在查询里改用窗口函数按业务规则打散SELECT city_code, city_name, tier_label, rank_in_tier, ROW_NUMBER() OVER (PARTITION BY tier ORDER BY rank_in_tier, city_code) AS rn FROM dim_city_tier WHERE data_year 2020 AND tier 3 ORDER BY rn LIMIT 10;ROW_NUMBER()保证每行唯一ORDER BY rank_in_tier, city_code里的第二排序键是关键的稳定器——只按排名排序时并列项的顺序由执行计划决定两次查询结果可能不同做 A/B 测试或缓存时会表现为数据抖动。加上编码做次级排序结果就确定了。6. 进阶把等级表做成可复用的查询资产导入对账通过只是及格线真正的价值在于让它变成一张随取随用的资产表。我的做法是建一个视图把当前有效年份和常用字段组合固化进去业务方不用记年份参数也不会误查历史数据。CREATE OR REPLACE VIEW v_city_tier AS SELECT city_code, city_name, province_name, tier, tier_label, rank_in_tier, data_year FROM dim_city_tier WHERE data_year 2020;视图里写死年份看着不优雅但它解决了一个很实际的问题下游几十个查询里只要有一个漏写data_year多版本共存后结果就会翻倍。视图把这个风险收敛到一个地方来年只需改这一行。配套的习惯是所有给业务用的查询都走视图物理表只给数据工程用。接着是几个高频衍生查询。按省份统计各线级城市数用于区域投放预算分配SELECT province_name, SUM(tier IN (1,2)) AS tier_1_2_cnt, SUM(tier 3) AS tier_3_cnt, SUM(tier IN (4,5,6)) AS tier_4_6_cnt, COUNT(*) AS total_cnt FROM v_city_tier GROUP BY province_name ORDER BY tier_1_2_cnt DESC, total_cnt DESC;SUM(条件)这种写法比COUNT(CASE WHEN ...)短MySQL 和 PostgreSQL 都支持副作用是可读性略差团队里统一一种就行。注意tier IN (1,2)在 MySQL 里返回布尔值 0/1求和即计数。再往下是把它接到业务表上。假设有张订单表带city_code想统计各线级 GMVSELECT c.tier_label, COUNT(DISTINCT o.order_id) AS order_cnt, SUM(o.amount) AS gmv, ROUND(SUM(o.amount) / COUNT(DISTINCT o.order_id), 2) AS aov FROM fact_order o LEFT JOIN v_city_tier c ON c.city_code o.city_code WHERE o.dt BETWEEN 2020-01-01 AND 2020-12-31 GROUP BY c.tier_label ORDER BY gmv DESC;这里必须用LEFT JOIN因为订单表里一定存在维度表覆盖不到的城市编码新设、撤并、写错。用INNER JOIN会静默丢掉这部分订单GMV 少算了你还以为是业务下滑。加上一列c.tier_label IS NULL的统计能直接看出维度覆盖率这个数字低于 95% 就说明维度表该更新了。一个具体技巧给city_code建一个应用层缓存时别缓存城市名 → 等级的映射缓存编码 → (等级, 线内排名)。城市名会变更名、加后缀编码不会。我见过因为缓存了名称映射某城市更名后缓存和库里对不上排查了两天才定位到的案例。从那以后我每次拿到这类表格包都强制先跑一遍行数对账和排名去重确认口径对齐了再谈分析——这一步花十分钟能省掉后面两周的返工。希望帮到你。本文还有配套的精品资源点击获取