城市分级表导入数据库实战：从Excel到SQL的完整指南

简介面向城市规划研究者、数据分析人员与投资决策者的城市分级数据工具包围绕2020年城市商业魅力排行榜将全国城市按经济规模、人口吸引力、创新能力与交通便利性等维度划入一至五线城市可用于区域对比、趋势研究与市场机会判断。压缩包共2个文件约17KB包含一份xlsx表格与一份sql数据库文件表格便于直接浏览与筛选各等级城市名单sql文件以自增id、城市名称、城市等级三个字段建表方便导入数据库后做查询统计与二次开发。已有10404人学习下载说明其在同类数据中具备一定参考价值。读者可据此快速获得结构清晰的城市等级底表结合自身业务完成人口流动、产业结构、教育资源分布等维度的交叉分析也可作为教学演示或数据可视化项目的基础数据集使用。1. 拿到一份城市分级表先别急着往库里灌做区域分析、门店选址、用户画像分层的同行多半都遇到过同一个尴尬手里有一批带城市字段的业务数据想按“一线/新一线/二线……五线”做聚合结果发现公司内部根本没有一份口径统一、能直接 join 的城市等级表。有人临时手搓一张 Excel有人从各种文章里复制粘贴最后同一座城市在不同报表里等级对不上汇报时被追问一句“这个分级依据是什么”就卡壳。这份资源就是冲这个场景来的一份 2020 年口径的中国城市等级划分表同时给了.xlsx表格和.sql文件两种形态。Excel 适合业务同学直接筛选、透视、做可视化SQL 文件适合工程师一次性导入数据库和订单表、门店表、用户表做关联。它解决的不是“城市有多少个”这种常识问题而是“把城市名映射成可计算的等级标签”这件脏活。适合数据分析、BI、后端开发以及任何需要按城市线级做维度拆分的从业者。2. 先看清表结构城市分级表到底存了什么2.1 一张分级表的最小字段集城市等级表看着简单真正能用的版本字段设计是有讲究的。常见做法是至少包含这么几列城市名称、所属省份、城市等级一线到五线、以及一个可选的排名或序号。城市名称是关联键省份用于纠偏和分组等级是核心标签排名则方便做“同等级内排序”这类需求。拿到.xlsx后第一件事不是导入而是打开看表头和前几十行确认三件事城市名用的是“市”后缀全称还是简称比如“北京市”还是“北京”等级列写的是“一线”还是“1”还是“一线城市”有没有合并单元格或表头占了两行。这三件事直接决定后面导入脚本怎么写。我一般会先另存一份副本再动手原始文件保持只读避免改坏了没法回溯。2.2 等级口径与城市数量的对应关系2020 年这套分级口径里一线城市是固定的四个新一线是十几个二线到五线逐级铺开总数覆盖全国地级及以上城市。这里有个容易被忽略的点不同机构发布的分级名单在“新一线”这一档上会有出入所以这份表的价值不在于“绝对权威”而在于“内部口径统一”。你把它作为公司内部分析的基准表比每次临时找名单要靠谱得多。实际使用时建议把等级做成有序枚举而不是纯文本。比如一线1、新一线2、二线3 一直到五线6这样排序、区间筛选、算平均线级都方便。文本标签保留给人看数值编码留给机器算两列并存是最省事的做法。import pandas as pd # 读取时先不指定 dtype观察原始数据长什么样 df pd.read_excel(city_level_2020.xlsx, sheet_name0, header0) print(df.columns.tolist()) # 看表头 print(df.head(10)) # 看前 10 行 print(df[城市等级].value_counts()) # 看等级分布是否合理这段代码的作用是先做“体检”而不是直接清洗。header0假定第一行是表头如果实际表头占了两行就要改成header1或先skiprows。value_counts()能快速暴露问题如果出现“一线城市”“一线”“1线”混用说明这一列需要统一映射后面导入数据库才不会出现同一等级被拆成多个值的情况。2.3 为什么同时给 Excel 和 SQL 两种形态这不是凑数。Excel 面向的是不会写 SQL 的业务方他们要做的是筛选、透视、贴到 PPT 里SQL 面向的是要把这张表变成数据库里一张维表dimension table的工程师。两种形态对应两条完全不同的使用路径也对应两类不同的坑Excel 的坑在编码和格式SQL 的坑在字符集和主键。从工程角度看正确姿势是“以 SQL 文件为准入库Excel 只作为人工核对和交付物”。因为 SQL 文件里的建表语句、字段类型、插入数据是一体的导入后就是一张可直接 join 的表而 Excel 每次读取都要重新推断类型城市名里的空格、全半角括号都可能让关联失败。把 SQL 当源头Excel 当快照这条原则能省掉后面很多对不上的麻烦。3. 把 SQL 文件导进库建表、导入、校验三步走3.1 建表语句里该盯的几个点打开.sql文件先找CREATE TABLE那段重点看三样字符集、城市名字段的长度、有没有主键或唯一索引。城市名是中文字符集必须是utf8mb4否则遇到生僻字或特殊符号会截断甚至报错。城市名字段长度给到VARCHAR(50)一般够用但如果你后续要拼“省市”做联合键就得留余量。主键这块如果原文件没给我一般会自己加一个自增 id 或者把城市名设成唯一索引。原因很实际导入过程中如果重复执行没有唯一约束就会插进去两份数据后面 join 出来的结果直接翻倍这种错误在聚合报表里极难发现。加唯一索引后重复导入会报错而不是静默污染数据这属于花一分钟换后面几小时排查时间的买卖。-- 建表时显式指定字符集避免中文乱码 CREATE TABLE dim_city_level ( id INT AUTO_INCREMENT PRIMARY KEY, city_name VARCHAR(50) NOT NULL COMMENT 城市名称, province VARCHAR(50) NOT NULL COMMENT 所属省份, city_level VARCHAR(20) NOT NULL COMMENT 等级文本如 一线/新一线, level_code TINYINT NOT NULL COMMENT 等级数值编码 1-6, rank_no INT DEFAULT NULL COMMENT 同等级内排名, UNIQUE KEY uk_city (city_name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci;建表语句里level_code单独留一列是刻意为之文本等级给人看数值编码给ORDER BY和区间统计用。UNIQUE KEY uk_city是防重复导入的闸门。COLLATE选通用排序规则即可除非你有跨库比对需求否则不必纠结具体用哪个变体。3.2 导入命令与常见报错导入方式取决于你用的库。命令行下最常见的是mysql客户端直接 source或者用图形工具执行脚本。如果 SQL 文件里已经带了INSERT语句直接执行整个文件即可如果只有建表没有数据就得先把 Excel 转成 CSV 再LOAD DATA。# 方式一直接执行带数据的 sql 文件 mysql -u root -p your_database city_level_2020.sql # 方式二先建表再把清洗后的 csv 灌进去 mysql -u root -p your_database -e LOAD DATA LOCAL INFILE city_level_clean.csv INTO TABLE dim_city_level FIELDS TERMINATED BY , ENCLOSED BY \ LINES TERMINATED BY \n IGNORE 1 ROWS (city_name, province, city_level, level_code, rank_no);第一种方式最省事前提是文件里的库名、表名和你环境一致不一致就先改文件头或手动USE目标库。第二种方式适合你已经在 Python 里清洗过一遍、想控制字段顺序的场景。IGNORE 1 ROWS跳过表头ENCLOSED BY处理带逗号的字段。如果报The used command is not allowed是客户端没开LOCAL_INFILE需要在连接参数里显式允许这是权限配置问题不是数据问题。3.3 导入后必须做的三条校验导完不校验等于没导。我固定跑三条总数对不对、等级分布合不合理、有没有空值或重复。总数可以对照 Excel 行数等级分布看每一档的城市数量是否落在常识范围内空值检查重点看省份和等级列。-- 1. 总行数 SELECT COUNT(*) FROM dim_city_level; -- 2. 各等级城市数量分布 SELECT city_level, COUNT(*) AS cnt FROM dim_city_level GROUP BY city_level ORDER BY MIN(level_code); -- 3. 空值与重复检查 SELECT COUNT(*) FROM dim_city_level WHERE province IS NULL OR province ; SELECT city_name, COUNT(*) FROM dim_city_level GROUP BY city_name HAVING COUNT(*) 1;第一条对总数第二条看分布第三条兜底。如果第二条里出现“一线”有十几个城市基本可以断定等级列映射错了或者数据串行了。第三条查出重复说明唯一索引没生效或者导入前表里已有旧数据需要先清表再导。这三条跑完这张维表才算可用。4. 和业务表关联城市名对齐才是真功夫4.1 关联键不统一是最大的翻车点维表建好了真正让人头疼的是和业务表 join。业务表里的城市字段可能是“北京”“北京市”“北京市朝阳区”甚至是拼音或编码。直接ON a.city b.city_name大概率关联率只有六七成剩下三成静默丢失报表数字偏小还找不到原因。常见做法是建一个映射层先把业务表的城市字段做标准化去空格、去“市”后缀、截取到市级再和维表关联。如果业务数据里是区县级就得先有一张区县到城市的对照这一步不能省。我一般会在 ETL 里单独加一个city_std字段专门存标准化后的城市名原始字段保留不动方便回溯。-- 标准化后再关联去掉“市”后缀并去空格 SELECT o.order_id, o.city_raw, c.city_level, c.level_code FROM fact_order o LEFT JOIN dim_city_level c ON TRIM(REPLACE(o.city_raw, 市, )) TRIM(REPLACE(c.city_name, 市, ));这里用LEFT JOIN而不是INNER JOIN是为了让关联不上的记录也保留下来方便统计关联率。REPLACE去“市”后缀是粗粒度处理对“北京市”有效但对“北京市朝阳区”无效那种情况需要先做字符串截取或正则提取。关联率低于 90% 时不要急着往下做分析先把没关联上的城市名SELECT DISTINCT出来看一眼往往能发现几个脏数据或口径差异。4.2 用等级编码做聚合而不是文本关联上之后聚合统计要用level_code而不是文本等级。原因很直接文本排序是字典序“一线”“三线”“二线”排出来是乱的数值编码才能正确做ORDER BY、BETWEEN、算平均线级。比如算“平均城市线级”这种指标只有数值列能算。-- 按线级统计订单量和客单价用数值编码排序 SELECT c.city_level, c.level_code, COUNT(*) AS order_cnt, AVG(o.amount) AS avg_amount FROM fact_order o JOIN dim_city_level c ON TRIM(REPLACE(o.city_raw, 市, )) TRIM(REPLACE(c.city_name, 市, )) GROUP BY c.city_level, c.level_code ORDER BY c.level_code;GROUP BY里同时带文本和编码是为了输出可读ORDER BY只认编码。如果只按文本分组排序一线和新一线的顺序会错位汇报时一眼就能被看出来。这类细节不影响数据正确性但影响可信度。4.3 把维表做成视图或宽表如果多个报表都要用城市等级别让每个查询都写一遍REPLACE关联逻辑。常见做法是建一个视图把标准化和关联封装进去业务方直接查视图。视图的好处是口径统一改一处全生效代价是每次查询都实时计算数据量大时性能一般。折中方案是做成物化宽表定时刷新。CREATE VIEW v_order_with_city_level AS SELECT o.*, c.city_level, c.level_code FROM fact_order o LEFT JOIN dim_city_level c ON TRIM(REPLACE(o.city_raw, 市, )) TRIM(REPLACE(c.city_name, 市, ));视图里保留o.*是为了不破坏原有字段引用。如果业务表字段很多建议显式列出需要的列而不是*避免后续加字段导致视图定义漂移。视图建好后关联率这个指标要定期监控业务侧新增城市或录入不规范时关联率会悄悄下降。5. 避坑清单这几处翻车我见过太多次5.1 现象导入后中文全是问号原因基本锁定在字符集。要么建表时没指定utf8mb4要么客户端连接字符集是latin1要么 SQL 文件本身保存编码不是 UTF-8。三者任一不对都会出问号或乱码。解决顺序是先确认文件编码用编辑器看别猜再确认建表语句字符集最后确认连接参数里character_set_client等几个变量。三处统一到utf8mb4后重新导入不要在原表上修修补补。5.2 现象关联率突然从 95% 掉到 70%原因通常是业务侧改了城市字段的录入格式比如从“北京”改成“北京市”或者新增了海外城市。维表里没有对应记录LEFT JOIN后等级为空。解决办法不是改维表去迁就脏数据而是在 ETL 里加标准化规则和未匹配告警。我一般会设一个阈值关联率低于阈值就阻断下游任务逼着上游把数据修好而不是让错误数据一路流到报表。5.3 现象同一城市出现两条不同等级原因多半是维表被重复导入且唯一索引没生效或者手工往表里补过数据。排查方法是按城市名分组数大于 1 查出来看两条记录的来源。解决是清表重导并确认唯一索引确实建上了。如果业务上确实需要保留历史版本比如不同年份口径不同那就该加一个version字段而不是让两条记录混在同一张表里。5.4 现象Excel 打开正常程序读出来列错位原因是 Excel 里有合并单元格或隐藏列pandas读出来会把合并区域的值只填在第一行其余为NaN。解决办法是读之前在 Excel 里取消合并并向下填充或者在代码里用ffill()补全。更稳妥的做法是让提供方给一份“扁平化”的导出文件一行一个城市不带任何合并。这类格式问题在跨团队交付时特别常见收到文件先看结构比先写代码重要。5.5 现象按线级统计的总数和全量对不上先查是不是用了INNER JOIN把未匹配记录丢了再查是不是GROUP BY时把NULL等级过滤掉了。LEFT JOIN产生的NULL等级在GROUP BY里会单独成一组如果报表只展示已知等级这部分就被隐藏了。正确做法是把NULL组显式命名为“未匹配”并展示出来让数据缺口可见而不是假装它不存在。6. 进阶用法把分级表变成可复用的分析底座这张表真正的价值不在“查一下某城市是几线”而在于它能成为一套可复用的分析底座。我习惯在它之上再叠两层一层是“城市-省份-大区”的层级映射方便按大区汇总另一层是“线级分组”把一线和新一线归为高线二线三线归为中线四线五线归为低线做粗粒度对比时不用每次写一长串IN。-- 在线级基础上派生大区分组便于粗粒度对比 SELECT CASE WHEN level_code IN (1, 2) THEN 高线 WHEN level_code IN (3, 4) THEN 中线 ELSE 低线 END AS tier_group, COUNT(*) AS city_cnt, SUM(order_cnt) AS total_orders FROM v_order_with_city_level GROUP BY tier_group ORDER BY MIN(level_code);这段派生的意义在于很多业务问题不需要细到“新一线还是二线”只需要“高线还是低线”。把分组逻辑固化下来不同报表之间口径就一致了。参数上唯一要调的是分组边界边界怎么划取决于你的业务分布没有标准答案但一旦定了就写进文档别让每个人各划各的。验证这套底座是否可靠我固定做两件事一是拿几个已知城市手工核对等级二是把关联率和未匹配城市清单定期导出给业务方确认。前者防低级错误后者防口径漂移。下面这张表是我常用的自检清单照着过一遍基本不会出大问题。检查项合格标准不合格时的动作总行数与源文件一致查是否重复导入或漏导等级分布各档数量落在常识范围查等级列映射关联率高于设定阈值导出未匹配城市名排查空值省份、等级列无空回源补全或标记重复城市唯一索引生效无重复清表重导血泪经验是这张表看着小但它是所有区域分析的起点起点歪了后面全歪。从那以后我每次导入这类维表都强制先跑一遍上面的自检清单再往下游放行宁可多花十分钟也不想到汇报前夜才发现数字对不上。希望帮到你。本文还有配套的精品资源点击获取