上线率统计

Question

一个table T
ID   CARDNO(手机卡号)  OnLineTime （上线时间）  OffLineTime（离线时间)   
1 9001 2008-09-24 11:28:42 2009-10-21 8:57:16
2 7655 2008-09-24 15:43:51 2009-10-21 8:57:16
3 9001 2008-09-24 15:44:15 2009-10-21 8:57:16
4 9001 2008-09-24 15:56:09 2009-10-21 8:57:16
5 9001 2008-09-24 16:11:01 2009-10-21 8:57:16
6 9001 2008-09-26 17:34:16 2009-10-21 8:57:16
7 8833 2008-09-26 17:34:16 2009-12-14 16:56:02
8 9003 2009-10-21 0:21:24          2009-10-21 0:47:31
9 9003 2009-10-21 0:47:43  2009-10-21 0:49:11
10 1688 2009-10-21 0:51:44           2009-10-21 0:52:21
11 1333 2009-10-21 1:02:18           2009-10-21 1:36:10  
12 1111 2009-10-21 1:32:29           2009-10-21 1:36:10  
13 9003 2009-10-21 1:45:43           2009-10-21 1:46:25  
14 9001 2009-10-21 8:53:07           2009-10-21 8:57:16
 
如何统计上线率？
本应用中，只要某一手机卡一日之中上线，哪怕是一秒种，也认为当日上线
所有上线天数除以某一时段(年，月)即为上线率

Answer 1

declare @startdate char(6)--起点日期
declare @enddate char(6)--终点日期
set @startdate = '20080101'
set @enddate = '20091201'--区间总天数
declare @allday_cnt int
set @allday_cnt = datediff(day,@startdate,@enddate)--查询上线率
select 
    cardno,[上线率] = convert(numeric(38,2),count(distinct convert(char(8),onlinetime,112)) * 1.0 / @allday_cnt)
from 
    T
where 
    offlinetime > onlinetime
group by 
    cardno

Answer 2

--加上测试数据
create table T(
    ID  int
    ,cardno  varchar(4)
    ,onlinetime datetime
    ,offlinetime datetime
)
insert into T
select 1 ,'9001','2008-09-24 11:28:42','2009-10-21 08:57:16' union all 
select 2 ,'7655','2008-09-24 15:43:51','2009-10-21 08:57:16' union all 
select 3 ,'9001','2008-09-24 15:44:15','2009-10-21 08:57:16' union all 
select 4 ,'9001','2008-09-24 15:56:09','2009-10-21 08:57:16' union all 
select 5 ,'9001','2008-09-24 16:11:01','2009-10-21 08:57:16' union all 
select 6 ,'9001','2008-09-26 17:34:16','2009-10-21 08:57:16' union all 
select 7 ,'8833','2008-09-26 17:34:16','2009-12-14 16:56:02' union all 
select 8 ,'9003','2009-10-21 00:21:24','2009-10-21 00:47:31' union all 
select 9 ,'9003','2009-10-21 00:47:43','2009-10-21 00:49:11' union all 
select 10,'1688','2009-10-21 00:51:44','2009-10-21 00:52:21' union all 
select 11,'1333','2009-10-21 01:02:18','2009-10-21 01:36:10' union all   
select 12,'1111','2009-10-21 01:32:29','2009-10-21 01:36:10' union all   
select 13,'9003','2009-10-21 01:45:43','2009-10-21 01:46:25' union all   
select 14,'9001','2009-10-21 08:53:07','2009-10-21 08:57:16'declare @startdate char(6)--起点日期
declare @enddate char(6)--终点日期
set @startdate = '20080101'
set @enddate = '20091201'--区间总天数
declare @allday_cnt int
set @allday_cnt = datediff(day,@startdate,@enddate)--查询上线率
select 
    cardno,[上线率] = convert(numeric(38,2),count(distinct convert(char(8),onlinetime,112)) * 1.0 / @allday_cnt)
from 
    T
where 
    offlinetime > onlinetime
group by 
    cardno--结果
/*
cardno 上线率
------ ---------------------------------------
1111   0.02
1333   0.02
1688   0.02
7655   0.02
8833   0.02
9001   0.07
9003   0.02(7 行受影响)*/

Answer 3

