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

Amit eddig senkitől se mertél megkérdezni: MODEL utasítás és lineáris regresszió Oracle-ben

Kínzó fájdalmat érez, ha leírva látja a REGR_SLOPE és REGR_INTERCEPT függvényeket? Furcsa remegés járja át a testét, ha egy SQL SELECT-ben MODEL REFERENCE-t és DIMENSION BY kifejezéseket talál?  Akkor ideje bepótolni a lemaradást, és elolvasni az alábbi bejegyzést, hiszen az Oracle MODEL klauzája nélkül ugyan lehet dolgozni, csak éppen minek. 

No de mi is az MODEL kifejezés és miért ennyire marhára fontos? A legjobb megfogalmazás, hogy egy újabb programozási lehetőség bevezetése az SQL nyelvbe: gyakorlatilag komplex kódblokkok (ciklusok, változók és vezérlési szerkezetek) támogatása, amelyek segítségével az SQL lekérdezések eredményei módosíthatóak. Ezek a módosítások lehetnek új sorok vagy oszlopok hozzáadása, illetve meglévő oszlopok értékeinek megváltoztatása. Az Oracle szerint a MODEL-ek használata az adattárházas környezetben lesznek igazán hasznosak, és be kell lássam, ezzel csak egyetérteni tudok.

Tegyük fel, hogy szeretnénk egy olyan lekérdezést írni, amely a tényadatok mellett nostradamusi precizítással megmondja a jövőt is. Tehát az üzleti igény: egy tény táblából lekérdezve lezárt negyedévekre a tényadatokat, míg az azt követő egy évre pedig az előrejelzéseket szeretnénk kinyerni. Ezt a MODEL kulcsszó és/vagy PLSQL kód, átmeneti és vagy külső táblák segítsége nélkül igen bajos volt leprogramoznunk, de szerencsére már nem az. 

Mielőtt belevágunk a fentiekbe, azért nézzünk egy gyors példát. Hogy iratnánk ki SQL-ben 1-5 -ig a számokat? Mindenkinek megvan a saját technikája, azonban nézzük hogy megy ez a MODEL-lel:

SQL> SELECT X FROM DUAL WHERE 1=2 
 MODEL
 DIMENSION BY (0 X) MEASURES (0 Y)
 (Y[FOR X FROM 1 TO 5 INCREMENT 1]=0);
          X
 ----------
          1
          3
          3
          4
          5
  

Nagyjából érthető a dolog: egy üres lekérdeshez (DUAL) hozzáadtunk egy dimenzió oszlopot egytől ötig terjedő értékekke, amihez definiáltunk mérési értékeket - jelen esetben nullát. Ez utóbbit is kiírhatnánk, elég lenne odabiggyeszteni egy Y -t a mezők közé. Vegyük észre, hogy mindkét oszlop a semmiből került oda, pontosabban a MODEL szakasz definiálta. Kellemes érzés: olyan SQL-eket írhatunk végre, amelyeknek kvázi semmi köze a forrástáblákhoz.

Amint valaki tud FOR ciklusokat rakni egy SQL-be, számos újabb design pattern-t tud használni, azonban ezekről most nem lesz szó. Egyszerűen fogadjuk el, hogy hasznos: nem csak hogy cursor-okat nem kell használnunk (hiszen ha valaki adattárházas környezetben kurzort használ, az per definíció marhára nem ért hozzá), de PL/SQL -t is jóval kevesebbet.

Viszont térjünk a tárgyra. 

Induljunk ki az OE sémában található SALES táblából, az úgyis mindenkinek megvan. Az alap tényadatunk legyen a negyedéves eladás két kategóriára:

  SELECT trunc( time_id, 'Q' ) qtr_id, prod_category, sum( quantity_sold ) qsold
     FROM sh.sales, sh.products
    WHERE products.prod_id = sales.prod_id AND prod_category IN ('Photo', 'Hardware')
 GROUP BY trunc( time_id, 'Q' ), prod_category 

Mivel kell valami előrejelzést adnunk, egészítsük ki az Oracle remek regressziós függvényeivel. A REGR_SLOPE megadja a lineáris függvény meredekségét, míg a REGR_INTERCEPT megadja az Y tengely metszéspontját:

 SELECT sls.*
             , regr_slope( qsold, sysdate - qtr_id ) 
                 OVER (PARTITION BY prod_category) slope
             , regr_intercept( qsold, sysdate - qtr_id ) 
                 OVER (PARTITION BY prod_category) intercept
          FROM (   SELECT  trunc( time_id, 'Q' ) qtr_id
                         , prod_category
                         , sum( quantity_sold ) qsold
                     FROM sh.sales, sh.products
                    WHERE products.prod_id = sales.prod_id 
                      AND prod_category IN ('Photo', 'Hardware')
                 GROUP BY trunc( time_id, 'Q' ), prod_category 
                ) sls 

