Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Как можно повторно использовать сложный подзапрос без WITH и дублирования?  [new]
Alibek B.
Member

Откуда:
Сообщений: 4003
У меня есть таблица-справочник, в которой есть значения для разных интервалов входных данных и "значение по умолчанию" для входных данных, не относящихся ни к одному интервалу.
В табличном виде это выглядит примерно так:
shedulenamehour_beghour_endis_systemv1v2
1По умолчанию00:0024:001......
2Остальное время00:0024:001......
2Утро06:0010:000......
2Вечер18:0022:000......

Мне нужно для разных schedule на определенное время дня получить действующие значения v1, v2 и т.д.
При этом, если на определенное время не найдена конкретная запись (с is_system=0), то нужно использовать запись по умолчанию (с is_system=1).
Если бы это была готовая таблица, то я бы использовал nvl и два джойна к этой таблице, внутри которых добавлял бы условие по is_system. Но запрос, которым я получаю вышеприведенную таблицу, довольно громоздкий, но использовать WITH я не хочу, потому что его бывает сложно комбинировать с другими WITH и не всегда получается засунуть его в подзапрос.
Если использовать подзапросы, то и без того громоздки запрос получается вдвое более громоздким. А с учетом того, что мне в одном запросе нужно получить значения на два момента времени (на текущий момент и на момент 5 минут назад), то запрос учетверяется.

+

Сам запрос, возвращающий интервалы и действующие в этих интервалах данные:
select S0.SCHEDULE_ID                  as "SCHEDULE#"
, S0.NAME                              as "SCHEDULE"
, S0.BLOCKS_QTY                        as "PARTS"
, S1.SEGMENT_ID                        as "SEGMENT#"
, S1.NAME                              as "SEGMENT"
, S1.IS_SYSTEM                         as "DEFAULT"
, S2.EXTENT_ID                         as "EXTENT#"
, S2.FIRST_BLOCK                       as "PART_FIRST"
, S2.LAST_BLOCK                        as "PART_LAST"
, trunc(ARG.MOMENT) + (1/S0.BLOCKS_QTY)*S2.FIRST_BLOCK    as "PERIOD_BEG"
, trunc(ARG.MOMENT) + (1/S0.BLOCKS_QTY)*(S2.LAST_BLOCK+1) as "PERIOD_END"
, S2.DAY_MASK_ID                       as "DAY#"
, SW.NAME                              as "DAY"
from BM_EXTENT S2
join BM_DAY_MASK SW on (SW.DAY_MASK_ID = S2.DAY_MASK_ID and SW.YEAR is null and SW.MONTH is null and SW.MDAY is null and (SW.WDAY is null or SW.WDAY = ceil(sysdate-trunc(sysdate,'IW'))))
join BM_SEGMENT S1 on (S1.SEGMENT_ID = S2.SEGMENT_ID)
join BM_SCHEDULE S0 on (S0.SCHEDULE_ID = S1.SCHEDULE_ID)
, (select to_date(:clock,'yyyy-mm-dd hh24:mi:ss') as MOMENT, to_date(:clock,'yyyy-mm-dd hh24:mi:ss') - :delta/86400 as BASE from dual) ARG
order by S0.SCHEDULE_ID, S1.SEGMENT_ID, S2.EXTENT_ID


BM_SCHEDULE — список различных расписаний, в соответствии с которым определяются интервалы.
В столбце BLOCKS_QTY указывается, на сколько частей делятся сутки для данного расписания (если задано 24, значит интервалы могут часовые, если задано 48, значит интервалы могут быть получасовые, если задано 1, значит интервал только один и охватывает сутки целиком).

BM_SEGMENT — интервалы группируются в сегменты, в расписании обязательно присутствует один сегмент по умолчанию (IS_SYSTEM=1) и произвольное количество дополнительных сегментов. Например в таблице с примерами для расписания 2 задан один системный сегмент "Остальное время" и два сегмента "Утро" и "Вечер".

BM_EXTENT — список интервалов, включенных в сегмент, которые задаются диапазоном. Область действия диапазона интервалов может быть ограничена днем недели, днем месяца, месяцев, годом (таблица BM_DAY_MASK).

