学以致用: 我的短信话单分析(页 1) - ORACLE等数据库 - LoveUnix 最强AIX小机技术论坛,UNIX,Linux,存储,数据库,中间件,开发,管理

2004-10-25 17:27 seven

1. 准备: 1.1 短信清单,可以到移动网站查自己的短信话单,cp下来,利用editplus的纵列选择功能把短信号码那一栏取出来,保存为sms_details 1.2 建表: 1.2.1 短信清单表: create table sms_details(sms_details varchar(11)); 1.2.2 手机号码与姓名对应表 create table sms2name(phone varchar(11) not null primary key, name varchar(8)); 2. 脚本 <div class='codetop'>CODE</div><div class='codemain'> #!/usr/bin/ksh db2 "connect to db_name" > /dev/null 2>&1; #连接数据库 db_name为具体数据库名 I=0 &nbsp;#记录短信清单条数 J=0 &nbsp;#记录成功插入数据库清单条数 K=0 &nbsp;#记录插入失败记录条数 echo "请输入短信清单:\c" read SMS_DETAILS while read PHONE do       I=`expr $I + 1`       db2 "insert into sms_details values('$PHONE')" > /dev/null 2>&1; #插库     if [ $? -eq 0 ];then              J=`expr $J + 1`       else              K=`expr $K + 1`       fi done < $SMS_DETAILS echo "共检索出 $I 条数据, 成功插入 $J 条数据, 插入失败 $K 条数据\n" #开始分析数据,生成报表 #使用到的高级SQL: CASE表达式, 左外连接 db2 -z report "select sms_details as phone, case when a.sms_details=b.phone then b.name else concat ('unknow_', sms_details) end as name, count(a.sms_details) as count from sms_details a left outer join sms2id b on a.sms_details=b.phone group by a.sms_details, b.phone, b.name" > /dev/null 2>&1; #删除短信清单 db2 "delete from sms" > /dev/null 2>&1; db2 "disconnect current" > /dev/null 2>&1; #断开数据库 </div> 3. 查看分析结果 more report 4. over啦~ <img src='style_emoticons/default/smile.gif' border='0' style='vertical-align:middle' alt='smile.gif' /> 现在知道每个月,和谁发的短信最多! 谁是潜在的目标了 <img src='style_emoticons/default/tongue.gif' border='0' style='vertical-align:middle' alt='tongue.gif' /> <img src='style_emoticons/default/haha.gif' border='0' style='vertical-align:middle' alt='haha.gif' /> <img src='style_emoticons/default/happy.gif' border='0' style='vertical-align:middle' alt='happy.gif' /> 附:报表样色 <div class='quotetop'>QUOTE</div><div class='quotemain'> PHONE      NAME              COUNT      ----------- ------------------ ----------- 137953**** aa                        60 139147**** bb                      120 139218**** cc    320 </div>

2004-10-26 16:37 wildhorse

不错，呵呵，向你学习。

2004-10-29 20:40 无双

好啊可以多用用 <img src='style_emoticons/default/laugh.gif' border='0' style='vertical-align:middle' alt='laugh.gif' />

2004-11-1 18:08 seven

更新: 1. 修改sms2id表结构,添加type字段,做分类统计使用 2. 修改分析SQL语句 SQL解释: 2.1 value()函数是用来测试空值的,如为空用''代替 2.2 使用group by 的grouping sets子句 2.3 统计使用了聚集和钻取,由group by grouping sets((a.sms_details, b.type, b.phone, b.name),(b.type), ())语句来实现 3. 最后用order by b.type, a.sms_details, b.name来增强报表的可读性 <div class='codetop'>CODE</div><div class='codemain'> db2 "select value(a.sms_details,'') as 电话号码, value(case when a.sms_details=b.phone then b.name else concat ('unknow_', a.sms_details) end,'') as 姓名, value(b.type,'') as 类别 , count(a.phoneno) as 统计 from sms_details a left outer join sms2id b on a.sms_details=b.phone group by grouping sets((a.sms_details, b.type, b.phone, b.name),(b.type), ()) order by b.type, a.sms_details, b.name" </div> 附报表样色: <div class='quotetop'>QUOTE</div><div class='quotemain'>电话号码&nbsp; &nbsp;&nbsp; &nbsp;  姓名&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  类别&nbsp; &nbsp; &nbsp;&nbsp; &nbsp;  统计 -----------&nbsp; ------------------&nbsp; &nbsp; &nbsp;  ---------- ----------- 139518***** TT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  好友&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 4 222333***** AA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 好友&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 好友&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  6 139147***** unknow_139147*****&nbsp; &nbsp;  其他&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 其他&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  1 111223***** BB&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 亲戚&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 2 139211***** JJ&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 亲戚&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 3 222333***** CC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 亲戚&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 亲戚&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  6 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  13 </div> 红色为小类统计 蓝色为总统计

2004-11-2 09:35 燕狂徒

支持原创，加精华

2004-11-15 17:07 jnnxjj

嘿嘿，我不用统计也知道自己每月给谁发的短信最多

2004-11-15 17:23 seven

呵呵,其实,这个贴我是抛出一个话题, DW,BI的东东,用到一些olap的函数 其实也就是olap报表的应用开发,而不是真的为了分析我的短线话单 <img src='style_emoticons/default/blush.gif' border='0' style='vertical-align:middle' alt='blush.gif' /> 这个才是我真的的意图,可惜,响应的人不多