mysql按小时分组统计存储过程_页面 PV 按照 年 月 日 小时 统计 的存储过程

-- Description:-- =============================================

CREATE proc [dbo].[procCountPV](

@ChannelID nvarchar(50),

@SourceID int

)

as

begin

declare @TEMID int; --临时ID

declare @Now datetime;

set @Now = GETDATE();

declare @Y smallint;--年

declare @M tinyint;--月

declare @W tinyint;--周

declare @D tinyint;--日

declare @H tinyint;--小时

set @Y = DATEPART(YY,@Now);

set @M = DATEPART(MM,@Now);

set @W = DATEPART(WW,@Now);

set @D = DATEPART(DD,@Now);

set @H = DATEPART(HH,@Now);

select @TEMID = [ID] from [PV] where [ChannelID] = @ChannelID and [SourceID]=@SourceID and [Y] = @Y and [M]=@M and [D]=@D and [H] = @H;

if @TEMID is null

Insert into [PV]([ChannelID],[SourceID],[Times],[Y],[M],[W],[D],[H]) values(@ChannelID ,@SourceID,1,@Y,@M,@W,@D,@H);

else

Update [PV] set [Times] = [Times]+1 where [ID]= @TEMID;

/*计算现在*/

Declare @NowHourTimes int;

Declare @NowDayTimes int;

Declare @NowWeekTimes int;

Declare @NowMonthTimes int;

Declare @NowYearTimes int;

--Y M D H

select @NowHourTimes = SUM([Times]) from [PV] where ChannelID = @ChannelID and SourceID = @SourceID and [Y] = @Y and [M]=@M and [D]=@D and [H] = @H;

--Y M D

select @NowDayTimes = SUM([Times]) from [PV] where ChannelID = @ChannelID and SourceID = @SourceID and [Y] = @Y and [M]=@M and [D]=@D;

--Y W

select @NowWeekTimes = SUM([Times]) from [PV] where ChannelID = @ChannelID and SourceID = @SourceID and [Y] = @Y and [W]=@W;

--Y M

select @NowMonthTimes = SUM([Times]) from [PV] where ChannelID = @ChannelID and SourceID = @SourceID and [Y] = @Y and [M]=@M;

--Y

select @NowYearTimes = SUM([Times]) from [PV] where ChannelID = @ChannelID and SourceID = @SourceID and [Y] = @Y;

if @NowHourTimes is null

set @NowHourTimes = 0;

if @NowDayTimes is null

set @NowDayTimes = 0;

if @NowWeekTimes is null

set @NowWeekTimes = 0;

if @NowMonthTimes is null

set @NowMonthTimes = 0;

if @NowYearTimes is null

set @NowYearTimes = 0;

/*计算之前*/

Declare @PreHourTimes int;

Declare @PreDayTimes int;

Declare @PreWeekTimes int;

Declare @PreMonthTimes int;

Declare @PreYearTimes int;

--Y M D H

Declare @PreHourDateTime datetime;

set @PreHourDateTime = DATEADD(HH,-1,@Now);

select @PreHourTimes = SUM([Times]) from [PV] where ChannelID = @ChannelID and SourceID = @SourceID and [Y] = DATEPART(YY,@PreHourDateTime) and [M]=DATEPART(MM,@PreHourDateTime) and [D]=DATEPART(DD,@PreHourDateTime) and [H] = DATEPART(HH,@PreHourDateTime);

--Y M D

Declare @PreDayDateTime datetime;

set @PreDayDateTime = DATEADD(DD,-1,@Now);

select @PreDayTimes = SUM([Times]) from [PV] where ChannelID = @ChannelID and SourceID = @SourceID and [Y] = DATEPART(YY,@PreDayDateTime) and [M]=DATEPART(MM,@PreDayDateTime) and [D]=DATEPART(DD,@PreDayDateTime);

--Y W

Declare @PreWeekDateTime datetime;

set @PreWeekDateTime = DATEADD(WW,-1,@Now);

select @PreWeekTimes = SUM([Times]) from [PV] where ChannelID = @ChannelID and SourceID = @SourceID and [Y] = DATEPART(YY,@PreWeekDateTime) and [W]= DATEPART(WW,@PreWeekDateTime);

--Y M

Declare @PreMonthDateTime datetime;

set @PreMonthDateTime = DATEADD(MM,-1,@Now);

select @PreMonthTimes = SUM([Times]) from [PV] where ChannelID = @ChannelID and SourceID = @SourceID and [Y] = DATEPART(YY,@PreMonthDateTime) and [M]= DATEPART(MM,@PreMonthDateTime);

--Y

select @PreYearTimes = SUM([Times]) from [PV] where ChannelID = @ChannelID and SourceID = @SourceID and [Y] = @Y - 1;

if @PreHourTimes is null

set @PreHourTimes = 0;

if @PreDayTimes is null

set @PreDayTimes = 0;

if @PreWeekTimes is null

set @PreWeekTimes = 0;

if @PreMonthTimes is null

set @PreMonthTimes = 0;

if @PreYearTimes is null

set @PreYearTimes = 0;

declare @HourRate float;

declare @DayRate float;

declare @WeekRate float;

declare @MonthRate float;

declare @YearRate float;

set @HourRate = 0;

set @DayRate = 0;

set @WeekRate = 0;

set @MonthRate = 0;

set @YearRate = 0;

if @PreHourTimes > 0

set @HourRate = ( @NowHourTimes - @PreHourTimes )/ (@PreHourTimes+0.0);

if @PreDayTimes > 0

set @DayRate = ( @NowDayTimes - @PreDayTimes )/ (@PreDayTimes+0.0);

if @PreWeekTimes > 0

set @WeekRate = ( @NowWeekTimes - @PreWeekTimes )/ (@PreWeekTimes+0.0);

if @PreMonthTimes > 0

set @MonthRate = ( @NowMonthTimes - @PreMonthTimes )/ (@PreMonthTimes+0.0);

if @PreYearTimes > 0

set @YearRate = ( @NowYearTimes - @PreYearTimes )/ (@PreYearTimes+0.0);

/*计算总量*/

declare @Total int;

select @Total = SUM([Times]) From [PV] where ChannelID = @ChannelID and SourceID = @SourceID;

if @Total is null

set @Total = 0;

declare @TempID int;

set @TempID = null;

/*操作CountSummary*/

Select @TempID = ID from [PVS]where ChannelID = @ChannelID and SourceID = @SourceID;

if @TempID is null

Insert into [PVS]([ChannelID],[SourceID],[HourRate],[HourTimes],[DayRate],[DayTimes],[WeekRate],[WeekTimes],[MonthRate],[MonthTimes],[YearRate],[YearTimes],[Total])

Values(@ChannelID,@SourceID,@HourRate,@NowHourTimes,@DayRate,@NowDayTimes,@WeekRate,@NowWeekTimes,@MonthRate,@NowMonthTimes,@YearRate,@NowYearTimes,@Total);

else

Update [PVS] set [HourRate]=@HourRate,[HourTimes]=@NowHourTimes,[DayRate]=@DayRate,[DayTimes]=@NowDayTimes,[WeekRate]=@WeekRate,[WeekTimes]=@NowWeekTimes,[MonthRate]=@MonthRate,[MonthTimes]=@NowMonthTimes,[YearRate]=@YearRate,[YearTimes]=@NowYearTimes,[Total]=@Total where ID = @TempID;

end

GO