 SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--exec pro_rpt_UserBehavior_Stat 'All96_doker_chaonv01','2006-6-21','2006-6-1','2006-7-1'
--exec pro_rpt_UserBehavior_Stat1 'All110_doker_88she04','2006-7-22','2006-7-1','2006-8-1'
--@magID varchar(200), --某刊杂志ID
--@dtoday datetime, --当天的日期 2006-08-18
--@bmon datetime, --本月第一天 2006-08-1
--@emon datetime --下月的第一天 2006-08-31
Create procedure dbo.pro_rpt_UserBehavior_Stat1
(
@magID varchar(200),
@dtoday datetime,
@bmon datetime,
@emon datetime
)
as
begin
set nocount on
select Code,(select Name from MagazineList as ml where ml.Code=md.MagazineCode)as mname,Volumn
from MagazineDetail as md
where Code=@magID
declare @mult int
declare @addt int
set @mult=13
set @addt=9
declare @mID nvarchar(100) --杂志ID
set @mID = (select top 1 MagazineCode from MagazineDetail where Code=@magID)
--用户操作临时表
--ename nvarchar(50), --操作名称
--otype nvarchar(100) , --操作对象类型
--oid nvarchar(200), --操作对象ID
--uame nvarchar(100), --用户ID
--sex bit null, --用户性别
--etime datetime, --时间
--edate nvarchar null --时间的日期部分
create table #userb(
ename nvarchar(50),
otype nvarchar(100),
oid nvarchar(200),
uame nvarchar(100),
sex bit null,
etime datetime,
edate nvarchar(50) null
)
--往用户操作临时表插入所要数据
insert into #userb
(ename,otype,oid,uame,sex,etime,edate)
SELECT ub.EventName,ub.ObjectType,ub.ObjectID,ub.UserName,dm.Sex,ub.EventTime
,convert(varchar(4),datepart(year,ub.EventTime))+'-'+convert(varchar(2),datepart(month,ub.EventTime))+'-'+convert(varchar(2),datepart(day,ub.EventTime))
FROM UserBehavior as ub
LEFT OUTER JOIN DokerMember as dm ON ub.UserName = dm.UserName
WHERE ub.ObjectID=@magID or ub.ObjectID=@mID
--select * from #userb
--每天下载量的统计表
create table #tdown(
dtime datetime,--日期
tdown int null default 0 --本日下载量
)
--插入数据
insert into #tdown
(dtime,tdown)
SELECT convert(datetime,edate),count(edate)
FROM #userb where ename='DownloadMagazine'
group by edate
--select * from #tdown order by dtime
--每天订阅量的统计表
create table #tsub (
stime datetime, --日期
tsub int null default 0 --本日订阅量
)
--插入数据
insert into #tsub
(stime,tsub)
SELECT convert(datetime,edate),count(edate)
FROM #userb where ename='SubscribeMagazine'
group by edate
--select * from #tsub order by stime
--每天下载量、订阅量的统计表 [经过处理 *@mult+@addt]
create table #tdownsub (
dtime datetime , --日期
stime datetime , --日期
tbdown decimal(10,2) null default 0.00, --本日真实下载载量
tbsub decimal(10,2) null default 0.00, --本日真实的订阅量
tdown decimal(10,2) null default 0.00, --本日下载量
tsub decimal(10,2) null default 0.00 --本日订阅量
)
--插入数据 注意要用**********full join***********
insert into #tdownsub
(dtime,stime,tbdown,tbsub,tdown,tsub)
select td.dtime,ts.stime,isnull(td.tdown,0),isnull(ts.tsub,0),0,0
from #tdown as td
full join #tsub as ts
on td.dtime=ts.stime
update #tdownsub set dtime=stime
where dtime is null
update #tdownsub set tdown=case
when tbdown>=0 and tbdown<=5 then tbdown*13+(select RandomNum from MagzineRandom where CreateTime=dtime)
when tbdown>=6 and tbdown<=15 then tbdown*11+(select RandomNum from MagzineRandom where CreateTime=dtime)
when tbdown>=16 and tbdown<=30 then tbdown*9+(select RandomNum from MagzineRandom where CreateTime=dtime)
when tbdown>=31 and tbdown<=60 then tbdown*7+(select RandomNum from MagzineRandom where CreateTime=dtime)
when tbdown>=61 and tbdown<=180 then tbdown*6+(select RandomNum from MagzineRandom where CreateTime=dtime)
when tbdown>=181 then tbdown*5+(select RandomNum from MagzineRandom where CreateTime=dtime)
end,tsub=case
when tbsub>=0 and tbsub<=5 then tbsub*13+(select RandomNum from MagzineRandom where CreateTime=dtime)
when tbsub>=6 and tbsub<=15 then tbsub*11+(select RandomNum from MagzineRandom where CreateTime=dtime)
when tbsub>=16 and tbsub<=30 then tbsub*9+(select RandomNum from MagzineRandom where CreateTime=dtime)
when tbsub>=31 and tbsub<=60 then tbsub*7+(select RandomNum from MagzineRandom where CreateTime=dtime)
when tbsub>=61 and tbsub<=180 then tbsub*6+(select RandomNum from MagzineRandom where CreateTime=dtime)
when tbsub>=181 then tbsub*5+(select RandomNum from MagzineRandom where CreateTime=dtime)
end
--select * from #tdownsub
--select * from #tdownsub
--总统计表
create table #tstat (
tdown int null default 0, --总下载量
ddwon int null default 0, --本日下载量
mdown int null default 0, --本月下载量
tsub int null default 0, --总订阅量
msub decimal(10,2) null default 0, --男会员订阅量
fsub decimal(10,2) null default 0, --女会员订阅量
nsub decimal(10,2) null default 0, --未知性别会员订阅量
)
declare @tdown int --总下载量
declare @ddwon int --本日下载量
declare @mdown int --本月下载量
declare @tsub int --总订阅量
declare @msub int --男会员订阅量
declare @fsub int --女会员订阅量
declare @nsub int --未知性别会员订阅量
declare @mbsub int --男会真实员订阅量
declare @fbsub int --女会真实员订阅量
--数据处理*13+（每天+9）
--总下载量
set @tdown =(select sum(tdown) from #tdownsub)
--本日下载量
set @ddwon =(select sum(tdown) from #tdownsub where dtime between @dtoday and DateAdd(day,1,@dtoday))
--本月下载量
set @mdown =(select sum(tdown) from #tdownsub where dtime between @bmon and @emon)
--总订阅量
set @tsub = (select sum(tsub) from #tdownsub)
--男会员订阅量
set @mbsub=(select count(*) from #userb where ename='SubscribeMagazine' and sex=0)
set @msub =(case
when @mbsub>=0 and @mbsub<=5 then @mbsub*13+9
when @mbsub>=6 and @mbsub<=15 then @mbsub*11+9
when @mbsub>=16 and @mbsub<=30 then @mbsub*9+9
when @mbsub>=31 and @mbsub<=60 then @mbsub*7+9
when @mbsub>=61 and @mbsub<=180 then @mbsub*6+9
when @mbsub>=181 then @mbsub*5+9
end)
--女会员订阅量
set @fbsub = (select count(*) from #userb where ename='SubscribeMagazine' and sex=1)
set @fsub =(case
when @fbsub>=0 and @fbsub<=5 then @fbsub*13+8
when @fbsub>=6 and @fbsub<=15 then @fbsub*11+8
when @fbsub>=16 and @fbsub<=30 then @fbsub*9+8
when @fbsub>=31 and @fbsub<=60 then @fbsub*7+8
when @fbsub>=61 and @fbsub<=180 then @fbsub*6+8
when @fbsub>=181 then @fbsub*5+8
end)
--未知性别员订阅量
set @nsub = @tsub-@msub-@fsub
--set @nsub = @mult*(select count(*) from #userb where ename='SubscribeMagazine' and sex is null)
--插入总统计数据
insert into #tstat (tdown,ddwon,mdown,tsub,msub,fsub,nsub) values (@tdown,@ddwon,@mdown,@tsub,@msub,@fsub,@nsub)
--返回总统计表的数据+男会员订阅率+女会员订阅率
select tdown,ddwon,mdown,tsub,convert(int,msub) as msub,convert(int,fsub) as fsub,convert(int,nsub) as nsub
,msub1 = case
when msub = 0 then '0%'
else
left(convert(varchar(100),msub * 100 / tsub),5) + '%'
end, fsub1 = case
when fsub = 0 then '0%'
else
left(convert(varchar(100),fsub * 100 / tsub),5) + '%'
end, nsub1 = case
when nsub = 0 then '0%'
else
left(convert(varchar(100),nsub * 100 / tsub),5) + '%'
end
from #tstat
declare @7down int --最近7天总下载量
declare @7sub int --最近7天总订阅量
declare @30down int --最近30天总载量
declare @30sub int --最近30天总订阅量
--最近7天总下载量
set @7down=(select sum(tdown) from #tdownsub where dtime between dateAdd(day,-7,@dtoday) and @dtoday)
--最近7天总订阅量
set @7sub=(select sum(tsub) from #tdownsub where dtime between dateAdd(day,-7,@dtoday) and @dtoday)
--最近30天总载量
set @30down=(select sum(tdown) from #tdownsub where dtime between dateAdd(day,-30,@dtoday) and @dtoday)
--最近30天总订阅量
set @30sub=(select sum(tsub) from #tdownsub where dtime between dateAdd(day,-30,@dtoday) and @dtoday)
--select @7down as down7,@7sub as sub7,@30down as down30,@30sub as sub30
--返回最近7天的统计数据
select dtime,datename(weekday,dtime) as tweekday,convert(int,tdown) as tdown,convert(int,tsub) as tsub
,tdown1 = case
when tdown = 0 then '0%'
else
left(convert(varchar(100),tdown * 100 / @7down),5) + '%'
end,tsub1 = case
when tsub = 0 then '0%'
else
left(convert(varchar(100),tsub * 100 / @7sub),5) + '%'
end
from #tdownsub
where dtime between dateAdd(day,-7,@dtoday) and @dtoday
order by dtime desc
--返回最近30天的统计数据
select dtime,datename(weekday,dtime) as tweekday,convert(int,tdown) as tdown,convert(int,tsub) as tsub
,tdown1 = case
when tdown = 0 then '0%'
else
left(convert(varchar(100),tdown * 100 / @30down),5) + '%'
end,tsub1 = case
when tsub = 0 then '0%'
else
left(convert(varchar(100),tsub * 100 / @30sub),5) + '%'
end
from #tdownsub
where dtime between dateAdd(day,-30,@dtoday) and @dtoday
order by dtime desc
drop table #tstat
drop table #tdownsub
drop table #tdown
drop table #tsub
drop table #userb
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
发表于
2006-12-29 10:54
Cameo
阅读( 854)
评论()
收藏
举报
|
|
