Model. SQL - высший пилотаж , имитация Excel
Категория: Блог
Опубликовано 24.05.2017
Оператор 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 я расскажу в следующих заметках...