Hallo zusammen,
ich zerbreche mir seit heute Vormittag den Kopf über einer Query und komme zu keinem Ergebnis. Es geht um Folgendes: Es gibt eine Tabelle, in der eine Pearson Correlation Matrix hinterlegt ist. Nennen wir die Tabelle einfach mal pearson; sie hat die Spalten user1, user2 und correlation. user1 und user2 sind Integer (IDs) und bilden den Primary Key, correlation ist float und enthält wie beim Pearson-Koeffizienten üblich Werte von -1 bis 1. Es gibt eine zweite Tabelle rating. Diese hat als Spalten userID, elementID, rating.*
Es geht jetzt darum, den k-Nearest Neighbour Algorithmus anzuwenden. Für Benutzer 1 soll für jedes Item in der Datenbank berechnet werden, welche Bewertung er diesem Item mit hoher Wahrscheinlichkeit geben wird. Dafür werden jeweils die drei ähnlichsten Benutzer gesucht, die das jeweilige Item ebenfalls bewertet haben. Aus deren Bewertungen setzt sich dann anteilig die errechnete Bewertung des Benutzers 1 zusammen.
Folgende Query löst das Problem soweit:
Die Query berechnet die Bewertung, die Benutzer 1 dem Element 1 geben würde.
Mein Problem ist jetzt: Ich möchte die Query gerne über ALLE Elemente laufen lassen, nicht nur über jeweils ein "hart" ins Query geschriebene Element. Das Ergebnis soll also eine Liste sein mit allen Element-IDs und der zugehörigen errechneten Bewertung. Es scheitert bei mir entweder daran, dass ich die elementID von den äußeren Querys nicht in den Subquerys benutzen darf oder daran, dass ich die Limitierung auf 3 (weil k = 3) nicht angeben kann.
Es ginge natürlich, einfach alle möglichen Element-IDs auszulesen und in PHP darüber zu iterieren und den Query für jede ID einzeln auszuführen. Aber ich hätte gerne eine MySQL-only Lösung.
Hat jemand eine Idee, wie ich das schaffen könnte?
Viele Grüße
Malte
ich zerbreche mir seit heute Vormittag den Kopf über einer Query und komme zu keinem Ergebnis. Es geht um Folgendes: Es gibt eine Tabelle, in der eine Pearson Correlation Matrix hinterlegt ist. Nennen wir die Tabelle einfach mal pearson; sie hat die Spalten user1, user2 und correlation. user1 und user2 sind Integer (IDs) und bilden den Primary Key, correlation ist float und enthält wie beim Pearson-Koeffizienten üblich Werte von -1 bis 1. Es gibt eine zweite Tabelle rating. Diese hat als Spalten userID, elementID, rating.*
Es geht jetzt darum, den k-Nearest Neighbour Algorithmus anzuwenden. Für Benutzer 1 soll für jedes Item in der Datenbank berechnet werden, welche Bewertung er diesem Item mit hoher Wahrscheinlichkeit geben wird. Dafür werden jeweils die drei ähnlichsten Benutzer gesucht, die das jeweilige Item ebenfalls bewertet haben. Aus deren Bewertungen setzt sich dann anteilig die errechnete Bewertung des Benutzers 1 zusammen.
Folgende Query löst das Problem soweit:
Code:
SELECT elId, SUM(partialRating) as prediction FROM (SELECT d.elementID as elId, d.rating*(pc.correlation/ (SELECT sum(correlation) FROM ( SELECT pci.correlation FROM pearson pci INNER JOIN rating d2 ON pci.user2 = d2.userID WHERE pci.user1 = 1 AND pci.user2 != 1 AND d2.elementID = 1 GROUP BY d2.elementID, pci.correlation ORDER BY pci.correlation DESC LIMIT 3 ) as sq ) ) as partialRating FROM pearson pc INNER JOIN rating d ON pc.user2 = d.userID WHERE pc.user1 = 1 AND pc.user2 != 1 AND d.elementID = 1 GROUP BY d.elementID, d.rating, pc.correlation ORDER BY pc.correlation DESC LIMIT 3) as predictedRating GROUP BY elId
Mein Problem ist jetzt: Ich möchte die Query gerne über ALLE Elemente laufen lassen, nicht nur über jeweils ein "hart" ins Query geschriebene Element. Das Ergebnis soll also eine Liste sein mit allen Element-IDs und der zugehörigen errechneten Bewertung. Es scheitert bei mir entweder daran, dass ich die elementID von den äußeren Querys nicht in den Subquerys benutzen darf oder daran, dass ich die Limitierung auf 3 (weil k = 3) nicht angeben kann.
Es ginge natürlich, einfach alle möglichen Element-IDs auszulesen und in PHP darüber zu iterieren und den Query für jede ID einzeln auszuführen. Aber ich hätte gerne eine MySQL-only Lösung.
Hat jemand eine Idee, wie ich das schaffen könnte?
Viele Grüße
Malte
Kommentar