Counting visits

That's not quite what I am after

for example:

192.168.0.3    31/01/2004   09:15:45   index.asp
192.168.0.3    31/01/2004   09:16:45   page1.asp
192.168.0.3    31/01/2004   09:17:45   page2.asp
192.168.0.3    31/01/2004   10:15:45   index.asp
192.168.0.3    31/01/2004   10:16:45   page1.asp
192.168.0.3    31/01/2004   12:15:45   index.asp

The above logs would represent 3 separate visits. The first 3 page requests have less than 15 minutes separating them. There is then an hour to the next visit and then 2 hours to the one after that.

Therefore SQL should return 31/01/2004   3

do you want these results by ip address? In your example you just put date and 3 visits. You just want the toal "new" requests by day.

So if you had 6 total page requests on one day, 2 were from the same IP address but 1 hour apart, 2 were from a second IP but within 15 minutes, and 2 were from totally separate IP addresses but within 15 minutes you would like your results to be the day and 5?

I think it's cursor time!

Create a stored procedure which uses a cursor to walk through the sorted set, use three variables, one to record the current IP, one to record the time and one to record the visist. If the IPs are different or the time difference > 15 add one the number of calls.

Good luck

ahh! cursor!! no!!! ;-) Depends on what he wants, there must be a set based way to do this too :), what would Itzhik Ben-Gan say???

like this

Select Ipaddr,count(*) as NoofVisits
  From (
select ipaddr,visittime
 from table as a
 Where datediff(d,a.visittime,getdate())<=5
   and Not exists (select b.visittime from table as b
                     where a.ipaddr = b.ipaddr
                       and datediff(mi,b.visittime,a.visittime)<=15
                       and datediff(d,b.visittime,getdate())<=5
                   )

        ) as Visits
 group by Ipaddr



yo need to establish for each ip address the start of each visit
this is the set of times for that ip which don't have an earlier visit within 15 minutes....

so we use NOT exists and for each page hit look for a previous page hit from that IP within the reviou 15 minutes....

i've just limited it to the basic 5 days....

you probably wnat to adjust that for a specific start of day time   e.g. midnight 5 days ago
(rather than run 1 4pm 5 days ago ,   run 2 from 4.15pm 5 days ago) as the result may fluctuate unpredicatbly

i think you should also decide wether its valid to "double count" visits across the 5 day boundary

   eg session starts 11:55pm  and finishes at 00:05am   on the 5 day cusp...
      so you'll be double counting that as a Visit (depending on the use you put the figures too..)

hth

   

This is how I would do it:

create table visits
(
ipaddress varchar(50),
visitdate datetime,
page       varchar(200)
)
go

set dateformat dmy
go

insert visits values (
'192.168.0.3' ,   '31/01/2004 09:15:45'   ,'index.asp')
insert visits values (
'192.168.0.3' ,   '31/01/2004 09:16:45',   'page1.asp')
insert visits values (
'192.168.0.3' ,   '31/01/2004 09:17:45',   'page2.asp')
insert visits values (
'192.168.0.3' ,   '31/01/2004 10:15:45',   'index.asp')
insert visits values (
'192.168.0.3' ,   '31/01/2004 10:16:45',   'page1.asp')
insert visits values (
'192.168.0.3' ,   '31/01/2004 12:15:45',   'index.asp')


insert visits values (
'192.168.1.3' ,   '31/01/2004 09:15:45'   ,'index.asp')
insert visits values (
'192.168.1.3' ,   '31/01/2004 09:40:45',   'page1.asp')
insert visits values (
'192.168.1.3' ,   '31/01/2004 09:41:45',   'page2.asp')
insert visits values (
'192.168.1.3' ,   '31/01/2004 10:15:45',   'index.asp')
insert visits values (
'192.168.1.3' ,   '31/01/2004 10:16:45',   'page1.asp')
insert visits values (
'192.168.1.3' ,   '31/01/2004 12:15:45',   'index.asp')

select ipaddress, visitdate
into #temp
from visits
order by ipaddress, visitdate asc

alter table #temp
add identcol integer identity(1,1)

alter table #temp
add mycount integer not null default 1