--> 测试数据: [T]
if object_id('[T]') is not null drop table [T]
create table [T] (ID int,CARDNO int,OnLineTime datetime,OffLineTime datetime)
insert into [T]
select 1,9001,'2008-09-24 11:28:42','2009-10-21 8:57:16' union all
select 2,7655,'2008-09-24 15:43:51','2009-10-21 8:57:16' union all
select 3,9001,'2008-09-24 15:44:15','2009-10-21 8:57:16' union all
select 4,9001,'2008-09-24 15:56:09','2009-10-21 8:57:16' union all
select 5,9001,'2008-09-24 16:11:01','2009-10-21 8:57:16' union all
select 6,9001,'2008-09-26 17:34:16','2009-10-21 8:57:16' union all
select 7,8833,'2008-09-26 17:34:16','2009-12-14 16:56:02' union all
select 8,9003,'2009-10-21 0:21:24','2009-10-21 0:47:31' union all
select 9,9003,'2009-10-21 0:47:43','2009-10-21 0:49:11' union all
select 10,1688,'2009-10-21 0:51:44','2009-10-21 0:52:21' union all
select 11,1333,'2009-10-21 1:02:18','2009-10-21 1:36:10' union all
select 12,1111,'2009-10-21 1:32:29','2009-10-21 1:36:10' union all
select 13,9003,'2009-10-21 1:45:43','2009-10-21 1:46:25' union all
select 14,9001,'2009-10-21 8:53:07','2009-10-21 8:57:16'declare @star datetime,@end datetime
set @star='2009-10-21'
set @end='2009-11-21'
select cardno,上线率=count(1)*1.0/datediff(dd,@star,@end) from [T]
where convert(varchar(10),onlinetime,120) between @star and @end
 group by cardno,convert(varchar(10),onlinetime,120)--结果：
cardno      上线率
----------- ---------------------------------------
1111        0.032258064516
1333        0.032258064516
1688        0.032258064516
9001        0.032258064516
9003        0.096774193548

Answer 4

--看了pt哥的例子，还是要加多一个时间范围限制
declare @startdate char(6)--起点日期
declare @enddate char(6)--终点日期
set @startdate = '20080101'
set @enddate = '20091201'--区间总天数
declare @allday_cnt int
set @allday_cnt = datediff(day,@startdate,@enddate)--查询上线率
select 
    cardno,[上线率] = convert(numeric(38,2),count(distinct convert(char(8),onlinetime,112)) * 1.0 / @allday_cnt)
from 
    T
where 
    offlinetime > onlinetime
and
    onlinetime between @startdate and @enddate
group by 
    cardno

Answer 5

--> 测试数据: [T]
if object_id('[T]') is not null drop table [T]
create table [T] (ID int,CARDNO int,OnLineTime datetime,OffLineTime datetime)
insert into [T]
select 1,9001,'2008-09-24 11:28:42','2009-10-21 8:57:16' union all
select 2,7655,'2008-09-24 15:43:51','2009-10-21 8:57:16' union all
select 3,9001,'2008-09-24 15:44:15','2009-10-21 8:57:16' union all
select 4,9001,'2008-09-24 15:56:09','2009-10-21 8:57:16' union all
select 5,9001,'2008-09-24 16:11:01','2009-10-21 8:57:16' union all
select 6,9001,'2008-09-26 17:34:16','2009-10-21 8:57:16' union all
select 7,8833,'2008-09-26 17:34:16','2009-12-14 16:56:02' union all
select 8,9003,'2009-10-21 0:21:24','2009-10-21 0:47:31' union all
select 9,9003,'2009-10-21 0:47:43','2009-10-21 0:49:11' union all
select 10,1688,'2009-10-21 0:51:44','2009-10-21 0:52:21' union all
select 11,1333,'2009-10-21 1:02:18','2009-10-21 1:36:10' union all
select 12,1111,'2009-10-21 1:32:29','2009-10-21 1:36:10' union all
select 13,9003,'2009-10-21 1:45:43','2009-10-21 1:46:25' union all
select 14,9001,'2009-10-21 8:53:07','2009-10-21 8:57:16'
go--存储过程
create proc sp_wsp
@year varchar(4),
@month varchar(2)
as
select cardno,上线率=ltrim(cast((datediff(dd,online,offline)+1)*100.0/
datepart(dd,dateadd(dd,-1,dateadd(mm,1,@year+'-'+@month+'-01'))) as numeric(5,2)))+'%'
 from
