簡介本資源圍繞通話掉線率統(tǒng)計場景提供基站維度數(shù)據(jù)分析的原始數(shù)據(jù)集與入庫腳本適合Hive/MySQL方向的數(shù)據(jù)分析初學(xué)者及需要處理話單數(shù)據(jù)的運維人員。壓縮包內(nèi)共2個文件包含1個SQL建表與導(dǎo)入腳本和1個CSV原始數(shù)據(jù)文件整體約13.03MBCSV對應(yīng)表結(jié)構(gòu)中的record_time、imei、cell、drop_num、duration字段SQL便于直接將數(shù)據(jù)導(dǎo)入MySQL進行進一步統(tǒng)計可據(jù)此還原出掉線率最高的前10基站分析過程。已有242人學(xué)習(xí)下載。讀者可獲得一份可直接導(dǎo)入使用的MySQL版本話單數(shù)據(jù)以及配套的建表語句和字段說明便于快速開展掉話率計算、基站排名等實踐練習(xí)也可作為學(xué)習(xí)Hive與MySQL數(shù)據(jù)入庫、查詢分析的入門數(shù)據(jù)素材。1. cdr_summ_imei_cell_info 是什么一個 7z 壓縮包里的信令數(shù)據(jù)底座拿到cdr_summ_imei_cell_info(csv-mysql).7z這類文件名時別急著解壓先把這個名字拆開讀——它基本把整個數(shù)據(jù)交付方案寫臉上了cdr_summ是 CDRCall Detail Record通話/呼叫詳情記錄的匯總結(jié)果imei是設(shè)備標(biāo)識cell_info是基站小區(qū)信息csv是交付格式mysql是目標(biāo)存儲7z是壓縮打包方式。這套東西在手機信令分析、區(qū)域人流洞察、通信網(wǎng)絡(luò)質(zhì)量評估項目里非常常見是一張已經(jīng)清洗好的“設(shè)備-小區(qū)-時間”三維事實表。本文會按“解壓 → 預(yù)檢 → 建表導(dǎo)入 → 字段處理 → 踩坑 → 聚合查詢”的順序把這條路完整走一遍。適合手里剛拿到這種 CSV 數(shù)據(jù)包、正琢磨怎么落庫的工程師和數(shù)據(jù)分析師。2. 先把 7z 安全解開Linux 與 Windows 下的解壓命令和預(yù)檢清單2.1 Linux 下解壓 7z 的最小命令7z 不是 Linux 默認(rèn)自帶的格式很多最小化安裝的服務(wù)器上連7z命令都不存在。所以第一步是裝工具。Debian/Ubuntu 系用p7zip-fullCentOS/RHEL 系用p7zip這個包提供7z、7za、7zr三個二進制日常用7z就夠了。# Debian/Ubuntu sudo apt update sudo apt install -y p7zip-full # CentOS/RHEL sudo yum install -y p7zip裝完之后不要直接7z x解壓先看一下壓縮包里到底有什么。7z l只列清單不落盤能讓你確認(rèn)里面是單個 CSV 還是多個 CSV路徑結(jié)構(gòu)長什么樣還能順帶看每個文件的壓縮前大小。這一步對后面判斷“要不要拆文件導(dǎo)、單表多大、磁盤夠不夠”非常關(guān)鍵。# 查看壓縮包內(nèi)容清單不占用磁盤 7z l cdr_summ_imei_cell_info(csv-mysql).7z # 解壓到指定目錄-o 后面直接跟路徑不要留空格 7z x cdr_summ_imei_cell_info(csv-mysql).7z -o./cdr_data有幾個細(xì)節(jié)值得強調(diào)。7z x會保留壓縮包內(nèi)的目錄結(jié)構(gòu)7z e會把所有文件平鋪到當(dāng)前目錄——如果壓縮包里按日期分了好幾個子目錄建議用x保留結(jié)構(gòu)否則同名文件會互相覆蓋。再有就是解壓路徑如果不存在7z會自動創(chuàng)建不需要先mkdir。另外如果這個包加密了解壓時會提示輸入密碼交互式輸入就行但把密碼直接寫在命令行參數(shù)里-p密碼會被 shell 歷史記錄和進程列表泄露多人在同一臺服務(wù)器上時尤其注意我一般寧可交互輸入。2.2 Windows 下解壓與編碼檢查Windows 端最省事的做法是裝 7-Zip右鍵壓縮包選“提取到當(dāng)前位置”或“提取到指定文件夾”。但如果你的數(shù)據(jù)流水線要寫成批處理腳本命令行方式更可靠。7-Zip 安裝目錄下的7z.exe可以在 cmd 里直接用注意路徑帶空格要加引號。C:\Program Files\7-Zip\7z.exe x D:\data\cdr_summ_imei_cell_info(csv-mysql).7z -oD:\data\cdr_csv -y-y是遇到同名文件直接覆蓋批量跑腳本時能省去卡在交互提示的問題。Windows 端比 Linux 多一個容易被忽略的檢查CSV 的編碼。用記事本或 Notepad 打開 CSV如果能看到中文看右下角編碼顯示常見的是 UTF-8、ANSIGBK、UTF-8 BOM 三種。后面的 MySQL 導(dǎo)入環(huán)節(jié)編碼必須和建表語句里的CHARACTER SET對上否則查出來的中文全是問號。2.3 解壓后第一時間做四件事解壓只是開始別急著往 MySQL 里灌。我拿到任何 CSV 數(shù)據(jù)包必做的四步預(yù)檢是看體積、看行數(shù)、看表頭、看編碼。# 看解壓后的文件體積和行數(shù) du -sh cdr_data/ wc -l cdr_data/*.csv # 看表頭前 5 行確認(rèn)列名和字段順序 head -n 5 cdr_data/cdr_summ_imei_cell_info.csv # 看文件編碼 file -i cdr_data/cdr_summ_imei_cell_info.csv這四個命令能過濾掉一大半后續(xù)麻煩。wc -l得到一個總行數(shù)這個數(shù)字要記住導(dǎo)入完成后和SELECT COUNT(*)對賬head看的是列結(jié)構(gòu)通常這類 CSV 的表頭類似date,imei,cell_id,lac_id,call_cnt,call_dur,data_bytes這種但也可能沒有表頭、直接是數(shù)據(jù)行這直接決定LOAD DATA要不要IGNORE 1 LINESfile -i看到的編碼決定導(dǎo)入時用utf8mb4還是先轉(zhuǎn)碼。另外用sed -n 2,5p抽幾行中間的數(shù)據(jù)看重點看有沒有奇怪的換行符、引號、逗號換位這關(guān)系到后面導(dǎo)入會不會錯位。3. 把 CSV 灌進 MySQL建表、LOAD DATA 與導(dǎo)入后校驗3.1 先定表結(jié)構(gòu)字段類型建錯后面全是淚看完了表頭下一步是建表。字段類型的選擇標(biāo)準(zhǔn)只有一個這個字段最終要拿來做什么。imei用來做設(shè)備去重和關(guān)聯(lián)必須能精確區(qū)分設(shè)備不能丟精度cell_id用來做小區(qū)聚合經(jīng)常作為GROUP BY和JOIN的關(guān)聯(lián)鍵時間字段要做范圍過濾和按天統(tǒng)計計數(shù)字段要聚合求和。把這些需求想清楚再動手寫 DDL。CREATE DATABASE IF NOT EXISTS sig_data DEFAULT CHARACTER SET utf8mb4; USE sig_data; CREATE TABLE cdr_summ_imei_cell_info ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, stat_date DATE NOT NULL COMMENT 統(tǒng)計日期, imei VARCHAR(20) NOT NULL COMMENT 設(shè)備唯一標(biāo)識不參與數(shù)值運算, cell_id VARCHAR(32) NOT NULL COMMENT 小區(qū)全球標(biāo)識或本地小區(qū)號, lac_id INT UNSIGNED NULL COMMENT 位置區(qū)碼, call_cnt INT UNSIGNED DEFAULT 0 COMMENT 呼叫次數(shù), call_dur INT UNSIGNED DEFAULT 0 COMMENT 通話時長單位秒, data_bytes BIGINT UNSIGNED DEFAULT 0 COMMENT 數(shù)據(jù)流量單位字節(jié), update_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 入庫時間, PRIMARY KEY (id), KEY idx_imei (imei), KEY idx_cell_date (cell_id, stat_date) ) ENGINEInnoDB;這里有兩個設(shè)計選擇值得展開。第一imei用VARCHAR(20)而不是BIGINT原因會在第四章詳細(xì)講核心是一個數(shù)字一旦超過 2^63-1BIGINT就裝不下了而且如果有前導(dǎo)零BIGINT會把它吞掉。第二索引不是越多越好idx_imei支撐“這個設(shè)備出現(xiàn)在哪些小區(qū)”的設(shè)備級查詢idx_cell_date (cell_id, stat_date)是個組合索引支撐“這個小區(qū)某天有多少設(shè)備”的區(qū)域級聚合。如果還有按小時分析的場景可以把stat_date改成stat_time DATETIME再加一個KEY idx_time (stat_time)。但這個表是匯總表粒度通常是“天設(shè)備小區(qū)”所以DATE夠了。3.2 LOAD DATA 導(dǎo)入的最小可用命令導(dǎo) CSV 進 MySQL最穩(wěn)的方式是LOAD DATA LOCAL INFILE。它比逐行INSERT快一個數(shù)量級幾千萬行的文件也能在分鐘級導(dǎo)完。但 MySQL 5.7 之后local_infile默認(rèn)是關(guān)的要先把開關(guān)打開。# 查看 local_infile 是否開啟 mysql -uroot -p -e SHOW VARIABLES LIKE local_infile; # 設(shè)置為 1當(dāng)前實例立即生效不用重啟 mysql -uroot -p -e SET GLOBAL local_infile 1;SET GLOBAL是運行時生效但新連接才有效所以你會看到有人開了開關(guān)后導(dǎo)入還是報錯那是因為那個會話是在開關(guān)打開前建立的重新連接一次即可。如果不想每次手動開可以在my.cnf的[mysqld]段寫local_infile1。接著是導(dǎo)入語句的核心部分LOAD DATA LOCAL INFILE /data/cdr_data/cdr_summ_imei_cell_info.csv INTO TABLE cdr_summ_imei_cell_info CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (stat_date, imei, cell_id, lac_id, call_cnt, call_dur, data_bytes);逐段解釋參數(shù)。CHARACTER SET utf8mb4要和 CSV 實際編碼一致如果源文件是 GBK 這里填gbk但更推薦先iconv轉(zhuǎn)成 UTF-8 再導(dǎo)因為 MySQL 的gbk跟你手頭那個 GBK 版本不一定完全對齊。FIELDS TERMINATED BY ,是列分隔符OPTIONALLY ENCLOSED BY 是說字段可能被雙引號包著也可能沒有這能解決一部分“字段里含逗號”的臟數(shù)據(jù)。LINES TERMINATED BY \n是行分隔符如果 CSV 是 Windows 下生成的行尾是\r\n這里最好寫成\r\n或者導(dǎo)入前用sed -i s/\r$//把\r剝掉否則最后一列會帶一個看不見的回車。IGNORE 1 LINES是跳過首行表頭如果文件沒有表頭這行刪掉。列清單(stat_date, imei, cell_id, lac_id, call_cnt, call_dur, data_bytes)的順序必須和 CSV 列順序完全一致。如果 CSV 里有不想導(dǎo)入的列可以用用戶變量占位比如(dummy, imei, cell_id, ...)MySQL 會把值扔進變量里不落表。這個技巧在實際項目中非常常用因為上游交付的 CSV 常?；熘恍﹥?nèi)部字段。3.3 導(dǎo)入后三步校驗別相信導(dǎo)入過程沒報錯導(dǎo)入完成不報錯只是最低標(biāo)準(zhǔn)數(shù)據(jù)對不對要主動驗證。我的習(xí)慣是連續(xù)跑三條 SQL逐條過。-- 1. 行數(shù)對賬和 wc -l 的結(jié)果比對 SELECT COUNT(*) FROM cdr_summ_imei_cell_info; -- 2. 關(guān)鍵字段完整度imei 和 cell_id 不能有空值 SELECT COUNT(*) AS total, SUM(imei IS NULL OR imei ) AS bad_imei, SUM(cell_id IS NULL OR cell_id ) AS bad_cell FROM cdr_summ_imei_cell_info; -- 3. 抽樣肉眼檢查 SELECT * FROM cdr_summ_imei_cell_info LIMIT 10;第二步的價值容易被低估。行數(shù)對賬對上不代表數(shù)據(jù)沒問題IMEI 為空或小區(qū) ID 為空的記錄在后面的聚合里會產(chǎn)生一個幽靈設(shè)備或幽靈小區(qū)查出來數(shù)據(jù)后發(fā)現(xiàn)對不上是常事。如果bad_imei或bad_cell不為零說明源文件里就有臟行這時候先別急著全量清掉用帶條件的DELETE把這批數(shù)據(jù)隔離出來統(tǒng)計占比再決定是丟棄還是回填。4. 字段與數(shù)據(jù)的落地處理IMEI、時間、小區(qū)信息的三個硬骨頭4.1 IMEI 別用整數(shù)前導(dǎo)零、超長與科學(xué)計數(shù)法IMEI 是 15 位或 17 位的數(shù)字串但“看著像數(shù)字”不代表“應(yīng)該用數(shù)字類型存”。這里有個很多新手踩過的坑如果用BIGINT存MySQL 底層是按整數(shù)處理的一旦 CSV 里的 IMEI 長度超過BIGINT能表達的范圍導(dǎo)入時要么報錯要么變成最接近的浮點數(shù)精度直接丟失。而且某些老設(shè)備的 IMEI 是 14 位數(shù)字開頭帶一個校驗位個別數(shù)據(jù)源還有前導(dǎo)零BIGINT會把它存成去掉前導(dǎo)零的整數(shù)后續(xù)和別的表做匹配時永遠(yuǎn)對不上。即使數(shù)值范圍沒超用整數(shù)存也有隱患。假如你中間用 pandas 讀 CSV 做清洗pd.read_csv(cdr.csv, dtype{imei: str})這行里的dtype一旦忘記指定pandas 會自作主張把它讀成int64等到寫回 CSV 時再丟一次精度。這種鏈條上的損耗最好在源頭就用VARCHAR切斷。我見過一個反例某團隊把 IMEI 存成DOUBLE結(jié)果查詢時看到1.23456E14這類科學(xué)計數(shù)法所有關(guān)聯(lián)查詢?nèi)珡U。所以imei字段請無條件用VARCHAR(20)別給它任何參與數(shù)值運算的機會。如果需要做設(shè)備級去重直接COUNT(DISTINCT imei)字符串去重對索引的壓力遠(yuǎn)小于超長整數(shù)的類型轉(zhuǎn)換。4.2 時間字段Unix 時間戳與字符串的解析時間字段是另一個容易翻車的點。CSV 里最常見的有三種表示標(biāo)準(zhǔn)字符串2023-06-01 12:00:00、日期2023-06-01、Unix 時間戳秒或毫秒級別的整數(shù)。第一種直接落進DATETIME列沒問題MySQL 能自動識別第二種落進DATE列沒問題第三種必須先轉(zhuǎn)換。-- 如果 CSV 里是 Unix 秒級時間戳導(dǎo)入時同步轉(zhuǎn)換 LOAD DATA LOCAL INFILE /data/cdr_data/ts.csv INTO TABLE cdr_summ_imei_cell_info FIELDS TERMINATED BY , IGNORE 1 LINES (stat_date, ts, imei, cell_id) SET stat_date FROM_UNIXTIME(ts, %Y-%m-%d %H:%i:%s);毫秒級時間戳要小心13 位的毫秒值直接FROM_UNIXTIME得到的是 1970 年附近的日期因為函數(shù)默認(rèn)按秒解析。處理方法是先除以 1000FROM_UNIXTIME(ts / 1000)。這個坑我真實踩過導(dǎo)完才發(fā)現(xiàn)日期全在 1970 年整批數(shù)據(jù)重跑了一次。如果是2023/06/01這種斜杠分隔的字符串MySQL 的嚴(yán)格模式下會直接報錯非嚴(yán)格模式下會寫入0000-00-00。這就是為什么前面一直強調(diào)先head看幾行數(shù)據(jù)看到格式不對先統(tǒng)一處理再導(dǎo)。4.3 cell_info 關(guān)聯(lián)基站維度表讓數(shù)據(jù)可用cell_id列本身只是一個編號它對應(yīng)哪個小區(qū)、在哪個經(jīng)緯度、覆蓋哪個行政區(qū)通常不在這張匯總表里。想要讓數(shù)據(jù)真正可分析得維護一張基站維度表把小區(qū)編號翻譯成地理位置和行政歸屬。常見的做法是建一張dim_cell_base列結(jié)構(gòu)大致是cell_id、base_station_name、province、city、district、longitude、latitude、coverage_type。CREATE TABLE dim_cell_base ( cell_id VARCHAR(32) NOT NULL PRIMARY KEY COMMENT 小區(qū)標(biāo)識, base_name VARCHAR(128) COMMENT 基站名稱, province VARCHAR(32), city VARCHAR(32), district VARCHAR(32), longitude DECIMAL(10, 6), latitude DECIMAL(10, 6), coverage_type VARCHAR(16) COMMENT 覆蓋類型4G/5G/室分/室外 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;維度表建好后查詢時用LEFT JOIN關(guān)聯(lián)比如統(tǒng)計某市某區(qū)每天活躍設(shè)備數(shù)SELECT b.city, b.district, d.stat_date, COUNT(DISTINCT d.imei) AS active_devices FROM cdr_summ_imei_cell_info d LEFT JOIN dim_cell_base b ON d.cell_id b.cell_id WHERE b.city 上海市 GROUP BY b.city, b.district, d.stat_date;如果聚合查詢頻繁且對響應(yīng)時間敏感可以把這個 JOIN 的結(jié)果物化成一張帶行政區(qū)的明細(xì)表省得每次查都做一次幾億行的大 JOIN。但物化會帶來數(shù)據(jù)冗余和更新成本建議一開始先用 JOIN等確認(rèn)了查詢模式再考慮物化。另外dim_cell_base里可能有部分cell_id匹配不上——這通常意味著上游數(shù)據(jù)里出現(xiàn)了新開基站的cell_id維度表還沒更新。這類缺失要在月度維護窗口統(tǒng)一補齊而不是邊查邊補。5. 避坑導(dǎo)入這類 CSV 進 MySQL 的 5 個高頻翻車點5.1 7z 密碼正確但一直報錯不是密碼問題是版本和編碼現(xiàn)象用7z x解壓一個帶密碼的包密碼確認(rèn)無誤終端卻反復(fù)提示W(wǎng)rong password或者報Cannot open file as archive。原因分兩類一是解壓工具的版本太舊不支持壓縮方使用的 AES-256 高強度加密二是密碼里含中文或特殊字符終端 locale 是POSIX或C時輸入的字節(jié)流和壓縮時不一致。解決先升級p7zip到 16.02 以上版本再把終端環(huán)境變量切到 UTF-8執(zhí)行export LC_ALLC.UTF-8后重試。如果還有問題換7z的 Windows 最新版試一次多半能解開。5.2 導(dǎo)入后中文全是亂碼CSV 編碼與表字符集不一致現(xiàn)象SELECT查出來的中文全是???或方框。原因CSV 是 GBK 編碼建表和導(dǎo)入都寫了utf8mb4數(shù)據(jù)在導(dǎo)入時被強制按 UTF-8 解析中文字節(jié)對不上。解決先file -i確認(rèn)源文件編碼再做一次轉(zhuǎn)碼再導(dǎo)入用iconv -f GBK -t UTF-8 cdr.csv cdr_utf8.csv。轉(zhuǎn)碼后別忘了重新看一次表頭因為轉(zhuǎn)換過程可能改變文件大小和末尾換行符LOAD DATA的LINES TERMINATED BY也要相應(yīng)調(diào)整。5.3 大 CSV 導(dǎo)入到一半斷開兩個全局參數(shù)和一個分片技巧現(xiàn)象幾千萬行的文件導(dǎo)到一半報MySQL server has gone away或Lost connection。原因單個 LOAD DATA 事務(wù)太大超過了max_allowed_packet或者超過了net_write_timeout的默認(rèn)時間——導(dǎo)入太久連接被服務(wù)端斷開。解決導(dǎo)入前調(diào)大這兩個參數(shù)SET GLOBAL max_allowed_packet 512 * 1024 * 1024; SET GLOBAL net_write_timeout 3600;但參數(shù)調(diào)大只是治標(biāo)。更穩(wěn)的通用做法是分片導(dǎo)入。用 Linux 的split命令把大文件拆成多個小文件每個 100 萬行左右一個文件導(dǎo)一次哪個文件失敗就只重導(dǎo)哪個。# 每 100 萬行切一個文件前綴 part_ split -l 1000000 cdr_summ_imei_cell_info.csv part_ # 對每個 part 文件循環(huán)導(dǎo)入 for f in part_*; do mysql -uroot -p --local-infile sig_data -e LOAD DATA LOCAL INFILE $f INTO TABLE cdr_summ_imei_cell_info FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY \ IGNORE 1 LINES (stat_date, imei, cell_id, lac_id, call_cnt, call_dur, data_bytes); done循環(huán)腳本注意兩點第一個文件要帶IGNORE 1 LINES跳過表頭后面的文件表頭已經(jīng)沒了要再帶IGNORE 1 LINES會把第一行真實數(shù)據(jù)丟掉。所以第一個文件單獨導(dǎo)其余文件不帶IGNORE。這個細(xì)節(jié)出錯率高我建議直接寫兩次命令而不是在循環(huán)里做區(qū)分。5.4 IMEI 變成科學(xué)計數(shù)法 1.23E14Excel 和 pandas 的雙重陷阱現(xiàn)象用 Excel 打開 CSVIMEI 列顯示1.23457E14精確數(shù)字全沒了。原因Excel 對超過 11 位的數(shù)字自動轉(zhuǎn)科學(xué)計數(shù)法而且一旦保存原始精度無法恢復(fù)。解決不要在 Excel 里打開這種原始數(shù)據(jù)文件。但這個坑還有一種隱蔽變體——如果你用 pandas 讀 CSV 后再寫回 CSV沒指定dtype同樣會經(jīng)歷int64再轉(zhuǎn)字符串的過程超長數(shù)字后半段變成 0。所以處理這類文件pd.read_csv(file, dtype{imei: str})是必寫參數(shù)不是可選項。如果發(fā)現(xiàn)手頭的 CSV 已經(jīng)被 Excel 污染過IMEI 已經(jīng)是科學(xué)計數(shù)法文本那只能找上游要原始文件重導(dǎo)或者用小區(qū)編號 時間 其他字段做近似匹配但這屬于緊急修復(fù)結(jié)構(gòu)化數(shù)據(jù)里不建議這么搞。5.5 數(shù)據(jù)錯位和行數(shù)對不上引號、逗號與行分隔符的隱藏問題現(xiàn)象LOAD DATA導(dǎo)完SELECT COUNT(*)的行數(shù)比wc -l少了或者某一行的字段值串到了相鄰列。原因CSV 個別字段內(nèi)部暗藏了逗號、換行符或雙引號導(dǎo)致解析器把一行拆成了兩行或把一列拆成了兩列。解決把FIELDS TERMINATED BY ,改成帶引號識別的版本即FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY 。如果源文件是用 Python 的csv.writer生成的它默認(rèn)會把含特殊字符的字段用雙引號包起來這個參數(shù)正好能處理。如果是手拼的 CSV 或者從 Excel 另存的 CSV字段里的逗號可能沒被正確轉(zhuǎn)義那就得先清洗。遇到臟數(shù)據(jù)用awk -F, {print NF}統(tǒng)計每行列數(shù)找出列數(shù)異于表頭的行單獨處理。6. 從一個可導(dǎo)入的表到一個可查詢的庫聚合驗證與增量更新6.1 用三條 SQL 驗證數(shù)據(jù)到底能不能用數(shù)據(jù)導(dǎo)入 MySQL 只是第一步真正判斷數(shù)據(jù)是否可用要看它能不能穩(wěn)定回答業(yè)務(wù)問題。我的驗證套路是三條 SQL 并行跑按天看設(shè)備數(shù)、按小區(qū)看呼叫量、抽樣看單個設(shè)備的行為軌跡。-- 按日活躍設(shè)備數(shù)驗證時間維度的覆蓋度 SELECT stat_date, COUNT(DISTINCT imei) AS active_devices FROM cdr_summ_imei_cell_info GROUP BY stat_date ORDER BY stat_date; -- 按小區(qū)看呼叫總量驗證空間維度的聚合能力 SELECT cell_id, SUM(call_cnt) AS total_calls FROM cdr_summ_imei_cell_info GROUP BY cell_id ORDER BY total_calls DESC LIMIT 20; -- 抽查一個設(shè)備的跨小區(qū)移動軌跡 SELECT stat_date, cell_id, lac_id FROM cdr_summ_imei_cell_info WHERE imei 861234567890123 ORDER BY stat_date;這三條 SQL 分別驗證了時間、空間、設(shè)備三個維度的可用性。如果某一天設(shè)備數(shù)驟降到 0大概率是當(dāng)天數(shù)據(jù)缺失如果某個小區(qū)的呼叫量異常高可能是同名小區(qū) ID 撞了如果單個設(shè)備的軌跡出現(xiàn)“瞬間跨省”那就要檢查是不是 IMEI 被多設(shè)備復(fù)用或數(shù)據(jù)源本身有噪聲。6.2 小區(qū)級、設(shè)備級的聚合查詢模板數(shù)據(jù)驗證通過后業(yè)務(wù)查詢就有了固定模板。區(qū)域人流分析最常用的是“按日按小區(qū)統(tǒng)計活躍設(shè)備數(shù)”設(shè)備畫像最常用的是“單個設(shè)備的時間-小區(qū)序列表”。前者適合用 GROUP BY 配合組合索引后者適合用單個 IMEI 做等值查詢。-- 按日按小區(qū)統(tǒng)計活躍設(shè)備數(shù)和人均呼叫次數(shù) SELECT stat_date, cell_id, COUNT(DISTINCT imei) AS active_devices, SUM(call_cnt) / COUNT(DISTINCT imei) AS avg_calls_per_device FROM cdr_summ_imei_cell_info WHERE stat_date BETWEEN 2023-06-01 AND 2023-06-30 GROUP BY stat_date, cell_id ORDER BY stat_date, active_devices DESC;如果聚合查詢經(jīng)常按照這個模板跑可以把這個匯總結(jié)果物化成一張小表agg_cell_daily每天由定時任務(wù)刷新前端查詢走小表響應(yīng)時間能壓到百毫秒級別。物化表不需要保留全部字段只需要stat_date、cell_id、active_devices、total_calls、total_dur這幾列體積比明細(xì)表小兩個數(shù)量級維護成本很低。6.3 用導(dǎo)入日志表實現(xiàn)增量的可重跑機制這種 CSV 數(shù)據(jù)包往往是按周期交付的比如每天凌晨生成前一天的匯總文件。如果每次都用LOAD DATA直接往正式表里插一旦同一個文件被重復(fù)執(zhí)行就會產(chǎn)生重復(fù)行。我的做法是建一張導(dǎo)入日志表每次導(dǎo)入前先查這個文件是否處理過處理過就跳過形成一個天然的冪等機制。CREATE TABLE cdr_import_log ( file_name VARCHAR(255) NOT NULL PRIMARY KEY, file_md5 CHAR(32) NOT NULL, row_count INT NOT NULL, imported_at DATETIME DEFAULT CURRENT_TIMESTAMP );導(dǎo)入流程變成三步算文件的 MD5 → 查cdr_import_log里有沒有同 MD5 的記錄 → 沒有才執(zhí)行LOAD DATA然后向日志表插一條記錄。這樣即使腳本被調(diào)度系統(tǒng)重跑或者有人手動重復(fù)執(zhí)行數(shù)據(jù)也不會重復(fù)入庫。如果發(fā)現(xiàn)某個文件導(dǎo)壞了刪除數(shù)據(jù)時也先查日志定位到具體文件和導(dǎo)入時間處理起來不會大海撈針。數(shù)據(jù)導(dǎo)入這件事做得多了之后我對任何數(shù)據(jù)包的第一反應(yīng)都是先看結(jié)構(gòu)再做操作從不在沒看清表頭、沒確認(rèn)編碼、沒記錄行數(shù)的情況下直接灌庫。灌庫后也一定留一手校驗和日志因為寧可慢十分鐘也不要在一個臟數(shù)據(jù)上重跑一整夜。希望這些流程和坑能幫你在自己的環(huán)境里少走一圈彎路。本文還有配套的精品資源點擊獲取