I need some ideas on my algorithm for a Hit Counter (Group By Time Interval)
My algorithm is for a 'hit counter', I am trying to not count the same person twice if that person came to the site twice in a time interval (For example if he comes twice in 5 minutes, I want to count it as 1 hit for this person)
Here's what my database looks like
UserIp UserId Date of user came
127.0.0.1 new.user.akb 26.03.2010 10:15:44
127.0.0.1 new.user.akb 26.03.2010 10:16:44
127.0.0.1 new.user.akb 26.03.2010 10:17:44
127.0.0.1 new.user.akb 26.03.2010 10:18:44
127.0.0.1 new.user.akb 26.03.2010 10:19:44
127.0.0.1 new.user.akb 26.03.2010 10:20:44
127.0.0.1 new.user.akb 26.03.2010 10:21:44
127.0.0.1 new.user.akb 26.03.2010 10:22:44
127.0.0.1 new.user.akb 26.03.2010 10:23:44
What I need to do is get number of distinct UserIPs from the table above that occured within a time interval. For example if I set the time interval for 5 minutes, and let's say that it starts at
26.03.2010 10:15:44
Then I will get 2 as the results, since there is 1 distinct value between 10:15 to 10:20 and , 1 other distinct value from 10:20 to 10:23,
For example if my in开发者_开发百科terval is 3 minutes than the return result will be 3
There's an almost identical question here: Group by Time interval.
The basics of it is that you need to group by the time interval by applying a floor to the datetime to flatten out the interval.
EDIT
Solved it using grouping:
SET DATEFORMAT dmy;
DECLARE @table TABLE
(
UserIp nvarchar(15),
UserId nvarchar(15),
VisitDate datetime
)
INSERT INTO @table
VALUES ('127.0.0.1', 'new.user.akb', '26.03.2010 10:15:44')
,('127.0.0.1', 'new.user.akb', '26.03.2010 10:16:44')
,('127.0.0.1', 'new.user.akb', '26.03.2010 10:17:44')
,('127.0.0.1', 'new.user.akb', '26.03.2010 10:18:44')
,('127.0.0.1', 'new.user.akb', '26.03.2010 10:19:44')
,('127.0.0.1', 'new.user.akb', '26.03.2010 10:20:44')
,('127.0.0.1', 'new.user.akb', '26.03.2010 10:21:44')
,('127.0.0.1', 'new.user.akb', '26.03.2010 10:22:44')
,('127.0.0.1', 'new.user.akb', '26.03.2010 10:23:44')
SELECT UserIp, UserId, MIN(VisitDate) AS firstVisit
FROM @table
GROUP BY dateadd(mi, (datepart(mi,VisitDate)/5)*5,
dateadd(hh, datediff(hh,0,VisitDate),0)),
UserIp, UserId
This gives the following result (my date format is ymd):
UserIp UserId firstVisit
--------------- --------------- -----------------------
127.0.0.1 new.user.akb 2010-03-26 10:15:44.000
127.0.0.1 new.user.akb 2010-03-26 10:20:44.000
(2 row(s) affected)
Which means you can count over this result set for a number of visits per 5min.
20000101 is some startdate:
select dateadd(mi, -d, '20000101') as d, num from
(select count(*) num, datediff(mi ,date_field, '20000101') / 5 * 5 d
from your_table
group by datediff(mi, date_field, '20000101') / 5 * 5 ) as a
order by d
And here's a C# solution using Linq:
var d1 = new Tuple<string, string, DateTime>("127.0.0.1", "new.user.akb", DateTime.Parse("26.03.2010 10:15:44"));
var d2 = new Tuple<string, string, DateTime>("127.0.0.1", "new.user.akb", DateTime.Parse("26.03.2010 10:16:44"));
var d3 = new Tuple<string, string, DateTime>("127.0.0.1", "new.user.akb", DateTime.Parse("26.03.2010 10:17:44"));
var d4 = new Tuple<string, string, DateTime>("127.0.0.1", "new.user.akb", DateTime.Parse("26.03.2010 10:18:44"));
var d5 = new Tuple<string, string, DateTime>("127.0.0.1", "new.user.akb", DateTime.Parse("26.03.2010 10:19:44"));
var d6 = new Tuple<string, string, DateTime>("127.0.0.1", "new.user.akb", DateTime.Parse("26.03.2010 10:20:44"));
var d7 = new Tuple<string, string, DateTime>("127.0.0.1", "new.user.akb", DateTime.Parse("26.03.2010 10:21:44"));
var d8 = new Tuple<string, string, DateTime>("127.0.0.1", "new.user.akb", DateTime.Parse("26.03.2010 10:22:44"));
var d9 = new Tuple<string, string, DateTime>("127.0.0.1", "new.user.akb", DateTime.Parse("26.03.2010 10:23:44"));
var list = new List<Tuple<string, string, DateTime>> {d1, d2, d3, d4, d5, d6, d7, d8, d9};
int interval = 3;
var query = list.GroupBy(data => ((int) (DateTime.Now - data.Item3).TotalMinutes)/interval*interval)
.Select(data => new {IP = data.First().Item1});
foreach (var entry in query)
{
Console.WriteLine(entry.IP);
}
精彩评论