(select distinct cardno,
online=case when datediff(dd,@year+'-'+@month+'-01',onlinetime)>0 then onlinetime else @year+'-'+@month+'-01' end,
offline=case when datediff(dd,dateadd(dd,-1,dateadd(mm,1,@year+'-'+@month+'-01')),offlinetime)>0 then dateadd(dd,-1,dateadd(mm,1,@year+'-'+@month+'-01')) else offlinetime end
from t where datediff(mm,onlinetime,@year+'-'+@month+'-01')>=0
and datediff(mm,@year+'-'+@month+'-01',offlinetime)>=0)a
goexec sp_wsp '2009','10'--结果：
cardno      上线率
----------- ------------------------------------------
1111        3.23%
1333        3.23%
1688        3.23%
7655        67.74%
8833        100.00%
9001        67.74%
9001        3.23%
9003        3.23%
9003        3.23%
9003        3.23%

Answer 6

if object_id('[T]') is not null drop table [T]
create table [T] (ID int,CARDNO int,OnLineTime datetime,OffLineTime datetime)
insert into [T]
select 1,9001,'2008-09-24 11:28:42','2009-10-21 8:57:16' union all
select 2,7655,'2008-09-24 15:43:51','2009-10-21 8:57:16' union all
select 3,9001,'2008-09-24 15:44:15','2009-10-21 8:57:16' union all
select 4,9001,'2008-09-24 15:56:09','2009-10-21 8:57:16' union all
select 5,9001,'2008-09-24 16:11:01','2009-10-21 8:57:16' union all
select 6,9001,'2008-09-26 17:34:16','2009-10-21 8:57:16' union all
select 7,8833,'2008-09-26 17:34:16','2009-12-14 16:56:02' union all
select 8,9003,'2009-10-21 0:21:24','2009-10-21 0:47:31' union all
select 9,9003,'2009-10-21 0:47:43','2009-10-21 0:49:11' union all
select 10,1688,'2009-10-21 0:51:44','2009-10-21 0:52:21' union all
select 11,1333,'2009-10-21 1:02:18','2009-10-21 1:36:10' union all
select 12,1111,'2009-10-21 1:32:29','2009-10-21 1:36:10' union all
select 13,9003,'2009-10-21 1:45:43','2009-10-21 1:46:25' union all
select 14,9001,'2009-10-21 8:53:07','2009-10-21 8:57:16'
goselect CARDNO,cast(count(dates) as decimal(12,4))/(select COUNT(*) from t) as 上线率
from
(
select CARDNO,CONVERT(varchar(12) , getdate(), 112 ) as dates from t
union all select CARDNO,CONVERT(varchar(12) , getdate(), 112 ) from t
) ta
group by CARDNOdrop table T--结果
1111 0.142857142857142
1333 0.142857142857142
1688 0.142857142857142
7655 0.142857142857142
8833 0.142857142857142
9001 0.857142857142857
9003 0.428571428571428

Answer 7

create proc sp_wsp
@year varchar(4),
@month varchar(2)=null
asdeclare @begindate datetime,@enddate datetime
if  @month is null
begin 
set @begindate=@year+'0101'
set @enddate=DATEADD(day,-1,DATEADD(year,1,@begindate))
end
else
begin
set @begindate=@year+'-'+@month+'-01'
set @enddate=DATEADD(day,-1,DATEADD(month,1,@begindate))
endselect  cardno,ltrim(cast(count(distinct dt)*100.0/(DATEDIFF(day,@begindate,@enddate)+1) as decimal(9,3)))+'%'  as rate from T a, (
select dateadd(day,number,@begindate)  as  dt from master.dbo.spt_values where type='p' 
and number between 0 and DATEDIFF(day,@begindate,@enddate)) b
where DATEDIFF(DAY,a.onlinetime,b.dt)>=0 and DATEDIFF(DAY,b.dt,offlinetime)>=0
group by cardno
exec sp_wsp @year=2009
cardno rate
------ ------------------------------------------
1111   0.274%
1333   0.274%
1688   0.274%
7655   80.548%
8833   95.342%
9001   80.548%
9003   0.274%(7 行受影响)
exec sp_wsp @year=2009,@month=10
cardno rate
------ ------------------------------------------
1111   3.226%
1333   3.226%
1688   3.226%
7655   67.742%
8833   100.000%
9001   67.742%
9003   3.226%(7 行受影响)

