Złożone zapytanie z obliczeniami w MySQL


(northwest) #1

Witam serdecznie,

Mam takie pytanko, mam 2 tabele:

CREATE TABLE IF NOT EXISTS `test` (

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `user` bigint(20) DEFAULT NULL,

  `p1` smallint(1) DEFAULT NULL,

  `p2` smallint(1) DEFAULT NULL,

  `p3` smallint(1) DEFAULT NULL,

  `p4` smallint(1) DEFAULT NULL,

  `p5` smallint(1) DEFAULT NULL,

  `p6` smallint(1) DEFAULT NULL,

  `p7` smallint(1) DEFAULT NULL,

  `p8` smallint(1) DEFAULT NULL,

  `p9` smallint(1) DEFAULT NULL,

  `p10` smallint(1) DEFAULT NULL,

  UNIQUE KEY `gt_id` (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE IF NOT EXISTS `loginy` (

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `login` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL,

  UNIQUE KEY `gt_id` (`gt_id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

w tabeli test pola oznaczają:

  • user = loginy.id

  • p1-p10= liczba od 1 do 10 (ocena)

Mam skrypt który ma w $_SESSION['dane'] - id usera (przykładowo).

Chce generalnie wyświetlić listę użytkowników wraz z informacją (procentową) jak bardzo podobną do mojej odpowiedź zaznaczyli...

SELECT * FROM test WHERE user = '$_SESSION['dane']' - zwróci mi informację o moich odpowiedziach....

Teraz chciałbym poznać odpowiedzi innych w formie

  • login_uzytkownika | ocena_procentowa

  • login_uzytkownika | ocena_procentowa

  • login_uzytkownika | ocena_procentowa

...

Wiecie może jak to zrobić?

Z góry dziękuje za pomoc,

Northwest


(Fordmtonly) #2
  1. 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ę.

  2. 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.

  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ź


(northwest) #3

w tabeli z testami są oceny wielu użytkowników. każdy użytkownik może mieć nieskończenie wiele ocen od innych użytkowników.

Ocenę chce uzyskać w formie procentowej, czyli:

  • pobrane z bazy moje odpowiedzi = 100%

  • szukamy w bazie ocen dla innych użytkowników i porównujemy ich z moją oceną - i wyświetlamy w % podobieństwo...

hmm... jak by to porównać... Załóżmy że wypełniam formularz zgłoszeniowy do biura pracy i na podstawie tych danych szukamy ludzi którzy

zaznaczyli odpowiedzi podobne do moich... nie wiem czy dość dobrze to zobrazowałem....


(Fordmtonly) #4

Nie wiem czy dobrze rozumiem :slight_smile: :

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 ?


(northwest) #5

tzn mamy pola: p1,p2,p3,p4,p5,p6,p7,p8,p9,p10 - to są odpowiedzi na pytania (najczęściej od 1 do 10, ale nie zawsze).

Jeden użytkownik może wypełnić tylko 1 test. Ale porównań ja -> inni użytkownicy może być nieskończenie wiele

Test jest wspólny dla wszystkich użytkowników...

chyba teraz lepiej to zobrazowałem :slight_smile: 1 test i 10 pytań (p1-p10) - i patrzymy który odpowiedział najbardziej podobnie do mnie :slight_smile:


(Fordmtonly) #6

teeeraz rozumiem :slight_smile:

to mnie zmyliło:

myślałem że to jest tak dziwnie zrobione że p1 oznacza ocenę 1, p2 ocenę 2 itd :slight_smile: trzeba było od razu napisać że pola pX to poszczególne pytania a ich wartość to ocena za odpowiedź na to pytanie :wink:

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.


(northwest) #7

tzn prościej :slight_smile:

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%)...

przedstawiamy to w formie

  • login | %

  • login | %

...

...


(Fordmtonly) #8

ok :slight_smile: 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 :slight_smile:


(northwest) #9

dokładnie :slight_smile: lista użytkowników i % w jakim odpowiedzieli podobnie do mnie :))


(Fordmtonly) #10

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.


(northwest) #11

jest super :))


(Fordmtonly) #12
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.


(northwest) #13

super :))