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 на практика

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

CREATE OR REPLACE VIEW products_sales AS 
 WITH t(skey, country, product, order_date, sales) AS ( 
    SELECT   1, 'Germany', 'Boston Crab Meat', TO_DATE('2005-01-01','RRRR-MM-DD'), 100  FROM DUAL UNION ALL
    SELECT   2, 'Germany', 'Carnarvon Tigers', TO_DATE('2005-06-12','RRRR-MM-DD'), 200  FROM DUAL UNION ALL
    SELECT   3, 'Germany', 'Escargots', NULL,                               300  FROM DUAL UNION ALL
    SELECT   4, 'Belgium', 'Boston Crab Meat', TO_DATE('2006-02-01','RRRR-MM-DD'), NULL FROM DUAL UNION ALL
    SELECT   5, 'Belgium', 'Carnarvon Tigers', TO_DATE('2006-06-12','RRRR-MM-DD'), 300  FROM DUAL UNION ALL
    SELECT   6, 'Belgium', 'Escargots', TO_DATE('2005-01-01','RRRR-MM-DD'), 100  FROM DUAL UNION ALL
    SELECT   7, 'Sweden', 'Boston Crab Meat', TO_DATE('2006-06-12','RRRR-MM-DD'), 100  FROM DUAL UNION ALL
    SELECT   8, 'Sweden', 'Carnarvon Tigers', NULL,                               NULL FROM DUAL UNION ALL
    SELECT   9, NULL,'Boston Crab Meat', TO_DATE('2005-02-01','RRRR-MM-DD'), 200 FROM DUAL UNION ALL
    SELECT  10, NULL,'Carnarvon Tigers', TO_DATE('2005-02-01','RRRR-MM-DD'), 800 FROM DUAL)
SELECT skey, country, product, order_date, sales
FROM t

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

 WITH products_sales (skey, country, product, order_date, sales) AS ( 
    SELECT   1, 'Germany', 'Boston Crab Meat', TO_DATE('2005-01-01','RRRR-MM-DD'), 100  FROM DUAL UNION ALL
    SELECT   2, 'Germany', 'Carnarvon Tigers', TO_DATE('2005-06-12','RRRR-MM-DD'), 200  FROM DUAL UNION ALL
    SELECT   3, 'Germany', 'Escargots', NULL,                               300  FROM DUAL UNION ALL
    SELECT   4, 'Belgium', 'Boston Crab Meat', TO_DATE('2006-02-01','RRRR-MM-DD'), NULL FROM DUAL UNION ALL
    SELECT   5, 'Belgium', 'Carnarvon Tigers', TO_DATE('2006-06-12','RRRR-MM-DD'), 300  FROM DUAL UNION ALL
    SELECT   6, 'Belgium', 'Escargots', TO_DATE('2005-01-01','RRRR-MM-DD'), 100  FROM DUAL UNION ALL
    SELECT   7, 'Sweden', 'Boston Crab Meat', TO_DATE('2006-06-12','RRRR-MM-DD'), 100  FROM DUAL UNION ALL
    SELECT   8, 'Sweden', 'Carnarvon Tigers', NULL,                               NULL FROM DUAL UNION ALL
    SELECT   9, NULL,'Boston Crab Meat', TO_DATE('2005-02-01','RRRR-MM-DD'), 200 FROM DUAL UNION ALL
    SELECT  10, NULL,'Carnarvon Tigers', TO_DATE('2005-02-01','RRRR-MM-DD'), 800 FROM DUAL)
 SELECT *
 FROM products_sales

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

SKEY	COUNTRY	PRODUCT	ORDER_DATE	SALES
PARTITION	DIMENSION	DIMENSION	DIMENSION	MEASURE

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

SKEY	COUNTRY	PRODUCT	ORDER_DATE	SALES
1	Germany	Boston Crab Meat	2005-01-01	100
2	Germany	Carnarvon Tigers	2005-06-12	200
3	Germany	Escargots	NULL	300
4	Belgium	Boston Crab Meat	2006-02-01	NULL
5	Belgium	Carnarvon Tigers	2006-06-12	300
6	Belgium	Escargots	2005-01-01	100
7	Sweden	Boston Crab Meat	2006-06-12	100
8	Sweden	Carnarvon Tigers	NULL	NULL
9	NULL	Boston Crab Meat	2005-02-01	200
10	NULL	Carnarvon Tigers	2005-02-01	800

Пример:

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

Germany + Belgium + Sweden
Germany + Belgium
Sweden + Germany
само за продуктите \’Boston Crab Meat\’ + \’Escargots\’

SELECT (CASE WHEN skey LIKE 'Total %' THEN skey ELSE NULL END) skey
, country
, product
, value
FROM products_sales
MODEL 
    DIMENSION BY (
        CAST(skey as VARCHAR2(40)) skey,
        NVL(country,'N/A') country,
        NVL(product, 'N/A') product
    )
    MEASURES (sales value)
    RULES(
          value['Total 1: Germany + Belgium + Sweden',NULL,NULL] = SUM(value)[ANY, country IN ('Germany','Belgium', 'Sweden'),ANY]
        , value['Total 2: Germany + Belgium',NULL,NULL]= SUM(value)[ANY, country IN ('Germany'),ANY] +
                                                SUM(value)[ANY, country IN ('Belgium'),ANY]
        , value['Total 3: Sweden + Germany',NULL,NULL]= SUM(value)[ANY, country IN ('Sweden'),ANY] +
                                                SUM(value)[ANY, country IN ('Germany'),ANY]
        , value['Total 4: Boston Crab Meat + Escargots',NULL,NULL] = SUM(value)[ANY,ANY,product IN ('Boston Crab Meat','Escargots')]

)

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

