Mnożenie rekordów w SQL

Podczas prac nad jednym z modułów systemu który rozwijam pojawił się pomysł mnożenia wartości zgrupowanych danych. Niestety standardowy mechanizm SQL pozwala jedynie na kilka operacji na rekordach: sum(), avg(), count() etc. Jak to zrobić? Okazuje się, że banalnie prosto:

W = exp(sum(log(kolumna)))

Czyli z grubsza wystarczy zamienić mnożenie na sumowanie logarytmów.

Taa. Jasne.

No to po kolei:

  • W – wynik
  • exp – exponens, czyli funkcja wykładnicza o podstawie e
  • sum – suma
  • log – logarytm naturalny, czyli o podstawie e, co w normalnym świecie zapisujemy jako ln (log to konwencja w SQL)
  • kolumna – sumowana kolumna

I już. No, prawie. Trzeba pamiętać, że logarytm z liczby zero nie istnieje (wartość dąży do -∞), dlatego oddzielnie trzeba dać warunki na zero.

No dobra, a dlaczego tak? Łopatologicznie:

a * b * c = a * b * c

Skoro tak, to jeśli obie strony zlogarytmujemy nadal będziemy mieć to samo. Przyjmijmy, że to logarytm naturalny:

ln(a*b*c) = ln(a*b*c)

Mnożąc obie strony przez 1 (słownie: jeden) będziemy nadal mieli to samo. A jedynkę możemy przecież wyrazić też jako ln(e):

ln(a*b*c) = ln(a*b*c) * ln(e)

Kolejny myk, to zastosowanie własności logarytmu, że potęgę wartości logarytmowanej można wyłączyć przed logarytm:

logabc = c * logab

Stosując rozumowanie odwrotne – wrzucanie stałej jako potęgę wartości logarytmowanej – możemy napisać w naszym przypadku:

ln(a*b*c) = ln(a*b*c) * ln(e) = ln(eln(a*b*c))

To nadal mnożenie, tylko zapisane naokoło. Pozostaje uwzględnić kolejną własność logarytmu: logarytm iloczynu = suma logarytmów (oczywiście o takiej samej podstawie):

log(a*b*c) = log(a) + log(b) + log(c)

Czyli w naszym wypadku:

ln(a*b*c) = ln(eln(a*b*c)) = ln(eln(a) + ln(b) + ln(c))

Super. Teraz znów zauważamy, że porównujemy logarytmy, czyli równie dobrze wystarczy nam porównywanie liczb logarytmowanych:

a*b*c = eln(a) + ln(b) + ln(c)

a*b*c = exp(ln(a) + ln(b) + ln(c))

Czyli iloczyn liczb to inaczej exp(suma logarytmów liczb), cbdo.

SELECT exp(sum(log(kolumna)))
FROM tabela

Oprócz zaprezentowanego wzoru spotkałem się też z inną metodą mnożenia *. Polega na tym samym schemacie, jednak zamiast stosowania funkcji exp() i logarytmów naturalnych używa schematu potęgowania:

SELECT POWER( 2, SUM( LOG( 2, kolumna_która_mnozymy )))
FROM tabela

Co wychodzi oczywiście na to samo, bo exp(x) to ex, a ln to logarytm o podstawie e.

Trochę więcej można poczytać o tym na access.vis.pl

Możemy rozszerzyć powyższy model także na dzielenie liczb – dzielenie to to samo co mnożenie do ujemnej potęgi:

3 / 4 = 3 * 4-1

W takim przypadku w tabeli bazy danych oprócz kolumny z wartościami do mnożenia musimy mieć współczynnik potęgi, do której podnosimy liczbę. Dla mnożenia współczynnik = 1, dla dzielenia współczynnik = -1. Wówczas lekko modyfikujemy zapytanie i mamy:

SELECT exp(sum(log(power(kolumna, współczynnik))))
FROM tabela

gdzie power(a, b) to funkcja podnosząca a do potęgi b.

W bazie zapiszemy kolumny mniej więcej tak:

kolumna współczynnik
3 1
4 -1

* http://www.goldenline.pl/forum/bazy-danych/676728

Advertisements

Skomentuj

Wprowadź swoje dane lub kliknij jedną z tych ikon, aby się zalogować:

Logo WordPress.com

Komentujesz korzystając z konta WordPress.com. Log Out / Zmień )

Zdjęcie z Twittera

Komentujesz korzystając z konta Twitter. Log Out / Zmień )

Facebook photo

Komentujesz korzystając z konta Facebook. Log Out / Zmień )

Google+ photo

Komentujesz korzystając z konta Google+. Log Out / Zmień )

Connecting to %s