Czy w miejsce kolumn od p1-p10 nie powinna być jedna kolumna o nazwie “OCENA” i jakiś constraint walidujący że wartość w kolumnie jest od 1 do 10 ? Po co rozbijać na tyle kolumn ta samą logiczną informację.
Co według Ciebie znaczy podobieństwo odpowiedzi ? Jakim wzorem chcesz to liczyć ? Chcesz sprawdzać dystans jaki dzieli ocena innego użytkownika od twojej oceny na to samo pytanie ? Jeżeli tak to ustosunkuj się do pkt. 3.
jeżeli w tabeli test znajdzie się więcej niż jeden wiersz dla jakiegoś użytkownika to co to znaczy ? że na to samo pytanie odpowiedział 2 razy inaczej ? Nigdzie nie widzę informacji na jakie pytanie jest dana odpowiedź
czyli tabela “test” to tabela przechowująca różne testy ??
czyli np jak mamy rekord o id=5 i user_id = 6 i ocenie =5 to możemy powiedzieć że użytkownik 6 w jakimś tam teście dostał ocenę 5 ?
a jak mamy rekord o id = 16 i user_id = 6 i ocenie = 10 to znaczy że w zupełnie innym teście zdobył 10 punktów ? Po czym poznajemy o jaki test w danym wierszu chodzi ? Czy może cała tabela dotyczy tylko i wyłącznie jednego testu ?
myślałem że to jest tak dziwnie zrobione że p1 oznacza ocenę 1, p2 ocenę 2 itd trzeba było od razu napisać że pola pX to poszczególne pytania a ich wartość to ocena za odpowiedź na to pytanie
w takim razie skoro dla każdego uzytkownika może byc tylko jeden wpis w tabeli test to kolumna id w tej tabeli jest niepotrzebna bo primary key może byc user_id.
Co do zapytania czy taki prosty wzór ci wystarczy na to podobieństwo ?
dla każdego pytania liczymy dystans odpowiedzi jakiegoś tam użytkownika od twojej odpowiedzi
sumujemy dystanse dla wszystkich pytań
najmniejsza powstała suma oznacza zatem najmniejszy ogólny dystans odpowiedzi innych userów do Twoich odpowiedzi.
sprawdzamy czy moje.p1 = user.p1, moje.p2 = user=p2 itp… i obliczamy podobieństwo… jeśli odpowiedz moja = odpowiedz użytkownika wtedy mamy np. 1 punkt lub 10% (10 pytań po 10% = 100%)…
ok czyli tak naprawdę chcesz tylko pokazać procentowo ilość identycznych odpowiedzi a nie bawić się w kalkulacje że jak Ty masz ocenę 5 a user X na to samo ma 6 a user Y na to samo ma 7 to user X jest bliżej trafnej odpowiedzi
Nie wiem czy Cie to usatysfakcjonuje ale tu masz zapytanie, które sobie napisałem na Oracle’u. Przełożenie tego na mySql nie powinno być ciężkie.
Nie używałem nigdy mySQLa i nie mam go pod ręką więc przeanalizuj chociaż to (raczej widziałem w googlach że te rzeczy które użyłem w zapytaniu występują również w mySql):
SELECT t1.user_id,
( (CASE t1.p1 WHEN t3.my_p1 THEN 1 ELSE 0 END)
+ (CASE t1.p2 WHEN t3.my_p2 THEN 1 ELSE 0 END)
+ (CASE t1.p3 WHEN t3.my_p3 THEN 1 ELSE 0 END)
+ (CASE t1.p4 WHEN t3.my_p4 THEN 1 ELSE 0 END)
+ (CASE t1.p5 WHEN t3.my_p5 THEN 1 ELSE 0 END)
+ (CASE t1.p6 WHEN t3.my_p6 THEN 1 ELSE 0 END)
+ (CASE t1.p7 WHEN t3.my_p7 THEN 1 ELSE 0 END)
+ (CASE t1.p8 WHEN t3.my_p8 THEN 1 ELSE 0 END)
+ (CASE t1.p9 WHEN t3.my_p9 THEN 1 ELSE 0 END)
+ (CASE t1.p10 WHEN t3.my_p10 THEN 1 ELSE 0 END))
* 10
AS perc_similarity
FROM test_test t1,
(SELECT t2.p1 AS my_p1,
t2.p2 AS my_p2,
t2.p3 AS my_p3,
t2.p4 AS my_p4,
t2.p5 AS my_p5,
t2.p6 AS my_p6,
t2.p7 AS my_p7,
t2.p8 AS my_p8,
t2.p9 AS my_p9,
t2.p10 AS my_p10
FROM test_test t2
WHERE t2.user_id = 10) t3
WHERE t1.user_id <> 10;
Co się dzieje w tym zapytaniu, zakładamy że user_id = 10 to Ty a userzy inni od 10 to cała reszta:
Robię złączenie między tabela test a Twoim jednym wierszem z tej tabeli. Nie mam warunku złączenia bo w tym przypadku iloczyn kartezjański może się wykonać bo i tak w drugiej wirtualnej tabeli mamy jeden wiersz tylko. Więc każdy z każdym da nam sytuację że do każdego wiersza z tabeli test dołączam wartości twoich odpowiedzi. Do porównania Twoich odpowiedzi z innymi używam prostych caseow które są w mySQL również. Mnożę * 10 żeby wyglądało jak procent i gotowe. Mam nadzieje że o to Ci chodziło.
SELECT t1.user_id,
t4.login,
( (CASE t1.p1 WHEN t3.my_p1 THEN 1 ELSE 0 END)
+ (CASE t1.p2 WHEN t3.my_p2 THEN 1 ELSE 0 END)
+ (CASE t1.p3 WHEN t3.my_p3 THEN 1 ELSE 0 END)
+ (CASE t1.p4 WHEN t3.my_p4 THEN 1 ELSE 0 END)
+ (CASE t1.p5 WHEN t3.my_p5 THEN 1 ELSE 0 END)
+ (CASE t1.p6 WHEN t3.my_p6 THEN 1 ELSE 0 END)
+ (CASE t1.p7 WHEN t3.my_p7 THEN 1 ELSE 0 END)
+ (CASE t1.p8 WHEN t3.my_p8 THEN 1 ELSE 0 END)
+ (CASE t1.p9 WHEN t3.my_p9 THEN 1 ELSE 0 END)
+ (CASE t1.p10 WHEN t3.my_p10 THEN 1 ELSE 0 END))
* 10
AS perc_similarity
FROM test_test t1,
(SELECT t2.p1 AS my_p1,
t2.p2 AS my_p2,
t2.p3 AS my_p3,
t2.p4 AS my_p4,
t2.p5 AS my_p5,
t2.p6 AS my_p6,
t2.p7 AS my_p7,
t2.p8 AS my_p8,
t2.p9 AS my_p9,
t2.p10 AS my_p10
FROM test_test t2
WHERE t2.user_id = 10) t3,
test_loginy t4
WHERE t1.user_id <> 10
AND t4.id = t1.user_id;
W tabeli loginy dziwne jest trochę to że kolumna loginy jest default null, chyba login powinien być zawsze NOT NULL.
:
lista użytkowników i % w jakim odpowiedzieli podobnie do mnie 