利用Sql处理MR栅格数据

披头

发布于 2020-07-22 15:32:13

1K0

发布于 2020-07-22 15:32:13

文章被收录于专栏：datartisandatartisan

工作中需要处理MR栅格数据，原始数据关键列类似这个样子：

要处理成这个样子(栅格内主服务小区CGI中可能包含50多个CGI，只提取前15个)

原始文件有100多个，一共有1000多万行，而且原始数据列特别多，大概几十列吧，合并成多个excel大文件也能搞，就是操作起来太费劲，这种活最合适采用数据库或者python干了，今天咱们就来看看sql如何实现

合并原始文件

关于合并csv文件的工作，之前有文章写过，本文就不再赘述，参考这篇文章即可 利用Python批量合并csv

数据库导入合并后文件

使用navicat可以方便快速的导入csv文件，这里要特别注意的是：文件导入时默认所有字段均为varchar(255)数据类型，注意修改栅格内主服务小区CGI字段类型为text、MR总点数为int、RSRP<=-110占比为float

结果查询

根据文件格式转换说明编写sql

栅格中心经纬度标记：中心经度_中心纬度；
RSRP覆盖率：1-RSRP<=-110占比；
栅格总采样点数：MR总点数；
小区1ECGI：栅格内主服务小区CGI按照分号拆分后的第1个CGI；
小区1采样点(>-110）数量:栅格内主服务小区采样点数量拆分后第1个数字 - 栅格内主服务小区弱覆盖采样点数量拆分后第1个数字；
小区1采样点数量：栅格内主服务小区采样点数量拆分后第1个数字
......

合并列

mysql合并列使用CONCAT函数或者CONCAT_WS函数，语法是：

CONCAT(str1,str2,...)

CONCAT_WS(separator,str1,str2,...)

这里的写法就是：

# 写法1
concat(mr.`中心经度`,'_',mr.`中心纬度`) AS `栅格中心经纬度标记`
# 写法2
concat_ws('_',mr.`中心经度`,mr.`中心纬度`) AS `栅格中心经纬度标记`

拆分列并提取元素

mysql使用SUBSTRING_INDEX(str,delim,count)，其含义是获取源字符串str中按照分隔符delim分割后，第count个分隔符之前的子字符串，支持正向和反向索引，分别以1和-1开头，示例查询如下

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'

获取第1个元素比较容易，获取第2个元素/第n个元素可以采用二次拆分的写法，类似这样：

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 1);
        -> 'www'
mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1);
        -> 'mysql'

具体到该问题，可以这么写：（由于待拆分列包含的分号个数不同，所以需要先拼接14个分号（想获取待拆分列前15个元素，待拆分列最少一个元素））

SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',2),';',-1) as 小区2采样点数量,

