SQL Guys, I need your help!

Izi · 12 Apr 2011 at 13:42

For a website I am doing I am tracking views, likes and ratings on posts.

I get a ton load more views than I do ratings or likes. What I want to be able to do in real time is query the database and update a 'whats happening now' area via AJAX. The problem: I don't want them all to be views.

Is there a way to select a distributed list of views, likes and ratings?

DB Table:
ItemId, TrackingTypeId, DateTimeOfTrack

basically I would like a mix of items from the table, without them all just being views.

I have thought about doing a SELECT TOP and UNION but then when I automatically update it will always just be views most of the time.

Haircut · 12 Apr 2011 at 14:22

I'm not quite sure what you mean?
Are views, likes and ratings different tracking types in your table?

If so, do you mean that you want to get the most recent N items for each TrackingTypeId at a particular point in time?

If so, have a look at the Rank analytic function.
It's supported in Oracle & SQL Server for sure, no idea about other databases and not sure what you're using.

http://www.oracle-base.com/articles/misc/RankDenseRankFirstLastAnalyticFunctions.php

Izi · 12 Apr 2011 at 14:50

Haircut said:
Are views, likes and ratings different tracking types in your table?

Yes, exactly.

I will take a look at the rank function

Haircut · 12 Apr 2011 at 14:53

Cool, something like this should give you the 10 most recent items of each type (assuming your db supports it)

Code:

SELECT ItemId, TrackingTypeId, DateTimeOfTrack
FROM
  (SELECT ItemId, TrackingTypeId, DateTimeOfTrack,
          RANK() OVER (PARTITION BY TrackingTypeId ORDER BY DateTimeOfTrack DESC) AS position
     FROM MyTable
  ) AS ss
WHERE position <= 10

Izi · 12 Apr 2011 at 15:09

Haircut said:
Cool, something like this should give you the 10 most recent items of each type (assuming your db supports it)

Code:

SELECT ItemId, TrackingTypeId, DateTimeOfTrack FROM (SELECT ItemId, TrackingTypeId, DateTimeOfTrack, RANK() OVER (PARTITION BY TrackingTypeId ORDER BY DateTimeOfTrack DESC) AS position FROM MyTable ) AS ss WHERE position <= 10

OK so I have that executing, but its grouping the tracking types together, not muddled up. I am ordering by CreatedOn datetime field:

Code:

SELECT     TrackingItemID, ActionTypeID, CreatedOn
FROM         (SELECT     TrackingItemID, ActionTypeID, CreatedOn, RANK() 
                                              OVER (PARTITION BY ActionTypeID
                       ORDER BY CreatedOn DESC) AS position
FROM         ProductTrackingItem) AS ss
WHERE     position <= 20
ORDER BY CreatedOn DESC

Any ideas?

Haircut · 12 Apr 2011 at 15:21

That doesn't sound right.
As long as you're ordering by the appropriate thing in the outer query it should sort itself out.
Are you sure you don't just have lots of entries within a single second that are coming back as the same datetime or anything like that?

Izi · 12 Apr 2011 at 15:33

Haircut said:
That doesn't sound right.
As long as you're ordering by the appropriate thing in the outer query it should sort itself out.
Are you sure you don't just have lots of entries within a single second that are coming back as the same datetime or anything like that?

you're right again, I was being stupid.

could you explain the position part a bit more? When I set this to a higher number I get more results. A lower number I get fewer results, my brain is farting trying to understand how its working.

Haircut · 12 Apr 2011 at 15:47

Basically, the subselect bit in the middle selects four things from your table, the TrackingItemID, ActionTypeIDCreatedOn date and the rank.
The rank is calculated with the analytic function and the way you're using it calculates the rank per ActionTypeIDordered by CreatedOn desc, so the most recent item for each ActionTypeId has a rank of 1, second most recent has a rank of 2 and so on (Read up on rank and dense_rank for how things are calculated when two values are the same)

So the outer query then uses the rank column (named as position for ease of use) and then just asks for all the rows where the rank is less than a particular number.
e.g. if you query where position <= 20 you're basically asking for all the items with a rank less than or equal to 20.
Because of how you defined the rank function above this equates to the 20 most recent items for each ActionTypeID

Hope that makes some sense.
Analytic functions can be a bit mind bending at times, but they're damn useful on databases that support them.

Izi · 12 Apr 2011 at 15:57

Haircut said:
Basically, the subselect bit in the middle selects four things from your table, the TrackingItemID, ActionTypeIDCreatedOn date and the rank.
The rank is calculated with the analytic function and the way you're using it calculates the rank per ActionTypeIDordered by CreatedOn desc, so the most recent item for each ActionTypeId has a rank of 1, second most recent has a rank of 2 and so on (Read up on rank and dense_rank for how things are calculated when two values are the same)

So the outer query then uses the rank column (named as position for ease of use) and then just asks for all the rows where the rank is less than a particular number.
e.g. if you query where position <= 20 you're basically asking for all the items with a rank less than or equal to 20.
Because of how you defined the rank function above this equates to the 20 most recent items for each ActionTypeID

Hope that makes some sense.
Analytic functions can be a bit mind bending at times, but they're damn useful on databases that support them.

Thanks, understood.

One last thing, is there a way to get the returned items to be not of the same product item.

The query is working well now, but the problem is I'd prefer if they weren't all shown from the same product. I.E 10 views all from the same product is a bit poop. Is the a way to group by the product items to get a more distinct set of products?