Looking for the maximum number of rows created in an hour?
Another option is to use the SQL DATEPART functions like this:
SELECT
DAY(TimeStamp), MONTH(TimeStamp), YEAR(TimeStamp),
DATEPART(HOUR, TimeStamp),
COUNT(*)
FROM
dbo.Products
GROUP BY
DAY(TimeStamp), MONTH(TimeStamp), YEAR(TimeStamp),
DATEPART(HOUR, TimeStamp)
ORDER BY
COUNT(*) DESC
This not only gives you the maximum number of views in any hour, but everything sorted by frequency. Keep in mind: other than RexM's solution, this is based on the "hourly" part of your "timestamp" - so if you have quite a few views at 7:59 am and another burst at 8:01 am, in my solution those t are shown together (since one hour = 7 and the other hour = 8).
If you want an "any 60 minute time span" approach, use the basic idea of RexM (DATEDIFF with minutes <= 60).
Mark
source to share
I don't know how easy it is to calculate this metric, but hopefully it helps. Without any SQL cursor, I would generate an SQL table of possible intervals with start and end times (2009-09-02T00: 00 to 2009-09-02T00: 59, 2009-09-02T00: 01 to 2009-09 -02T01 : 00, etc.), and then cross join using LINQ to SQL:
var rates = from r in db.Reviews
from i in db.Intervals
where i.Begin <= r.Timestamp && r.Timestamp <= i.End
group r by i.Begin into reviews
select reviews.Count();
var maxRate = rates.Max();
I haven't tried the code, but it should get you started. You could improve performance by limiting how far back to check (last 7 days, 30 days, etc.), or create fewer intervals (starting at a quarter of an hour, for example).
source to share
If you want "which product gets the most reviews in the 60 minute block between dateX and dateY" then it gets a little more complicated.
One way to think about this is "for each review in that time period, how many other reviews are there for the same Product ID in the next 60 minutes." After you get this logic, the query becomes clearer:
SELECT TOP 1
PR.ProductID,
-- start of 60 minute block
PR.Timestamp,
ReviewCount = (
SELECT COUNT(*)
FROM ProductReviews PR1
-- from parent time
WHERE PR1.Timestamp >= PR.Timestamp
-- until 60 mins later
AND PR1.Timestamp <= DATEADD( minute, 60, PR.Timestamp )
-- that matches ProductID
AND PR1.ProductID = PR.ProductID
)
FROM ProductReviews PR
-- in the last 24 hours
WHERE PR.Timestamp > ( GETDATE() - 1 )
ORDER BY ReviewCount DESC
Knows what?
source to share
If you are working with a set 60 minute time block (for example, the last 60 minutes), then it is quite simple:
SELECT TOP 1
PR.ProductID,
COUNT(*)
FROM ProductReviews PR
WHERE PR.Timestamp BETWEEN DATEADD( minute, -60, GETDATE() ) AND GETDATE()
GROUP BY PR.ProductID
ORDER BY COUNT(*) DESC
If you want it for any 60 minute interval, it gets more difficult!
source to share
If you were happy with the fixed hours for your windows, I can think of this a little smarter using a trigger. The trigger will insert / update into the "history" table and just distinguish between insert or update based on the current time.
You could combine any other approach with this, it would add a nice layer of caching.
source to share