请教大家一个问题，给出思路即可，需要用到存储过程，用到周几的判断？
表是这样的：
主叫区号
主叫号码
通话开始时间
通话时长(秒)
通话费用(元)
023
18983699699
2009/02/28 10:00:34
95
1.2
010
52057197
2009/02/28 12:48:25
275
0.5
010
13581636551
2009/02/28 13:36:53
72
0.2
010
13501255723
2009/03/01 10:01:39
53
0.1
要统计出这个月总共的周一至周五的
时间段 通话次数 话费 话费次数
8-9点 10
5.7 11％
9-10点
2 11.7 3％
忘了说明了：）
话费次数是指时间段通话次数除以总通话次数，比如8-9点有10次，总通话次数是12次，就是10/12的百分比。
谢谢大家：）
测试数据：
-- Create table
create table HFFX
(
QUHAOVARCHAR2(12),
ZJHAOMAVARCHAR2(30),
SHIJIANDATE,
SHICHANG NUMBER,
HUAFEI NUMBER(9,2),
BZ VARCHAR2(20)
)
insert into hffx (bz,quhao,ZJHAOMA,SHICHANG,huafei) values('1','023','18983699699','95','1.2');
insert into hffx (bz,quhao,ZJHAOMA,SHICHANG,huafei) values('2','010','52057197','275','0.5');
insert into hffx (bz,quhao,ZJHAOMA,SHICHANG,huafei) values('3','010','13581636551','72','0.2');
insert into hffx (bz,quhao,ZJHAOMA,SHICHANG,huafei) values('4','010','13501255723','53','0.1');
insert into hffx (bz,quhao,ZJHAOMA,SHICHANG,huafei) values('5','010','13501255723','90','0.2');
insert into hffx (bz,quhao,ZJHAOMA,SHICHANG,huafei) values('6','010','13901165196','113','0.2');
insert into hffx (bz,quhao,ZJHAOMA,SHICHANG,huafei) values('7','010','65539282','80','0.2');
insert into hffx (bz,quhao,ZJHAOMA,SHICHANG,huafei) values('8','010','13701066897','65','0.2');
insert into hffx (bz,quhao,ZJHAOMA,SHICHANG,huafei) values('9','010','62947081','469','0.8');
insert into hffx (bz,quhao,ZJHAOMA,SHICHANG,huafei) values('10','0318','3839100','227','2.4');
insert into hffx (bz,quhao,ZJHAOMA,SHICHANG,huafei) values('11','0517','82260151','211','2.4');
insert into hffx (bz,quhao,ZJHAOMA,SHICHANG,huafei) values('12','010','15810353576','287','0.5');
insert into hffx (bz,quhao,ZJHAOMA,SHICHANG,huafei) values('13','010','15101129861','101','0.2');
insert into hffx (bz,quhao,ZJHAOMA,SHICHANG,huafei) values('14','021','57846297','171','1.8');
insert into hffx (bz,quhao,ZJHAOMA,SHICHANG,huafei) values('15','022','22140660','55','0.6');
insert into hffx (bz,quhao,ZJHAOMA,SHICHANG,huafei) values('16','0351','6808558','62','1.2');
insert into hffx (bz,quhao,ZJHAOMA,SHICHANG,huafei) values('17','022','22140660','360','3.6');
insert into hffx (bz,quhao,ZJHAOMA,SHICHANG,huafei) values('18','0574','88916425','206','2.4');
insert into hffx (bz,quhao,ZJHAOMA,SHICHANG,huafei) values('19','0574','88916425','482','5.4');
insert into hffx (bz,quhao,ZJHAOMA,SHICHANG,huafei) values('20','010','15810060065','217','0.4');
update hffx set shijian=to_date('2009/02/28 10:00:34','yyyy-mm-dd hh24:mi:ss') where bz='1';
update hffx set shijian=to_date('2009/02/28 12:48:25','yyyy-mm-dd hh24:mi:ss') where bz='2';
update hffx set shijian=to_date('2009/02/28 13:36:53','yyyy-mm-dd hh24:mi:ss') where bz='3';
update hffx set shijian=to_date('2009/03/01 10:01:39','yyyy-mm-dd hh24:mi:ss') where bz='4';
update hffx set shijian=to_date('2009/03/01 10:12:58','yyyy-mm-dd hh24:mi:ss') where bz='5';
update hffx set shijian=to_date('2009/03/02 9:45:55','yyyy-mm-dd hh24:mi:ss') where bz='6';
update hffx set shijian=to_date('2009/03/02 14:25:49','yyyy-mm-dd hh24:mi:ss') where bz='7';
update hffx set shijian=to_date('2009/03/02 15:51:08','yyyy-mm-dd hh24:mi:ss') where bz='8';
update hffx set shijian=to_date('2009/03/03 9:26:13','yyyy-mm-dd hh24:mi:ss') where bz='9';
update hffx set shijian=to_date('2009/03/04 9:58:06','yyyy-mm-dd hh24:mi:ss') where bz='10';
update hffx set shijian=to_date('2009/03/04 15:19:13','yyyy-mm-dd hh24:mi:ss') where bz='11';
update hffx set shijian=to_date('2009/03/04 18:12:06','yyyy-mm-dd hh24:mi:ss') where bz='12';
update hffx set shijian=to_date('2009/03/05 10:32:08','yyyy-mm-dd hh24:mi:ss') where bz='13';
update hffx set shijian=to_date('2009/03/05 10:41:53','yyyy-mm-dd hh24:mi:ss') where bz='14';
update hffx set shijian=to_date('2009/03/06 10:14:18','yyyy-mm-dd hh24:mi:ss') where bz='15';
update hffx set shijian=to_date('2009/03/06 10:23:04','yyyy-mm-dd hh24:mi:ss') where bz='16';
update hffx set shijian=to_date('2009/03/06 10:24:46','yyyy-mm-dd hh24:mi:ss') where bz='17';
update hffx set shijian=to_date('2009/03/06 15:15:27','yyyy-mm-dd hh24:mi:ss') where bz='18';
update hffx set shijian=to_date('2009/03/06 16:59:20','yyyy-mm-dd hh24:mi:ss') where bz='19';
update hffx set shijian=to_date('2009/03/06 17:41:54','yyyy-mm-dd hh24:mi:ss') where bz='20';
[ 本帖最后由 irene121 于 2009-5-6 11:02 编辑 ]
|
