简介面向需要进行基站掉话率分析的数据处理技术人员这份压缩包提供从原始通话话单中统计掉线率最高前10基站的完整数据与SQL方案适用于Hive/MySQL环境下的话务数据清洗、统计与网络质量评估。资源共2个文件包括一份CSV原始数据文件和对应的MySQL版SQL脚本整体压缩后大小为13.03MB。CSV中逐条记录了record_time通话时间、imei基站编号、cell手机编号、drop_num掉话秒数、duration通话持续总秒数等字段利用duration与drop_num可计算各基站掉话率SQL脚本则将表结构和导入查询逻辑一并封装方便直接导入MySQL数据库进行排序统计省去自行建表解析的麻烦。已有242人学习下载适合作为Hive/MySQL数据处理练习、基站网络质量分析或相关课程设计的参考素材也可以在此基础上扩展掉话率计算、TOP基站筛选等后续分析。1. 解开 cdr_summ_imei_cell_info7z 包里那份 csv 话单聚合数据能做什么这个文件名拆开就是三件事cdr_summ是 Call Detail Record 的汇总话单汇总imei是终端设备编号cell_info是基站小区基础信息最后的(csv-mysql)表示这份数据以 csv 文件交付、面向 MySQL 导入整包用 .7z 压缩。在手机信令和人口流动分析这类项目里这种中间交付物很常见上游把每天几亿条原始话单按设备、小区、时段聚合成 csv下游再灌进自己的库。无论你拿到的 csv 是 2023 年全国区县级粒度还是单城市全网粒度只要字段带cdr_summ、imei、cell_info处理套路就是一套——确认粒度、建表、导入、校验、关联位置信息。这篇笔记直接把这条链路讲透边讲边给可抄的语句和参数。2. 读懂 csv 的数据粒度与字段构成imei×cell 的聚合表决定你能回答什么问题2.1 从原始 CDR 到 cdr_summ中间发生了什么原始话单一条记录长什么样决定了这份汇总 csv 是怎么来的。运营商侧每通电话、每次上网会生成一条 CDR包含主被叫号码、IMEI、IMSI、LAC位置区编码、Cell ID小区编号、开始时间、结束时间、上下行流量等十几二十个字段。一天下来一张表少说几亿行直接丢给下游团队别说 MySQL任何数据库都不愿意接。所以上游通常会做一个聚合动作把原始 CDR 按“设备 小区 时间片”分组统计出呼叫次数、通话时长、流量字节数甚至基于信令驻留推导出停留秒数。这一步做完几亿条原始记录塌缩成几百万行甚至几十万行的汇总表这才是cdr_summ的由来。对应的聚合逻辑大致是这样的 SQL-- 上游常见的聚合逻辑把原始 CDR 按设备、小区、小时做 GROUP BY SELECT imei, lac, cell_id, DATE_FORMAT(stat_time, %Y-%m-%d) AS stat_date, -- 按天分片 HOUR(stat_time) AS stat_hour, -- 按小时分片 COUNT(*) AS call_cnt, -- 呼叫次数 SUM(TIMESTAMPDIFF(SECOND, start_time, end_time)) AS duration, -- 累计通话秒数 SUM(data_flow) AS data_flow -- 累计流量字节 FROM raw_cdr GROUP BY imei, lac, cell_id, DATE_FORMAT(stat_time, %Y-%m-%d), HOUR(stat_time);注意这个 GROUP BY 的粒度imei lac cell_id 日期 小时。粒度直接决定下游能回答什么问题——按小区聚合你能算一台设备在某时段出现在哪个基站下、停留了多久如果只按 imei 聚合丢掉了小区就只剩“这台设备今天打了几个电话”空间维度全丢整个 csv 的价值就去掉一大半。这里还需要想清楚一个关键选型为什么按 imei 而不按手机号MSISDN聚合。IMEI 是设备标识手机号是可以换卡的一个人换了 SIM 卡之后 MSISDN 变了但设备没变。在做职住分析、人口流动这类场景时追踪“这台设备去过哪”比“这个号码打过几个电话”更有物理意义。当然 imei 也有它的脏点部分话单里 IMEI 缺失或者有人用改机软件伪造导致同一台设备出现多个 imei。这类数据落到 csv 里之后导入阶段没法修只能在分析阶段靠时长阈值、轨迹合理性去过滤。2.2 典型的字段模板与列类型选择拿到cdr_summ_imei_cell_info.csv之后第一件事是打开表头不是急着建库。常见交付字段大致长这样字段名类型建议含义是否可空imeiVARCHAR(20)终端设备标识否lacINT位置区编码否cell_idINT小区编号否cgiVARCHAR(16)lac cell_id 拼接的小区全球识别码否stat_dateDATE聚合日期否stat_hourTINYINT聚合小时0-23否call_cntINT呼叫次数否durationINT累计通话秒数否data_flowBIGINT累计流量字节是stay_secINT信令推导驻留秒数是类型选择上有几个容易翻车的点。lac和cell_id这类编号在 csv 里看起来是数字但本质上是一个标识符不是用来做算术的用 INT 保存是合理的省空间、查询快。但要注意很多 csv 里 cell_id 带前导零比如00123Excel 打开会丢掉零所以 csv 阶段不能用 Excel 直接编辑。cgi字段如果源文件直接给了就按 VARCHAR 原样存如果源文件只有 lac 和 cell_id 两列就自己拼接注意两端都要补零成等宽否则同一小区的 cgi 会出现两种写法。duration和stay_sec用 INT 够不够是个值得较真的问题。一天累计通话秒数单设备最多也就 86400 秒但这是单设备单小区单小时的值累计量级不大不过如果是按周、按月汇总的 csvduration可能到百万级还在 INT 范围内。真正要小心的是data_flow流量字节数轻松上亿必须用 BIGINT否则导入后数据溢出直接报错或变成负数。时间字段是 csv 里最容易出幺蛾子的地方。有的 csv 给的是2023-01-01 08:00:00这种完整时间戳有的直接拆成stat_date和stat_hour两列。如果是完整时间戳导入时要在SET子句里拆开如果文件里已经拆好了反而省事。需要警惕的是stat_hour24这种脏值部分上游系统会把凌晨 0 点写成 24导入后TINYINT存得下但HOUR()函数和排序都会出问题需要提前挡掉。2.3 csv 文件为什么是瓶颈pandas 读取 vs MySQL 导入csv 文件到 MySQL 有两条路用 pandas 读进来再逐条 INSERT或者用 MySQL 自带的LOAD DATA INFILE文本协议直灌。很多新手习惯用 pandas因为代码写起来顺手。但一个几 GB 的 csvpandasread_csv会把整个文件读进内存加上 DataFrame 的副本开销机器内存直接见底。# pandas 方式——适合小文件大文件别硬来 import pandas as pd # usecols 只取需要的列dtype 全指定为 str避免 pandas 自作主张推断类型 df pd.read_csv( cdr_summ_imei_cell_info.csv, dtype{ imei: string, lac: string, # 这里不转 int防止前导零丢失 cell_id: string, stat_date: string, }, usecols[imei, lac, cell_id, stat_date, stat_hour, call_cnt, duration, data_flow, stay_sec], ) # 走 MySQL 连接池批量写入比逐条 execute 快但远不如 LOAD DATA # 这里只是备用方案文件超过 500MB 时直接放弃 pandas这段代码的关键是dtype全部指定为stringlac和cell_id一旦让 pandas 自动推断成 int64前导零就没了后面关联cell_info表时会大量失配。usecols是另一个必须养成的习惯csv 交付文件经常附带一堆用不上的辅助列只读需要的列能省三分之一内存。但说实话文件超过 500MB 之后pandas 路线的性价比就很低了。我在生产环境里见过同事用 pandas 导一个 3GB 的 csv跑了四十分钟没结束最后 OOM 被系统 kill。换LOAD DATA LOCAL INFILE之后同样的文件基本是分钟级到秒级的差距——因为 MySQL 的 LOAD DATA 走的是文本解析协议不经过客户端逐行组装 INSERT 语句少了网络往返和 SQL 解析开销。所以我的习惯是小文件100MB 以内随手用 pandas 处理加清洗没问题大文件直接走LOAD DATA清洗逻辑放到 SQL 的SET子句里做。后面第三章给的完整链路就是以LOAD DATA为主线的方案。3. 把 csv 灌进 mysql7z 解压、建表 DDL 与 LOAD DATA 调优3.1 解压 .7z 与 csv 预检先看编码、行数和表头第一步是解压。Linux 环境默认不带 7z 命令需要先装 p7zip 系列工具# Debian/Ubuntu 系 apt-get install -y p7zip-full # CentOS/RHEL 系 yum install -y p7zip # 解压注意文件名里有括号用单引号包住防止 shell 转义 7z x cdr_summ_imei_cell_info(csv-mysql).7z解压这个动作本身有讲究。文件名里的括号在 bash 里会被解释成子 shell 语法不转义或不用引号会直接报bash: syntax error near unexpected token。加上单引号是最省心的做法。还有一种情况是拿到手的是.7z.001、.7z.002这种分卷包7z x命令会自动识别分卷不用手动合并但前提是所有分卷都在同一个目录里。解压完成后别急着建表三条命令先探底# 查看文件体积和行数行数要减掉表头那一行 ls -lh *.csv wc -l *.csv # 查看文件编码常见输出 US-ASCII / UTF-8 / ISO-8859 或 GBK 系 file -bi *.csv # 预览前 5 行确认分隔符、表头、时间格式 head -5 *.csvfile -bi这条很多人不看但它能省掉后面一大截乱码排查。如果输出是text/plain; charsetiso-8859-1说明文件大概率是 GBK/GB18030 编码导入时必须显式声明字符集如果输出charsetutf-8直接导即可。head -5则要看三件事分隔符是不是纯逗号、有没有ENCLOSED BY 的必要、时间列到底是2023-01-01还是2023/01/01这决定后面STR_TO_DATE的格式串。3.2 建表 DDL把聚合键、时间、计数值一次定对预检完就可以建表了。下面的 DDL 是按imei × 日期 × 小时 × cgi四个键做唯一约束的典型设计也是我处理这类 csv 的默认模板-- 话单按 imei × 小区 × 小时汇总表 CREATE TABLE IF NOT EXISTS cdr_summ_imei_cell ( imei VARCHAR(20) NOT NULL COMMENT 终端设备标识, lac INT NOT NULL COMMENT 位置区编码, cell_id INT NOT NULL COMMENT 小区编号, cgi VARCHAR(16) NOT NULL COMMENT laccell_id 拼接的小区识别码, stat_date DATE NOT NULL COMMENT 聚合日期, stat_hour TINYINT NOT NULL COMMENT 聚合小时(0-23), call_cnt INT NOT NULL DEFAULT 0 COMMENT 呼叫次数, duration INT NOT NULL DEFAULT 0 COMMENT 通话时长(秒), data_flow BIGINT NOT NULL DEFAULT 0 COMMENT 流量字节数, stay_sec INT NOT NULL DEFAULT 0 COMMENT 信令驻留秒数, PRIMARY KEY (imei, stat_date, stat_hour, cgi), KEY idx_date_cgi (stat_date, cgi), KEY idx_cgi (cgi) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENTcdr_summ_imei_cell_info 导入表;主键选imei stat_date stat_hour cgi理由是这套键天然对应聚合粒度能直接防止重复导入如果 csv 里分区粒度不是小时而是 15 分钟主键里就要换成stat_time完整时间戳。data_flow用 BIGINT 是必须的一个设备一天刷几个 GB 视频就是几千万字节INT 上限 21 亿看着够但小区级累计很容易破。stay_sec这类可能为空的字段显式DEFAULT 0比允许 NULL 好——分析阶段SUM(stay_sec)遇到 NULL 会直接变 NULL还得套IFNULL脏数据自己给自己挖坑。字段注释一定要写。csv 交付文件经常没有配套字段说明文档注释写清楚“信令推导驻留秒数”和“通话时长”的区别两个月后你自己回来看表也不会猜错。3.3 用 LOAD DATA LOCAL INFILE 导入指令与参数调优建完表主菜是LOAD DATA。完整命令如下-- 导入前先把会话字符集切到 utf8mb4 SET NAMES utf8mb4; LOAD DATA LOCAL INFILE /data/cdr_summ_imei_cell_info/cdr_summ_imei_cell_info.csv INTO TABLE cdr_summ_imei_cell CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (imei, lac, cell_id, cgi, stat_date, stat_hour, call_cnt, duration, data_flow, stay_sec) SET stat_date STR_TO_DATE(stat_date, %Y-%m-%d), stat_hour IF(stat_hour , 0, CAST(stat_hour AS UNSIGNED)), cgi IF(cgi , CONCAT(LPAD(lac, 5, 0), LPAD(cell_id, 5, 0)), cgi);逐段拆解。LOCAL关键字表示文件在客户端本地不是 MySQL 服务器磁盘上这样即使数据库跑在远程机器也不用把 csv 先传到服务器日常开发最省事。FIELDS TERMINATED BY ,是字段分隔符如果 csv 里某些文本字段内含逗号比如city字段写成北京市,朝阳区这种情况必须加OPTIONALLY ENCLOSED BY 否则 LOAD DATA 会把一个字段劈成两列后面所有列错位。LINES TERMINATED BY \n是另一个高频坑。Windows 下用 Excel 或记事本编辑过的 csv行结束符可能是\r\n这里不写\r\n的话每行末尾会多出一个\r字符串字段不明显但如果是stat_hour这种数字字段CAST(\r AS UNSIGNED)会得到 0还静默成功校验时才发现小时全变成 0 了。所以预检阶段head -5看的就是这个。stat_date、stat_hour这种带的是 MySQL 用户变量作用是把 csv 里的原始字符串先存进变量再在SET里做转换。STR_TO_DATE(stat_date, %Y-%m-%d)负责把2023/01/01这类分隔符不标准的日期纠正过来IF(stat_hour , 0, ...)处理空字符串——csv 里的小时列如果是空直接 CAST 成 UNSIGNED 会得到 0但会先报一个 warning大量字段告警时一眼看过去全是红色没法区分真正的问题。关于cgi这一列的SET如果源 csv 里没有 cgi 列但建表 DDL 里 cgi 是 NOT NULLLOAD DATA 会报Column cgi cannot be null。这时候有两种修法——要么把 DDL 里的 cgi 改成可空导入完成后统一 UPDATE要么像上面这样用IF(cgi , ...)在导入时自动拼接。第二种更省事拼接时LPAD(lac, 5, 0)保证等宽避免同一个小区出现00001和1两种写法。注意如果 csv 里本身有 cgi 列这个SET完全不干扰它只在 cgi 为空时触发生成。大文件导入还有一个调优组合导入前临时调整几个会话级参数-- 导入前执行减少磁盘刷盘频率批量提交更快生产环境谨慎 SET SESSION innodb_flush_log_at_trx_commit 2; SET SESSION autocommit 0; SET FOREIGN_KEY_CHECKS 0; -- 导入完成后记得恢复 -- SET SESSION autocommit 1; -- SET FOREIGN_KEY_CHECKS 1;innodb_flush_log_at_trx_commit2的意思是每秒刷一次日志而不是每次事务都刷导入速度能快一个量级但代价是 MySQL 进程突然崩溃时可能丢最后一秒的数据。导入场景这是可以接受的业务场景千万别改。还有更激进的做法是导入前ALTER TABLE ... DISABLE KEYS但 InnoDB 表这个语句效果有限MyISAM 才有明显收益现在默认引擎都是 InnoDB不用浪费时间。3.4 不清空重导staging 表 原子切换实际项目中 csv 经常要反复导入——上游重新跑数、你发现脏数据太多要重来。这时候直接往正式表里 LOAD DATA主键冲突会让导入中断清空表重导又怕中途失败把好数据也带走。我的做法是先导一张 staging 表校验没问题再切换。-- 1. 复制表结构建 staging 表 CREATE TABLE stage_cdr_summ LIKE cdr_summ_imei_cell; -- 2. 往 staging 表灌数据LOAD DATA 命令同上INTO 换成 stage 表 LOAD DATA LOCAL INFILE /data/cdr_summ_imei_cell_info/cdr_summ_imei_cell_info.csv INTO TABLE stage_cdr_summ -- ... 其余语句同上 ... -- 3. 校验 staging 表行数和关键指标 SELECT COUNT(*) FROM stage_cdr_summ; -- 4. 原子切换旧表改名备份新表顶上 RENAME TABLE cdr_summ_imei_cell TO bk_cdr_summ_imei_cell, stage_cdr_summ TO cdr_summ_imei_cell;RENAME TABLE是原子操作过程中查询要么看到旧表要么看到新表不会出现读到一半表结构消失的情况。备份表bk_cdr_summ_imei_cell保留一两天再 DROP确认新数据没问题的后悔药就在这里。这套 staging 流程最大的好处是LOAD DATA 失败时直接在 staging 表上 DROP 重来正式表完全不受影响。坏处是要占双倍磁盘空间一张 10GB 的 cdr_summ 表磁盘要有 20GB 余量才玩得起。磁盘紧张时退而求其次先备份旧表数据到 csv 再清空重导但就没有原子切换这个保障了。4. 导入后的三道关卡索引设计、行数校验与脏数据清理4.1 主键和二级索引imei 单独索引是第一个大坑导入完成不等于能用。这个表最常跑的查询有两种按 imei 查某台设备的时空轨迹按日期和 cgi 统计某个小区的人流。主键(imei, stat_date, stat_hour, cgi)已经覆盖了第一种查询——WHERE imeixxx直接走主键最左前缀不需要额外建索引。但第二种查询WHERE stat_date2023-01-01 AND cgixxxx在主键里完全用不上因为主键最左列是 imei。所以 DDL 里给了两个二级索引idx_date_cgi (stat_date, cgi)和idx_cgi (cgi)。idx_cgi在只有几百万行时看着冗余但当你后面 JOIN cell_info 表做轨迹分析时ON a.cgi b.cgi没有索引就是全表扫描百万行加几万行的笛卡尔积查一次卡一分钟很常见。这里最容易犯的错是另外单独建一个KEY idx_imei (imei)——主键最左列已经是 imei再建一个单列索引纯属浪费写放大。判断一个索引该不该建别猜用EXPLAIN验证-- 查看查询是否走索引rows 字段预估扫多少行 EXPLAIN SELECT * FROM cdr_summ_imei_cell WHERE stat_date 2023-01-01 AND cgi 0000100001\GEXPLAIN输出里type是ref、rows预估个位数说明索引走对了如果是ALL全表扫描就是索引列顺序写反了。联合索引idx_date_cgi两个列的先后顺序也有讲究先等值列cgi再范围列stat_date通常更好但 cgi 的区分度如果不高整个城市只有几千个小区先放日期反而更合理。我的经验是直接用(stat_date, cgi)日期先等值过滤再在小区上过滤这符合大多数“某天哪些小区人多”的查询模式。4.2 LOAD DATA 后的三句校验 SQL总数、独立设备数与抽样导入完第一件事是校验。三句 SQL 把基本盘打一遍-- 第一句总数与 wc -l 对比wc -l 结果减 1减去表头 SELECT COUNT(*) AS total_rows, COUNT(DISTINCT imei) AS dev_cnt, COUNT(DISTINCT cgi) AS cell_cnt FROM cdr_summ_imei_cell; -- 第二句关键指标 SUM和上游提供的汇总数对账 SELECT SUM(call_cnt), SUM(duration), SUM(data_flow), SUM(stay_sec) FROM cdr_summ_imei_cell; -- 第三句自检唯一性主键理论上不允许重复查出来就是设计问题 SELECT imei, stat_date, stat_hour, cgi, COUNT(*) AS dup_cnt FROM cdr_summ_imei_cell GROUP BY imei, stat_date, stat_hour, cgi HAVING COUNT(*) 1 LIMIT 10;第一句的COUNT(*)要和wc -l对上差一行都说明文件行数不对或者 LOAD DATA 丢行。wc -l统计的是换行符个数csv 最后一行如果没有换行符wc -l会少一行所以更稳的算法是wc -l结果跟SELECT COUNT(*)加一对比允许表头行存在。第二句的 SUM 对账是硬仗——如果上游给了“该文件总话单量 xxx 万条”之类的清单这里就能直接核对没有清单就只能和源文件抽样验证。第三句更重要。表上主键已经是(imei, stat_date, stat_hour, cgi)正常情况下根本查不出重复能查出COUNT(*) 1只有一种可能——建表时主键没建成或者导入走了 REPLACE 模式导致数据被覆盖但没报错。这个检查跑一遍花不了几秒钟但能挡住后面所有统计结果虚高的灾难。还有个抽样验证的小技巧值得用随机抽一台设备的某个小时看它在 csv 里对应行的数值和表里是否一致。-- 抽样查某台设备 2023-01-01 早上 8 点的小区分布 SELECT cgi, call_cnt, duration, stay_sec FROM cdr_summ_imei_cell WHERE imei 861234567890123 AND stat_date 2023-01-01 AND stat_hour 8;我一般是拿 csv 里对应行用grep搜出来手工对比一次。抽样不要只抽一行至少抽三行覆盖不同时段不然撞上脏数据的概率太低等于没验。4.3 空值、缺省与脏时间导入后必须处理的四种脏数据LOAD DATA 导入完成只是第一关脏数据清理才是日常。按出现频率排这四种最恶心。第一种是空字符串和NULL混杂。csv 里的空值可能表现为真空、NULL字符串、\N三种写法。LOAD DATA在SET子句里处理了一部分但stay_sec这种没显式处理的字段导入后可能是 0 也可能是 NULL。统一处理用一条 UPDATE-- 把 NULL 统一刷成 0避免 SUM 统计直接变 NULL UPDATE cdr_summ_imei_cell SET stay_sec 0, data_flow 0 WHERE stay_sec IS NULL OR data_flow IS NULL;第二种是时间字段越界。stat_date 0000-00-00在 MySQL 严格模式下根本插不进去但如果表里已经存在说明当时建表时没开严格模式或者 csv 里有非法值被 STR_TO_DATE 转成了 NULL。查法很简单-- 查非法日期重点是 0000-00-00 和远超当天的未来时间 SELECT stat_date, COUNT(*) FROM cdr_summ_imei_cell WHERE stat_date 0000-00-00 OR stat_date CURDATE() GROUP BY stat_date; 提示日期字段查出来是 NULL 也要小心。STR_TO_DATE 转不了的日期返回 NULL但 DDL 里 stat_date 是 NOT NULL严格模式下会直接报错。所以能用 NOT NULL 的字段尽量别留 NULL 口子脏数据在导入期暴露比在分析期暴露好一万倍。第三种是 imei 脏值。正常 IMEI 是 15 位数字但测试卡、模拟器、山寨机经常给出1234567890、355895051234567816位或者带字母的。处理方式是筛选出来单独归档不直接删-- 导出非法 imei 清单交给上游确认 SELECT imei, COUNT(*) AS cnt FROM cdr_summ_imei_cell WHERE imei NOT REGEXP ^[0-9]{15}$ GROUP BY imei;第四种是 cgi 拼接不一致。建表 DDL 里cgi列如果允许空字符串并且导入时IF(cgi, ...)没有触发就会出现一部分行是0000100001这种等宽字符串另一部分是源文件里直接给的1或100001长度不齐。JOIN cell_info 表时这种不一致会让匹配率掉到一半以下。检查也很简单按长度分组看一眼SELECT LENGTH(cgi) AS len, COUNT(*) AS cnt FROM cdr_summ_imei_cell GROUP BY LENGTH(cgi);正常情况只应该有一个分组。两个分组就说明 cgi 来源不统一用 UPDATE 把短格式统一成等宽格式即可。5. 从 csv 到 mysql 的翻车现场五条高频报错与排查路径5.1 ERROR 2002socket 连不上不是密码错现象很典型刚装完 MySQLmysql -uroot -p回车直接报ERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysql.sock (2)。第一反应是密码记错了其实根本不是这是客户端走 Unix socket 连不上服务端。原因一般三个MySQL 服务没启动启动的 socket 路径不是/tmp/mysql.sock或者你人在客户端机器上但目标 MySQL 在远程服务器。排查顺序固定# 看服务是否在跑 systemctl status mysql # 看 MySQL 实际监听的 socket 路径 mysqladmin --socket/var/run/mysqld/mysqld.sock ping如果systemctl显示服务是 running就看 socket 路径是否存在。MySQL 8.0 默认 socket 可能在/var/run/mysqld/mysqld.sock而客户端默认找/tmp/mysql.sock路径对不上就是 2002。最省心的解法是跳过 socket直接用 TCPmysql -h127.0.0.1 -P3306 -uroot -p-h127.0.0.1会强制走 TCP 协议而不是 socket。注意这里写localhost还是会走 socket只有写 IP 才走 TCP。docker 容器里连宿主机 MySQL 也同理必须加-h指定宿主 IP否则容器内根本没有宿主的 socket 文件报错一模一样。5.2 ERROR 1290secure-file-priv 拦住了 LOAD DATA现象执行LOAD DATA INFILE不带 LOCAL时报ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement。原因MySQL 默认开启secure_file_priv限制服务端只能从指定目录读文件。我用的是LOAD DATA LOCAL INFILE不走这条路但如果你图省事把 csv 传到服务器上用不带 LOCAL 的版本就会撞上这个限制。-- 先看限制目录在哪 SHOW VARIABLES LIKE secure_file_priv;输出如果是/var/lib/mysql-files/把 csv 挪到该目录再导入如果输出是空字符串表示服务端读文件被完全禁止只能改用LOAD DATA LOCAL并在客户端连接时加参数mysql --local-infile1 -uroot -p -h127.0.0.1注意--local-infile1是客户端参数MySQL 8.0 默认客户端这个选项是关的不加它执行LOAD DATA LOCAL会报另一个错ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides。改 my.cnf 放开secure_file_priv也是办法但生产库上改这个属于打开文件读取权限风险自己掂量。5.3 7z 解压时报 CRC 错误密码正确却反复失败这个报错发生在第一步还没到 MySQL 呢但它足够劝退很多人。现象是7z x解压到一半突然报CRC Failed或者输入密码后明明是对的却提示错误。原因分析第一压缩包下载不完整文件在传输过程中损坏CRC 校验自然过不去第二p7zip 版本太老对某些新压缩算法的 7z 包兼容性差第三文件名里带括号或特殊符号shell 把参数截断了。处理办法按顺序试# 1. 先测完整性不实际解压 7z t cdr_summ_imei_cell_info(csv-mysql).7z # 2. 测出来 CRC 错误重新下载并对比文件大小 ls -lh cdr_summ_imei_cell_info(csv-mysql).7z # 3. 升级 p7zip 到 16.02 以上 apt-get install --only-upgrade p7zip-full密码“正确却报错”还有一个隐蔽原因密码里带空格或特殊字符时终端输入法和键盘布局干扰导致实际输入的字符不对。7z 命令行读密码用-p参数时密码在进程列表里是明文可见的我一般先复制到剪贴板再粘贴规避手输错误。还有一种情况是加密头-mheon的包老版本 7z 工具解不了升级版本即可解决。5.4 csv 中文乱码与 GBK/UTF-8 错乱现象导入 MySQL 后province、city字段全是乱码或者插入时报Incorrect string value: \xE5\x8C\x97... for column city。原因这个场景太常见了。手机信令数据的 csv 很多来自运营商内部系统导出时用的 GBK 编码而表是 utf8mb4LOAD DATA 又没有声明字符集MySQL 把 GBK 字节流按 utf8mb4 解析中文必然乱码。# 解压后先用 file 命令确认编码 file -bi cdr_summ_imei_cell_info.csv # 输出 charsetiso-8859-1 或 charsetunknown 时多半是 GBK # 转码GBK 转 UTF-8文件大时用 iconv 会比 pandoc 之类快很多 iconv -f GBK -t UTF-8 cdr_summ_imei_cell_info.csv cdr_summ_imei_cell_info_utf8.csv转码后导入时仍然显式声明字符集最稳LOAD DATA 里CHARACTER SET utf8mb4不能省。还有一个隐藏坑csv 带 BOM 头\xEF\xBB\xBF第一列字段名会带着不可见字符IGNORE 1 LINES跳过头行后列名对不上或者第一个字段名变成\xEF\xBB\xBFimei查询时得用反引号包着写。用sed -i 1s/^\xEF\xBB\xBF//把 BOM 删掉再导。5.5 Excel 编辑过的 csv多字段、回车符、时间格式三重错位现象LOAD DATA 导入后行数比wc -l多出不少或者call_cnt明明是数字但查出来是 0某些行的字段串列stat_date变成45292这种 Excel 日期序列号。原因这个 csv 被 Excel 打开并重新保存过。Excel 保存 CSV 时会干三件坏事字段内如果有逗号它会给字段加双引号但引号规则和 RFC 4180 不一定完全一致单元格里的换行符会被直接写进 csv导致一行物理行变成多行LOAD DATA 按\n切行时直接切碎日期列被格式化成mm/dd/yyyy或序列号STR_TO_DATE解析失败返回 NULL。处理办法就一条别用 Excel 编辑 csv。如果源文件只有 Excel 处理过的版本用代码清洗# Python 清洗被 Excel 搞坏的 csv按标准 CSV 规则重写 import csv with open(cdr_summ_from_excel.csv, r, encodingutf-8) as f_in, \ open(cdr_summ_clean.csv, w, encodingutf-8, newline) as f_out: reader csv.reader(f_in) writer csv.writer(f_out) for row in reader: # csv 模块已经处理了引号和字段内换行这里只修日期 writer.writerow([cell.replace(/, -) for cell in row])这里csv.reader会正确处理引号包裹的字段内逗号和换行把 Excel 版 csv 重新标准化。日期里的/替换成-是为了让 MySQL 的STR_TO_DATE能按%Y-%m-%d解析。经验之谈拿到 csv 第一件事就复制一份原始包到只读目录所有清洗都在副本上做源文件永远不动。6. 让 cdr_summ 数据产生价值把 imei 轨迹切成停留点的窗口函数技巧6.1 先补一张 cell_info 经纬度表cdr_summ 本身只有小区编号没有经纬度不关联 cell_info 表就是一堆数字。单元格位置信息通常单独维护cgi、lac、cell_id、经度、纬度、所属区县。把这表也灌进 MySQL然后就能做空间关联了。-- 典型 cell_info 表结构csv 导入方式同前 CREATE TABLE cell_info ( cgi VARCHAR(16) PRIMARY KEY COMMENT 小区识别码, lac INT NOT NULL COMMENT 位置区编码, cell_id INT NOT NULL COMMENT 小区编号, lon DECIMAL(10, 6) NOT NULL COMMENT 经度, lat DECIMAL(10, 6) NOT NULL COMMENT 纬度, city VARCHAR(64) COMMENT 所属城市, district VARCHAR(64) COMMENT 所属区县, scene_type VARCHAR(32) COMMENT 场景商场/园区/交通枢纽/居住区 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;scene_type字段是进阶分析的关键——有了它才能区分“晚上停在居住区”和“白天停在办公园区”这是职住判断的基础。如果没有现成的场景标注可以用 POI 数据匹配小区位置周边有什么设施来打标但那个工程量大先保证经纬度和区县字段准确就足够跑通主流程。6.2 用窗口函数把轨迹切成停留点ST_Distance_Sphere 与 LAG接下来是让这份数据值钱的核心操作把 imei 的时空轨迹转成“在哪停留了多久”。原理很简单——按 imei 和时间排序计算每个点与上一个点的距离和时差距离小于阈值且持续超过时长的归为同一段停留否则视为移动。-- MySQL 8.0 窗口函数版识别单设备连续停留 WITH ordered AS ( SELECT imei, stat_date, stat_hour, cgi, lon, lat, -- 取上一个观测点的时间、坐标NULL 表示该设备第一条记录 LAG(CONCAT(stat_date, , LPAD(stat_hour, 2, 0), :00:00)) OVER (PARTITION BY imei ORDER BY stat_date, stat_hour) AS prev_ts, LAG(lon) OVER (PARTITION BY imei ORDER BY stat_date, stat_hour) AS prev_lon, LAG(lat) OVER (PARTITION BY imei ORDER BY stat_date, stat_hour) AS prev_lat FROM cdr_summ_imei_cell JOIN cell_info USING (cgi) ), move_flags AS ( SELECT imei, stat_date, stat_hour, cgi, lon, lat, prev_ts, prev_lon, prev_lat, -- 距离超过 300 米或时间间隔超过 2 小时视为新的一段轨迹 CASE WHEN prev_ts IS NULL OR TIMESTAMPDIFF(MINUTE, STR_TO_DATE(prev_ts, %Y-%m-%d %H:%i:%s), CONCAT(stat_date, , LPAD(stat_hour, 2, 0), :00:00) ) 120 OR ST_Distance_Sphere(POINT(lon, lat), POINT(prev_lon, prev_lat)) 300 THEN 1 ELSE 0 END AS is_new_segment FROM ordered ) SELECT imei, cgi, MIN(stat_date) AS seg_start_date, MIN(stat_hour) AS seg_start_hour, COUNT(*) AS total_obs, MAX(lon) AS lon, MAX(lat) AS lat FROM ( SELECT imei, stat_date, stat_hour, cgi, lon, lat, -- 累加拐点标记生成分段编号 SUM(is_new_segment) OVER (PARTITION BY imei ORDER BY stat_date, stat_hour ROWS UNBOUNDED PRECEDING) AS seg_id FROM move_flags ) seg_start GROUP BY imei, seg_id, cgi ORDER BY imei, seg_start_date, seg_start_hour;这段 SQL 的核心是LAG窗口函数取上一个观测点然后用ST_Distance_Sphere计算两个经纬度点的球面距离单位是米。ST_Distance_Sphere是 MySQL 8.0 才有的函数5.7 里没有5.7 环境要退而求其次用 Haversine 公式自己算。is_new_segment标记打出来后再用一次SUM() OVER做累加分段每个段落的 cgi、起止时间和观测次数就是一次“停留”。跑完这段 SQL每个 imei 会得到一串驻留点序列配合cell_info.scene_type就能判断这个设备晚上住在哪个区、白天出现在哪个园区区县级职住比、跨城通勤 OD 都是从这个结果继续汇总得到的。这也回到标题里cell_info存在的意义——cdr_summ 提供轨迹cell_info 提供位置语义两者结合才是完整的分析链路。我处理 2023 年区县级手机信令数据时的习惯是先跑一段小规模验证比如抽 100 台设备跑通整条 SQL确认分段结果合理再放开全量跑。这个习惯帮我躲过好几次全量跑完才发现时间字段拼接错误、全部驻留点散架的事故。数据文件到手先花十分钟做字段级抽样比导入后补救省一倍时间希望帮到你。本文还有配套的精品资源点击获取