Louis S. Berman
Louis S. Berman

Reputation: 994

Tag Cloud based on weighted usaage

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...

Database Diagram

Upvotes: 0

Views: 103

Answers (1)

HABO
HABO

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

Related Questions