В информационной системе есть тарифы, у тарифа задано расписание (SCHEDULE_ID) и заданы различные параметры, привязанные в интервалам. Мне нужно найти те тарифы, у которых за последние 5 минут изменились параметры, и определить для них текущие и предыдущие параметры.


Сообщение было отредактировано: 12 ноя 21, 15:36
12 ноя 21, 15:34    [22395164]     Ответить | Цитировать Сообщить модератору
 Re: Как можно повторно использовать сложный подзапрос без WITH и дублирования?  [new]
Alibek B.
Member

Откуда:
Сообщений: 4003
С WITH получается так:
+

with CAL as
(
select S0.SCHEDULE_ID                  as "SCHEDULE#"
, S0.NAME                              as "SCHEDULE"
, S0.BLOCKS_QTY                        as "PARTS"
, S1.SEGMENT_ID                        as "SEGMENT#"
, S1.NAME                              as "SEGMENT"
, S1.IS_SYSTEM                         as "DEFAULT"
, S2.EXTENT_ID                         as "EXTENT#"
, S2.FIRST_BLOCK                       as "PART_FIRST"
, S2.LAST_BLOCK                        as "PART_LAST"
, trunc(ARG.MOMENT) + (1/S0.BLOCKS_QTY)*S2.FIRST_BLOCK    as "PERIOD_BEG"
, trunc(ARG.MOMENT) + (1/S0.BLOCKS_QTY)*(S2.LAST_BLOCK+1) as "PERIOD_END"
, to_char(trunc(ARG.MOMENT) + (1/S0.BLOCKS_QTY)*S2.FIRST_BLOCK, 'hh24:mi')
  ||' - '||
  to_char(trunc(ARG.MOMENT) + (1/S0.BLOCKS_QTY)*(S2.LAST_BLOCK+1)-1/86400, 'hh24:mi')
                                       as "PERIOD"
, S2.DAY_MASK_ID                       as "DAY#"
, SW.NAME                              as "DAY"
from BILLING.BM_EXTENT S2
join BILLING.BM_DAY_MASK SW on (SW.DAY_MASK_ID = S2.DAY_MASK_ID and SW.YEAR is null and SW.MONTH is null and SW.MDAY is null and (SW.WDAY is null or SW.WDAY = ceil(sysdate-trunc(sysdate,'IW'))))
join BILLING.BM_SEGMENT S1 on (S1.SEGMENT_ID = S2.SEGMENT_ID)
join BILLING.BM_SCHEDULE S0 on (S0.SCHEDULE_ID = S1.SCHEDULE_ID)
, (select to_date(:clock,'yyyy-mm-dd hh24:mi:ss') as MOMENT, to_date(:clock,'yyyy-mm-dd hh24:mi:ss') - :delta/86400 as BASE from dual) ARG
)
select C0."SCHEDULE#"
, C0."SCHEDULE"
, C0."PARTS"
, C0."SEGMENT#"
, case when CC."SEGMENT#" is null then C0."SEGMENT#" else CC."SEGMENT#" end as "CURR_SEGMENT#"
, case when CC."SEGMENT#" is null then C0."SEGMENT"  else CC."SEGMENT"  end as "CURR_SEGMENT"
, case when CC."SEGMENT#" is null then null          else CC."PERIOD"   end as "CURR_PERIOD"
, case when CC."SEGMENT#" is null then 1                                end as "CURR_DEFAULT"
, case when CP."SEGMENT#" is null then C0."SEGMENT#" else CP."SEGMENT#" end as "PREV_SEGMENT#"
, case when CP."SEGMENT#" is null then C0."SEGMENT"  else CP."SEGMENT"  end as "PREV_SEGMENT"
, case when CP."SEGMENT#" is null then null          else CP."PERIOD"   end as "PREV_PERIOD"
, case when CP."SEGMENT#" is null then 1                                end as "PREV_DEFAULT"
, case when nvl(CC."SEGMENT#",C0."SEGMENT#") != nvl(CP."SEGMENT#",C0."SEGMENT#") then 1 end as "IS_CHANGED"
from CAL C0
left join CAL CC on (CC."SCHEDULE#" = C0."SCHEDULE#" and CC."DEFAULT" = 0 and CC.PERIOD_BEG <= to_date(:clock,'yyyy-mm-dd hh24:mi:ss') and CC.PERIOD_END > to_date(:clock,'yyyy-mm-dd hh24:mi:ss'))
left join CAL CP on (CP."SCHEDULE#" = C0."SCHEDULE#" and CP."DEFAULT" = 0 and CP.PERIOD_BEG <= to_date(:clock,'yyyy-mm-dd hh24:mi:ss') - :delta/86400 and CP.PERIOD_END > to_date(:clock,'yyyy-mm-dd hh24:mi:ss') - :delta/86400)
where C0."DEFAULT" = 1
order by C0."SCHEDULE"


