统计回复率的问题?

[wqs_qiu_shi]

ID(主键) | 手机号 | 业务号码 | 业务类型 | 群发时间 　　　 表a.群发记录

ID(主键) | 手机号 | 拨打过业务号码| 业务类型 | 拨打日期 | 拨打时长 表b.用户拨打记录表


如果群发一个业务后，用户在72小时内拨打本业务，视为回复。
要求统计出 电话号码为 "13810000000"，按拨打的业务类型不同，算出各个业务的该用户回复率.

1.用户在本业务群发72小时内拨打多次，也视为本次群发有一次回复.回付率是根据 回复次数/群发次数 .
2.如果只有拨打记录，没有群发记录，则显示 "没有群发"

[wqs_qiu_shi]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IvrCallRecord]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IvrCallRecord]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IvrSentRecord]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IvrSentRecord]
GO

CREATE TABLE [dbo].[IvrCallRecord] (
[CallID] [bigint] IDENTITY (1, 1) NOT NULL ,
[mobile] [char] (11) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[CallActNumber] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[CAllActType] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[CallDate] [datetime] NOT NULL ,
[CallTime] [float] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[IvrSentRecord] (
[RecoId] [bigint] IDENTITY (1, 1) NOT NULL ,
[Mobile] [char] (11) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[SentActNumber] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[SentActType] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[SentTime] [datetime] NOT NULL
) ON [PRIMARY]
GO
表结构如上



ID(主键) | 手机号 | 业务号码 | 业务类型 | 群发时间 　　　 表a.群发记录
1 13600000000 125902828 娱乐休闲 2003-10-15 10:00:11
4 13600000000 125906789 音乐听听 2003-10-15 10:00:11
5 13810546287 125906729 休闲娱乐 2003-10-15 10:00:11


ID(主键) | 手机号 | 拨打过业务号码| 业务类型 | 拨打日期 | 拨打时长 　 表b.用户拨打记录表
3 13600000000 125902828 休闲娱乐 2003-10-16 10:00:00 2
3 13500000000 125902828 休闲娱乐 2003-10-15 12:00:00 2
3 13600000000 125902829 休闲娱乐 2003-10-16 10:00:00 2

如果群发一个业务后，用户在72小时内拨打本业务，视为回复。
要求统计出 电话号码为 "13810000000"，按拨打的业务类型不同，算出各个业务的该用户回复率.

1.用户在72小时内拨打多次，也视为本次群发有一次回复.回付率是根据 回复次数/群发次数 .
2.如果只有拨打记录，没有群发记录，则显示 "没有群发"



查询13600000000需要的结果

125902829 回复率 100
125902828 回复率 没有群发

[了缘]

up好难，等高手

[wqs_qiu_shi]

我还想统计出,回复的个数.
就是在群发表中存在,而且在拨打表中也存在的手机用户个数．

要考虑：72小时内回复.
　　　　群发表中的记录有可能是重复的，过虑相同手机号．

[paoluo]

我也帮UP一下，期待高手！！

[子陌红尘]

三日内会对同一手机号码关于同一业务多次群发？

[wqs_qiu_shi]

三日内不会对同一手机号码关于同一业务进行多次群发

[子陌红尘]

三日内同一项业务只对同一个手机号码群发一次，显然只有三种可能性：

1、已群发，已回复 --回复率 100%
2、已群发，未回复 --回复率 0%
3、未群发，已回复 --没有群发

-------------------------------------------------------------------------------------



--生成测试数据
CREATE TABLE [dbo].[IvrSentRecord] (
[RecoId] [bigint] IDENTITY (1, 1) NOT NULL ,
[Mobile] [char] (11) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[SentActNumber] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[SentActType] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[SentTime] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[IvrCallRecord] (
[CallID] [bigint] IDENTITY (1, 1) NOT NULL ,
[mobile] [char] (11) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[CallActNumber] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[CAllActType] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[CallDate] [datetime] NOT NULL ,
[CallTime] [float] NOT NULL
) ON [PRIMARY]
GO

insert into IvrSentRecord(Mobile,SentActNumber,SentActType,SentTime) select '13600000000','125902828','娱乐休闲','2003-10-15 10:00:11'
insert into IvrSentRecord(Mobile,SentActNumber,SentActType,SentTime) select '13600000000','125906789','音乐听听','2003-10-15 10:00:11'
insert into IvrSentRecord(Mobile,SentActNumber,SentActType,SentTime) select '13810546287','125906729','休闲娱乐','2003-10-15 10:00:11'

insert into IvrCallRecord(Mobile,CallActNumber,CallActType,CallDate,CallTime) select '13600000000','125902828','休闲娱乐','2003-10-16 10:00:00',2
insert into IvrCallRecord(Mobile,CallActNumber,CallActType,CallDate,CallTime) select '13500000000','125902828','休闲娱乐','2003-10-15 12:00:00',2
insert into IvrCallRecord(Mobile,CallActNumber,CallActType,CallDate,CallTime) select '13600000000','125902829','休闲娱乐','2003-10-16 10:00:00',2



--执行统计
select
mobile = case when a.mobile is null then b.mobile else a.mobile end,
ActNumber = case when a.SentActNumber is null then b.CallActNumber else a.SentActNumber end,
Rate = max(case
when a.SentActNumber is null then '没有群发'
when b.CallActNumber is null then '0'
else '100'
end),
RevertNum = sum(case when b.mobile is null then 0 else 1 end)
from
IvrSentRecord a
full outer join
IvrCallRecord b
on
a.mobile = b.mobile and a.SentActNumber = b.CallActNumber and (datediff(hour,a.SentTime,b.CallDate) between 0 and 72)
group by
case when a.mobile is null then b.mobile else a.mobile end,
case when a.SentActNumber is null then b.CallActNumber else a.SentActNumber end

[子陌红尘]

要统计某个用户的回复信息，只需要在以上查询中增加where条件过滤即可。

不过群发表和回复表数据量通常都数以十万乃至百万计，直接在原表上统计效率会很低，建议将统计日期范围内的数据导入临时表再做统计分析处理