http://www.sharepointr.net/ORABLOG/default.aspx: MODEL MODEL

MODEL Yapısını elimden geldiğince açıklamaya çalışacağım. Hatalar olabilir eksikler olabiir şimdiden kusura bakmayın.

Model Clause tabloya yeni bir veri eklemez yada onlar üzerinde bir değişiklik yapmaz sadece select ile beraber çalıştığı için bize görüntü oluşturur.
örneğin bizim amrketimizde ürünler satiliyor olsun. 2007-2006-2005 yıllarında satışlar yaptık. Bu zamana yapmiş olduğumuz satışlari aynı tabloda 2008 olarak görmek isteyebiliriz ama 2008 olarak görduğumuz veri sadece o anlıktır database e 2008 için veri yazılmaz.
zaten MODEL sadece SELECT ile beraber çalışıyor.

MODEL [main]
[reference models]
[PARTITION BY ()]
DIMENSION BY ()
MEASURES ()
[IGNORE NAV] | [KEEP NAV]
[RULES
[UPSERT | UPDATE]
[AUTOMATIC ORDER | SEQUENTIAL ORDER]
[ITERATE (n) [UNTIL ] ]
( = ... )

DIMENSION BY da tekrarlamaları kabul etmez unique olmalıdır. aksi halde
ORA-32638: Non unique addressing in MODEL dimensions
hatasını alırsınız.
Aşağidakileri yapabilirsiniz.
DIMENSION BY ( KEY AS KEY_3 )
DIMENSION BY ( KEY * 10 AS KEY_3 )
DIMENSION BY ( ROWNUM AS KEY_3 )
DIMENSION BY ( GROUP_1, GROUP_2 ) group_2 nin uniquely olmasına gerek yoktur.

Fakat
select içerisinde şöle diyip
KEY AS KEY_3
ROWNUM AS KEY_3
KEY * 10 AS KEY_3
model içinde
dimension by ( KEY_3 )
böyle kullanmazsiniz.
MEASURES yapısı içinde bunu yapabilriz.
MEASURES
( num_val ,
NUM_VAL * 10 AS NUM_VAL_2 ,
SYSDATE AS DATE_VAL_2 ,
'A BRIEF NOTE' AS NOTE
)

ana yapısı bu şekilde,

Model fonksiyonları aşağıdakilerdir.
CV
ITERATION_NUMBER (döngü gibi bişey)
PRESENTNNV (NVL fonksiyonuna benziyor değeri olmayana değer atiyor.)
PRESENTV
PREVIOUS

bu hazir fonksiyonları kullanarak işinizi kolaylaştırabilirsiniz.
Kullanırken dikkat etmeniz gerekiyor. SELECT ile beraber model_clause içerinde kullanılmalıdır.
CV, PRESENTNNV,PRESENTV sağ eşitliğin sağ tarafında olmalıdır.

CV () foksıyonu bir tür grup işlemi yapıyor. Aşağıdaki kodda da görüleceği gibi
('Mouse Pad', 'Standard Mouse') da Mause Pad ve Standart Mause için ayrı ayrı işlem yapıyor.

SELECT country, prod, year, s
FROM sales_view_ref
MODEL
PARTITION BY (country)
DIMENSION BY (prod, year)
MEASURES (sale s)
IGNORE NAV
UNIQUE DIMENSION
RULES UPSERT SEQUENTIAL ORDER
(
s[FOR prod IN ('Mouse Pad', 'Standard Mouse'), 2001] =
s[CV( ), 1999] + s[CV( ), 2000]
)
ORDER BY country, prod, year;

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
COUNTRY PROD YEAR S
---------- ----------------------------------- -------- ---------
France Mouse Pad 1998 2509.42
France Mouse Pad 1999 3678.69
France Mouse Pad 2000 3000.72
France Mouse Pad 2001 6679.41
France Standard Mouse 1998 2390.83
France Standard Mouse 1999 2280.45
France Standard Mouse 2000 1274.31
France Standard Mouse 2001 3554.76

şimdi bunu örnekler ile açmaya çalışalim. Herkezin rahatlaca kullanabileceği tablolar olması açısından SH user tablalarinı tercih ettim.
Önce bir görünüm (view) oluşturalim

CREATE OR REPLACE VIEW sales_view_ref AS
SELECT country_name country,
prod_name prod,
calendar_year YEAR,
SUM(amount_sold) sale,
COUNT(amount_sold) cnt
FROM SALES,times,customers,COUNTRIES,products
WHERE SALES.time_id = times.time_id AND
SALES.prod_id = products.prod_id AND
SALES.cust_id = customers.cust_id AND
customers.country_id = COUNTRIES.country_id AND
( customers.country_id = 52779 OR
customers.country_id = 52776 ) AND
( prod_name = 'Standard Mouse' OR
prod_name = 'Mouse Pad' )
GROUP BY country_name,prod_name,calendar_year;
///////////////////////////////////////////////////////////////////
SELECT country, prod, YEAR, sale
FROM sales_view_ref
ORDER BY country, prod, YEAR;
çıktısı şu şekilde bu bizim örnek çiktimiz olacak.
COUNTRY PROD YEAR SALE
---------- ----------------------------------- -------- ---------
France Mouse Pad 1998 2509.42
France Mouse Pad 1999 3678.69
France Mouse Pad 2000 3000.72
France Mouse Pad 2001 3269.09
France Standard Mouse 1998 2390.83
France Standard Mouse 1999 2280.45
France Standard Mouse 2000 1274.31
France Standard Mouse 2001 2164.54
Germany Mouse Pad 1998 5827.87
Germany Mouse Pad 1999 8346.44
Germany Mouse Pad 2000 7375.46
Germany Mouse Pad 2001 9535.08
Germany Standard Mouse 1998 7116.11
Germany Standard Mouse 1999 6263.14
Germany Standard Mouse 2000 2637.31
Germany Standard Mouse 2001 6456.13
Bu tabloyu iyice kavramanızda fayda var

//////////////////////////////////////////////////////////////////////////////
---ülkelere göre toplaya toplaya gidiyor.
SELECT country, year, sale, csum
FROM
(SELECT country, year, SUM(sale) sale
FROM sales_view_ref
GROUP BY country, year
)
MODEL DIMENSION BY (country, year)
MEASURES (sale, 0 csum) -- başlangiç değeri 0
RULES (csum[any, any]=
SUM(sale) OVER (PARTITION BY country
ORDER BY year
ROWS UNBOUNDED PRECEDING)
)
ORDER BY country, year;

COUNTRY YEAR SALE CSUM
--------------- ---------- ---------- ----------
France 1998 4900.25 4900.25
France 1999 5959.14 10859.39
France 2000 4275.03 15134.42
France 2001 5433.63 20568.05
Germany 1998 12943.98 12943.98
Germany 1999 14609.58 27553.56
Germany 2000 10012.77 37566.33
Germany 2001 15991.21 53557.54
//////////////////////////////////////////////////////////////////////////////
SELECT country, prod, year, s
FROM sales_view_ref
MODEL
PARTITION BY (country)
DIMENSION BY (prod, year)
MEASURES (sale s) -- sale sutunu üzerinde işlem yap
IGNORE NAV
UNIQUE DIMENSION
RULES UPSERT SEQUENTIAL ORDER ITERATE(2) --ITERATE(2) burada 2 defa tekrarlanacağini belirtiyor.
(
s['Mouse Pad', 2001 + ITERATION_NUMBER] = -- ITERATION_NUMBER ilk değeri sıfırdır. ve birer birer artar.
s['Mouse Pad', 1998 + ITERATION_NUMBER]
)
ORDER BY country, prod, year;

COUNTRY PROD YEAR S
---------- ----------------------------------- -------- ---------
France Mouse Pad 1998 2509.42
France Mouse Pad 1999 3678.69
France Mouse Pad 2000 3000.72
France Mouse Pad 2001 2509.42
France Mouse Pad 2002 3678.69
France Standard Mouse 1998 2390.83
France Standard Mouse 1999 2280.45
France Standard Mouse 2000 1274.31
France Standard Mouse 2001 2164.54
Germany Mouse Pad 1998 5827.87
Germany Mouse Pad 1999 8346.44
Germany Mouse Pad 2000 7375.46
Germany Mouse Pad 2001 5827.87
Germany Mouse Pad 2002 8346.44
Germany Standard Mouse 1998 7116.11
Germany Standard Mouse 1999 6263.14
Germany Standard Mouse 2000 2637.31
Germany Standard Mouse 2001 6456.13

18 rows selected.
//////////////////////////////////////////////////////////////////////////////////

PRESENTNNV (cell_reference, expr1, expr2)
cell_reference etkilenecek alan
model_clause null değilse expr1 değeri dönüyor eğer null ise o zaman expr2 dönüyor.
SELECT country, prod, year, s
FROM sales_view_ref
MODEL
PARTITION BY (country)
DIMENSION BY (prod, year)
MEASURES (sale s)
IGNORE NAV
UNIQUE DIMENSION
RULES UPSERT SEQUENTIAL ORDER
( s['Mouse Pad', 2002] =
PRESENTNNV(s['Mouse Pad', 2002], s['Mouse Pad', 2002], 10)
)
ORDER BY country, prod, year;

COUNTRY PROD YEAR S
---------- ----------------------------------- -------- ---------
France Mouse Pad 1998 2509.42
France Mouse Pad 1999 3678.69
France Mouse Pad 2000 3000.72
France Mouse Pad 2001 3269.09
France Mouse Pad 2002 10
France Standard Mouse 1998 2390.83
France Standard Mouse 1999 2280.45
France Standard Mouse 2000 1274.31
France Standard Mouse 2001 2164.54
Germany Mouse Pad 1998 5827.87
Germany Mouse Pad 1999 8346.44
Germany Mouse Pad 2000 7375.46
Germany Mouse Pad 2001 9535.08
Germany Mouse Pad 2002 10
Germany Standard Mouse 1998 7116.11
Germany Standard Mouse 1999 6263.14
Germany Standard Mouse 2000 2637.31
Germany Standard Mouse 2001 6456.13

18 rows selected.

PRESENTV ( cell_reference , expr1 , expr2 )
cell_reference varsa expr1 degerini yolluyor yoksa expr2 değeri dönüyor.

SELECT country, prod, year, s
FROM sales_view_ref
MODEL
PARTITION BY (country)
DIMENSION BY (prod, year)
MEASURES (sale s)
IGNORE NAV
UNIQUE DIMENSION
RULES UPSERT SEQUENTIAL ORDER
(
s['Mouse Pad', 2001] =
PRESENTV(s['Mouse Pad', 2000], s['Mouse Pad', 2000], 0)
)
ORDER BY country, prod, year;

COUNTRY PROD YEAR S
---------- ----------------------------------- -------- ---------
France Mouse Pad 1998 2509.42
France Mouse Pad 1999 3678.69
France Mouse Pad 2000 3000.72
France Mouse Pad 2001 3000.72
France Standard Mouse 1998 2390.83
France Standard Mouse 1999 2280.45
France Standard Mouse 2000 1274.31
France Standard Mouse 2001 2164.54
Germany Mouse Pad 1998 5827.87
Germany Mouse Pad 1999 8346.44
Germany Mouse Pad 2000 7375.46
Germany Mouse Pad 2001 7375.46
Germany Standard Mouse 1998 7116.11
Germany Standard Mouse 1999 6263.14
Germany Standard Mouse 2000 2637.31
Germany Standard Mouse 2001 6456.13

16 rows selected.
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

RETURN UPDATED ROWS

SELECT SUBSTR(country,1,20) country,
      SUBSTR(prod,1,15) prod, year, sales
FROM sales_view
WHERE country IN ('Italy','Japan')
  MODEL RETURN UPDATED ROWS  /* burada bunun bulunmasının sebebi bise oluşturaği görüntüde sadece
 MODEL e maruz kalanlar olsun tablonun verilerini görmek istemiyorum. 
Eğer bunu yazmaz isek Sales_view tablosunda buna maruz kalan bütün satirlar gelir. 
RETURN UPDATED ROWS ile sadece etkilenen yeni oluşturulanları göreceğiz.
 */
/*
 /*SQL> SELECT SUBSTR(country,1,20) country,
  2         SUBSTR(prod,1,15) prod, YEAR, SALES
  3    FROM   sales_view
  4    WHERE   country='Italy'*/
eğer RETURN UPDATED ROWS yazmaz isek yukaridaki sorgunun çıktısını verir.*/
     PARTITION BY (country)
    DIMENSION BY (prod, year)
    MEASURES (sale sales)
    RULES (
      sales['Bounce', 2002] = sales['Bounce', 2001] + sales['Bounce', 2000],
      sales['Y Box', 2002] = sales['Y Box', 2001],
      sales['2_Products', 2002] = sales['Bounce', 2002] + sales['Y Box', 2002])
ORDER BY country, prod, year;

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

INCREMENT model ile kullanımı
Aşağıdaki örnekte ikişer ikişer artarak ekrana yazdırıyor.

select integer_value
from   dual
where  1=2
model
 dimension by ( 0 as key )
 measures     ( 0 as integer_value )
 rules upsert ( integer_value[ for key from 2 to 10 INCREMENT 2 ] = cv(key) )
;

INTEGER_VALUE
-------------
           2
           4
           6
           8
          10

kaynaklar
http://www.java2s.com/Tutorial/Oracle/0320__Analytical-Functions/TheMODELstatementdoescalculationsonacolumninarowbasedonotherrowsinaresultset.htm
http://www.sqlsnippets.com/en/topic-11663.html
http://www.orafaq.com/node/69
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/toc.htm
http://www.oracle.com/technology/obe/obe10gdb/bidw/sqlmodel/sqlmodel.htm#sd

http://www.sharepointr.net/ORABLOG/default.aspx

Tuesday, June 2, 2009

MODEL MODEL_Clause

No comments:

My Blog List

KATEGORİLER

Blog Archive