I learned that you should always (well, mostly) try to let the database do most if not all of the work if possible for efficiency reasons.
After reading chapter 2 of the book Programming Collective Intelligence (by Toby Segaran) I picked up SQL for dummies and started typing.
+------+-----------------------+--------+ | user | movie | rating | +------+-----------------------+--------+ | John | Donny Darko | 3 | | John | Existenze | 5 | | John | Sex and the city | 4 | | John | The Hours | 2.5 | | John | The Matrix | 0 | | Tim | Blade | 4 | | Tim | Donny Darko | 2 | | Tim | Starwars 4 | 5 | | Tim | The Matrix | 5 | | Toby | Alien versus Predator | 3 | | Toby | Donny Darko | 5 | | Toby | The Hours | 4.5 | | Toby | The Matrix | 2.5 | +------+-----------------------+--------+
+-------+-------+------+------+--------+--------+-------+---+ | user1 | user2 | sum1 | sum2 | sum1sq | sum2sq | psum | n | +-------+-------+------+------+--------+--------+-------+---+ | Tim | John | 7 | 3 | 29 | 9 | 6 | 2 | | Toby | John | 12 | 5.5 | 51.5 | 15.25 | 26.25 | 3 | | Toby | Tim | 7.5 | 7 | 31.25 | 29 | 22.5 | 2 | +-------+-------+------+------+--------+--------+-------+---+The query around that then picks the results and calculates the Pearson coefficient from that:
+-------+-------+------------------+---+ | user1 | user2 | r | n | +-------+-------+------------------+---+ | Toby | John | 0.99942379712877 | 3 | | Tim | John | -1 | 2 | | Toby | Tim | -1 | 2 | +-------+-------+------------------+---+
SELECT user1, user2, ((psum - (sum1 * sum2 / n)) / sqrt((sum1sq - pow(sum1, 2.0) / n) * (sum2sq - pow(sum2, 2.0) / n))) AS r, n FROM (SELECT n1.user AS user1, n2.user AS user2, SUM(n1.rating) AS sum1, SUM(n2.rating) AS sum2, SUM(n1.rating * n1.rating) AS sum1sq, SUM(n2.rating * n2.rating) AS sum2sq, SUM(n1.rating * n2.rating) AS psum, COUNT(*) AS n FROM testdata AS n1 LEFT JOIN testdata AS n2 ON n1.movie = n2.movie WHERE n1.user > n2.user GROUP BY n1.user, n2.user) AS step1 ORDER BY r DESC, n DESC
For contact info, see this page.