Answer 8

对原问题，我可能描述的不清楚，
对于一些信号强的手机，其从开机以后，可能一直处于开机状态，因此其离线时间为NULL,
其记录可能是这个样子的8 9003,2009-10-21 0:21:24,2009-11-30 0:47:31  (9003在线连续1月以上)
9 9004,2009-10-30 0:21:24, null               (9004这条记录从 10月30到现在一直在线）
对于一些弱信号区的手机，其在线时间不连续,记录很多，一天之中也频繁掉线
其记录可能是这个样子的
10 9005,2009-10-21 0:21:24,2009-10-21 0:47:15  （在线几分钟）
10 9005,2009-10-21 0:50:24,2009-10-21 10:47:15 （离线3分钟后重新上线，持续几个小)
10 9005,2009-10-21 12:50:24,2009-10-24 8:30:18 
总之信号越稳定，记录越少。

Answer 9

where DATEDIFF(DAY,a.onlinetime,b.dt)>=0 and DATEDIFF(DAY,b.dt,isnull(offlinetime,getdate()))>=0

Answer 10

declare @startdate char(8)--起点日期
declare @enddate char(8)--终点日期
set @startdate = '20080101'
set @enddate = '20091201'--构造天数表
if object_id('tempdb..#','U') is not null
    drop table #;
create table #([allday] char(8))--区间总天数
declare @allday_cnt int
set @allday_cnt = datediff(day,@startdate,@enddate)declare @i int
set @i = 0
while(@i < @allday_cnt)
begin
    insert into # select convert(char(8),dateadd(day,@i,@startdate),112)
    print convert(char(8),dateadd(day,@i,@startdate),112)
    set @i = @i + 1
end--查询全量天数表
select * from #;

Answer 11

--加上测试数据
declare @T table (
    ID  int
    ,cardno  varchar(4)
    ,onlinetime datetime
    ,offlinetime datetime
)
insert into @T
select 1 ,'9001','2008-09-24 11:28:42','2009-10-21 08:57:16' union all 
select 2 ,'7655','2008-09-24 15:43:51','2009-10-21 08:57:16' union all 
select 3 ,'9001','2008-09-24 15:44:15','2009-10-21 08:57:16' union all 
select 4 ,'9001','2008-09-24 15:56:09','2009-10-21 08:57:16' union all 
select 5 ,'9001','2008-09-24 16:11:01','2009-10-21 08:57:16' union all 
select 6 ,'9001','2008-09-26 17:34:16','2009-10-21 08:57:16' union all 
select 7 ,'8833','2008-09-26 17:34:16','2009-12-14 16:56:02' union all 
select 8 ,'9003','2009-10-21 00:21:24','2009-10-21 00:47:31' union all 
select 9 ,'9003','2009-10-21 00:47:43','2009-10-21 00:49:11' union all 
select 10,'1688','2009-10-21 00:51:44','2009-10-21 00:52:21' union all 
select 11,'1333','2009-10-21 01:02:18','2009-10-21 01:36:10' union all   
select 12,'1111','2009-10-21 01:32:29','2009-10-21 01:36:10' union all   
select 13,'9003','2009-10-21 01:45:43','2009-10-21 01:46:25' union all   
select 14,'9001','2009-10-21 08:53:07','2009-10-21 08:57:16'declare @startdate datetime--起点日期
declare @enddate datetime--终点日期
declare @rq datetime,@days int
set @startdate = '2008-01-01'
set @enddate = '2009-12-01'
set @days=datediff(dd,@startdate,@enddate)
declare @dd table (rq datetime)
set @rq=@startdate
while @rq<=@enddate
begin
insert into @dd values(@rq)
set @rq=dateadd(dd,1,@rq)
end
select t.cardno,round(count(distinct d.rq)*1.00/@days*100,2) as 日上线率
from @t t,@dd d
where convert(varchar(10),d.rq,120) between convert(varchar(10),t.onlinetime,120)
     and convert(varchar(10),t.offlinetime,120)
