13
10/2010
0

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:

https://dwbi.blog.hu/api/trackback/id/tr352368809

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.

Nincsenek hozzászólások.