Wyszukiwanie najbardziej pasujących szeregów danych. Opcje

[eshlox]

Witam.

Ostatnio tak myślę nad jednym zapytaniem, lub kilkoma.. lub czymkolwiek.. co prawdopodobnie będę potrzebował. Nie mogę jakoś wymyślić nic sensownego. Aczkolwiek, nie jest to chyba proste zadanie ;-)

Powiedzmy, że mamy taką tabelę mysql:

id | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | n

Id standardowo będzie zwiększane o 1 przy dodawaniu nowego szeregu danych, a w komórkach 1,2,3 itd będą wstawiane tylko i wyłącznie cyfry typu INT. Prawdopodobnie przedział od 1 - 9, chociaż myślę, że nie ma to tutaj znaczenia. Jak widać na końcu dałem n, bo z czasem ilość kolumn tabeli może się zwiększać o dalsze, nazwane n+1 i także zawierające INT.
Więc dajmy na to, niech na początku będzie id i 8 kolumn odnośnie danych. (i = id, skróciłem aby "tabela" była równa ;-))

i | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8
1 | 3 | 3 | 3 | 0 | 1 | 3 | 2 | 1
2 | 2 | 2 | 5 | 1 | 2 | 9 | 5 | 2
3 | 3 | 3 | 3 | 0 | 2 | 4 | 1 | 5
4 | 3 | 3 | 3 | 4 | 1 | 3 | 2 | 0

I teraz główna kwestia. Wybieramy sobie jakiś szereg, np. o id 1 i według niego szukamy n najbardziej pasujących do niego. Czyli tutaj dla przykładu, najwięcej zgadza się z szeregiem 4, bo 75% liczb w kolumnach się pokrywa, później szereg 3 - 50% no i na końcu szereg 2, który ma 0% zgodności. Limit to np. 10 najbardziej trafnych wyników. Wszystko posortowane od najtrafniejszego do najmniej trafnego. I także wynik procentowy względem każdego wyszukanego wyniku. (Wiadomo także, że może się okazać, iż jest więcej szeregów pasujących procentowo.)

Mam nadzieję, że każdy zrozumie o co mi chodzi ;->

Jakby ktoś miał pomysły, jak takie coś uzyskać, to chętnie posłucham ;-)

Pozdrawiam.

[jmail]

nie mogłeś wymyślić bardziej skomplikowanego sposobu na zabicie mysql'a? (IMG:style_emoticons/default/haha.gif)

W Windows wchodzisz menu start -> panel sterowania -> narzędzia administracyjne -> usługi -> prawym przyciskiem na mysql -> stop

a na przykłąd w ubuntu wpisujesz w terminal

sudo /etc/init.d/mysql stop

po pierwsze musiałbyś zmienić nazwy kolumn. na przykłąd nadać im przedrostek c bo inaczej się może wykrzaczyć. Ale ok na razie zostaw jak jest ;P
po drugie musiałbyś utworzyć procedurę mysql, która w pętli "przeleci" to za ciebie (IMG:style_emoticons/default/tongue.gif)

Po nie wiem dwóch godzinach intensywnego kodu (IMG:style_emoticons/default/biggrin.gif)

[SQL] pobierz, plaintext 
 
DELIMITER $$
 
DROP PROCEDURE IF EXISTS ZabijMySQLfunc $$
CREATE DEFINER=`root`@`%` PROCEDURE ZabijMySQLfunc(IN iloscKolumn int,IN iloscWynikow int,IN wzorcowyWiersz int)
 
BEGIN
 
    SET @iloscWynikow = iloscWynikow;
 
    CREATE TEMPORARY TABLE IF NOT EXISTS
        wyniki
        (
            idWiersza int NOT NULL PRIMARY KEY
            ,iloscDopasowan int NOT NULL DEFAULT 0
        );
    INSERT INTO
        wyniki
        (idWiersza)
    SELECT
        id
    FROM
        tabelaWzorcowa
    WHERE
        id <> wzorcowyWiersz;
 
    SET @iloscPrzebiegow = 0;
 
    etykieta1: LOOP
        IF @iloscPrzebiegow < iloscKolumn THEN
            SET @iloscPrzebiegow = @iloscPrzebiegow + 1;
            SET @kolumna1 = concat('`t1`.`',@iloscPrzebiegow,'`');
            SET @kolumna2 = concat('`t2`.`',@iloscPrzebiegow,'`');
 
            UPDATE
                wyniki
            SET
                iloscDopasowan = iloscDopasowan + 1
            WHERE
                idWiersza IN
                (
                    SELECT
                        t2.id
                    FROM
                        tabelaWzorcowa t1
                    INNER JOIN
                        tabelaWzorcowa t2
                    ON
                        @kolumna1 = @kolumna2
                    WHERE
                        t1.id = wzorcowyWiersz
                );
            ITERATE etykieta1;
        END IF;
        LEAVE etykieta1;
    END LOOP etykieta1;
 
    PREPARE STMT FROM 'SELECT idWiersza,iloscDopasowan FROM wyniki ORDER BY iloscDopasowan desc LIMIT ?';
    EXECUTE STMT USING @iloscWynikow;
 
    DROP TABLE wyniki;
 
