页面 PV 按照年月日小时统计的存储过程-MySQL-Tutorial-php.cn

需求：需要做一个统计网站是这样的：网站上有视频频道、图片频道、新闻频道等需要做一个统计，能够统计这几个频道中每个资源某个小时、某天、某周、某月、某年、总的访问的次数从上述需求看，只要统计每个资源，一天24 个小时的访问量然后分类汇总就

需求：需要做一个统计
网站是这样的：网站上有视频频道、图片频道、新闻频道等

需要做一个统计，能够统计这几个频道中每个资源某个小时、某天、某周、某月、某年、总的访问的次数

从上述需求看，只要统计每个资源，一天24 个小时的访问量然后分类汇总就可以算出某天、某周、某月、某年、总的范围的次数

原理就是这样。

表结构如下：
[Channel] 频道表
ID
Name //频道名称

[Video] 视频表
ID
...

[Photo] 图库表
ID
...

[News] 新闻表
ID
...

存储过程用了下面的两张表详细的写下：

[PV] 表
[ID] [int] IDENTITY(1,1) NOT NULL, [ChannelID] [int] NOT NULL,//频道ID [SourceID] [int] NOT NULL,//源ID [Times] [int] NOT NULL,//次数 [Y] [smallint] NULL,//年如 2000 [M] [tinyint] NULL,//月如 12 [W] [tinyint] NULL,//周如 50 [D] [tinyint] NULL,//日如 21 [H] [tinyint] NULL//小时如 16

[PVS] 汇总结果表 [ID] [int] IDENTITY(1,1) NOT NULL, [ChannelID] [int] NOT NULL,//频道ID [SourceID] [int] NOT NULL,//源ID [HourRate] [float] NULL,//当前小时与上个小时相比上升的速率 [HourTimes] [int] NULL,//当前小时访问的次数 [DayRate] [float] NULL,//今天与昨天相比上升的速率 [DayTimes] [int] NULL,//今天访问的次数 [WeekRate] [float] NULL,//当周与上周相比上升的速率 [WeekTimes] [int] NULL,//当周访问的次数 [MonthRate] [float] NULL,//当月与上月相比上升的速率 [MonthTimes] [int] NULL,//当周访问的次数 [YearRate] [float] NULL,//今年与上一年相比上升的速率 [YearTimes] [int] NULL,//今年访问的次数 [Total] [int] NULL//访问的总次数

-- =============================================
-- Author:		<Author,FHZ,>
-- Create date: <Create Date,2011-11-25 15:55,>
-- Description:	<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

Nach dem Login kopieren

CREATE TABLE [dbo].[PV](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ChannelID] [int] NOT NULL,
	[SourceID] [int] NOT NULL,
	[Times] [int] NOT NULL,
	[Y] [smallint] NULL,
	[M] [tinyint] NULL,
	[W] [tinyint] NULL,
	[D] [tinyint] NULL,
	[H] [tinyint] NULL,
 CONSTRAINT [PK_PV] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[PV] ADD  CONSTRAINT [DF_PV_Times]  DEFAULT ((0)) FOR [Times]
GO

ALTER TABLE [dbo].[PV] ADD  CONSTRAINT [DF_PV_Y]  DEFAULT ((2000)) FOR [Y]
GO

ALTER TABLE [dbo].[PV] ADD  CONSTRAINT [DF_PV_M]  DEFAULT ((1)) FOR [M]
GO

ALTER TABLE [dbo].[PV] ADD  CONSTRAINT [DF_PV_W]  DEFAULT ((1)) FOR [W]
GO

ALTER TABLE [dbo].[PV] ADD  CONSTRAINT [DF_PV_D]  DEFAULT ((1)) FOR [D]
GO

ALTER TABLE [dbo].[PV] ADD  CONSTRAINT [DF_PV_H]  DEFAULT ((0)) FOR [H]
GO

Nach dem Login kopieren

CREATE TABLE [dbo].[PVS](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ChannelID] [int] NOT NULL,
	[SourceID] [int] NOT NULL,
	[HourRate] [float] NULL,
	[HourTimes] [int] NULL,
	[DayRate] [float] NULL,
	[DayTimes] [int] NULL,
	[WeekRate] [float] NULL,
	[WeekTimes] [int] NULL,
	[MonthRate] [float] NULL,
	[MonthTimes] [int] NULL,
	[YearRate] [float] NULL,
	[YearTimes] [int] NULL,
	[Total] [int] NULL,
 CONSTRAINT [PK_PVS] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Nach dem Login kopieren

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

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