Klauzula MODEL w Oracle SQL – arkusz kalkulacyjny wewnątrz zapytania

Większość osób pracujących na co dzień z Oracle SQL zna GROUP BY, funkcje analityczne typu SUM() OVER() czy CONNECT BY. Znacznie mniej osób słyszało o klauzuli MODEL – a to jedno z najciekawszych i najmniej wykorzystywanych narzędzi w całym silniku Oracle. Pozwala ona wykonywać obliczenia „komórka po komórce”, tak jak w arkuszu kalkulacyjnym, ale bez wychodzenia z SQL-a.

W tym wpisie: Oracle SQL MODEL pokażemy, do czego MODEL się przydaje i jak napisać pierwszy praktyczny przykład: obliczenie prognozy sprzedaży na kolejny miesiąc na podstawie danych historycznych.

Dlaczego warto poznać MODEL?

W klasycznym SQL każdy wiersz wyniku jest w dużej mierze niezależny – łatwo policzyć sumę czy średnią, trudniej odwołać się do „wartości z innego wiersza, przeliczonej według własnej reguły” (np. „przyszły miesiąc = poprzedni miesiąc razy 1,1”). Zwykle rozwiązuje się to za pomocą:

funkcji analitycznych (LAG, LEAD),
rekurencyjnych CTE,
albo – co gorsza – pętli PL/SQL i tabel tymczasowych.

MODEL pozwala zdefiniować te reguły deklaratywnie, w jednym zapytaniu, adresując komórki tak jak w Excelu: po współrzędnych (np. [rok, miesiąc]).

Podstawowa struktura

SELECT produkt, rok, sprzedaz
FROM   sprzedaz_tab
MODEL
    PARTITION BY (produkt)
    DIMENSION BY (rok)
    MEASURES (sprzedaz)
    RULES (
        sprzedaz[2026] = sprzedaz[2025] * 1.10
    )
ORDER BY produkt, rok;

Kluczowe elementy:

PARTITION BY – dzieli dane na niezależne „arkusze” (tu: osobny arkusz dla każdego produktu).
DIMENSION BY – kolumna(y), które stają się „współrzędnymi komórek” (tu: rok).
MEASURES – wartości, na których operujemy (tu: sprzedaż).
RULES – reguły obliczeniowe, czyli serce klauzuli.

Przykład praktyczny: prognoza sprzedaży na 3 miesiące

Załóżmy tabelę z historyczną sprzedażą miesięczną:

CREATE TABLE sprzedaz_tab (
    produkt   VARCHAR2(30),
    miesiac   NUMBER,
    sprzedaz  NUMBER
);

INSERT INTO sprzedaz_tab VALUES ('Kurs SQL', 1, 12000);
INSERT INTO sprzedaz_tab VALUES ('Kurs SQL', 2, 13500);
INSERT INTO sprzedaz_tab VALUES ('Kurs SQL', 3, 12800);

Chcemy wygenerować prognozę na miesiące 4, 5 i 6, przyjmując prosty model: każdy kolejny miesiąc to średnia z trzech poprzednich, powiększona o 5% trendu wzrostowego.

SELECT produkt, miesiac, sprzedaz
FROM   sprzedaz_tab
MODEL
    PARTITION BY (produkt)
    DIMENSION BY (miesiac)
    MEASURES (sprzedaz)
    RULES (
        sprzedaz[4] = AVG(sprzedaz)[miesiac BETWEEN 1 AND 3] * 1.05,
        sprzedaz[5] = AVG(sprzedaz)[miesiac BETWEEN 2 AND 4] * 1.05,
        sprzedaz[6] = AVG(sprzedaz)[miesiac BETWEEN 3 AND 5] * 1.05
    )
ORDER BY produkt, miesiac;

To, co tu się dzieje, jest niemożliwe do zapisania w jednej linijce zwykłym SQL-em: reguła dla miesiąca 5 odwołuje się do wartości, która sama została dopiero co wyliczona w regule dla miesiąca 4. MODEL domyślnie przelicza reguły sekwencyjnie (SEQUENTIAL ORDER), więc kolejne obliczenia „widzą” wcześniej wygenerowane komórki – zupełnie jak przeciąganie formuły w Excelu w dół kolumny.

Przydatne opcje, o których warto wiedzieć

RULES UPDATE (domyślne) vs RULES UPSERT – UPSERT pozwala tworzyć nowe komórki (jak w przykładzie powyżej, gdzie miesiące 4–6 nie istniały w danych źródłowych). Bez tego Oracle zgłosi błąd, próbując zaktualizować nieistniejący wiersz.
FOR miesiac FROM 4 TO 6 INCREMENT 1 – zamiast wypisywać trzy osobne reguły, można je uogólnić pętlą deklaratywną:

RULES UPSERT (
    sprzedaz[FOR miesiac FROM 4 TO 6 INCREMENT 1] =
        AVG(sprzedaz)[miesiac BETWEEN CV(miesiac)-3 AND CV(miesiac)-1] * 1.05
)

CV(miesiac) oznacza „bieżącą wartość wymiaru” – odpowiednik odwołania względnego do komórki w Excelu.

Kiedy to ma sens, a kiedy nie

MODEL sprawdza się świetnie do:

prognoz i symulacji „what-if” bezpośrednio na danych w bazie,
raportów finansowych z regułami narastającymi (budżety, korekty, alokacje kosztów),
wypełniania brakujących okresów w szeregach czasowych według zdefiniowanej logiki.

Nie warto go używać do prostych agregacji – tam GROUP BY i funkcje analityczne będą czytelniejsze i szybsze. MODEL bywa też trudniejszy do zoptymalizowania na bardzo dużych zbiorach danych, więc warto go testować na realnych wolumenach przed wdrożeniem produkcyjnym.

Podsumowanie

Klauzula MODEL to jedna z tych funkcji Oracle, które rzadko trafiają do podręczników na poziomie podstawowym, a potrafią zastąpić dziesiątki linijek PL/SQL jednym, deklaratywnym blokiem RULES. Jeśli pracujesz z raportowaniem, prognozami albo modelami finansowymi w SQL – zdecydowanie warto dodać ją do swojego zestawu narzędzi.

Chcesz nauczyć się takich technik krok po kroku, na praktycznych przykładach biznesowych? Sprawdź nasze szkolenie SQL dla analityków — program obejmuje m.in. funkcje okna, CTE i analizę szeregów czasowych.