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

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

CountryProduct NameYearSales
PartitonDimensionDimensionMeasure

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

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

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

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

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

SKEYCOUNTRYPRODUCTORDER_DATESALES
PARTITIONDIMENSIONDIMENSIONDIMENSIONMEASURE

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

SKEYCOUNTRYPRODUCTORDER_DATESALES
1GermanyBoston Crab Meat2005-01-01100
2GermanyCarnarvon Tigers2005-06-12200
3GermanyEscargotsNULL300
4BelgiumBoston Crab Meat2006-02-01NULL
5BelgiumCarnarvon Tigers2006-06-12300
6BelgiumEscargots2005-01-01100
7SwedenBoston Crab Meat2006-06-12100
8SwedenCarnarvon TigersNULLNULL
9NULLBoston Crab Meat2005-02-01200
10NULLCarnarvon Tigers2005-02-01800

Пример:

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

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

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

TOTALCOUNTRYPRODUCTVALUE
GermanyBoston Crab Meat100
GermanyCarnarvon Tigers200
GermanyEscargots300
BelgiumBoston Crab Meat
BelgiumCarnarvon Tigers300
BelgiumEscargots100
SwedenBoston Crab Meat100
SwedenCarnarvon Tigers
N/ABoston Crab Meat200
N/ACarnarvon Tigers800
Total 1: Germany + Belgium + Sweden1100
Total 2: Germany + Belgium1000
Total 3: Sweden + Germany700
Total 4: Boston Crab Meat + Escargots800
  • В какъв ред сървърът изпълнява заявката?
  1. FROM
  2. WHERE
  3. MODEL
  4. SELECT
  5. ORDER BY

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

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

DIMENSION BY

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

skeysales
1100
2200
3300
4NULL
5300
6100
7100
8NULL
9200
10800

Резултат:

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

Пример:

Проблемът може да се реши като се използва 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.

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

skeysalesm_1
1100200
2200300
33000
4400
5300400
6100400
7100400
80
92000
108000

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

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

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

countryproductsalesm_1
BelgiumBoston Crab Meat2100
BelgiumCarnarvon Tigers3002100
BelgiumEscargots1002100
GermanyBoston Crab Meat1002100
GermanyCarnarvon Tigers2002100
GermanyEscargots3002100
SwedenBoston Crab Meat1002100
SwedenCarnarvon Tigers2100
Boston Crab Meat2002100
Carnarvon Tigers8002100

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

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

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

countryproductsalessm
BelgiumEscargots100400
BelgiumBoston Crab Meat400
BelgiumCarnarvon Tigers300400
GermanyEscargots300600
GermanyBoston Crab Meat100600
GermanyCarnarvon Tigers200600
SwedenBoston Crab Meat100100
SwedenCarnarvon Tigers100
Carnarvon Tigers8001000
Boston Crab Meat2001000

Заключение

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

Искате да научите повече за SQL и възможностите за анализ на данни с него? Вижте повече за специализираното обучение SQL & PowerBI | Анализ на данни и изготвяне на отчети