[MySQL]Złożone zapytanie z obliczeniami Opcje

[northwest]

Witam serdecznie,
Mam takie pytanko, mam 2 tabele:

[SQL] pobierz, plaintext 
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 ;
[SQL] pobierz, plaintext 

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

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

Wiecie może jak to zrobić?


Z góry dziękuje za pomoc,
Northwest

Ten post edytował northwest 28.02.2011, 14:17:54

[AlexDeLarge]

[SQL] pobierz, plaintext 
SELECT loginy.login,
  ((ABS(test.p1 - $prawidlowaOdpowiedz1) / $maximumPunktowZaPytanie1) * 100) AS pkt1,
  ((ABS(test.p2 - $prawidlowaOdpowiedz2) / $maximumPunktowZaPytanie2) * 100) AS pkt2,
  ((ABS(test.p3 - $prawidlowaOdpowiedz3) / $maximumPunktowZaPytanie3) * 100) AS pkt3,
  ((ABS(test.p4 - $prawidlowaOdpowiedz4) / $maximumPunktowZaPytanie4) * 100) AS pkt4,
  ((ABS(test.p5 - $prawidlowaOdpowiedz5) / $maximumPunktowZaPytanie5) * 100) AS pkt5,
  ((ABS(test.p6 - $prawidlowaOdpowiedz6) / $maximumPunktowZaPytanie6) * 100) AS pkt6,
  ((ABS(test.p7 - $prawidlowaOdpowiedz7) / $maximumPunktowZaPytanie7) * 100) AS pkt7,
  ((ABS(test.p8 - $prawidlowaOdpowiedz8) / $maximumPunktowZaPytanie8) * 100) AS pkt8,
  ((ABS(test.p9 - $prawidlowaOdpowiedz9) / $maximumPunktowZaPytanie9) * 100) AS pkt9,
  ((ABS(test.p10 - $prawidlowaOdpowiedz10) / $maximumPunktowZaPytanie10) * 100) AS pkt10
FROM test JOIN loginy ON test.user=loginy.id
[SQL] pobierz, plaintext 

[Valdi_B]

Proponuję pewne rozwinięcie powyższej kwerendy:

Sama powyższa kwerenda daje wykaz różnic oddzielnie "po poszczególnych pytaniach", co ciężko ocenić "na oko".
Warto by mieć jakiś jeden syntetyczny wskaźnik, np. sumę różnic w zakresie poszczególnych odpowiedzi.

Można zrobić coś takiego:

[SQL] pobierz, plaintext 
SELECT login, pkt1+pkt2+pkt3+pkt4+pkt5+pkt6+pkt7+pkt8+pkt9+pkt10 AS sum_pkt
FROM (...) AS p
ORDER BY sum_pkt
[SQL] pobierz, plaintext 

Zamiast wielokropka do powyższej kwerendy trzeba wstawić kwerendę z poprzedniego postu.
Jest to więc w istocie kwerenda "ciągnąca" dane z kwerendy zagnieżdżonej.
Fragment "AS p" wynika stąd, że MySQL w przypadku zagnieżdżonych kwerend wymaga nadania im synonimu.
Dzięki sortowi na sumę różnic mamy "najlepiej pasujących" na początku.

Takie rozwiązanie jest bliższe postulowanemu formatowi "login_uzytkownika | ocena_procentowa" (jedna).

Inny wariant: Zamiast zwykłej sumy można ew. liczyć sumę kwadratów różnic (stosownie modyfikując powyższą kwerendę).

Ten post edytował Valdi_B 28.02.2011, 17:17:57