TOTAL	COUNTRY	PRODUCT	VALUE
-	Germany	Boston Crab Meat	100
-	Germany	Carnarvon Tigers	200
-	Germany	Escargots	300
-	Belgium	Boston Crab Meat	-
-	Belgium	Carnarvon Tigers	300
-	Belgium	Escargots	100
-	Sweden	Boston Crab Meat	100
-	Sweden	Carnarvon Tigers	-
-	N/A	Boston Crab Meat	200
-	N/A	Carnarvon Tigers	800
Total 1: Germany + Belgium + Sweden	-	-	1100
Total 2: Germany + Belgium	-	-	1000
Total 3: Sweden + Germany	-	-	700
Total 4: Boston Crab Meat + Escargots	-	-	800

В какъв ред сървърът изпълнява заявката?

FROM
WHERE
MODEL
SELECT
ORDER BY

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

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

DIMENSION BY

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

SELECT skey
, sales
FROM products_sales
MODEL 
    DIMENSION BY (skey)
    MEASURES(sales)
    RULES()

skey	sales
1	100
2	200
3	300
4	NULL
5	300
6	100
7	100
8	NULL
9	200
10	800

Резултат:

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

Пример:

--ORA-32638: Non unique addressing in MODEL dimensions
SELECT country
, sales
FROM products_sales
MODEL 
    DIMENSION BY (country)
    MEASURES(sales)
    RULES()

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

SELECT country
, sales
FROM products_sales
MODEL
    --решение на проблема с уникалноста, но ораничаващо
    RULES() 
    UNIQUE SINGLE REFERENCE
    DIMENSION BY (country)
    MEASURES(sales)
    RULES()
ORDER BY 1

--group_1,group_2 в комбинация правят редовете уникални
SELECT country
, product
, sales
FROM products_sales
MODEL 
    DIMENSION BY (country, product)
    MEASURES(sales)
    RULES()

--използване на PARTITION BY
SELECT country
, product
, sales
FROM products_sales
MODEL
  PARTITION BY ( country )
  dimension by ( product )
  measures     ( sales )
  rules        ( )  
ORDER BY
  country ,
  product

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

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

Пример:

SELECT c
, p
FROM products_sales
MODEL 
    DIMENSION BY (country c, products p)
    MEASURES(sales)
    RULES()

MEASURES

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

Пример:

SELECT skey
, order_date
, value
, now
, note
FROM products_sales
MODEL
    DIMENSION BY (skey)
    MEASURES(
        order_date
        , sales * 1.2 value
        , SYSDATE now
        , 'note' note
    )
    RULES()

RULES

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

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

SELECT skey
, sales
, m_1
FROM products_sales
MODEL
    DIMENSION BY (
        skey
    )
    MEASURES (
          sales
        , 0 m_1 --изчислимо поле
    )
    RULES(
          m_1[1                   ] = 100 * 2
        , m_1[skey = 2            ] = 100 * 3
        , m_1[skey BETWEEN 4 AND 7] = 100 * 4
    )
ORDER BY skey

чрез 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.

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

skey	sales	m_1
1	100	200
2	200	300
3	300	0
4	-	400
5	300	400
6	100	400
7	100	400
8	-	0
9	200	0
10	800	0

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

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

SELECT
  country 
, product 
, sales 
, m_1
FROM
  products_sales
MODEL
    DIMENSION BY ( 
            country
           , product 
    )
    MEASURES (
            sales
            , 0 m_1 
    )
    RULES( 
        m_1[ANY, ANY] = SUM(sales)[ANY, ANY] 
    )
ORDER BY
  country
, product
, sales

country	product	sales	m_1
Belgium	Boston Crab Meat	-	2100
Belgium	Carnarvon Tigers	300	2100
Belgium	Escargots	100	2100
Germany	Boston Crab Meat	100	2100
Germany	Carnarvon Tigers	200	2100
Germany	Escargots	300	2100
Sweden	Boston Crab Meat	100	2100
Sweden	Carnarvon Tigers	-	2100
-	Boston Crab Meat	200	2100
-	Carnarvon Tigers	800	2100

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

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

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

SELECT country
, product
, sales
, sm
FROM products_sales
MODEL
    DIMENSION BY (
            country
          , product
    )
    MEASURES (
            sales
          , SUM(sales) OVER (PARTITION BY country) sm
    )
    RULES(
    )
ORDER BY 1

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

country	product	sales	sm
Belgium	Escargots	100	400
Belgium	Boston Crab Meat	-	400
Belgium	Carnarvon Tigers	300	400
Germany	Escargots	300	600
Germany	Boston Crab Meat	100	600
Germany	Carnarvon Tigers	200	600
Sweden	Boston Crab Meat	100	100
Sweden	Carnarvon Tigers	-	100
-	Carnarvon Tigers	800	1000
-	Boston Crab Meat	200	1000

Заключение

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

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

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

Научете повече

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