河南中医药大学 《 MySQL数据库应用》课程设计报告 题目:个人通信数据分析 院系:信息技术学院 班级: XXXXXXXXXXX 学生学号: XXXXXXX 学生姓名: XXXXXX 联系方式 XXXXX 指导教师: XXXXX 完成日期:X年X月X日
0 河南中医药大学 《MySQL 数据库应用》课程设计报告 题目:个人通信数据分析 院 系: 信息技术学院 班 级: XXXXXXXXXXX 学生学号: XXXXXXX 学生姓名 : XXXXXX 联系方式: XXXXX 指导教师: XXXXX 完成日期: XX 年 XX 月 XX 日
《MySL数据库应用》课程设计报告 题目:个人通信数据分析 1.题目概述 2.1数据获取:从中国移动网上营业厅获取个人6月来通信数据,具体步骤:2 22数据预处理:使用 Microsoft excel工作表处理得到的各个表,具体步骤: 2.3数据转化:将修改后的表用记事本打开,另存为文本文档类型,格式为UTF-8:处理后 如下 24新建 mysql数据库gtx,添加 tonghua, duanxin, liuliang表,sql语句如下: 2.5使用 Navicat导入数据 3数据分析工作过程 55677 3.1分析个人语音通信中每个月的主叫和被叫的总次数及平均次 3.2分析个人语音通信中每个月主叫和被叫联系人总人数及平均人数 33分析个人语音通信中每个月主叫和被叫通信总时长及平均时长 3.4分析个人每月前5名语音主叫联系人和被叫联系人及其通信时长; 3.5分析个人每月语音通信中郑州市和非郑州市主叫和被叫的总时长 36分析个人短信通话中日平均短信数和月平均短信数 3.7分析个人短信通话中每月前5名联系人 3.8分析个人近6个月来日平均网络流量和月平均网络流量 3.9分析个人通信行为模式,如通话高峰、短信高峰及网络高峰 111 3.10分析个人每月平均通话费用,短信费用及流量费用以及每总费用(按月份降序);19 4.结论与总结
1 《MySQL 数据库应用》课程设计报告........................................................................................ 0 题目:个人通信数据分析 .......................................................................................................... 0 1.题目概述.............................................................................................................................. 2 2.1 数据获取:从中国移动网上营业厅获取个人 6 月来通信数据,具体步骤:................... 2 2.2 数据预处理:使用 Microsoft Excel 工作表处理得到的各个表,具体步骤:................... 3 2.3 数据转化:将修改后的表用记事本打开,另存为文本文档类型,格式为 UTF-8;处理后 如下:.............................................................................................................................. 5 2.4 新建 mysql 数据库 grtx,添加 tonghua,duanxin,liuliang 表,sql 语句如下:.................... 5 2.5 使用 Navicat 导入数据:.............................................................................................. 6 3.数据分析工作过程................................................................................................................ 7 3.1 分析个人语音通信中每个月的主叫和被叫的总次数及平均次........................................ 7 3.2 分析个人语音通信中每个月主叫和被叫联系人总人数及平均人数;.............................. 8 3.3 分析个人语音通信中每个月主叫和被叫通信总时长及平均时长;................................. 9 3.4 分析个人每月前 5 名语音主叫联系人和被叫联系人及其通信时长;.............................10 3.5 分析个人每月语音通信中郑州市和非郑州市主叫和被叫的总时长;.............................14 3.6 分析个人短信通话中日平均短信数和月平均短信数;..................................................14 3.7 分析个人短信通话中每月前 5 名联系人;...................................................................15 3.8 分析个人近 6 个月来日平均网络流量和月平均网络流量;...........................................17 3.9 分析个人通信行为模式,如通话高峰、短信高峰及网络高峰;....................................18 3.10 分析个人每月平均通话费用,短信费用及流量费用以及每总费用(按月份降序);....19 4.结论与总结 .........................................................................................................................21
1.题目概述 随着智能手机的发展及普及,越来越多的人开始看中通信业务中所反映出的 个人行为习惯及各项通信消费所占的比重。本次课程设计通过对个人上网方式以及各 项费用的分析,总结出个人上网通信习惯以及消费习惯,帮助个人选择更合适的手机 套餐。 本次课程设计中个人电话使用中国移动通信,5月底变换通信套餐为“校园 网”套餐(每月38元套餐,包括240分钟校园wi局域网上网,500M国内流量, 120通话分钟,2.5G省内,3G校园流量,共5.5G流量),6月内通信所在地均为郑 州,通信联系人所在地主要也为郑州本地。1月到2月中旬在使用得无线网主为4G 流量上网,2月份开学后到校主要使用无线wifi上网。通信中由于上网业务使用较多, 短信业务相对使用较少。 本次课程设计因为数据较为密集,几乎没有不通信的日期,故在统计平均值, 如平均日短信数时忽略未有短信通信的日期。由于本次课程设计开始较早,故6月份 的数据较少,仅有的数据不满足有些题目的要求(如6月还无被叫等),故一些查询 中没有显示6月的信息,月平均通话费用因为6月费用为0而小于正常平均值。为了 适应不同的应用,査询结果的平均值有小数时并未设置取整等格式,保留原来小数形 式。由于通话时间较短,故在统计高峰时只按起始时间统计。 所需关键技术有:使用 Excel进行数据加工和预处理,使用 Navicat for Mysql 数据导入多条数据,使用 Mysql对应的sql语句查询分析 技术环境:安装 Microsoft Excel工作表软件, Mysql5.5软件, Navicat for Mysql 软件 Mysql Workbench软件及 windows10操作系统的计算机。 相关因素:数据来源:中国移动网上营业厅查询业务。 2.数据实现工作过程 21数据获取:从中国移动网上营业厅获取个人6月来通信数据,具 体步骤 1)用浏览器打开中国移动网上营业厅官网 2)搜索“通话记录查询”,找到详单查询中的月账单查询;
2 1.题目概述 随着智能手机的发展及普及,越来越多的人开始看中通信业务中所反映出的 个人行为习惯及各项通信消费所占的比重。本次课程设计通过对个人上网方式以及各 项费用的分析,总结出个人上网通信习惯以及消费习惯,帮助个人选择更合适的手机 套餐。 本次课程设计中个人电话使用中国移动通信,5 月底变换通信套餐为“校园 网”套餐(每月 38 元套餐,包括 240 分钟校园 wifi 局域网上网,500M 国内流量, 120 通话分钟,2.5G 省内,3G 校园流量,共 5.5G 流量),6 月内通信所在地均为郑 州,通信联系人所在地主要也为郑州本地。1 月到 2 月中旬在使用得无线网主为 4G 流量上网,2 月份开学后到校主要使用无线 wifi 上网。通信中由于上网业务使用较多, 短信业务相对使用较少。 本次课程设计因为数据较为密集,几乎没有不通信的日期,故在统计平均值, 如平均日短信数时忽略未有短信通信的日期。由于本次课程设计开始较早,故 6 月份 的数据较少,仅有的数据不满足有些题目的要求(如 6 月还无被叫等),故一些查询 中没有显示 6 月的信息,月平均通话费用因为 6 月费用为 0 而小于正常平均值。为了 适应不同的应用,查询结果的平均值有小数时并未设置取整等格式,保留原来小数形 式。由于通话时间较短,故在统计高峰时只按起始时间统计。 所需关键技术有:使用 Excel 进行数据加工和预处理,使用 Navicat for Mysql 数据导入多条数据,使用 Mysql 对应的 sql 语句查询分析。 技术环境:安装 Microsoft Excel 工作表软件,Mysql5.5 软件,Navicat for Mysql 软件,Mysql Workbench 软件及 windows10 操作系统的计算机。 相关因素: 数据来源:中国移动网上营业厅查询业务。 2.数据实现工作过程 2.1 数据获取:从中国移动网上营业厅获取个人 6 月来通信数据,具 体步骤: 1)用浏览器打开中国移动网上营业厅官网; 2)搜索“通话记录查询”,找到详单查询中的月账单查询;
3)通过拨打10086人工服务获得服务密码; 4)登录后下载每月通话,短信及流量数据的 Excel表单。获得部分表如 1通话详单 本地被叫理话费000元本地主叫遇话费16.53元本地长培基本费(不含湛澳台)000元 地长速费(不舍港台)119元 时长通信共蓝资优惠费资源收通但费(元) 620711 1分4秒丰地神州行轻松卡C需餐 16:2B15 48秒本地神州行轻松卡C餐 15:1033 本地神州行轻松卡C套 丰地神州行轻松卡C密餐 182106 1分3秒本地神州行轻松卡C套薔 242017/1 本地州行轻松卡C喜簧 地神州行轻松卡C素餐 214428 神州行轻松卡C喜餐 2820171/7 37秒丰地州行轻松卡C餐 20170101-20170131h+ 2.2数据预处理:使用 Microsoft Excel工作表处理得到的各个表, 具体步骤: 1)用 Microsoft exce工作表打开数据表单 2)删除表头题目,表尾总计,以及无用列(如通话表中的套餐优惠,通 信资源) 3)由于日期行为合并行,故需要修改日期行:取消合并单元格,复制各 日期到各对应行的末尾,删除原始日期的单元格,选中所有数据所占的单元格范围, 选择编辑,定位,空值,删除,将日期排好位置, 4)处理特殊列:将通话中的通信时长统一换算成秒单位,在流量表中将 流量转化为kb。点击新列输入“=”,在插入函数中输入=1F( SERROR(HND("分 El),0, LEFT(El, FINDO 分 E1-1 60)+IF(ISERROR(FINDC 秒 ",E)0.JF( ISERROR(FⅠND"分"E1)MD(E1,1,FIND("秒",E1)1.MD(E1,FIND("分 "1)+1FIND("秒",E1)}FIND("分",E1)l)) -IF(SERROR(FINDOM" BI),IF(SERROR(FIND(K", B1),0,(Bl, 1, FINDO K, B1l), MID(Bl, 1, FINDCM", B1)-1)*1024+IF(SERROR(FINDOK" B1),0, MID(B1
3 3)通过拨打 10086 人工服务获得服务密码; 4)登录后下载每月通话,短信及流量数据的 Excel 表单。获得部分表如 下 : 2.2 数据预处理:使用 Microsoft Excel 工作表处理得到的各个表, 具体步骤: 1)用 Microsoft Excel 工作表打开数据表单; 2)删除表头题目,表尾总计,以及无用列(如通话表中的套餐优惠,通 信资源); 3)由于日期行为合并行,故需要修改日期行:取消合并单元格,复制各 日期到各对应行的末尾,删除原始日期的单元格,选中所有数据所占的单元格范围, 选择编辑,定位,空值,删除,将日期排好位置。 4)处理特殊列:将通话中的通信时长统一换算成秒单位,在流量表中将 流量转化为 kb。点击新列输入“=”,在插入函数中输入=IF(ISERROR(FIND("分 ",E1)),0,LEFT(E1,FIND(" 分 ",E1)-1)*60)+IF(ISERROR(FIND(" 秒 ",E1)),0,IF(ISERROR(FIND(" 分 ",E1)),MID(E1,1,FIND(" 秒 ",E1)-1),MID(E1,FIND(" 分 ",E1)+1,FIND("秒",E1)-FIND("分",E1)-1))) =IF(ISERROR(FIND("M",B1)),IF(ISERROR(FIND("K",B1)),0,--MID(B1,1,FIND(" K",B1)-1)),MID(B1,1,FIND("M",B1)-1)*1024+IF(ISERROR(FIND("K",B1)),0,MID(B1
FINDCM"B)+2,FIND"K"B)(FIND("M"B1)+2))(上式中表格名与操作中“通信 时长”列第一行数据所在位置有关),拉动输出列右下角得到十字,得到一列数据。 5)每张表将对应的各月份添加在新列(有些 MYSQL图形化软件不支持 month函数); 7)插入新“序号”列 6)另存为csv格式 处理后如下 10:34:44被叫 37156776323本地 15537127519本地 315:10:33被叫 17719822768本地 621:17:16被叫 13838516056本地 0000000008 1 75 567890123456 721:19:36被叫 13838516056本地 821:23:27被叫 13838516056本地 922:27:20被叫 13838516056本地 11036 1016:51:01被叫 17719822758本地 1119:29:55被叫 18638005466本地 13838516056本地 18037888656本地 0. 1420:30:02主叫 13503818025本地 520:43:15被叫 13673697112本地 21:44:2 1710:43:47被叫 17734818612本地 11111111 1812:45:10被叫 3516056本 1915:23:18主叫 18039333821本地 0.58 2021:18:53被叫 13838516056本地 218:52:47被叫 15639711080本地 2321:46:00被叫 2411:03:30被叫 13838516056本地 37156680372本地 37156680372本 37160980271本地 4 18603719315本地 2911:40:19主叫 15639701650本地 1 0主叫 3116:40:42主叫 13503818025本地 3221:24:45被叫
4 FIND("M",B1)+2,FIND("K",B1)-(FIND("M",B1)+2))))(上式中表格名与操作中“通信 时长”列第一行数据所在位置有关),拉动输出列右下角得到十字,得到一列数据。 5)每张表将对应的各月份添加在新列(有些 MYSQL 图形化软件不支持 month 函数); 7)插入新“序号”列; 6)另存为 .csv 格 式 。 处 理 后 如 下 :
23数据转化:将修改后的表用记事本打开,另存为文本文档类型, 格式为UTF8;处理后如下: g,10.:3:4被m,371579632本地,0.1,6 3851605 13838516056,本地,0,1,1036 3503818025,本地,0.29,1,18 5,20:43:15,被 16,21:44:28,主叫,18614980732,本地,0.29,1,23 345 26,14:19:15,主叫,37156680372 27.8:30:37.被叫,37160980271.本地,0.1.47 24新建mysq数据库grtx,添加 tonghua, duanxin, luliang表,sql 语句如下: create database grtx create table tonghua th xuhao int auto increment primary key th qishi time not null th fangshi char(4)not null th duifang char(11)not null th leixing char(4)not null th feiyang float, th yuefen in th shichang int not null create table duanxi dx xuhao int auto increment primary key, dx qishi time not null, dx duifang char(15) not null
5 2.3 数据转化:将修改后的表用记事本打开,另存为文本文档类型, 格式为 UTF-8;处理后如下: 2.4 新建 mysql 数据库 grtx,添加 tonghua,duanxin,liuliang 表,sql 语句如下: create database grtx; use grtx; create table tonghua ( th_xuhao int auto_increment primary key, th_qishi time not null, th_fangshi char(4) not null, th_duifang char(11) not null, th_leixing char(4)not null, th_feiyong float, th_yuefen int not null, th_shichang int not null ); create table duanxin ( dx_xuhao int auto_increment primary key, dx_qishi time not null, dx_duifang char(15) not null
dx yuefen int not null create table liuliang 11 xuhao int increment primary key, 11 feiyang float ll rigi date not null ll liuliang int not null, II yuefen int not null 2.5使用 Navicat导入数据: 1)右击表名,选择导入数据 2)选择txt文本格式 3)更改栏位分隔符为“,”,文本限定符为“无 你可以为源定义一些附加的选项,(4/8) 一个数编行 时间分符 小数卓符号 日期时闻排序日期时间 上步下-步→取 4)定义源附加选项
6 dx_feiyong float, dx_riqi date not null, dx_yuefen int not null ); create table liuliang ( ll_xuhao int auto_increment primary key, ll_qishi time not null, ll_feiyong float, ll_riqi date not null, ll_liuliang int not null, ll_yuefen int not null ); 2.5 使用 Navicat 导入数据: 1)右击表名,选择导入数据; 2)选择 txt 文本格式; 3)更改栏位分隔符为“,”,文本限定符为“无”; 4)定义源附加选项:
你可以定义栏位对应。设置对应指定的源栏位和目的栏位之间的对应关系。(6/8) 20170601-20170606 目标表 栏位 luliang 5)定义栏位对应,[一 上步[下步→[取消 6)导入成功。 3.数据分析工作过程 31分析个人语音通信中每个月的主叫和被叫的总次数及平均次 数;SQL语句: select th fangshi as方式 th yuefen as月份 count(th xuhao)as总 E from tonghua group by th fangshi, th yuefen; select th fangshi as方式, count(th xuhao) ount(distinct(th yuefen)as平均通话次 a from tonghua group by th fangshi 果
7 5)定义栏位对应: 6)导入成功。 3.数据分析工作过程 3.1 分析个人语音通信中每个月的主叫和被叫的总次数及平均次 数;SQL 语句:select th_fangshi as 方式,th_yuefen as 月份,count(th_xuhao) as 总 数 from tonghua group by th_fangshi,th_yuefen; select th_fangshi as 方式,count(th_xuhao)/count(distinct(th_yuefen)) as 平均通话次 数 from tonghua group by th_fangshi; 结果:
ysq1> select th fangshi as方式, th yuefen as月份, count( th xuhao)as总数from by th fangshi, th 主主被被被 11 rows in set (0.00 sec) mysql> select th fangshi as H=t, count (th xuhao)/count(distinct (th yuefen))a 均通话次数from hua group by th fangshi 平均通话次数 41.4000 3.2分析个人语音通信中每个月主叫和被叫联系人总人数及平均 人数;SQL语句: select th fangshi as方式 th yuefen as月 份 count(distinct(th duifang)as联系人总数 from tonghua group by th fangshi,th yuefen select th fangshi as平均人数 count( th duifang)count(distinct(th yuefen)as联系 人平均数 from tonghua group by th fangshi
8 3.2 分析个人语音通信中每个月主叫和被叫联系人总人数及平均 人数; SQL 语句: select th_fangshi as 方 式 ,th_yuefen as 月 份 ,count(distinct(th_duifang)) as 联 系 人 总 数 from tonghua group by th_fangshi,th_yuefen; select th_fangshi as 平均人数,count(th_duifang)/count(distinct(th_yuefen)) as 联系 人平均数 from tonghua group by th_fangshi;
系人总数公 fangshi1as方式, th yuefen as月份, count(distinct(th duifang) th fangshi, th yuefen; 月份|联系人总数 sq1> select th fangshi as平均人数, count( th huifang)/ count( distinct( th yuefen) 联系人平均数 平均人数 联系人平均数 结果 33分析个人语音通信中每个月主叫和被叫通信总时长及平均时 长;SQL语句: select th fangshi as方式 th yuefen as月份, sum(th shichang)as 通信总时(秒 )"from tonghua group by th fangshi, th yuefen select th fangshi as方式,sum(th shichang )/count(distinct(th yuefen)as"月平均时 长(秒” from tonghua group by th fangshi 结 果
9 结果: 3.3 分析个人语音通信中每个月主叫和被叫通信总时长及平均时 长;SQL 语句:select th_fangshi as 方式,th_yuefen as 月份,sum(th_shichang) as " 通信总时(秒)" from tonghua group by th_fangshi,th_yuefen; select th_fangshi as 方式,sum(th_shichang)/count(distinct(th_yuefen)) as "月平均时 长(秒)" from tonghua group by th_fangshi; 结果: