Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I think the best way to do this is to summarize your data first, then generate your report.</p> <p>Here's how I'd do it.</p> <ol> <li><p>Create a SummaryTable with the FACTS you want (e.g. UserIP, SessionStart, SessionEnd, PageViews)</p></li> <li><p>Figure out what you consider a new visit (e.g. I think IIS default session timeout is 20 minutes, so any consecutive hit by an IP after 20 minutes I'll consider a new visit.)</p></li> <li><p>Create a cursor to calculate the summarized data based on your rule.</p> <pre><code>-- 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 <pre><code>-- Check rule IF @UserIP = @ThisUserIP AND @ThisVisitTime &amp;lt;= 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 </code></pre> END CLOSE StatCursor DEALLOCATE StatCursor </code></pre></li> <li><p>Create a query to get the data you need, example (All time Hits by IP).</p> <p>SELECT UserIP, COUNT(UserIP) FROM StatSummary GROUP BY UserIP</p></li> </ol>
    singulars
    1. This table or related slice is empty.
    1. POSQL Server Date Range Problem When Using Data From Web Server Logs
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USZachary
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COZachary: I actually tried the approach with the cursor and it worked really well for really small data sets (in the thousands of rows) but the actual data set that I need to work with is in the billions of rows and this cursor approach is actually processing rows slower than my web servers are creating log entries.
      singulars
      1. PO
      1. USλ Jonas Gorauskas
    2. COWow, that's some serious data... I use AWStats for most of my logging and what it does is generate the summaries for the current day on a daily/hourly basis... so your never waiting to do it all at one time. Once the raw data is processed, you never have to process or use it again! Do the reports have to be real-time? Can you process in batch? Also, do you have any indexes on your raw data?
      singulars
      1. PO
      1. USZachary
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload