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

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

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

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

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'
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')]
    
)

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

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 + Sweden--1100
Total 2: Germany + Belgium--1000
Total 3: Sweden + Germany--700
Total 4: Boston Crab Meat + Escargots--800
  • В какъв ред сървърът изпълнява заявката?
  1. FROM
  2. WHERE
  3. MODEL
  4. SELECT
  5. 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()
skeysales
1100
2200
3300
4NULL
5300
6100
7100
8NULL
9200
10800

Резултат:

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

Пример:

--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.

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

skeysalesm_1
1100200
2200300
33000
4-400
5300400
6100400
7100400
8-0
92000
108000

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

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

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

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

countryproductsalesm_1
BelgiumBoston Crab Meat-2100
BelgiumCarnarvon Tigers3002100
BelgiumEscargots1002100
GermanyBoston Crab Meat1002100
GermanyCarnarvon Tigers2002100
GermanyEscargots3002100
SwedenBoston Crab Meat1002100
SwedenCarnarvon Tigers-2100
-Boston Crab Meat2002100
-Carnarvon Tigers8002100

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

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

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

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

countryproductsalessm
BelgiumEscargots100400
BelgiumBoston Crab Meat-400
BelgiumCarnarvon Tigers300400
GermanyEscargots300600
GermanyBoston Crab Meat100600
GermanyCarnarvon Tigers200600
SwedenBoston Crab Meat100100
SwedenCarnarvon Tigers-100
-Carnarvon Tigers8001000
-Boston Crab Meat2001000

Заключение

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

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

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