Oracle SQL

Фильтр по тегу: measures


Sum по disitnct полю , в другой колонке

Чалышев Максим Михайлович
Учебный тренинг. SQL от новичка до профессионала.
Бесплатные авторские видеокурсы SQL, PLSQL, JAVA
-- итак требуется найти сумму по distinct полю на основе идентификатора, который находится в другой колонке
-- е1сть доп условие нельзя использовать подзапрос, то есть необходимо выполнить задачу за одно обращение


-- подготовим данные для тестового примера
drop table t; -- если надо
create table t as
WITH r AS (
select /*+ materialize */ * from ALL_OBJECTS WHERE ROWNUM < 100 )
select object_id as id,length(r.object_name) as fid,
 (select count(1) from r a where length(r.object_name) = length(a.object_name))  as price   
from r


-- есть вторичный ключ fid от которого зависит значение в поле price
-- необходимо найти сумму по полю price в distinct поля fid одним запросом, то есть без вложенного select

select sum(price) sm from t 
-- это неправильное решение

select sum(distinct price) sm from t 
-- и это неправильное решение price может быть одинаковый для разных fid
-- надо как то так
select sum(price distinct fid) sm from t
-- но так написать нельзя

-- первое решение,
-- работает быстро, но решение не идеально, ключ может быть и не числовой
-- сумма Id + price - сумма id
-- , во избежание неприятностей использовали to_number(rpad(fid, 20)
select sum(distinct to_number(rpad(fid,10,'0')) +price) -  sum(distinct to_number(rpad(fid,10,'0'))) as sm  from t


-- решение второе - используем model
  select r from  t 
   model
   return updated rows   
   dimension by(id)
  measures(0 r, nullif(price, lag(price)over(partition by fid order by id)) lg)
  rules upsert(r[0] = sum(lg)[any])
добавлено: 23 май 18 просмотры: 2649, комментарии: 1



MODEL. SQL высший пилотаж. Часть 2. Сложные последовательности.

Чалышев Максим Михайлович
Учебный тренинг. SQL от новичка до профессионала.
Бесплатные авторские видеокурсы SQL, PLSQL, JAVA

Используем Model для создания массивов
-- создание одномерного массива
SELECT *
FROM dual
    MODEL DIMENSION BY (0 as т1)
    MEASURES (cast(dummy as varchar2(20)) as ct) 
    RULES (        
        ct[0] = '1',
        ct[1] = '2',       
        ct[1] = '3',
        ct[2] = '4',
        ct[3] = '5'
    ) order by 1;
--Т1	CT
--0	1
--1	3
--2	4
--3	5


читать дальше...
добавлено: 03 июл 15 просмотры: 5192, комментарии: 0



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

Чалышев Максим Михайлович
Авторский курс. 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

читать дальше...
добавлено: 02 июл 15 просмотры: 10101, комментарии: 0