使用Web服务器日志中的数据时出现SQL Server日期范围问题

试运行下面的代码。代码对每个IP地址的访问进行分组和编号。然后,它将查看与“阈值”值相比“uritem”命中的次数。我在一个名为“Foo”的表上测试了代码,在运行测试之前需要检查表和列名。

DECLARE @threshold INT;  
SET @threshold = 4;  --this number should not include the initial visit
DECLARE @lookbackdays int; 
SET @lookbackdays = 300; 

;WITH postCTE as  
(  
SELECT   
    ipaddress,  
    uristem,  
    requestdatetime,  
    RowNumber = ROW_NUMBER() OVER (ORDER BY ipaddress,requestdatetime ASC)  
FROM  
    Foo  --put your table name here
WHERE  
    requestdatetime > GETDATE() - @lookbackdays 
)  
--select * from postCTE

SELECT   
    p1.ipaddress AS [ipaddress],   
    p2.RowNumber - p1.RowNumber +1 AS [Requests], 
    p1.requestdatetime AS [DateStart]
FROM  
    postCTE p1  
INNER JOIN  
    postCTE p2   
    ON p1.ipaddress = p2.ipaddress   
    AND p1.Rownumber = p2.RowNumber - (@threshold )  
WHERE  
    DATEDIFF(minute,p1.requestdatetime,p2.requestdatetime) <= 60 

我对SQL 2008的测试结果是

ipaddress   Requests    DateStart
10.1.1.100  5   2010-10-15 13:30:30.000
10.1.1.100  5   2010-10-15 16:55:10.000

我认为最好的方法是先汇总数据,然后生成报告。

我会这样做的。

1. 创建包含所需事实的摘要表(例如UserIP、SessionStart、SessionEnd、PageViews)

2. 弄清楚您认为是什么样的新访问(例如,我认为I is默认会话超时是20分钟,因此20分钟后任何连续被IP击中的情况,我都会考虑新访问)

3. 创建光标以根据规则计算汇总数据。

   -- Summary Data
   DECLARE @UserIP AS VARCHAR(15)
   DECLARE @SessionStart AS DateTime
   DECLARE @SessionEnd AS DateTime
   DECLARE @PageViews AS INT
   
   
   -- Current Values
   DECLARE @ThisUserIP AS VARCHAR(15)
   DECLARE @ThisVisitTime AS DateTime
   DECLARE @ThisPage AS VARCHAR(100)
   
   
   -- Declare Cusrsor
   DECLARE StatCursor CURSOR FAST_FORWARD FOR
   -- Query, make sure you sort by IP/Date so their data is in cronological order
   SELECT IPAddress, RequestDateTime, UriStem
   FROM Stats
   ORDER BY IPAddress, RequestDateTime
   
   
   OPEN StatCursor
   FETCH NEXT FROM StatCursor
   INTO @ThisUserIP, @ThisVisitTime, @ThisPage
   
   
   -- Start New Summary
   SELECT @UserIP = @ThisUserIP, @SessionStart = @ThisVisitTime, @SessionEnd = @ThisVisitTime, @PageViews = 1
   
   
   FETCH NEXT FROM StatCursor
   INTO @ThisUserIP, @ThisVisitTime, @ThisPage
   
   
   WHILE @@FETCH_STATUS = 0
   BEGIN
   
   
   -- Check rule
   IF @UserIP = @ThisUserIP AND @ThisVisitTime <= DATEADD(MI,30,@SessionEnd)
       BEGIN
           -- Same User and Session / Add to Summary
           SELECT @PageViews = @PageViews + 1, @SessionEnd = @ThisVisitTime
       END
   ELSE
       BEGIN
           -- Different User or New User / Write Current Summary and Start New Summary
           INSERT INTO StatSummary (UserIP, SessionStart, SessionEnd, PageViews) VALUES (@UserIP, @SessionStart, @SessionEnd, @PageViews)
           SELECT @UserIP = @ThisUserIP, @SessionStart = @ThisVisitTime, @SessionEnd = @ThisVisitTime, @PageViews = 1
       END
   
   
   FETCH NEXT FROM StatCursor
   INTO @ThisUserIP, @ThisVisitTime, @ThisPage
   
   
   END
   
   
   CLOSE StatCursor
   DEALLOCATE StatCursor
   

4. 创建一个查询以获取所需的数据,例如(IP的所有时间命中率)。

   选择UserIP,COUNT(UserIP)FROM StatSummary GROUP BY UserIP