1 / 44

10g SQL MODEL clause

10g SQL MODEL clause. Чекин Калоянов, ТехноЛогика ЕООД tkaloyanov@technologica.com BGOUG, Пампорово – 14-16 април 2006. Съдържание. Какво е SQL MODEL клаузата Общи положения Структура на SQL заявка с MODEL и синтаксис на MODEL клаузата, задължителни и опционни клаузи

manasa
Télécharger la présentation

10g SQL MODEL clause

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 10g SQL MODEL clause Чекин Калоянов, ТехноЛогика ЕООД tkaloyanov@technologica.com BGOUG, Пампорово – 14-16 април 2006

  2. Съдържание • Какво е SQL MODEL клаузата • Общи положения • Структура на SQL заявка с MODEL и синтаксис на MODEL клаузата, задължителни и опционни клаузи • Примери – базирани на схема SH

  3. Какво eSQL MODEL Електронна таблица в базата данни: • Резултатът от SQL заявка се преобразува в многомерен масив (един или няколко) • Към клетките на масивасе прилагат формулиза изчисляване на нови стойности с възможности за: • Промяна на съществуващите клетки • Създаване на нови клетки (колонки) и редове • Междуредови изчисления (inter-row calculations),с SQL функции, включително агрегации като COUNT, MAX, MIN, SUM, AVG • Връща всички редове или само тези които са променени и новосъздадени Model е релация, може да се включи във views,insert,update,…

  4. Какво е SQL MODEL MODEL PARTITION BY() DIMENSION BY() MEASURES() select from where RULES ()

  5. SQL MODEL понятияmulti-dimensional arrays • PARTITION • Определя отделни многомерни подмасиви • Формулите се прилагат отделно към всеки подмасив • DIMENSION • Идентифицира уникално всеки ред • MEASURES • Клетка от многомерния масив - аналог на measuresот factтаблица в една starсхема • Идентифицира се уникално чрез всички дименсии • Само нейната стойностможе да бъде променяна или създавана PARTITION DIMENSION MEASURE DIMENSION

  6. SQL MODEL понятияcell references • Positional cell reference– използува се при промяна и създаване на нови клетки sales['China', 'dvd ', 2000] • Symbolic cell references– използува се само при промяна на съществуващи клетки - включва булеви условия към дименсия(и) <,>, =, in, between... sales[prod= 'dvd ', year>1999] Възможно е рефериране с вмъкнати клетки (nested cells) sales[CV(), best_year['Finding Fido',CV(year)]]

  7. SQL MODEL понятия rules • RULES - формули/правила, прилагани върху клетките на многомерния масив. Всяко правило е присвояване - лявата част указва клетка или набор от клетки, а дясната може да съдържа константи, променливи, отделни клетки или агрегации върху набор от клетки attnd[ 2006,'BGOUG' ] = attnd[ 2005,'BGOUG' ]*1.10, attnd[ FOR year IN (2006,2007,2008), 'BGOUG' ] = attnd[ CV(year) - 1, 'BGOUG' ] *1.10 • Правилата могат да бъдат зависими едно от друго • Могат да използуват wild cards и FOR цикли • Може да се укажат брой итерацииза изпълнение

  8. Структура на SQL заявката withinline view[,inline view,...] select column [,column,...] | SQL Function(column) |user defined function| scalar subquery | user defined aggregate | cursor expression | case expression | analytical functionfrom table [AS OF or VERSIONS] [SAMPLE clause] | view | inline view | external table | table function | cast nested table where <condition> | <join condition>connect bygroup by [ ROLLUP | CUBE | GROUPING SETS] having MODEL ...order [siblings] by [nulls first|nulls last]

  9. SQL MODEL пример 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-- връща само променени/създадени PARTITION BY (country) -- за всяка страна DIMENSION BY (prod, year) -- колонки указващи клетките MEASURES (sale as 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

  10. MODEL [IGNORE NAV | [KEEP NAV] [UNIQUE DIMENSION | UNIQUE SINGLE REFERENCE] [RETURN {ALL|UPDATED} ROWS] [REFERENCE <ref-name> ON (<query>) DIMENSION BY (<cols>) MEASURES (<cols>) [IGNORE NAV | KEEP NAV] [UNIQUE DIMENSION | UNIQUE SINGLE REFERENCE] ] [MAIN <main-name>] [PARTITION BY (<cols>)] DIMENSION BY (<cols>) MEASURES (<cols>) [IGNORE NAV | KEEP NAV] [UNIQUE DIMENSION | UNIQUE SINGLE REFERENCE] [RULES] [UPSERT | UPDATE] [AUTOMATIC ORDER | SEQUENTIAL ORDER] [ITERATE (<number>) [UNTIL <condition>]] (<rule>, <rule>,.., <rule>) MODEL клауза - синтаксис

  11. SQL MODEL понятияreturn all / updated rows • RETURN ALL ROWS – връщат се всички редове, променени и непроменени от правилата (default) • RETURN UPDATED ROWS – връщат се само променени и/или създадени редове

  12. SQL MODEL понятияupdate & upsert • Два метода за присвояване на стойности на клетките в MODEL клаузата: • UPDATE - присвояването се извършва само за клетки, присъстващи в многомерния масив. Ако няма такава клетка, не се прави нищо. • UPSERT - присвояването се както върху съществуващи клетки, така и с вмъкване на нови редове, ако не съществуват такива. Важи само за позиционно рефериране отляво и една клетка е реферирана. (default)

  13. SQL MODEL понятияupdate & upsert - пример SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod, year, salesFROM sales_viewWHERE country='Italy'MODELRETURNUPDATEDROWSPARTITIONBY (country) DIMENSIONBY (prod, year)MEASURES (sale as sales)RULES ( sales['Y Box',FORyearFROM 2000 TO 2006 INCREMENT 1] = 100 + max(sales)[prod = 'Y Box', year BETWEEN 1998 AND 2001] )ORDERBY country, prod, year

  14. SQL MODEL понятияupdate & upsert - пример SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod, year, salesFROM sales_viewWHERE country='Italy'MODELRETURNUPDATEDROWSPARTITIONBY (country) DIMENSIONBY (prod, year)MEASURES (sale as sales)RULESUPDATE( sales['Y Box',FORyearFROM 2000 TO 2006 INCREMENT 1] = 100 + max(sales)[prod = 'Y Box', year BETWEEN 1998 AND 2001] )ORDERBY country, prod, year

  15. SQL MODEL понятияupdate & upsert - пример SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod, year, salesFROM sales_viewWHERE country='Italy'MODELRETURNUPDATEDROWSPARTITIONBY (country) DIMENSIONBY (prod, year)MEASURES (sale as sales)RULESUPDATE(UPSERTsales['Y Box',FORyearFROM 2000 TO 2006 INCREMENT 1] = 100 + max(sales)[prod = 'Y Box', year BETWEEN 1998 AND 2001] )ORDERBY country, prod, year

  16. SQL MODEL понятия sequential& automaticorder, ordered rules • Поредност на изпълнение на всички правила • RULES SEQUENTIAL ORDER – правилата се изпълняват в реда в който са зададени (default) • RULES AUTOMATIC ORDER - правилата се изпълняват в реда на тяхната зависимост • Ordered rules = правила с ORDER BY в лявата част sales[ANY] ORDER BY year DESC= sales[cv(year)-1] • Необходимо при ANY и при символно рефериране, когато резултатът може да зависи от поредността на достъп до клетките (ORA-32637: self cyclic rule in sequential order MODEL). Изразите в такъв ORDER BY могат да включват константи, measures, dimensionстойности, както и опциите [ASC | DESC] [NULLS FIRST | NULLS LAST]

  17. SQL MODEL понятия ordered rules- пример SELECT year, salesFROM sales_viewWHERE country='Italy' AND prod='Bounce' MODELDIMENSIONBY (year )MEASURES (sale sales) RULESSEQUENTIALORDER ( sales[ANY] = sales[CV(year)-1] )ORDERBY year)

  18. SQL MODEL понятия ordered rules- пример SELECT year, salesFROM sales_viewWHERE country='Italy' AND prod='Bounce' MODELDIMENSIONBY (year )MEASURES (sale sales) RULESAUTOMATICORDER ( sales[ANY] = sales[CV(year)-1] )ORDERBY year

  19. SQL MODEL понятия ordered rules- пример SELECT year, salesFROM sales_viewWHERE country='Italy' AND prod='Bounce' MODELDIMENSIONBY (year )MEASURES (sale sales) RULESSEQUENTIALORDER ( sales[ANY] ORDERBYyearDESC = sales[CV(year)-1] )ORDERBY year)

  20. SQL MODEL понятия FOR loop • FOR цикли върху стойности на дименсия • FOR dimension-value FROM low-value TO high-value INCREMENT|DECREMENT incr-value • [ FOR ] dimension IN (xxx, yyy, zzz) или за всички дименсии FOR (d1,..., dn) IN ((d1_val1,..., dn_val1),..., (d1_valm,..., dn_valm)) • FOR dimension IN (subquery) или FOR(dim1,dim2,dim3)IN (select dim1,dim2,dim3 from some_table) • FOR конструкцията може да се разглежда като макрос, който от едно правило генерира множество правила с позиционно рефериране, т.е. дава възможност за вмъкване на нови клетки(UPSERT). SQL MODEL има ограничение от 10,000 правила и при FOR циклите трябва да се има предвид

  21. SQL MODEL понятия FOR loop- пример SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod, year, salesFROM sales_viewWHERE country='Italy'MODELRETURNUPDATEDROWSPARTITIONBY (country) DIMENSIONBY (prod, year)MEASURES (sale sales) RULES ( sales['Mouse Pad', FOR year FROM 2005 TO 2010 INCREMENT 1] = 1.2 * sales[cv(prod), 2001] )ORDERBY country, prod, year

  22. SQL MODEL понятия FOR loop- пример SELECT SUBSTR(country,1,20) countr, SUBSTR(prod,1,15) prod, year, salesFROM sales_viewWHERE country='Italy'MODELRETURNUPDATEDROWSPARTITIONBY (country) DIMENSIONBY (prod, year)MEASURES (sale sales)RULES (-- създава или променя клетки sales['Home Theatre', FOR year IN (2006,2007,2008)] = 100 + max(sales)[prod like 'Home Theatre%' , year BETWEEN 1998 AND 2002] )ORDERBY country, prod, year

  23. SQL MODEL понятия FOR loop- пример SELECT SUBSTR(country,1,20) countr, SUBSTR(prod,1,15) prod, year, salesFROM sales_viewWHERE country='Italy'MODELRETURNUPDATEDROWSPARTITIONBY (country) DIMENSIONBY (prod, year)MEASURES (sale sales)RULES (-- САМО ПРОМЯНА на съществуващи клетки sales['Home Theatre', /*FOR*/ year IN (2006,2007,2008)] = 100 + max(sales)[prod like 'Home Theatre%' , year BETWEEN 1998 AND 2002] )ORDERBY country, prod, year

  24. SQL MODEL понятия FOR loop- пример SELECT country, prod, year, sFROM sales_viewMODELRETURNUPDATEDROWSDIMENSIONBY (country, prod, year)MEASURES (sale as s)RULESUPSERT ( s[FOR (country, prod, year)IN (SELECTDISTINCT 'new_country', prod, yearFROM sales_view WHERE country = 'China')] = s['China',CV(),CV()] )ORDERBY country, year, prod /* IN заявката не може да бъде корелирана с външни заявки и трябва да връща < 10000 реда */

  25. SQL MODEL понятия CV() фунция и ANY • CV() = текуща стойност дименсия • може да се използува само в дясната страна на правилата • CV() функцията има като аргумент dimension key. Може да се използува без аргумент, при което се приема позиционно рефериране на клетката. • ANY = всички стойности на дименсията, включително и NULLs. При символично рефериране се използува IS ANY. Не позволява включването на нови клетки, независимо от типа на рефериране.

  26. SQL MODEL понятия CV() фунция - пример SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod, year, sales, trunc(growth,2) growthFROM sales_viewWHERE country='Italy'MODELRETURNUPDATEDROWSPARTITIONBY (country) DIMENSIONBY (prod, year)MEASURES (sale assales, 0 growth)RULES ( growth[prod in ('Bounce','Y Box','Mouse Pad') ,year between 1998 and 2000]= 100* (sales[cv(prod), cv(year)] - sales[cv(prod), cv(year)-1] )/sales[cv(prod), cv(year) -1] )ORDERBY country, prod, year

  27. SQL MODEL понятия ANY - пример SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod, year, sales, trunc(growth,2) growthFROM sales_viewWHERE country='Italy'MODELRETURNUPDATEDROWSPARTITIONBY (country) DIMENSIONBY (prod, year)MEASURES (sale sales, 0 growth)RULES ( growth[prod in ('Bounce','Y Box','Mouse Pad'), ANY] = 100* (sales[cv(prod), cv(year)] - sales[cv(prod),cv(year) -1] ) /sales[cv(prod),cv(year) -1] )ORDERBY country, prod, year

  28. SQL MODEL понятияnulls интерпретация • IGNORE NAV – (Non-Available Values) указва липсващите стойностида се интерпретират като: • 0 за цифрови данни • Празен стринг за символни данни • ’01-JAN-2001’ за дати • NULL за други типове данни • KEEP NAV – интерпретира NULL нормално (default)

  29. SQL MODEL понятияnulls интерпретация - пример SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod, year, salesFROM sales_viewWHERE country='Italy'MODELKEEPNAVRETURNUPDATEDROWSPARTITIONBY (country) DIMENSIONBY (prod, year)MEASURES (sale sales)RULES ( sales['Mouse Pad', 2005] = sales['Mouse Pad', 1999] + sales['Mouse Pad', 2004] )ORDERBY country, prod, year

  30. SQL MODEL понятияnulls интерпретация - пример SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod, year, salesFROM sales_viewWHERE country='Italy'MODELIGNORE NAVRETURNUPDATEDROWSPARTITIONBY (country) DIMENSIONBY (prod, year)MEASURES (sale sales)RULES ( sales['Mouse Pad', 2005] = sales['Mouse Pad', 1999] + sales['Mouse Pad', 2004] )ORDERBY country, prod, year

  31. SQL MODEL понятияmain & reference models • MAIN MODEL – върху който се прилагат правилата • REFERENCE MODEL – един или повече read-only многомерни масиви, които могат да се използват като look-up таблици.Техните колонки се използват само в дясната страна на правилата. REFERENCE model_name ON (query) DIMENSION BY (cols) MEASURES (cols) [reference options]

  32. SQL MODEL понятияmain & reference models- пример SELECT SUBSTR(country,1,20) country, year, localsales, dollarsalesFROM sales_viewWHERE country IN ( 'Canada', 'Brazil')GROUPBY country, yearMODELRETURNUPDATEDROWSREFERENCE conv_refmodel ON (SELECT country, exchange_rate AS er FROM dollar_conv)DIMENSIONBY (country) MEASURES (er) IGNORENAVMAIN main_model DIMENSIONBY (country, year)MEASURES (SUM(sale) sales, 0 localsales, 0 dollarsales) IGNORENAVRULES (/* нарастване на продажбите в Канада с 22% */localsales['Canada', 2005] = sales[cv(country), 2001] * 1.22, dollarsales['Canada', 2005] = sales[cv(country), 2001] * 1.22 *conv_refmodel.er['Canada'],/* нарастване на продажбите вБразилия с 34% */ localsales['Brazil', 2005] = sales[cv(country), 2001] * 1.34, dollarsales['Brazil', 2005] = sales['Brazil', 2001] * 1.34 *er['Brazil'])

  33. SQL MODEL понятияiterate • RULES […] ITERATE(n) … - указва брой итерация за изпълнение на правилата ITERATE (number_of_iterations) [ UNTIL (condition) ] • number_of_iterations – положителна integer константа • condition – проверява се в края на поредна итерация • PREVIOUS(cell) - връща стойността на клетката след края на предходната итерация • ITERATION_NUMBER – връща текущия номер на итерацията (започва с 0)

  34. SQL MODEL понятияiterate- пример SELECT cell numFROM DUALMODELDIMENSIONBY (0 attr)MEASURES (0 cell)RULESITERATE (7) ( cell[iteration_number] = iteration_number)

  35. SQL MODEL понятияiterate- пример SELECT cell num, chr(97+cell) letter, to_char(cell + to_date('14-04-2006','DD-MM-YYYY'),'DAY') day, to_char( (cell+1 + sysdate), 'DD-MM-YYYY') the_dateFROM DUALMODELDIMENSIONBY (0 attr)MEASURES (0 cell)RULESITERATE (7)( cell[iteration_number] = iteration_number)

  36. SQL MODEL понятияiterate- пример SELECT trunc(b,2) b , accountFROMledger MODELIGNORENAVDIMENSIONBY (account) MEASURES (balance b) RULESITERATE (100) UNTIL ( ABS( (PREVIOUS(b['Net']) - b['Net']) ) < 0.01 )( b['Net'] = b['Salary'] - b['Interest'] - b['Tax'], b['Tax'] = (b['Salary'] - b['Interest']) * 0.38 + b['Capital_gains'] *0.28, b['Interest'] = b['Net'] * 0.30, b['Iteration Count']= ITERATION_NUMBER + 1)

  37. SQL MODEL понятияpivoting- пример SELECT deptno,ename,row_number() OVER ( PARTITIONBY deptnoORDERBY sal descNULLSLAST) rnkFROM empORDERBY deptno, rnk

  38. SQL MODEL понятияpivoting- пример SELECT deptno, nr1 , nr2, nr3FROM empMODELRETURNUPDATEDROWSPARTITIONBY ( deptno)DIMENSIONBY ( row_number() OVER ( PARTITIONBY deptnoORDERBY sal descNULLSLAST) rnk )MEASURES (ename,lpad(' ',10) nr1, lpad(' ',10) nr2, lpad(' ',10) nr3)RULESUPSERT( nr1 [0] = ename [1] , nr2 [0] = ename [2] , nr3 [0] = ename [3])

  39. SQL MODEL понятияunique references • UNIQUE DIMENSION – указва, че PARTITION BYиDIMENSION BYколонките вMODELклаузата трябва да идентифицират уникално всяка клетка в модела.Тази уникалност се проверява в процеса на изпълнение, когато е необходимо, и води до известен overhead. (default) • UNIQUE SINGLE REFERENCE – указва, че PARTITION BYиDIMENSION BYклаузите идентифицират уникално клетките в дясната страна на правилата. Това може да намали времето за обработка, като изключи проверките по време на изпълнение.

  40. SQL функции, свързани с MODEL • CV() - текуща стойност на дименсията PREVIOUS – връща стойността на клетката в началото на итерацията • ITERATION_NUMBER – връща текущия номер на итерацията в правилата • PRESENTV (cell, expr1, expr2) - връща expr1, ако клетката съществува, в противен случай връща expr2 • PRESENTNNV (cell, expr1, expr2) - връща expr1, ако клетката съществуваи не е NULL , в противен случай връща expr2

  41. MODEL 10g Calculate Columns using inter-row calculations (direct cell-reference, max, min, sum, count, etc.) Update cells Insert rows Suppress untouched rows Use reference models Analytical Functions 8.1.6 EE, 9iR2 SE Calculate Columns usinginter-row calculations (lag, lead, first, last, rank, sum, max, min, count, avg,…) No update of values No creation of rows No suppression of rows - More efficient (performance) Easier syntax MODEL vs Analytical Functions

  42. SQL MODEL – добра или не • MODEL клаузата ще ви хареса защото: • Тя е като нов език, нещата се разглеждат по нов начин • Дава достъп до редовете както в масив; • Може да манипулирате данните, да генерирате редове и да добавяте колонки • Предоставя рекурсия в SQL • Позволява извършване сложни inter-row изчисления • MODEL клаузата ще ви досажда с ограниченията: • Не можете да сложите bind променлива в ITERATE (може да се заобиколи с добавяне на UNTIL(iteration_number > = :x) ) • Сегашният лимит за брой правила е 10,000 • При FOR loops не може да използвате корелирани subqueries

  43. SQL MODELДокументация и публикации • Първи публикации - 2003 (10gR1) • Документация • 10g Data Warehousing Guide - 22. SQL For Modeling , SQL Reference • www.oracle.com/technology/obe/obe10gdb/bidw • Публикации • www.oracle.com/technology/oramag • technology.amis.nl • www.rittman.net • www.dbasupport.com/oracle/ora10g

  44. next

More Related