Már csak egy olyan lekérdezésre van szükségünk, amely a múltban a tényadatot veszi, a jövőben pedig a regresszió alapján előrejelzést készít. Így fog ez kinézni, ne ijedjünk meg tőle, mindjárt nézzük sorrol sorra:

SELECT qtr_id, prod_category, qsold
   FROM ( SELECT sls.*
               , regr_slope( qsold, sysdate - qtr_id ) 
                   OVER (PARTITION BY prod_category) slope
               , regr_intercept( qsold, sysdate - qtr_id ) 
                   OVER (PARTITION BY prod_category) intercept
            FROM (   SELECT  trunc( time_id, 'Q' ) qtr_id
                           , prod_category
                           , sum( quantity_sold ) qsold
                       FROM sh.sales, sh.products
                      WHERE products.prod_id = sales.prod_id 
                        AND prod_category IN ('Photo', 'Hardware')
                   GROUP BY trunc( time_id, 'Q' ), prod_category 
                  ) sls 
        ) sls
 MODEL
   REFERENCE r ON
   (
   SELECT 1 d
       , max( trunc( time_id, 'Q' ) ) date_from
       , max( trunc( time_id, 'Q' ) ) + 365 date_to 
     FROM sh.sales
   )
   DIMENSION BY( d )
   MEASURES( date_from, date_to )
   PARTITION BY( prod_category )
   DIMENSION BY( qtr_id )
   MEASURES( qsold, slope, intercept )
   UNIQUE DIMENSION
   RULES UPSERT SEQUENTIAL ORDER
     ( 
     qsold [FOR qtr_id FROM date_from[1] TO date_to[1] 
                                 INCREMENT INTERVAL '3' MONTH] =
         slope[date_from[1]] * ( sysdate - cv( qtr_id ) ) 
                                 + intercept[date_from[1]] 
     )
 ORDER BY 1, 2

Nem olyan vészes azért a helyzet. A SELECT elején megadjuk a fenti lekérdezésünket, amiben megtalálható a historikus tényadat és a lineáris regresszió leíró függvénye is. Készítettünk egy referenciát (REFERENC r), amely megadja az utolsó futó hónap dátumát, illetve az előrejelzés intervallumát. Sajnos SYSDATE-t nem lehet használni a kalkulációban, ezért ha nem a historikus tényadatok, hanem az aktuális nap alapján akarunk időintervallumot definiálni, azt is a REFERENCE klauzában kell megtenni. 

A következő feladat a két dimenzió és tényadat párt kell definiálni. Az első a dátumra vonatkozik, itt egy üres, egy elemű dimenziót veszünk fel, amelynek segítségével a két mérés (a választott időintervallum két értéke) könnyen megcímezhető. A második dimenzió-tényadat pár maga a historikus és előrejelzés adata. Dimenziónak (DIMENSION BY) a dátumot vesszük fel (lehet több dimenziót is megadni), míg mérőszámnak az értékesített mennyiséget és a regressziós változókat állítjuk be (MEASURES). Lehetőség van a dimenziókat és méréseket particiónálni, ami aféle group by szabályként működik. Például ha termékcsoportonként akarunk előrejelezni, érdemes PARTITION BY -ba felvenni azt. Ez elsősorban kényelmi funkció, nem kell plussz dimenziókat definiálnunk a szabálybázisban ezekre a dimenziókra, mégsem vonja össze az értékeit.

Most jön az izgalmas rész. A fenti időablak alapján definiáltunk egy szabályt (RULES), amely a quantity_sold értékeinek beilleszt az általunk megadott dimenziók mentén új értékeket. A FOR -ral beállítjuk, hogy a nyitott hónaptól kezdve egy évig negyedévent készüljön új dimenzió. A dimenzióhoz tartozó érték pedig legyen a lineáris regresszió függvényére illesztett, dimenzióhoz tartozó érték. Az iterációban / értékadásban az aktuális sor és/vagy dimenzió értékét a CV() függvénnyel lehet elérni, így a CV(qtr_id) mindig az aktuálisan előrejelzendő negyedévet adja meg.

A nagy mű így néz ki:

Összefoglalva a MODEL menő dolog, és elég ciki, ha valaki nem tudja használni Oracle SQL felhasználó létére.

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.