update #temp
set mycount = Case When datediff(mi, #temp.visitdate, t2.visitdate) <= 15
                  Then 0
                  Else 1
              End
from #temp t2, #temp
where #temp.identcol = (t2.identcol - 1) and
      #temp.ipaddress = t2.ipaddress

select ipaddress, sum(mycount)
from #temp
group by ipaddress

HTH

If we are understanding the question correctly, I like using the derived table in Lowfat's answer. Less work ;) Not sure which would perform better, I would assume overall about the same, or maybe even better performance with Lowfat's because adding the identity column could take a slight performance hit, and there are no indexes on the temp tables above, while the table that contains the info could have indexes already createdclas used by the derived table query.

sorry have re-read and you want visits by day...

Select visittime,count(*) as NoofVisits
  From (
select ipaddr,convert(datetime,convert(char(11),visittime)) as visittime
 from table as a
 Where datediff(d,a.visittime,getdate())<=5
   and Not exists (select b.visittime from table as b
                     where a.ipaddr = b.ipaddr
                       and datediff(mi,b.visittime,a.visittime)<=15
                       and datediff(d,b.visittime,getdate())<=5
                   )

        ) as Visits
 group by Visittime
 order by Visittime

Lowfatspread,

Would this not always return true (i.e exists)

Not exists (select b.visittime from table as b
                     where a.ipaddr = b.ipaddr
                       and datediff(mi,b.visittime,a.visittime)<=15
                       and datediff(d,b.visittime,getdate())<=5
                   )

Yes, I need it grouped by visitdatetime, not ipaddress, so:

01/02/2004    50
31/01/2004    48
30/01/2004    70
29/01/2004    30
38/01/2004    26

I vaguely understand what is going on in lowfat's query but it isn't returning anything at all. I think the problem lies with the NOT EXISTS a.visitip = b.visitip - also not too sure why we are converting the visitdatetime?

I have modified the query to match my field names:

Select visitdatetime,count(*) as NoofVisits
  From (
select visitip,convert(datetime,convert(char(11),visitdatetime)) as visitdatetime
 from visitors as a
 Where datediff(d,a.visitdatetime,getdate())<=5
   and Not exists (select b.visitdatetime from visitors as b
                     where a.visitip = b.visitip
                       and datediff(mi,b.visitdatetime,a.visitdatetime)<=15
                       and datediff(d,b.visitdatetime,getdate())<=5
                   )

        ) as Visits
 group by visitdatetime
 order by visitdatetime

I have put the contents of the table in to a csv file here: http://www.jfm.biz/export.csv if anyone is interested...

Existing identity field may be used if there are no gaps. To make sure that they increase by 1 only I am adding that field to the temp table. You can create a stored procedure out of the sqls posted below, if you need.

create table visits
(
visitip       varchar(50),
visitdatetime datetime,
visitpage          varchar(200)
)
go

set dateformat dmy
go

insert visits values (
'192.168.0.3' ,   '31/01/2004 09:15:45'   ,'index.asp')
insert visits values (
'192.168.0.3' ,   '31/01/2004 09:16:45',   'page1.asp')
insert visits values (
'192.168.0.3' ,   '31/01/2004 09:17:45',   'page2.asp')
insert visits values (
'192.168.0.3' ,   '31/01/2004 10:15:45',   'index.asp')
insert visits values (
'192.168.0.3' ,   '31/01/2004 10:16:45',   'page1.asp')
insert visits values (
'192.168.0.3' ,   '31/01/2004 12:15:45',   'index.asp')

insert visits values (
'192.168.1.3' ,   '30/01/2004 09:15:45'   ,'index.asp')
insert visits values (
'192.168.1.3' ,   '30/01/2004 09:40:45',   'page1.asp')
insert visits values (
'192.168.1.3' ,   '30/01/2004 09:41:45',   'page2.asp')
insert visits values (
'192.168.1.3' ,   '30/01/2004 10:15:45',   'index.asp')
insert visits values (
'192.168.1.3' ,   '30/01/2004 10:16:45',   'page1.asp')
insert visits values (
'192.168.1.3' ,   '30/01/2004 12:15:45',   'index.asp')


select  cast(cast(visitdatetime as varchar(12)) as datetime) visitdate , visitdatetime
into #temp
from visits
order by  visitdatetime asc


alter table #temp
add identcol integer identity(1,1)

alter table #temp
add mycount integer not null default 1


update #temp
set mycount = Case When datediff(mi, #temp.visitdatetime, t2.visitdatetime) <= 15
                  Then 0
                  Else 1
              End
from #temp t2, #temp
where #temp.identcol = (t2.identcol - 1) and
      #temp.visitdate = t2.visitdate

select visitdate, sum(mycount)
from #temp
group by visitdate

drop table #temp


HTH

Here is the stored procedure

drop procedure sp_GetDateCount
go

create procedure sp_GetDateCount
As
Begin

create table #temp (
visitdate      datetime,
visitdatetime  datetime,
identcol       integer identity(1,1),
mycount        integer default 1
)

-- Add date conditions as necessary
Insert #temp  (visitdate, visitdatetime)
select  cast(cast(visitdatetime as varchar(12)) as datetime) visitdate , visitdatetime
from visits
order by  visitdatetime asc

update #temp
set mycount = Case When datediff(mi, #temp.visitdatetime, t2.visitdatetime) <= 15
                  Then 0
                  Else 1
              End
from #temp t2, #temp
where #temp.identcol = (t2.identcol - 1) and
      #temp.visitdate = t2.visitdate

select visitdate, sum(mycount)
from #temp
group by visitdate

drop table #temp

End

go

exec sp_GetDateCount

go

HTH

I like that, Namasi. Clever idea to create your temp tables and do the join where the current ID is equal to the last one to evaluate the datediff..  Very nice.

I am trying to wrap my head around a method not using a temp table, your answer is sufficient, I just like teasing the brain, trying to figure out a derived table way of doing this.

Thanks, Mike!!!

I was trying to see if temp tables can be omitted, but no luck. I see you doing well in this forum. Good Luck!!! There are lots of great individuals in this forum and learnt so much from them.

Regards!!!

absolutely. I've learned quite a bit from many individuals, and I also find you learn a lot just by answering questions that you may not know the answer to. It's fun to try and fine new solutions.

namasi  yes thanks yoiur right

so this....
Would this not always return true (i.e exists)

Not exists (select b.visittime from table as b
                     where a.ipaddr = b.ipaddr
                       and datediff(mi,b.visittime,a.visittime)<=15
                       and datediff(d,b.visittime,getdate())<=5
                   )

SHOULD BE

Not exists (select b.visittime from table as b
                     where a.ipaddr = b.ipaddr
                       and datediff(mi,b.visittime,a.visittime) between -15 and 0
                       and b.visittime < a.visittime
                   )


but the query
is

Select visittime,count(*) as NoofVisits
  From (
select ipaddr,convert(datetime,convert(char(11),visittime)) as visittime
 from table as a
 Where datediff(d,a.visittime,getdate())<=5
and Not exists (select b.visittime from table as b
                     where a.ipaddr = b.ipaddr
                       and datediff(mi,b.visittime,a.visittime) between -15 and 0
                       and b.visittime < a.visittime
                   )

        ) as Visits
 group by Visittime
 order by Visittime



i'm converting the visittime to drop the time component so that the count can take place just over the Date

the sub query is just attemptine to get the start of each page view for a 15 minute period

hth

bbowman,

Any updates?

Regards,

Namasi.

Ok, back again...

Both suggestions look good. However I am now doing visits for the current month, not just the previous past 5 days. This does mean that speed will be quite important.

One thing that is really confusing is that the two queries return completely different results?

namesi
      01/02/2004      17
      02/02/2004      14
      03/02/2004      10
      04/02/2004      12
      05/02/2004      18
      06/02/2004      6
      07/02/2004      14
      08/02/2004      10
      09/02/2004      10

lowfat
      01/02/2004      58
      02/02/2004      49
      03/02/2004      19
      04/02/2004      39
      05/02/2004      54
      06/02/2004      15
      07/02/2004      79
      08/02/2004      64
      09/02/2004      35

bbowman:

Could you verify the data to see which one is correct. ?

You can verify the data as follows.
01/02/2004     17

You could run this query and count to see which quesry is correct:
select * from ipaddr where year(visittime) =  2004 and month(visittime) = 1 and day(visittime) = 2

Regards.

ok results below - having printed this out and gone through manually I think the figure should actually be 21 for this day?


SELECT     id, visitdatetime, visitip
FROM         visitors
WHERE     (YEAR(visitdatetime) = 2004) AND (MONTH(visitdatetime) = 2) AND (DAY(visitdatetime) = 1)
ORDER BY id

      492      01/02/2004 01:33:00      159.230.136.87
      493      01/02/2004 01:33:10      159.230.136.87
      494      01/02/2004 01:33:42      159.230.136.87
      495      01/02/2004 01:33:44      159.230.136.87
      496      01/02/2004 03:41:01      216.176.136.101
      497      01/02/2004 03:41:28      216.176.136.101
      498      01/02/2004 03:42:22      216.176.136.101
      499      01/02/2004 03:42:32      216.176.136.101
      500      01/02/2004 03:42:32      216.176.136.101
      501      01/02/2004 03:48:07      216.176.136.101
      502      01/02/2004 07:00:44      66.77.73.66
      503      01/02/2004 09:51:32      192.168.0.3
      504      01/02/2004 09:52:32      192.168.0.3
      505      01/02/2004 09:52:32      192.168.0.3
      506      01/02/2004 09:52:32      192.168.0.3
      507      01/02/2004 09:53:05      192.168.0.3
      508      01/02/2004 10:15:28      213.153.222.242
      509      01/02/2004 10:45:41      211.157.36.7
      510      01/02/2004 10:45:53      211.157.36.7
      511      01/02/2004 10:46:01      211.157.36.7
      512      01/02/2004 10:46:04      211.157.36.7
      513      01/02/2004 10:46:10      211.157.36.7
      514      01/02/2004 10:46:15      211.157.36.7
      515      01/02/2004 10:46:17      211.157.36.7
      516      01/02/2004 10:46:24      211.157.36.7
      517      01/02/2004 10:46:30      211.157.36.7
      518      01/02/2004 10:46:33      211.157.36.7
      519      01/02/2004 10:46:42      211.157.36.7
      520      01/02/2004 10:46:46      211.157.36.7
      521      01/02/2004 10:47:02      211.157.36.7
      522      01/02/2004 11:36:05      192.168.0.3
      523      01/02/2004 11:38:05      192.168.0.3
      524      01/02/2004 11:39:05      192.168.0.3
      525      01/02/2004 11:51:03      192.168.0.3
      526      01/02/2004 12:11:07      66.77.73.171
      527      01/02/2004 12:11:23      192.168.0.3
      528      01/02/2004 12:11:31      66.77.73.171
      529      01/02/2004 12:11:49      207.33.111.35
      530      01/02/2004 12:11:56      66.77.73.171
      531      01/02/2004 12:12:11      66.77.73.171
      532      01/02/2004 12:12:13      66.77.73.171
      533      01/02/2004 12:12:31      66.77.73.171
      534      01/02/2004 12:13:00      66.77.73.171
      535      01/02/2004 12:15:46      192.168.0.3
      536      01/02/2004 12:15:48      192.168.0.3
      537      01/02/2004 12:17:15      192.168.0.3
      538      01/02/2004 12:39:40      192.168.0.3
      539      01/02/2004 12:40:19      192.168.0.3
      540      01/02/2004 12:45:35      192.168.0.3
      541      01/02/2004 14:13:20      192.168.0.3
      542      01/02/2004 14:14:30      192.168.0.3
      543      01/02/2004 14:52:22      192.168.0.3
      544      01/02/2004 15:20:50      192.168.0.3
      545      01/02/2004 15:20:51      192.168.0.3
      546      01/02/2004 15:21:07      192.168.0.3
      547      01/02/2004 15:21:27      192.168.0.3
      548      01/02/2004 15:21:30      192.168.0.3
      549      01/02/2004 15:23:22      192.168.0.3
      550      01/02/2004 15:26:03      192.168.0.3
      551      01/02/2004 15:45:55      192.168.0.3
      552      01/02/2004 15:58:54      192.168.0.3
      553      01/02/2004 16:27:23      192.168.0.3
      554      01/02/2004 16:27:30      192.168.0.3
      555      01/02/2004 16:27:47      192.168.0.3
      556      01/02/2004 16:28:14      192.168.0.3
      557      01/02/2004 16:29:35      192.168.0.3
      558      01/02/2004 16:34:08      192.168.0.3
      559      01/02/2004 18:26:52      205.188.208.102
      560      01/02/2004 18:51:40      192.168.0.3
      561      01/02/2004 18:52:04      192.168.0.3
      562      01/02/2004 18:52:13      192.168.0.3
      563      01/02/2004 18:52:21      192.168.0.3
      564      01/02/2004 18:52:25      192.168.0.3
      565      01/02/2004 18:54:08      192.168.0.3
      566      01/02/2004 18:55:50      172.136.183.46
      567      01/02/2004 18:56:46      172.136.183.46
      568      01/02/2004 19:29:05      192.168.0.3
      569      01/02/2004 19:29:27      192.168.0.3
      570      01/02/2004 19:32:37      192.168.0.3
      571      01/02/2004 19:32:59      192.168.0.3
      572      01/02/2004 19:33:54      192.168.0.3
      573      01/02/2004 19:33:55      192.168.0.3
      574      01/02/2004 19:33:56      192.168.0.3
      575      01/02/2004 19:34:08      192.168.0.3
      576      01/02/2004 19:35:10      192.168.0.3
      577      01/02/2004 19:35:18      192.168.0.3
      578      01/02/2004 19:36:02      192.168.0.3
      579      01/02/2004 19:38:44      192.168.0.3
      580      01/02/2004 19:39:05      192.168.0.3
      581      01/02/2004 19:39:07      192.168.0.3
      582      01/02/2004 19:39:08      192.168.0.3
      583      01/02/2004 19:50:15      67.75.63.197
      584      01/02/2004 19:50:27      67.75.63.197
      585      01/02/2004 19:50:50      67.75.63.197
      586      01/02/2004 19:51:37      67.75.63.197
      587      01/02/2004 19:51:54      67.75.63.197
      588      01/02/2004 19:52:08      67.75.63.197
      589      01/02/2004 19:52:10      67.75.63.197