case when 
    LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',2),';',-1))>0 
    then
    (SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',2),';',-1)-SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区弱覆盖采样点数量`,';;;;;;;;;;;;;;'),';',2),';',-1)) 
    else '' end as '小区2采样点(>-110）数量'

完整sql如下：

SELECT
concat(mr.`中心经度`,'_',mr.`中心纬度`) AS `栅格中心经纬度标记`,
-- concat_ws('_',mr.`中心经度`,mr.`中心纬度`) AS `栅格中心经纬度标记`,
round( 100 - mr.`RSRP<=-110占比`,2) AS `RSRP覆盖率`,
mr.`MR总点数` AS `栅格总采样点数`,
SUBSTRING_INDEX(mr.`栅格内主服务小区CGI`,';',1) as 小区1ECGI,
SUBSTRING_INDEX(mr.`栅格内主服务小区采样点数量`,';',1)-SUBSTRING_INDEX(mr.`栅格内主服务小区弱覆盖采样点数量`,';',1) as '小区1采样点(>-110）数量',
SUBSTRING_INDEX(mr.`栅格内主服务小区采样点数量`,';',1) as 小区1采样点数量,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',2),';',-1) as 小区2ECGI,
case when LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',2),';',-1))>0 then
 (SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',2),';',-1)-SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区弱覆盖采样点数量`,';;;;;;;;;;;;;;'),';',2),';',-1)) else '' end as '小区2采样点(>-110）数量',
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',2),';',-1) as 小区2采样点数量,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',3),';',-1) as 小区3ECGI,
case when LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',3),';',-1))>0 then
 (SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',3),';',-1)-SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区弱覆盖采样点数量`,';;;;;;;;;;;;;;'),';',3),';',-1)) else '' end as '小区3采样点(>-110）数量',
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',3),';',-1) as 小区3采样点数量,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',4),';',-1) as 小区4ECGI,
case when LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',4),';',-1))>0 then
 (SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',4),';',-1)-SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区弱覆盖采样点数量`,';;;;;;;;;;;;;;'),';',4),';',-1)) else '' end as '小区4采样点(>-110）数量',
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',4),';',-1) as 小区4采样点数量,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',5),';',-1) as 小区5ECGI,
case when LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',5),';',-1))>0 then
 (SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',5),';',-1)-SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区弱覆盖采样点数量`,';;;;;;;;;;;;;;'),';',5),';',-1)) else '' end as '小区5采样点(>-110）数量',
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',5),';',-1) as 小区5采样点数量,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',6),';',-1) as 小区6ECGI,
case when LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',6),';',-1))>0 then
 (SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',6),';',-1)-SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区弱覆盖采样点数量`,';;;;;;;;;;;;;;'),';',6),';',-1)) else '' end as '小区6采样点(>-110）数量',
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',6),';',-1) as 小区6采样点数量,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',7),';',-1) as 小区7ECGI,
case when LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',7),';',-1))>0 then
 (SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',7),';',-1)-SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区弱覆盖采样点数量`,';;;;;;;;;;;;;;'),';',7),';',-1)) else '' end as '小区7采样点(>-110）数量',
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',7),';',-1) as 小区7采样点数量,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',8),';',-1) as 小区8ECGI,
case when LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',8),';',-1))>0 then
 (SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',8),';',-1)-SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区弱覆盖采样点数量`,';;;;;;;;;;;;;;'),';',8),';',-1)) else '' end as '小区8采样点(>-110）数量',
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',8),';',-1) as 小区8采样点数量,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',9),';',-1) as 小区9ECGI,
case when LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',9),';',-1))>0 then
 (SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',9),';',-1)-SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区弱覆盖采样点数量`,';;;;;;;;;;;;;;'),';',9),';',-1)) else '' end as '小区9采样点(>-110）数量',
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',9),';',-1) as 小区9采样点数量,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',10),';',-1) as 小区10ECGI,
case when LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',10),';',-1))>0 then
 (SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',10),';',-1)-SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区弱覆盖采样点数量`,';;;;;;;;;;;;;;'),';',10),';',-1)) else '' end as '小区10采样点(>-110）数量',
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',10),';',-1) as 小区10采样点数量,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',11),';',-1) as 小区11ECGI,
case when LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',11),';',-1))>0 then
 (SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',11),';',-1)-SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区弱覆盖采样点数量`,';;;;;;;;;;;;;;'),';',11),';',-1)) else '' end as '小区11采样点(>-110）数量',
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',11),';',-1) as 小区11采样点数量,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',12),';',-1) as 小区12ECGI,
case when LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',12),';',-1))>0 then
 (SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',12),';',-1)-SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区弱覆盖采样点数量`,';;;;;;;;;;;;;;'),';',12),';',-1)) else '' end as '小区12采样点(>-110）数量',
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',12),';',-1) as 小区12采样点数量,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',13),';',-1) as 小区13ECGI,
case when LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',13),';',-1))>0 then
 (SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',13),';',-1)-SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区弱覆盖采样点数量`,';;;;;;;;;;;;;;'),';',13),';',-1)) else '' end as '小区13采样点(>-110）数量',
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',13),';',-1) as 小区13采样点数量,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',14),';',-1) as 小区14ECGI,
case when LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',14),';',-1))>0 then
 (SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',14),';',-1)-SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区弱覆盖采样点数量`,';;;;;;;;;;;;;;'),';',14),';',-1)) else '' end as '小区14采样点(>-110）数量',
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',14),';',-1) as 小区14采样点数量,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',15),';',-1) as 小区15ECGI,
case when LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区CGI`,';;;;;;;;;;;;;;'),';',15),';',-1))>0 then
 (SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',15),';',-1)-SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区弱覆盖采样点数量`,';;;;;;;;;;;;;;'),';',15),';',-1)) else '' end as '小区15采样点(>-110）数量',
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(mr.`栅格内主服务小区采样点数量`,';;;;;;;;;;;;;;'),';',15),';',-1) as 小区15采样点数量
FROM
mr

-- end --

感觉有收获的话，帮忙点个赞呗

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2020-07-18，如有侵权请联系 cloudcommunity@tencent.com 删除

文件存储