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

добавлено: 02 июл 15
понравилось:0
просмотров: 10169
комментов: 0

теги:

Автор: Myp3_u_K

Чалышев Максим Михайлович
Авторский курс. SQL от новичка до профессионала. Бесплатное вводное занятие. Сертификат. Записывайся!
Прокачаю до уровня БОГ!


Оператор 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 я расскажу в следующих заметках...

Комментарии




Необходимо войти на сайт, чтобы оставлять комментарии