Model. SQL - высший пилотаж , имитация Excel

Оператор sql Model позволяет рассматривать результат запроса как многомерный массив
при этом в SQL задаем оси измерения этого массива (идентифицируем данные по осям)

использование Model, так же позволит нам подводить промежуточные и общие итоги, с применением агрегатных функции 

SELECT *
FROM table1 -- таблица или запрос
MODEL DIMENSION BY (field1 , field2, ..)--оси, определение осей измерений по которым мы строим массив (поля для поиска уникальной ячейки)
    MEASURES (field3) -- определяющее поле 
    RULES (    cnt['res1', 'res2'] = res3 -- результат который вносится массив
            )  ORDER BY field1; -- сортировка по полю 

итак, нет ничего лучше для понимания сложной SQL конструкции, чем живой пример
продемонстрируем возможности model -- модели

create table pen(prt number, cnt number, color varchar2(15)); 
-- таблица(ручки) где (prt - партия поставки), cnt - количество в данной поставке, color - цвет ручек в поставке (red, green, black) )
-- заполним таблицу 
insert into pen(prt,cnt,color) values(1,5,'red');
insert into pen(prt,cnt,color) values(1,5,'black');
insert into pen(prt,cnt,color) values(2,3,'green');
insert into pen(prt,cnt,color) values(2,1,'red');
insert into pen(prt,cnt,color) values(3,1,'red');
insert into pen(prt,cnt,color) values(4,4,'black');
insert into pen(prt,cnt,color) values(7,3,'red');

определим измерения это color , prt 

SELECT * FROM pen
MODEL DIMENSION BY (prt, color) -- измерения оси строим по полям prt, color  
    MEASURES (cnt) -- работаем с cnt 
    RULES (
        cnt[any, 'red'] = cnt[cv(prt), 'red'] * 10 -- для каждого prt и color = red - cnt в итговом запросе умножаем на 10
            )
ORDER BY prt;
--   	PRT	COLOR	CNT
--	1	black	5
--	1	red	50
--	2	red	10
--	2	green	3
--	3	red	10
--	4	black	4
--	7	red	30

-- добавим итоги
SELECT * FROM pen
MODEL DIMENSION BY (prt, color) -- измерения оси строим по полям prt, color  
    MEASURES (cnt) -- работаем с cnt 
    RULES (
        --cnt[any, 'red'] = cnt[cv(prt), 'red'] * 10, -- для каждого prt и color = red - cnt в итговом запросе умножаем на 10
        cnt[NULL, 'SUMM RED'] = sum(cnt)[ANY, 'red'], --  итог, только по red
        cnt[NULL, 'SUMM'] = sum(cnt)[ANY, ANY] -- общий итог 
            )
ORDER BY prt;

--     prt color cnt
--	1	red	5
--	1	black	5
--      2	green	3
--	2	red	1
--	3	red	1
--	4	black	4
--	7	red	3
--		SUMM RED	10
--		SUMM	32

-- более интересно итог по трем партиям (1..3)

SELECT * FROM pen
MODEL DIMENSION BY (prt, color) -- измерения оси строим по полям prt, color  
    MEASURES (cnt) -- работаем с cnt 
    RULES (
        --cnt[any, 'red'] = cnt[cv(prt), 'red'] * 10, -- для каждого prt и color = red - cnt в итговом запросе умножаем на 10
        cnt[FOR prt FROM 1 TO 3 INCREMENT 1,  'SUMMPART'] = sum(cnt)[cv(prt), ANY], -- итог по трем партиям
        cnt[NULL, 'SUMM RED'] = sum(cnt)[ANY, 'red'], --  итог, только по red
        cnt[NULL, 'SUMM'] = sum(cnt)[ANY, ANY] -- общий итог 
            )
ORDER BY prt;

--  	PRT	COLOR	CNT
--	1	red	5
--	1	black	5
--	1	SUMMPART	10
--	2	green	3
--	2	red	1
--	2	SUMMPART	4
--	3	red	1
--	3	SUMMPART	1
--	4	black	4
--	7	red	3
--		SUMM	47
--		SUMM RED	10

-- еще более интересно, доп итоги по цвету ручек!!!

SELECT * FROM pen
MODEL DIMENSION BY (prt, color) -- измерения оси строим по полям prt, color  
    MEASURES (cnt) -- работаем с cnt 
    RULES (
        --cnt[any, 'red'] = cnt[cv(prt), 'red'] * 10, -- для каждого prt и color = red - cnt в итговом запросе умножаем на 10
       -- cnt[FOR prt FROM 1 TO 3 INCREMENT 1,  'SUMMPART'] = sum(cnt)[cv(prt), ANY], -- итог по трем партиям
        cnt[NULL, FOR color IN (SELECT color FROM pen group by color)] = sum(cnt)[ANY, cv(color)],  -- ИТОГ ПО ЦВЕТУ РУЧЕК!!!
        cnt[NULL, 'SUMM RED'] = sum(cnt)[ANY, 'red'], --  итог, только по red
        cnt[NULL, 'SUMM'] = sum(cnt)[ANY, ANY] -- общий итог 
            )
ORDER BY prt;

--   	PRT	COLOR	CNT
--	1	red	5
--	1	black	5
--	2	green	3
--	2	red	1
--	3	red	1
--	4	black	4
--	7	red	3
--		green	3
--		red	10
--		black	9
--		SUMM RED	20
--		SUMM	64

-- кроме суммы можно использовать и другие агрегатные функции , например avg

SELECT * FROM pen
MODEL DIMENSION BY (prt, color) -- измерения оси строим по полям prt, color  
    MEASURES (cnt) -- работаем с cnt 
    RULES (
        --cnt[any, 'red'] = cnt[cv(prt), 'red'] * 10, -- для каждого prt и color = red - cnt в итговом запросе умножаем на 10
       -- cnt[FOR prt FROM 1 TO 3 INCREMENT 1,  'SUMMPART'] = sum(cnt)[cv(prt), ANY], -- итог по трем партиям
        cnt[NULL, FOR color IN (SELECT color FROM pen group by color)] = sum(cnt)[ANY, cv(color)],  -- ИТОГ ПО ЦВЕТУ РУЧЕК!!!
        cnt[NULL, 'AVG RED'] = avg(cnt)[ANY, 'red']--, --  среднee партия по цвету red
        --cnt[NULL, 'SUMM'] = sum(cnt)[ANY, ANY] -- общий итог 
            )
ORDER BY prt;

--   	PRT	COLOR	CNT
--	1	red	5
--	1	black	5
--	2	green	3
--	2	red	1
--	3	red	1
--	4	black	4
--	7	red	3
--      	green	3
--	        red	10
--		black	9
--		AVG RED	4

более интересные примеры использования model я расскажу в следующих заметках...

• < Назад
• Вперёд >