Witam,

Posiadam tabelę np.

chars
- id
- char

sums
- id
- char_id
- sum

Gdzie dla każdego wpisu w chars istnieje 10 rekordów w sums dla danego char. Np.

chars:
- 1
- 'A'

sums:
- 1
- 1
- 235

Teraz potrzebne mi optymalne zapytanie które na podstawie 10 liczb wyszuka najbliższy znak (chars.char) dla najbardziej podobnej sumy (sums.sum)

Ma ktoś pomysł jak to zrobić?

A kiedy suma będzie najbardziej podobna do znaku?

Wtedy gdy sumy wejściowe (10 liczb) będą najbliższe odpowiednim sumą dla danego znaku (biorąc pod uwagę kolejność tzn. dla 1 liczby wejściowej porównujemy tylko 1 sumy znaków).

Np. mamy w bazie 2 znaki 'A' oraz 'B'

Sumy dla 'A' i 'B' (dla uproszczenia tylko 3 sumy):

'A' 'B'
52 8
123 115
15 21

Dane wejściowe (sumy) :
38, 120, 8

Znak wynikowy: 'A'

Głównie chodzi o to, że mam obrazki dla każdego znaku. Obrazek jest przerobionym dźwiękiem na png, a następnie ten png jest przetwarzany na 2 kolorowy (czarny i biały). Następnie wykonuję 10 próbek dla każdego znaku i obliczam ilość białych pikseli w każdej z nich.

Znalazłem skrypt ale tylko dla porównania 1 sumy, nie wiem jak tu wkleić optymalnego join`a:

[SQL] pobierz, plaintext 
SELECT top 4 Number
FROM (
  SELECT Number
  FROM NumberTable
  WHERE number BETWEEN 1009-100 AND 1009+100
)
WHERE number <> 1009
ORDER BY abs(Number - 1009)
[SQL] pobierz, plaintext 

Gdzie Number to sums.sum, 1009 to suma której szukamy a 100 to zakres.

Jeśli dobrze zrozumiałem.

[SQL] pobierz, plaintext 
SELECT tmp.char
FROM 
((SELECT c.char,MIN(ABS(s.sum-38)) AS diff FROM chars AS c,sums AS s
WHERE c.id=s.char_id
GROUP BY c.char
ORDER BY diff LIMIT 0,1)
UNION
(SELECT c.char,MIN(ABS(s.sum-120)) AS diff FROM chars AS c,sums AS s
WHERE c.id=s.char_id
GROUP BY c.char
ORDER BY diff LIMIT 0,1)
UNION
(SELECT c.char,MIN(ABS(s.sum-8)) AS diff FROM chars AS c,sums AS s
WHERE c.id=s.char_id
GROUP BY c.char
ORDER BY diff LIMIT 0,1)
) AS tmp
GROUP BY tmp.char
ORDER BY COUNT(tmp.char) DESC LIMIT 0,1
[SQL] pobierz, plaintext 

On wprowadza 10 liczb

Ale dla przykładu podał 3 wejściowe. Rozbuduje sobie.

@trueblue Wydaje mi się, że to zapytanie nie rozwiąże mojego problemu.

Przebudowałem nieco strukturę tabel :

chars
- id
- char ('A', 'B' itd.)

samples
- id
- char_id (chars.id)
- part (od 0 do 9)
- sum (od 0 do 99999)

Myślałem o rozbiciu tego na np. 10 zapytań dla każdego part, i w wyniku otrzymamy 10 najbardziej podobnych znaków dla każdego partu osobno np. 'A', 'B', 'C', 'A', 'D', 'A' ... 'A' i wtedy wybiorę ten znak który występuje najczęściej - ale to rozwiązanie także nie jest do końca satysfakcjonujące ponieważ niektóre ze znaków naprawdę mają podobne part`y.

Zawsze mogę pobrać wszystkie znaki i party a następnie dla każdego obliczać coś ala 'stopień zgodności' - i znak z największym stopniem zgodności byłby wybierany - ale to wiąże się z pobraniem kilku k rekordów za każdym razem i foreachem po nich. Hmm.

Dlaczego dodałeś kolumnę part? Czy odpowiada ona numerowi próbki?

Dokładnie. I porównujemy tylko te same party ze sobą.

Aktualnie zrobiłem tak, że pobieram wszystkie znaki wraz z partami do tablicy - robię foreach po tej tablicy i dla każdego partu każdej litery/znaku sumuję "różnicę". Potem sprawdzam gdzie różnica wynosiła najmniej i wybieram ten znak - skuteczność 99% z wyjątkami - problemem jest to, że przetwarzam wszystko po stronie PHP a wolałbym po stronie MySQL (wiadomo - szybciej).

To jest moja metoda która na wejściu dostaje tablice próbek (partów) a na wyjściu dopasowany znak.

[PHP] pobierz, plaintext 
 
    public function matchChar($s)
    {
        if (!is_array($s))
            return false;
 
        $char = '';
        $samples = array();
        $differences = array();
        $chars = $this->db->selectAssocs("SELECT * FROM chars");
 
        foreach($chars as $i => $char)
            $samples[$i] = $this->db->selectAssocs("SELECT * FROM samples WHERE char_id = " . $char['id']);
 
        foreach($chars as $i => $char)
        {
            $difference = 0;
            foreach($samples[$i] as $part => $sample) {
                if(!isset($differences[$i])) $differences[$i] = 0;
                $differences[$i] += abs($s[$part] - $sample['sum']);
            }
        }
 
        $i = array_keys($differences, min($differences));
        $i = $i[0];
        $char = $chars[$i]['char'];
 
        return $char;
    }
 
[PHP] pobierz, plaintext 

No, tak. Mój SQL tego nie uwzględniał, tj. porównania N-tej próbki wejściowej z N-tą próbką w tabeli.

[SQL] pobierz, plaintext 
SELECT tmp.char
FROM 
((SELECT c.char,MIN(ABS(s.sum-38)) AS diff FROM chars AS c,sums AS s
WHERE c.id=s.char_id AND s.part=0
GROUP BY c.char
ORDER BY diff LIMIT 0,1)
UNION
(SELECT c.char,MIN(ABS(s.sum-120)) AS diff FROM chars AS c,sums AS s
WHERE c.id=s.char_id AND s.part=1
GROUP BY c.char
ORDER BY diff LIMIT 0,1)
UNION
(SELECT c.char,MIN(ABS(s.sum-8)) AS diff FROM chars AS c,sums AS s
WHERE c.id=s.char_id AND s.part=2
GROUP BY c.char
ORDER BY diff LIMIT 0,1)
) AS tmp
GROUP BY tmp.char
ORDER BY COUNT(tmp.char) DESC LIMIT 0,1
[SQL] pobierz, plaintext