我要按照这种模式来统计,其基本格式是:目前 我打算传2个参数:输入一个起始时间 和一个终止时间
比如
:exec '2012-11-24 00:00:00','2012-11-24 00:59:59'
这样只会查询出一条结果,所以不符合上面的结果 想修改一下 ,没有具体的思路 问问大家怎么修改好create procedure proc_hour(
@sBeginDate varchar(18),--2012 11-11 00:00:00
@sEndDate varchar(18)
)
AS
declare @personviews int ,@orderuserviews int,@userviews int ,@singleorder int ,
@monthorder int,@playall int,@playuser int,
@freeplay int ,@kokplay int,@mvplay int,
@zxychplay int ,@hjychplay int,@unsubscribe int--访问人数
select @personviews = count(distinct userid ) from userlog where accesstime between @sBeginDate and @sEndDate;
--订购用户访问(这里除去免费的点播数用户就是订购用户的访问)
select @orderuserviews = count(*) from userlog where userid in(select userid from usercharge where fee <> '' ) and accesstime between @sBeginDate and @sEndDate;
--用户总访问量
select @userviews = count(*) from userlog where accesstime between @sBeginDate and @sEndDate;
--单点新增订购数(包含MV 和卡拉OK单点 1元)
select @singleorder =count(*) from usercharge where fee ='1000000074' and orderdesc like '%1元%' and accesstime between @sBeginDate and @sEndDate;
--包月新增订购数(MV和卡拉OK包月新增用户)
select @monthorder = count(*) from usercharge where fee ='1000000074' and orderdesc like '%计费成功%' and accesstime between @sBeginDate and @sEndDate;
--总点播次数(包含随便听听)
select @playall = count(*) from userplaysongdata where accesstime between @sBeginDate and @sEndDate;
--点播用户数
select @playuser = count(distinct userid) from userplaysongdata where accesstime between @sBeginDate and @sEndDate;
--随便点播次数(免费)
select @freeplay = count(*) from usercharge where fee ='1000000074' and accesstime between @sBeginDate and @sEndDate;
--卡拉OK点播次数
select @kokplay =COUNT(1) FROM userplaysongdata a
INNER JOIN songlistdata b ON a.sourcefilename = b.sourcefilename where accesstime between @sBeginDate and @sEndDate
and b.videotype = 'xcjc' GROUP BY videotype
--MV点播次数
select @mvplay = COUNT(1) FROM userplaysongdata a
INNER JOIN songlistdata b ON a.sourcefilename = b.sourcefilename where accesstime between @sBeginDate and @sEndDate
and b.videotype = 'mvdg' GROUP BY videotype
--怀旧演唱会点播次数
select @hjychplay =COUNT(1) FROM userplaysongdata a
INNER JOIN songlistdata b ON a.sourcefilename = b.sourcefilename where accesstime between @sBeginDate and @sEndDate
and b.videotype = 'hjych' GROUP BY videotype
--最新演唱会点播次数
select @zxychplay =COUNT(1) FROM userplaysongdata a
INNER JOIN songlistdata b ON a.sourcefilename = b.sourcefilename where accesstime between @sBeginDate and @sEndDate
and b.videotype = 'zxych' GROUP BY videotypeselect @sBeginDate 时间,@personviews 访问人数 ,@userviews 用户总访问量,@orderuserviews 订购用户访问量 ,
@playuser 点播用户数,@playall 总点播次数 ,@kokplay 卡拉OK点播次数,@mvplay mv点播次数 ,
@zxychplay 最新演唱会点播次数 ,@hjychplay 怀旧演唱会点播次数, @freeplay 免费点播次数 ,
@singleorder 单点新增订购数 ,@monthorder 包月新增订购数
select
personviews = count(distinct userid ) ,
userviews =count
from userlog
where accesstime between @sBeginDate and @sEndDate
group by datepart(hour,accesstime)