MySQL数据库查询相关数据

2020-08-10

数据处理

使用源数据，excel进行数据处理

  1. 使用函数

    IF(CELL("format",B5)="D1",B5," ")

    将数据进行筛选

  2. 数据格式转化

    通话时间转化为秒单位

=IF(ISERROR(FIND("分",F2)),0,LEFT(F2,FIND("分",F2)-1)*60)+IF(ISERROR(FIND("秒",F2)),0,IF(ISERROR(FIND("分",F2)),MID(F2,1,FIND("秒",F2)-1),MID(F2,FIND("分",F2)+1,FIND("秒",F2)-FIND("分",F2)-1)))

流量单位转化为KB

=IF(ISERROR(FIND("M",E2)),IF(ISERROR(FIND("K",E2)),0,--MID(E2,1,FIND("K",E2)-1)),MID(E2,1,FIND("M",E2)-1)*1024+IF(ISERROR(FIND("K",E2)),0,MID(E2,FIND("M",E2)+2,FIND("K",E2)-(FIND("M",E2)+2))))

MySQL数据库查询相关数据

分析相关数据

1.分析个人语音通信中每个月的主叫和被叫的总次数及平均次数；

select 通信方式,月份 ,count(序号)from b group by 通信方式,月份;

image-20200620110553322

select 通信方式,月份 ,count(序号)/6 as 平均次数 from b group by 通信方式,月份;

image-20200620110754178

2.分析个人语音通信中每个月主叫和被叫联系人总人数及平均人数；

select 通信方式,月份 ,count(对方号码) as 次数 from b group by 通信方式,月份;

image-20200620110917340

select 通信方式,月份 ,count(对方号码)/6 as 平均人数 from b group by 通信方式,月份;

image-20200620111112080

3.分析个人语音通信中每个月主叫和被叫通信总时长及平均时长；

select 通信方式,月份 ,sum(时长) as 总时长 from b group by 通信方式,月份;

image-20200620111339895

select 通信方式,月份 ,sum(时长)/6 as 平均时长 from b group by 通信方式,月份;

image-20200620111435752

4.分析个人每月前5名语音主叫联系人和被叫联系人及其通信时长；

select 月份,通信方式,对方号码,sum(时长) from b where 月份=i group by 月份,通信方式,对方号码 order by 月份,通信方式,sum(时长) desc limit 5;

image-20200620112308084

5.分析个人每月语音通信中郑州市和非郑州市主叫和被叫的总时长

select 通信方式,月份,sum(时长) as 平均时长 from b WHERE 通信地点 like “%郑州%” group by 通信方式,月份;

image-20200620113432900

select 通信方式,月份,sum(时长) as 平均时长 from b WHERE 通信地点 not like “%郑州%” group by 通信方式,月份;

image-20200620113450470

6.分析个人短信通话中日平均短信数和月平均短信数

select 通信方式,COUNT(序号)/5 as 月平均短信 from a group by 通信方式;

image-20200620114231308

select 通信方式,COUNT(序号)/150 as 日平均短信 from a group by 通信方式;

image-20200620114253295

7.分析个人短信通话中每月前5名联系人

select 月份,通信方式,对方号码,COUNT(对方号码) as 数量 from a WHERE 月份=i group by 月份,通信方式,对方号码 ORDER BY COUNT(对方号码) desc;

image-20200620115040711

8.分析个人近6个月来日平均网络流量和月平均网络流量

SELECT sum(流量) /5 AS 月平均流量 FROM c;

image-20200620115203823

SELECT sum(流量) /150 AS 日平均流量 FROM c;

image-20200620115231718

9.分析个人通信行为模式，如通话高峰、短信高峰及网络高峰

把时间段分为8组 0-3点为第一个时间段，timegroup值为0； 3-6点为第二个时间段，timegroup值为1；6-9点为第三个时间段，timegroup值为2；以此类推,timegroup的取值为0-7；

select floor(hour(时间)/3) as timegroup,sum(时长) as “该时间段内通信时长(秒)” from b group by timegroup order by sum(时长) desc;

image-20200620161231537

select floor(hour(时间)/3) as timegroup,count(序号) as “时间段内短信条数” from a group by timegroup order by count(序号) desc;

image-20200620155433760

select floor(hour(时间)/3) as timegroup,sum(流量) as “时间段内流量kb” from c group by timegroup order by sum(流量) desc;

image-20200620160718713

从以上的分析中，我们可以得知：通话高峰为下午6点到9点；短信高峰为上午9点到12点；流量使用高峰为上午6点到9点。