高级查询优化 先谢谢了 有分
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,wang.ys>
-- Create date: <Create Date,,2008-10-31>
-- Description: <Description,,获取数据>
-- =============================================
ALTER PROCEDURE [dbo].[GetDailyTrafficByChannelIdAndDate]
@cid int,
@startTime datetime,
@endTime datetime
AS
BEGIN
SET NOCOUNT ON;
select
cooperId = @cid
,min(fileid)as mediafileid
,watchTime=dateadd(s,(datediff(s,@startTime,date))/60*60,@startTime)
,avgBitrate =
( select avg(Avgbandwidth) from logs
where date <=dateadd(s,(datediff(s,@startTime,a.date))/60*60,@startTime)
and dateadd(s,[x-duration],date)>=dateadd(s,(datediff(s,@startTime,a.date))/60*60,@startTime))
,duration =1
,TotalTraffic =
( select sum(Avgbandwidth)from logs
where date <= dateadd(s,(datediff(s,@startTime,a.date))/60*60,@startTime)
and
dateadd(s,[x-duration],date) > dateadd(s,-5, (dateadd(s,(datediff(s,@startTime,a.date))/60*60,@startTime)))
)
,MaxTraffic=
(select sum(Avgbandwidth) from logs
where date <=dateadd(s,(datediff(s,@startTime,a.date))/60*60,@startTime)
and dateadd(s,[x-duration],date)>=dateadd(s,(datediff(s,@startTime,a.date))/60*60,@startTime)
)
from logs a
where date between @startTime and @endtime and cid = @cid and status = 0
group by (datediff(s,@startTime,date))/60,fileid
END