Результаты правильные, но я бы хотел составить запрос без WITH.
Но без него получается очень громоздко.

Сообщение было отредактировано: 12 ноя 21, 16:10
12 ноя 21, 16:04    [22395177]     Ответить | Цитировать Сообщить модератору
 Re: Как можно повторно использовать сложный подзапрос без WITH и дублирования?  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 19924
Alibek B.
я бы использовал nvl и два джойна к этой таблице, внутри которых добавлял бы условие по is_system.

Зачем так извращаться?
Воспользуйтесь любым подходящим методом получения top-1.

Alibek B.

использовать WITH я не хочу, потому что его бывает сложно комбинировать с другими WITH и не всегда получается засунуть его в подзапрос.

???
Вот тут вообще не понял.
12 ноя 21, 16:42    [22395200]     Ответить | Цитировать Сообщить модератору
 Re: Как можно повторно использовать сложный подзапрос без WITH и дублирования?  [new]
Alibek B.
Member

Откуда:
Сообщений: 4003
andrey_anonymous
Воспользуйтесь любым подходящим методом получения top-1.

Не совсем понял идею.
Делать сортировку в подзапросе (order by is_system desc, hour_beg asc) и брать первую запись?
По-моему это еще сложнее, чем в моем варианте.

andrey_anonymous
Вот тут вообще не понял.

Мне потом полученную конструкцию нужно будет использовать в более сложном запросе, где уже есть with.
Если ее добавлять как подзапрос — with ... select ... from ... (with ... ) cal join ... — то я сталкивался с тем, что такой запрос странно себя ведет, мне не всегда понятно, как он работает. То ли проявляются какие-то баги (версия Oracle 10g), то ли скорее всего я какие-то нюансы не учитываю, но я несколько раз сталкивался со сложностями и стараюсь избегать вложенных with.
А если добавлять его в цепочку — with cal as ..., sq1 as ... select ... — то такой запрос сложно сопровождать.
12 ноя 21, 17:00    [22395214]     Ответить | Цитировать Сообщить модератору
 Re: Как можно повторно использовать сложный подзапрос без WITH и дублирования?  [new]
booby
Member

Откуда:
Сообщений: 2671
Alibek B.,

Есть такое слово - представление - создается командой create or replace view
Любое созданное представление можно использовать в качестве таблицы-источника данных в предложении From

Ваш With в вашей версии системы, вы можете привести к форме "параметризованного представления", например, используя sys_context
и опираясь на либо именованный, либо на клиентский контекст ('CLIENTCONTEXT')
12 ноя 21, 17:47    [22395251]     Ответить | Цитировать Сообщить модератору
 Re: Как можно повторно использовать сложный подзапрос без WITH и дублирования?  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 19924
Alibek B.
По-моему это еще сложнее, чем в моем варианте.

Зато заметно эффективнее в некоторых применениях и уж точно лаконичнее чем двойной join.
Попробуйте внимательно изучить
http://orasql.org/2012/09/21/distinct-values-by-index-topn/
http://orasql.org/2013/07/05/topn-2/

Alibek B.

А если добавлять его в цепочку — with cal as ..., sq1 as ... select ... — то такой запрос сложно сопровождать.

Это верное применение with.
И сопровождать его совершенно не сложно, если ввести элементарные правила форматирования и совсем чуть-чуть привыкнуть.
12 ноя 21, 21:02    [22395352]     Ответить | Цитировать Сообщить модератору
 Re: Как можно повторно использовать сложный подзапрос без WITH и дублирования?  [new]
