Reputation: 994
I'd like to create a weighted usage ranking / popularity query (or batch update, if the query proves to strenuous for real-time use!) but I've been drawing a blank. Hopefully you'll have a better idea as to how to do this.
I've simplified my database to help illustrate the problem (see diagram, below!) Basically, when a User selects a specific Blog via a Tag, I add an entry to the TagLog table. Assume for this example that the collection of Blogs and Tags remain static. Assuming the above, I'd like to do the following:
The real difficulty comes from the fact that I'd like to weight the results such that more recent TagLog entries have greater significance.
Any help in this regard would be greatly appreciated! Thanks...
Upvotes: 0
Views: 103
Reputation: 15816
This should get you headed somewhere useful:
-- Sample data.
declare @Blogs as Table ( BlogId Int Identity, URL VarChar(256) )
insert into @Blogs ( URL ) values
( 'www.google.com' ), ( 'www.java.com' )
declare @Tags as Table ( TagId Int Identity, BlogId Int, Tag VarChar(64) )
insert into @Tags ( BlogId, Tag ) values
( 1, 'Not Evil' ), ( 2, 'Buggy' )
declare @TagLog as Table ( TagId Int, UserGuid UniqueIdentifier, Visited DateTime )
insert into @TagLog ( TagId, UserGuid, Visited ) values
( 1, NewId(), '20130502' ), ( 1, NewId(), '20130508' ), ( 1, NewId(), '20130515' ),
( 2, NewId(), '20130501' ), ( 2, NewId(), '20130508' ), ( 2, NewId(), '20130515' )
declare @Now as DateTime = '20130516' -- Test value.
-- Display all sample data.
select *, DateDiff( day, TL.Visited, @Now ) as Age -- Use appropriate units, e.g. week, minute.
from @Blogs as B inner join
@Tags as T on T.BlogId = B.BlogId inner join
@TagLog as TL on TL.TagId = T.TagId
-- Compute a weight based on age.
-- Use the reciprocal of the age so that newer visits have higher weight.
-- Add 1.0 to avoid divide by zero errors.
select T.TagId, Count( 42 ) as Visits, Sum( 1.0 / ( DateDiff( day, TL.Visited, @Now ) + 1.0 ) ) as AgeWeight
from @Blogs as B inner join
@Tags as T on T.BlogId = B.BlogId inner join
@TagLog as TL on TL.TagId = T.TagId
group by T.TagId
Upvotes: 1