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

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

теги:

Автор: Myp3_u_K

Чалышев М.М www.orasource.ru ; моё резюме
изучаем 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 я расскажу в следующих заметках...

Комментарии




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