Alibek B.
Member

Откуда:
Сообщений: 4003
booby
Ваш With в вашей версии системы, вы можете привести к форме "параметризованного представления", например, используя sys_context

Звучит интересно, почитаю. Не знал, что так можно.

andrey_anonymous
Это верное применение with.

С форматированием я бы еще справился, но усложняется и отладка запроса. Не получится просто скопировать фрагмент собственно запроса, отладить/скорректировать и вернуть обратно; с запросом нужно копировать и часть with, попутно адаптируя текст запроса.
За наводку с TopN спасибо, поизучаю, но выглядит довольно сложно. Сложнее, чем двойной джойн.

Сообщение было отредактировано: 12 ноя 21, 23:10
12 ноя 21, 23:09    [22395420]     Ответить | Цитировать Сообщить модератору
 Re: Как можно повторно использовать сложный подзапрос без WITH и дублирования?  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 19924
Alibek B.
Не получится просто скопировать фрагмент собственно запроса, отладить/скорректировать и вернуть обратно; с запросом нужно копировать и часть with, попутно адаптируя текст запроса.

With - часть запроса, копировать надо весь запрос.
Отлаживаться с with сильно легче, поскольку можно отладить по отдельности все представления, не корежа запрос.
Особенно оцените когда двинетесь на более свежие версии rdbms, которые не требуют обязательного упоминания в запросе всех веток with.
происходит это так:
-- Пусть есть запрос
with v1 as(...)
, v2 as (...)
, v3 as (...)
select ...
from v1, v2 
where ...
;

-- Отлаживаем v1
with v1 as(...)
select * from v1;
-- эта часть осталась за запяточием и не мешает
, v2 as (...)
, v3 as (...)
select ...
from v1, v2, v3
where ...
;

-- Отлаживаем v2
with v1 as(...)
--select * from v1; -- в любой момент можем дернуть v1, просто удалив "--"
, v2 as (...)
select * from v2; -- в 10g так, емнип, не получится, поскольку в запросе должно быть упомянуто v1, но в более свежих версиях без проблем
-- эта часть осталась за запяточием и не мешает
, v3 as (...)
select ...
from v1, v2, v3
where ...
;

-- Отлаживаем v2 в 10g
with v1 as(...)
--select * from v1; -- в любой момент можем дернуть v1, просто удалив "--"
, v2 as (...)
select v2.*, (select null from v1 where 1=0) Z from v2; -- в 10g делал так
-- эта часть осталась за запяточием и не мешает
, v3 as (...)
select ...
from v1, v2, v3
where ...
;

-- Отлаживаем v3 в 10g
with v1 as(...)
--select * from v1; -- в любой момент можем дернуть v1, просто удалив "--"
, v2 as (...)
--select v2.*, (select null from v1 where 1=0) Z from v2; -- в любой момент можем дернуть v2, просто удалив "--"
, v3 as (...)
select v3.*, (select null from v1,v2 where 1=0) Z from v3; -- в 10g делал так
-- эта часть осталась за запяточием и не мешает
select ...
from v1, v2, v3
where ...
;


Надеюсь, идея понятна.
Кроме того, в том же pl/sql developer SQL окно позволяет выделить непосредственно тот кусок текста, который надо выполнить, не вырезая его из запроса.
Для отладки это не очень удобно, а вот просто глянуть на данные - вполне.
-- Пусть есть запрос
with v1 as(select ...from ...) -- выделили мышкой, жамкнули "выполнить" *
, v2 as (...)
, v3 as (...)
select ...
from v1, v2, v3
where ...
;
--* Данный способ не подходит для веток, ссылающихся на другие ветки with
13 ноя 21, 11:32    [22395501]     Ответить | Цитировать Сообщить модератору
 Re: Как можно повторно использовать сложный подзапрос без WITH и дублирования?  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 58433
В жабе ещё можно куски запроса отделить точками с запятой
И запрос будет выполняться от одной до другой
13 ноя 21, 11:50    [22395505]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить