Üzleti Intelligencia Hírek, újdonságok és tesztek az üzleti intelligencia és az adattárházak világából

Mértani közép Oracle alá (GEOMEAN)

Bárki, aki három napnál többet dolgozott a "hagyományos" relációs adatbázis-kezelőkkel, hamar rájöhetett, hogy mindig azok a függvények hiányoznak a legjobban, amik nincsenek is implementálva egyáltalán. Ezúttal a mértani közép (geometric mean, GEOMEAN) függvénybe futottam bele, ami sajnos nincsen az Oracle RDBMS-ben alapban.

Ez azért is baj, mivel a mértani közép gyakran használt függvény az adattárházakban és a fölé épülő elemző rendszerekben. Mondjuk, hogy van öt számunk (5, 7, 55, 6, és 3), akkor azok mértani átlaga kb. 8,08 - vagyis az értékkészletből kilógó 55 nem vitte nagyon félre az átlagszámot. Ugyanezekre a számokra a számtani közép 15,2 lenne. De ha az ilyen hasznos, miért nincs benne a legtöbb DBMS-ben?

Az indok, amiért a legtöbb adatbázis-kezelő nem implementálja a mértani közepet, hogy az alap képlet szerint (n alapú gyök alatt az n darab vizsgált szám szorzata) elég nehéz kiszámolni. Ugyanis már egy kisebb táblában is az értékek egymással való összeszorzásakor belecsúszhatunk a számtartomány (vagy az oszlopszélesség) felső korlátjába, így ezzel a képlettel csak kevés értékkel működne megfelelően. Szerencsére van egy másik, kevésbé ismert módszer is. Ennek alapja a következő felismerés, mi szerint:

(mivel )

továbbá:

(mivel )

Ennek alapján adja magát az ötlet, hogy az egyes értékek b alapú logaritmusát összegezzük, majd osszuk el az értékek darabszámával. Így megkapjuk a mértani közép b alapú logaritmusát, amit egy antilog függvénnyel az eredménnyé alakíthatunk. Ennek alapján a végső mértani közép képletünk e alapú logaritmust használva:

Óriási előny, hogy az egyes lépésekben nem végzünk szorzást, csak a logaritmusok összeadását, vagyis nem fogunk kicsúszni az adatbázis-kezelő számtani kapacitásából.

Ennek alapján ha Oracle-ben akarjuk megvalósítani aggregációs függvényként a fentiek, a következő osztályt kell implementálni:

 CREATE OR REPLACE TYPE BODY geomean_impl IS   STATIC FUNCTION ODCIAggregateInitialize( sctx IN OUT geomean_impl )
     RETURN NUMBER IS
   BEGIN
     sctx   := geomean_impl( 0, 0 );
     RETURN odciconst.success;
   END;
 
   MEMBER FUNCTION ODCIAggregateIterate( self IN OUT geomean_impl, value IN NUMBER )
     RETURN NUMBER IS
   BEGIN
     v_ln_sum      := v_ln_sum + ln( value );
     v_num_items   := v_num_items + 1;
 
     RETURN odciconst.success;
   END;
 
   MEMBER FUNCTION ODCIAggregateTerminate( self IN geomean_impl, returnvalue OUT NUMBER, flags IN NUMBER )
     RETURN NUMBER IS
   BEGIN
     returnvalue   := exp( v_ln_sum / v_num_items );
 
     RETURN odciconst.success;
   END;
 
   MEMBER FUNCTION ODCIAggregateMerge( self IN OUT geomean_impl, ctx2 IN geomean_impl )
     RETURN NUMBER IS
   BEGIN
     self.v_ln_sum      := self.v_ln_sum + ctx2.v_ln_sum;
     self.v_num_items   := self.v_num_items + ctx2.v_num_items;
 
     RETURN odciconst.success;
   END;
 END;
 / 

A függvény teljes forrása itt található.

A bejegyzés trackback címe:

Kommentek:

A hozzászólások a vonatkozó jogszabályok  értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai  üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a  Felhasználási feltételekben és az adatvédelmi tájékoztatóban.