people who viewed this also viewed....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

people who viewed this also viewed....

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-11-03 : 15:50:43

Hi,

I have a simple table as shown below. It represents a social networking feature, of users viewing users.

I would like to come up with a feature that represents "users who viewed this user, also viewed this user" So I would like to write a SPROC that is passed 1 parameter @userID
This data is probably best returned in a fashion that most "relevant" results are returned first.

Please note that multiple views from one profile viewing another are recorded.

Does anyone have an approach to this they would recommend?

Any help greatly appreciated!

Thanks again,
mike123

CREATE TABLE [dbo].[tblProfileViews_Users](
[viewID] [int] IDENTITY(1,1) NOT NULL,
[viewerUserID] [int] NOT NULL,
[profileUserID] [int] NOT NULL,
[viewDate] [smalldatetime] NOT NULL
) ON [PRIMARY]

GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-03 : 15:55:09

What is relevant?
The number of times a user has been viewed, or number of minutes since last view?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-03 : 16:02:59

Something similar to

CREATE PROCEDURE dbo.uspOtherRelevants
(
	@userID INT
)
AS

SET NOCOUNT ON

SELECT		w.profileUserID,
		COUNT(DISTINCT w.viewerUserID) AS otherProfileView,
		MAX(w.theDate) AS theViews
FROM		(
			SELECT		viewerUserID
			FROM		tblProfileViews_Users
			WHERE		profileUserID = @userID
			GROUP BY	viewerUserID
		) AS s
INNER JOIN	tblProfileViews_Users AS w ON w.viewerUserID = s.viewerUserID
GROUP BY	w.profileUserID

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-11-03 : 16:10:39

Hey Peso!

Good point. I was thinking the user views would be relevant.

What do you think of this explanation ? Does it make sense? (minus the session functionality since we don't accomodate for it in our design)

Create a table with 3 fields:
- userID
- similarUserID
- counter

When userA visits profile of userB:
1. fetch the list of all profiles visited by userA back to whichever happened last between:
a. the last visit of userA to profile of userB
b. the start of out time window / session / whatever we wanna call it
2. for each profile on that list (let's call each profile: userC):
- increase the counter field for (userID = userB AND similarUserID = userC) in our table (or create it with counter=1 if not present)
- increase the counter field for (userID = userC AND similarUserID = userB) in our table (or create it with counter=1 if not present)

to get the profiles similar to a given userX:
SELECT FROM [our table] WHERE userID = userX ORDER BY counter DESC

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-11-03 : 16:23:50

quote:

Originally posted by Peso

Something similar to

CREATE PROCEDURE dbo.uspOtherRelevants
(
	@userID INT
)
AS

SET NOCOUNT ON

SELECT		w.profileUserID,
		COUNT(DISTINCT w.viewerUserID) AS otherProfileView,
		MAX(w.theDate) AS theViews
FROM		(
			SELECT		viewerUserID
			FROM		tblProfileViews_Users
			WHERE		profileUserID = @userID
			GROUP BY	viewerUserID
		) AS s
INNER JOIN	tblProfileViews_Users AS w ON w.viewerUserID = s.viewerUserID
GROUP BY	w.profileUserID

E 12°55'05.63"
N 56°04'39.26"

Hey Peso,

Running this right now. I have alot of rows in this table (too much). I've let it run 5 minutes already. Perhaps what I need is a procedure I can run every 24 hours or so to update a table that contains results, and I could query that ?

Your thoughts appreciated.

Thanks again :)
mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources