页面 PV 按照 年 月 日 小时 统计 的存储过程
程序员文章站
2022-05-15 08:10:45
...
需求:需要做一个统计 网站是这样的:网站上有 视频频道、图片频道、新闻频道等 需要做一个统计,能够统计这几个频道 中每个资源 某个小时、 某天、某周、某月、某年、总的 访问的次数 从上述需求看,只要统计每个资源,一天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:-- Create date: -- 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
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
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
上一篇: PHP中的<<<运算符,php中运算符
下一篇: 简单的PHP返回一张图片(非验证码形式)