SQL: Клаузата MODEL - мощният инструмент на Oracle за извършване на изчисления

Автор: Десислава Христова

Клаузата MODEL (от Oracle 10g) е мощно разширение към стандартната SELECT заявка, даваща възможност да се решат проблеми като:

  • изчисления, в които участват стойности от съседни редове на резултата
  • получаване на междинни резултати по групи
  • итерации за генериране на нови стойности

и редица други, за които традиционно се използват многократно свързване на таблици със себе си (self join) или PL/SQL код.

Причините, и любопитен факт около решението да се добави MODEL към SELECT, са преодоляването на ограниченията на MS Excel за броя на редовете както и изпълнението на комплексни изчисления директно върху сървъра.

Клаузата MODEL позволява да дефинираме масив от данни с резултатите от заявката и последващо извършване на изчисления по определени от нас параметри и с формули подобни на тези в MS Excel или с агрегиращи и аналитични функции директно зададени в клаузата.

Клаузата MODEL се състои от три основни части:

  • PARTITION BY

Дефинира логически групи или блокове от данни подобно на PARTITION BY при аналитичните функции. Формулите в RULES се прилагат към всеки блок независимо от останалите блокове.

  • DIMENSION BY

Дефинира характеристиките на масива с данни (дата, дъвжава и др.) и се използват за еднозначно идентифициране на всеки ред. Можете да гледате на тях като ключове в релационна таблица и върху дименсиите могат да се налагат ограничения във формулите в RULES.

  • MEASURES

Еквивалентни са на мерките на таблицата с факти при схема звезда
( Star Schema ) и по принцип са числови стойности като продажби или разходи например. Те са това, което изчисляваме или се съдържа в данните от таблицата.

Пример: Sales

  • Partition: Country
  • Dimension: Product Name, Year
  • Measure: Sales

С подходящи формули и функции, в моделната клауза ще се изчисли сумата на продажбите на продуктите за всяка година като получените стойности ще са изчислени по държави.

Клаузата MODEL на практика

Ако искате да следвате примерите от статията можете да използвате следващата заявка, за да създадете изгледа, от който ще бъде източник на данните за заявките:

Ако нямате достатъчно права да създавате изгледи, можете да използвате:

Ролята на отделните колони от тестовите данни:

Данните, с които разполагаме:

Пример:

Да се изчисли сумата на продажбите:

  1. Germany + Belgium + Sweden
  2. Germany + Belgium
  3. Sweden + Germany
  4. само за продуктите 'Boston Crab Meat' + 'Escargots'

Полученият резултат е:

  • В какъв ред сървърът изпълнява заявката?
  1. FROM
  2. WHERE
  3. MODEL
  4. SELECT
  5. ORDER BY

Важно е да запомните:

Клаузата MODEL се изпълнява преди SELECT частта на заявката, което означава че в SELECT могат да се поставят колони САМО от DIMENSION или MEASURE, а ако искаме допълнително да използваме и агрегиращи или аналитични функции, то те трябва да са в RULES.

DIMENSION BY

DIMENSION BY определя колоните, които ще се виждат в SELECT и може да бъде както от колона от таблиците във FROM така и изчислима колона. Стойностите в тези колони трябва да позволяват еднозначно идентифициране на редовете (т.е. не може да има два реда със съвпадащи стойности в съответните колони).

Резултат:

При възникване на проблем с уникалността на редовете се получава следната грешка:

Пример:

Проблемът може да се реши като се използва UNIQUE SINGLE REFERENCE или се добави втора дименсия или с формиране на логически блок с PARTITION BY, ако дименсията има неповтарящи се стойности в рамките на блока.

  • задаване на псевдоними на колоните в SELECT

Ако е необходимо да се задават псевдоними на колоните, то те се поставят в DIMENSION BY.

Пример:

MEASURES

В MEASURES се поставят колоните, които съдържат числова стойност и ще участват във формулите или функциите в RULES. Освен колоните от извадката, могат да присъстват и псевдоколони (като SYSDATE) или изчислими колони (sales * 1.2).

Пример:

RULES

RULES позволява да се задават формули и функции, чрез които да извършваме изчисления с колоните в MEASURES.

Пример с формула:

  • чрез m_1[1] (референция към клетки) директно се обръщаме към клетката в колона m_1, където стойността на key e 1. Придаваме ѝ стойност 100 * 2.
  • при m_1[key = 2] логиката е същата. Обръщаме се към клетката, където стойността на key e 2. Придаваме ѝ стойност 100 * 3.
  • при m_1[key BETWEEN 4 AND 7] се целим върху повече от една клетка. Там където стойността на key e между 4 и 7 му придаваме стойност 100 * 4.

Резултатът е:

Пример с агрегираща функция:

Изчисляване на сумата на продажбите за всички държави и продукти

Резултатът е:

Пример с аналитична функция:

Изчисляване на сума на продажбите по държави

Резултатът е:

Заключение

Като извод можем да отбележим, че клаузата Model е мощно разширение на SQL, което позволява по лесен начин бързо да се извършат изчисленията, които по принцип е трудно да бъдат направени, използвайки други средства. Тази клауза е много полезна
и благодарение на нея се преодоляват доста ограничения, които по принцип се срещат в процеса на работа.

Искате да научите повече за SQL и възможностите за анализ на данни с него?

Вижте повече за специализирания курс SQL | Анализ на данни и изготвяне на отчети

© Copyright 2019 DeviseExpert Всички права запазени
envelopephone-handsetmap-marker linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram