related rooms query

[Uri Lazar]

hi, im working on this for a long time. i'm using MSsql-server2000
i have a table [visits] that records users visits to rooms. the columns are
room_id, user_id, visits.
i want to write a query that can calculate the top 10 rooms that are related
to any given room. i was thinking of firstly making a function that counts
how many users visited both room A and room B, and then running this
function on A and all other rooms, and order by the result. i keep getting
weird errors when doing that. please elaborate.

[John Bell]

Hi

Please post DDL (Create table statements you can use the generate SQL script
option in EM), example data (as insert statements), expected output and your
current queries. That will remove any ambiguity

It is not clear how you relate a users movement from one room to another,
what if the user has two browsers or shortcuts to specific rooms?

John

"Uri Lazar" <ar*******@bezeqint.net> wrote in message
news:3f******@news.bezeqint.net...

hi, im working on this for a long time. i'm using MSsql-server2000
i have a table [visits] that records users visits to rooms. the columns are room_id, user_id, visits.
i want to write a query that can calculate the top 10 rooms that are related to any given room. i was thinking of firstly making a function that counts
how many users visited both room A and room B, and then running this
function on A and all other rooms, and order by the result. i keep getting
weird errors when doing that. please elaborate.

[David Portas]

Without DDL and example data I'm not sure I've fully understood your
requirement. Here's some assumed DDL and sample data:

CREATE TABLE RoomVisits (roomid INTEGER NOT NULL /* REFERENCES Rooms
(roomid) */, userid INTEGER NOT NULL /* REFERENCES Users (userid) */, visits
INTEGER NOT NULL CHECK (visits>0), PRIMARY KEY (roomid, userid))

INSERT INTO RoomVisits VALUES (1,100,1)
INSERT INTO RoomVisits VALUES (2,100,1)
INSERT INTO RoomVisits VALUES (3,100,4)
INSERT INTO RoomVisits VALUES (4,100,2)
INSERT INTO RoomVisits VALUES (1,222,2)
INSERT INTO RoomVisits VALUES (2,222,4)

Apparently for each room "A" you want the top 10 related rooms "B", ordered
by total number of visits to B. Rooms are deemed related if any user has
visited both - is that correct? If so, it seems a slightly artificial
requirement. Surely by that definition if users are making tours of rooms
then every room will inevitably become related to every other, unless there
are many more rooms than users.

Anyway, here's the query. First create a view which lists each related A-B
combination and the corresponding total number of visits to B.

CREATE VIEW Related_Room_Visits (room_A, room_B, visits_to_B)
AS
SELECT A.roomid, B.roomid, MAX(C.tot_visits)
FROM RoomVisits AS A
JOIN RoomVisits AS B
ON A.userid = B.userid AND A.roomid <> B.roomid
JOIN
(SELECT roomid, SUM(visits) AS tot_visits
FROM RoomVisits
GROUP BY roomid) AS C
ON B.roomid = C.roomid
GROUP BY A.roomid, B.roomid

Now display just the Top N for each room A. For my example data I've just
specified TOP 2 but you can change this as required:

SELECT R1.room_A, R1.room_B, R1.visits_to_B
FROM Related_Room_Visits AS R1
JOIN Related_Room_Visits AS R2
ON R1.room_A=R2.room_A AND R1.visits_to_B <= R2.visits_to_B
GROUP BY R1.room_A, R1.room_B, R1.visits_to_B
HAVING COUNT(*) <= 2 /* Top 2 for each Room_A */
ORDER BY R1.room_A, R1.room_B, R1.visits_to_b DESC

If this doesn't help then please post DDL, post some sample data as INSERT
statements and give an example of your required result.

--
David Portas
------------
Please reply only to the newsgroup
--