需求：需要做一个统计 网站是这样的：网站上有 视频频道、图片频道、新闻频道等 需要做一个统计，能够统计这几个频道 中每个资源 某个小时、 某天、某周、某月、某年、总的 访问的次数 从上述需求看，只要统计每个资源，一天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