group by t.cardno
     

Answer 12

结果:
cardno 日上线率
------ ---------------------------------------
1111   0.1400000000000
1333   0.1400000000000
1688   0.1400000000000
7655   56.1400000000000
8833   61.7100000000000
9001   56.1400000000000
9003   0.1400000000000

Answer 13

create table T(
    ID  int
    ,cardno  varchar(4)
    ,onlinetime datetime
    ,offlinetime datetime
)
insert into T
select 1 ,'9001','2008-09-24 11:28:42','2009-10-21 08:57:16' union all 
select 2 ,'7655','2008-09-24 15:43:51','2009-10-21 08:57:16' union all 
select 3 ,'9001','2008-09-24 15:44:15','2009-10-21 08:57:16' union all 
select 4 ,'9001','2008-09-24 15:56:09','2009-10-21 08:57:16' union all 
select 5 ,'9001','2008-09-24 16:11:01','2009-10-21 08:57:16' union all 
select 6 ,'9001','2008-09-26 17:34:16','2009-10-21 08:57:16' union all 
select 7 ,'8833','2008-09-26 17:34:16','2009-12-14 16:56:02' union all 
select 8 ,'9003','2009-10-21 00:21:24','2009-10-21 00:47:31' union all 
select 9 ,'9003','2009-10-21 00:47:43','2009-10-21 00:49:11' union all 
select 10,'1688','2009-10-21 00:51:44','2009-10-21 00:52:21' union all 
select 11,'1333','2009-10-21 01:02:18','2009-10-21 01:36:10' union all   
select 12,'1111','2009-10-21 01:32:29','2009-10-21 01:36:10' union all   
select 13,'9003','2009-10-21 01:45:43','2009-10-21 01:46:25' union all   
select 14,'9001','2009-10-21 08:53:07','2009-10-21 08:57:16'
godeclare @dt1 as datetime
declare @dt2 as datetime--临时表，用于拆分时间
SELECT TOP 8000 id = IDENTITY(int, 0, 1) INTO # FROM syscolumns a, syscolumns b --假设为2009年12月份的上线率
set @dt1 = '2009-12-01'
set @dt2 = '2009-12-31'
select m.cardno , 上线率 = cast(count(distinct convert(varchar(10),dateadd(day,n.id,m.onlinetime),120))*100.0 / (datediff(day,@dt1,@dt2)+1) as decimal(18,2)) from t m, # n where convert(varchar(10),dateadd(day,n.id,m.onlinetime),120) between @dt1 and @dt2 group by m.cardno
/*
cardno 上线率                  
------ -------------------- 
1111   100.00
1333   100.00
1688   100.00
7655   100.00
8833   100.00
9001   100.00
9003   100.00（所影响的行数为 7 行）
*/--假设为2009年11月份的上线率
set @dt1 = '2009-10-01'
set @dt2 = '2009-10-30'
select m.cardno , 上线率 = cast(count(distinct convert(varchar(10),dateadd(day,n.id,m.onlinetime),120))*100.0 / (datediff(day,@dt1,@dt2)+1) as decimal(18,2)) from t m, # n where convert(varchar(10),dateadd(day,n.id,m.onlinetime),120) between @dt1 and @dt2 group by m.cardno
/*
cardno 上线率                  
------ -------------------- 
1111   33.33
1333   33.33
1688   33.33
7655   100.00
8833   100.00
9001   100.00
9003   33.33（所影响的行数为 7 行）
*/--假设为2009-10-01 到 2009-12-31的上线率
set @dt1 = '2009-10-01'
set @dt2 = '2009-12-31'
select m.cardno , 上线率 = cast(count(distinct convert(varchar(10),dateadd(day,n.id,m.onlinetime),120))*100.0 / (datediff(day,@dt1,@dt2)+1) as decimal(18,2)) from t m, # n where convert(varchar(10),dateadd(day,n.id,m.onlinetime),120) between @dt1 and @dt2 group by m.cardno
/*
cardno 上线率                  
------ -------------------- 
1111   78.26
1333   78.26
1688   78.26
7655   100.00
8833   100.00
9001   100.00
9003   78.26（所影响的行数为 7 行）
*/drop table t, #