ReSQL.ru
     
powered by simpleCommunicator - 2.0.29     © 2024 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Map
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как можно повторно использовать сложный подзапрос без WITH и дублирования?
9 сообщений из 9, страница 1 из 1
Как можно повторно использовать сложный подзапрос без WITH и дублирования?
    #40111387
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
У меня есть таблица-справочник, в которой есть значения для разных интервалов входных данных и "значение по умолчанию" для входных данных, не относящихся ни к одному интервалу.
В табличном виде это выглядит примерно так:
shedulenamehour_beghour_endis_systemv1v21По умолчанию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 минут назад), то запрос учетверяется.

 

Сам запрос, возвращающий интервалы и действующие в этих интервалах данные:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
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 минут изменились параметры, и определить для них текущие и предыдущие параметры.
...
Рейтинг: 0 / 0
12.11.2021, 15:34
| Ответить | Цитировать | Написать  
Как можно повторно использовать сложный подзапрос без WITH и дублирования?
    #40111397
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
С WITH получается так:
 

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
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.
Но без него получается очень громоздко.
...
Рейтинг: 0 / 0
12.11.2021, 16:04
| Ответить | Цитировать | Написать  
Как можно повторно использовать сложный подзапрос без WITH и дублирования?
    #40111416
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Alibek B.
я бы использовал nvl и два джойна к этой таблице, внутри которых добавлял бы условие по is_system.

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

Alibek B.

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

???
Вот тут вообще не понял.
...
Рейтинг: 0 / 0
12.11.2021, 16:42
| Ответить | Цитировать | Написать  
Как можно повторно использовать сложный подзапрос без WITH и дублирования?
    #40111426
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
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 ... — то такой запрос сложно сопровождать.
...
Рейтинг: 0 / 0
12.11.2021, 17:00
| Ответить | Цитировать | Написать  
Как можно повторно использовать сложный подзапрос без WITH и дублирования?
    #40111458
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
Alibek B.,

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

Ваш With в вашей версии системы, вы можете привести к форме "параметризованного представления", например, используя sys_context
и опираясь на либо именованный, либо на клиентский контекст ('CLIENTCONTEXT')
...
Рейтинг: 0 / 0
12.11.2021, 17:47
| Ответить | Цитировать | Написать  
Как можно повторно использовать сложный подзапрос без WITH и дублирования?
    #40111538
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
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.
И сопровождать его совершенно не сложно, если ввести элементарные правила форматирования и совсем чуть-чуть привыкнуть.
...
Рейтинг: 0 / 0
12.11.2021, 21:02
| Ответить | Цитировать | Написать  
Как можно повторно использовать сложный подзапрос без WITH и дублирования?
    #40111599
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
booby
Ваш With в вашей версии системы, вы можете привести к форме "параметризованного представления", например, используя sys_context

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

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

С форматированием я бы еще справился, но усложняется и отладка запроса. Не получится просто скопировать фрагмент собственно запроса, отладить/скорректировать и вернуть обратно; с запросом нужно копировать и часть with, попутно адаптируя текст запроса.
За наводку с TopN спасибо, поизучаю, но выглядит довольно сложно. Сложнее, чем двойной джойн.
...
Рейтинг: 0 / 0
12.11.2021, 23:09
| Ответить | Цитировать | Написать  
Как можно повторно использовать сложный подзапрос без WITH и дублирования?
    #40111658
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Alibek B.
Не получится просто скопировать фрагмент собственно запроса, отладить/скорректировать и вернуть обратно; с запросом нужно копировать и часть with, попутно адаптируя текст запроса.

With - часть запроса, копировать надо весь запрос.
Отлаживаться с with сильно легче, поскольку можно отладить по отдельности все представления, не корежа запрос.
Особенно оцените когда двинетесь на более свежие версии rdbms, которые не требуют обязательного упоминания в запросе всех веток with.
происходит это так:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
-- Пусть есть запрос
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 окно позволяет выделить непосредственно тот кусок текста, который надо выполнить, не вырезая его из запроса.
Для отладки это не очень удобно, а вот просто глянуть на данные - вполне.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
-- Пусть есть запрос
with v1 as(select ...from ...) -- выделили мышкой, жамкнули "выполнить" *
, v2 as (...)
, v3 as (...)
select ...
from v1, v2, v3
where ...
;
--* Данный способ не подходит для веток, ссылающихся на другие ветки with
...
Рейтинг: 0 / 0
13.11.2021, 11:32
| Ответить | Цитировать | Написать  
Как можно повторно использовать сложный подзапрос без WITH и дублирования?
    #40111661
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
В жабе ещё можно куски запроса отделить точками с запятой
И запрос будет выполняться от одной до другой
...
Рейтинг: 0 / 0
13.11.2021, 11:50
| Ответить | Цитировать | Написать  
9 сообщений из 9, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как можно повторно использовать сложный подзапрос без WITH и дублирования?
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru, Чат Кролика       Пашэ: Рисунки, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=40111387&tid=1879759]:
 0ms
get settings:
 16ms
get forum list:
 5ms
check forum access:
 1ms
check topic access:
 1ms
track hit:
 56ms
get topic data:
 3ms
get forum data:
 1ms
get page messages:
 201ms
get tp. blocked users:
 0ms
others: 378ms
total:  662ms
созд. / загр.: 662ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]