数字辅助表在统计方面的应用

以下是用数字辅助表统计产品日访问情况的一个小技巧.

先建立一个数字辅助表 dbo.Nums

 ---auther:cowge
if Object_ID('dbo.Nums') is not null
   begin
     drop table dbo.Nums
   end
 Create table dbo.Nums(N int)
 declare @N bigint
 declare @R int
 set @N=10000
 set @R=1
 insert into dbo.Nums(N) values(1)
 while @R*2<=@N
  begin
          insert into dbo.Nums(N) select N+@R from dbo.Nums
          set @R=@R*2
  end
 insert into dbo.Nums(N) select N+@R from dbo.Nums where N+@R<= @N;

go

产品日志表

USE [Northwind]
GO
/****** 对象:  Table [dbo].[T_Product_Log]    脚本日期: 12/18/2008 22:00:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T_Product_Log](
 [LogID] [int] IDENTITY(1,1) NOT NULL,
 [ProductID] [int] NULL,
 [Time] [smalldatetime] NULL CONSTRAINT [DF_T_Product_Log_Time]  DEFAULT (getdate()),
 [IP] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_T_Product_Log] PRIMARY KEY CLUSTERED
(
 [LogID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

 

填充数据

 

使其在 2008-11-18 21:07:00 到 2008-12-18 21:07:00 其实有相关的访问数据

 

现在我们就开始进入主题

 

构造一个日期和该日期产品访问数的语句

 

实现语句如下

 

 --auther:cowge
 declare @sdate datetime
 declare @edate datetime
 declare @ProductID int
 set @sdate='2008-11-18 21:07:00'
 set @edate='2008-12-18 21:07:00'
 set @ProductID=1
 
 
 select xdata=CONVERT(varchar(10),@sdate+N-1,121),
 ydata=(
  select count(*) from dbo.T_Product_Log where ProductID=@ProductID
  and  [Time]>=CONVERT(varchar(10),@sdate+N-1,121) and [Time]<CONVERT(varchar(10),@sdate+N,121)
)

 from dbo.Nums where N<=datediff(day,@sdate,@edate)+1

 

简单介绍一下

xdate 是指2008-11-18至2008-12-18的日期

 ydata是指对应xdate日期的产品访问总数.