Posted by windandwaves on 09/30/36 11:31

Ian B wrote:
> The problem is actually not as difficult as you might think - and you
> can get accurate results
>
> :-)
>
>
> First thing you need to realise is that you need your table in two
> ways:
>
> a) you need to look up id's which have accessed same page as the
> current page
> b) you need to look up the pages which those id's have accessed
>
> also
>
> c) you need to exclude the current page in b)
> d) you need to count the number of times each related page occurs
> e) you need to rank the results in order of popularity
>
>
> This might sound like a programming problem, but it can be done in one
> SQL statement
>
> I'll build it up so you can see how I got there:
>
> SELECT * FROM mytable AS t1
>
> gets the whole table and lets you refer to it as "t1"
>
>
> SELECT * FROM mytable AS t1
> LEFT JOIN mytable AS t2 ON t1.session_id = t2.session_id
>
> this gets you shedloads of results - one line for every combination
> so if id#1 has visited pages a, b & c then you get
>
> a-a
> a-b
> a-c
> b-a
> b-b
> b-c
> c-a
> c-b
> c-c
>
> but we only want the ones for, say, page "a", and we only need
> (from this data) the 'other' page
>
> SELECT t2.page_name FROM mytable AS t1
> LEFT JOIN mytable AS t2 ON t1.session_id = t2.session_id
> WHERE t1.page_name = 'a'
>
> this (for id#1)
>
> would produce
>
> b
> c
>
> running against all the data, would produce a whole list of pages -
> all the pages that anyone who has visited page 'a' has also been to
>
> If we group by page name, we'll get one line per page name
>
>
> SELECT t2.page_name FROM mytable AS t1
> LEFT JOIN mytable AS t2 ON t1.session_id = t2.session_id
> WHERE t1.page_name = 'a'
> AND t2.page_name <> 'a'
> GROUP BY t2.page_name
>
> g
> d
> e
> f
> b
> c
>
> If we add a count, we get the number of times that page turned up
>
>
> SELECT t2.page_name,Count(t2.page_name) AS popularity FROM mytable AS
> t1
> LEFT JOIN mytable AS t2 ON t1.session_id = t2.session_id
> WHERE t1.page_name = 'a'
> AND t2.page_name <> 'a'
> GROUP BY t2.page_name
>
>
> g 10
> d 27
> e 19
> f 41
> b 110
> c 83
>
>
> And if we order by descending popularity, we're almost there
>
>
>
> SELECT t2.page_name,Count(t2.page_name) AS popularity FROM mytable AS
> t1
> LEFT JOIN mytable AS t2 ON t1.session_id = t2.session_id
> WHERE t1.page_name = 'a'
> AND t2.page_name <> 'a'
> GROUP BY t2.page_name
> ORDER BY Count(t2.page_name) DESC
>
> b 110
> c 83
> f 41
> d 27
> e 19
> g 10
>
>
>
> The last thing to do is limit the results to the top 5 results
>
>
> SELECT t2.page_name,Count(t2.page_name) AS popularity FROM mytable AS
> t1
> LEFT JOIN mytable AS t2 ON t1.session_id = t2.session_id
> WHERE t1.page_name = 'a'
> AND t2.page_name <> 'a'
> GROUP BY t2.page_name
> ORDER BY Count(t2.page_name) DESC
> LIMIT 5
>
>
> b 110
> c 83
> f 41
> d 27
> e 19
>
> And there's your list
>
>
> ...
>
> Ian

The only thing one could probably add is to factor in how many places were
visited by someone in total. That is, a person who looked at alsmost all
pages should not have an influence as great as someone who only looked at
two pages (i.e. those links are meaningful).

How to do that in SQL as described above, I have no idea, but I may look
into this later.

[Back to original message]