END $$
 
DELIMITER ;
 
[SQL] pobierz, plaintext 

wywołujesz to

[SQL] pobierz, plaintext 
 
CALL ZabijMySQLfunc(tu wstawiasz parametry);
 
[SQL] pobierz, plaintext 

I prośba - nie każ mi tego tłumaczyć. Kolejnego litra wódki nie zniosę (IMG:style_emoticons/default/biggrin.gif)

//edit

no dobra przesadziłem (IMG:style_emoticons/default/biggrin.gif) to była godzina 20 i litr też nie poszedł. mam jeszcze pół szklanki na dnie (IMG:style_emoticons/default/biggrin.gif)

Ten post edytował jmail 16.10.2009, 04:35:13

[eshlox]

Huh ;-) Jak tylko będę miał chwilę czasu to sprawdzę to.. Ale rzeczywiście gdy rekordów będzie dużo.. i gdyby takie coś wykonać jednoczenie ileś razy.. Muszę pomyśleć chyba nad lepszym rozwiązaniem takiego porównywania.

Ale dzięki za kod ;-)

[jmail]

może napisz do czego to potrzebujesz to może coś lepszego się wymyśli. ale niczego szybszego niż takie zapytanie nie wymyślisz. Jeżelimówimy o danych z bazy. Taką samą ilość zapytań bedziesz musiał wykonać przez PHP a przez bazę szybciej to pójdzie.

[eshlox]

No ja tylko szukam sposobu jak najlepiej dokonać takiego porównania. W sumie nawet te cyfry mogą być ułożone jedna po drugiej i zapisane jako ciąg w bazie danych. Ale czy to coś zmieni..

[jmail]

ale kurde do czego to potrzebujesz? co będziesz porównywał? rozmairy stóp czy co?

[kitol]

jeżeli przeprojektujesz tabelę do postaci trzech kolumn:

numer_serii
numer_pomiaru_w_serii,
wartosc_pomiaru

to rozwiazanie będzie prawie banalne:

[SQL] pobierz, plaintext 
SELECT t1, SUM(dopasowanie)/COUNT(dopasowanie) FROM (
SELECT b.t1,  b.t3 = a.t3 dopasowanie FROM `test4` a
JOIN (SELECT * FROM test4 WHERE t1!=1) b USING(t2)
 WHERE a.t1=1
) c GROUP BY t1
[SQL] pobierz, plaintext 

sprawdzane na tabeli:

[SQL] pobierz, plaintext 
CREATE TABLE `test4` (
  `t1` int(10) UNSIGNED NOT NULL,
  `t2` int(10) UNSIGNED NOT NULL,
  `t3` int(10) UNSIGNED NOT NULL,
  PRIMARY KEY  (`t1`,`t2`)
) ENGINE=MyISAM;
 
INSERT INTO `test4` (`t1`, `t2`, `t3`) VALUES
(1, 1, 3),
(1, 2, 3),
(1, 3, 3),
(1, 4, 0),
(1, 5, 1),
(1, 6, 3),
(1, 7, 2),
(1, 8, 1),
(2, 1, 2),
(2, 2, 2),
(2, 3, 5),
(2, 4, 1),
(2, 5, 2),
(2, 6, 9),
(2, 7, 5),
(2, 8, 2),
(3, 1, 3),
(3, 2, 3),
(3, 3, 3),
(3, 4, 0),
(3, 5, 2),
(3, 6, 4),
(3, 7, 1),
(3, 8, 5),
(4, 1, 3),
(4, 2, 3),
(4, 3, 3),
(4, 4, 4),
(4, 5, 1),
(4, 6, 3),
(4, 7, 2),
(4, 8, 0);
[SQL] pobierz, plaintext 

Ten post edytował kitol 19.10.2009, 10:48:14