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

Откуда:
Сообщений: 117
Добрый день.

Я создал запрос, такой чтобы из длинного списка тарифов на каждый день, сделать короткий список с диапазоном дат (с - по).
Полагаю, что можно всё тоже самое сделать проще. Хотя вроде бы и так не плохо.
Посоветуйте.
PS тестовые данные with r as включены для удобства.

with r as
 (select to_date('2015-01-01', 'yyyy-mm-dd') res_date, 'RATE1' rate   from dual union all
  select to_date('2015-01-02', 'yyyy-mm-dd') res_date, 'RATE1' rate   from dual  union all
  select to_date('2015-01-03', 'yyyy-mm-dd') res_date, 'RATE2' rate  from dual union  all
  select to_date('2015-01-04', 'yyyy-mm-dd') res_date, 'RATE2' rate from dual union all
  select to_date('2015-01-05', 'yyyy-mm-dd') res_date, 'RATE2' rate from dual union all
  select to_date('2015-01-06', 'yyyy-mm-dd') res_date, 'RATE2' rate from dual union all
  select to_date('2015-01-07', 'yyyy-mm-dd') res_date, 'RATE2' rate  from dual union all
  select to_date('2015-01-08', 'yyyy-mm-dd') res_date, 'RATE1' rate from dual union all
  select to_date('2015-01-09', 'yyyy-mm-dd') res_date, 'RATE1' rate  from dual),
f as
 (select rownum n, t.*
    from (select res_date,
                 rate,
                 case
                   when LAG(rate, 1) over(ORDER BY res_date) = rate then
                    '1'
                   else
                    '0'
                 end as ooo
            from r
           order by res_date) t
   where ooo = '0'),
l as
 (select rownum n, t.*
    from (select res_date,
                 rate,
                 case
                   when LEAD(rate, 1) over(ORDER BY res_date) = rate then
                    '1'
                   else
                    '0'
                 end as ooo
            from r
           order by res_date) t
   where ooo = '0')

select f.n, f.res_date, l.res_date, f.rate
  from f, l
 where f.n = l.n
   and f.rate = l.rate
 order by 2
14 мар 19, 15:21    [21832711]     Ответить | Цитировать Сообщить модератору
 Re: Упростить SQL запрос  [new]
MazoHist
Member

Откуда:
Сообщений: 78
STFF start_of_group
with r as
 (select to_date('2015-01-01', 'yyyy-mm-dd') res_date, 'RATE1' rate   from dual union all
  select to_date('2015-01-02', 'yyyy-mm-dd') res_date, 'RATE1' rate   from dual  union all
  select to_date('2015-01-03', 'yyyy-mm-dd') res_date, 'RATE2' rate  from dual union  all
  select to_date('2015-01-04', 'yyyy-mm-dd') res_date, 'RATE2' rate from dual union all
  select to_date('2015-01-05', 'yyyy-mm-dd') res_date, 'RATE2' rate from dual union all
  select to_date('2015-01-06', 'yyyy-mm-dd') res_date, 'RATE2' rate from dual union all
  select to_date('2015-01-07', 'yyyy-mm-dd') res_date, 'RATE2' rate  from dual union all
  select to_date('2015-01-08', 'yyyy-mm-dd') res_date, 'RATE1' rate from dual union all
  select to_date('2015-01-09', 'yyyy-mm-dd') res_date, 'RATE1' rate  from dual)
  select rate, min(res_date), max(res_date) from (
  select res_date, rate, sum(sog) over (order by res_date) sog from (
select res_date, rate, case when rate <> nvl( lag(rate) over (order by res_date),'~') then 1 end sog from r))
group by rate, sog
14 мар 19, 15:52    [21832755]     Ответить | Цитировать Сообщить модератору
 Re: Упростить SQL запрос  [new]
maximand
Member

Откуда:
Сообщений: 117
MazoHist,

СПАСИБО ОГРОМНОЕ!
14 мар 19, 16:18    [21832787]     Ответить | Цитировать Сообщить модератору
 Re: Упростить SQL запрос  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17157
with r as
 (select to_date('2015-01-01', 'yyyy-mm-dd') res_date, 'RATE1' rate   from dual union all
  select to_date('2015-01-02', 'yyyy-mm-dd') res_date, 'RATE1' rate   from dual  union all
  select to_date('2015-01-03', 'yyyy-mm-dd') res_date, 'RATE2' rate  from dual union  all
  select to_date('2015-01-04', 'yyyy-mm-dd') res_date, 'RATE2' rate from dual union all
  select to_date('2015-01-05', 'yyyy-mm-dd') res_date, 'RATE2' rate from dual union all
  select to_date('2015-01-06', 'yyyy-mm-dd') res_date, 'RATE2' rate from dual union all
  select to_date('2015-01-07', 'yyyy-mm-dd') res_date, 'RATE2' rate  from dual union all
  select to_date('2015-01-08', 'yyyy-mm-dd') res_date, 'RATE1' rate from dual union all
  select to_date('2015-01-09', 'yyyy-mm-dd') res_date, 'RATE1' rate  from dual
)
select rm.*
  from r match_recognize(
            order by res_date
            measures s.rate as rate
                   , first(res_date) as date_from
                   , last(res_date) as date_till
            pattern(s next*)
            define next as s.res_date <= next.res_date and s.rate = next.rate
            ) rm
;

RATE  DATE_FROM   DATE_TILL
----- ----------- -----------
RATE1 01.01.2015  02.01.2015
RATE2 03.01.2015  07.01.2015
RATE1 08.01.2015  09.01.2015

SQL> 
14 мар 19, 18:19    [21832918]     Ответить | Цитировать Сообщить модератору
 Re: Упростить SQL запрос  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1645
andrey_anonymous,

если есть order by res_date,
s.res_date <= next.res_date надо (обязательно) указывать ?


.....
stax
14 мар 19, 18:36    [21832931]     Ответить | Цитировать Сообщить модератору
 Re: Упростить SQL запрос  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17157
Stax
(обязательно) указывать ?

Полагаю, что нет
14 мар 19, 19:34    [21832979]     Ответить | Цитировать Сообщить модератору
 Re: Упростить SQL запрос  [new]
maximand
Member

Откуда:
Сообщений: 117
andrey_anonymous,

И вам спасибо. К сожалению у меня 11G - это не работает. Но на 12ом всё окей.
15 мар 19, 10:19    [21833364]     Ответить | Цитировать Сообщить модератору
 Re: Упростить SQL запрос  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9364
Проще:

with r as
 (select to_date('2015-01-01', 'yyyy-mm-dd') res_date, 'RATE1' rate   from dual union all
  select to_date('2015-01-02', 'yyyy-mm-dd') res_date, 'RATE1' rate   from dual  union all
  select to_date('2015-01-03', 'yyyy-mm-dd') res_date, 'RATE2' rate  from dual union  all
  select to_date('2015-01-04', 'yyyy-mm-dd') res_date, 'RATE2' rate from dual union all
  select to_date('2015-01-05', 'yyyy-mm-dd') res_date, 'RATE2' rate from dual union all
  select to_date('2015-01-06', 'yyyy-mm-dd') res_date, 'RATE2' rate from dual union all
  select to_date('2015-01-07', 'yyyy-mm-dd') res_date, 'RATE2' rate  from dual union all
  select to_date('2015-01-08', 'yyyy-mm-dd') res_date, 'RATE1' rate from dual union all
  select to_date('2015-01-09', 'yyyy-mm-dd') res_date, 'RATE1' rate  from dual
)
select rm.*
  from r match_recognize(
            order by res_date
            measures rate as rate
                   , first(res_date) as date_from
                   , last(res_date) as date_till
            pattern(same_rate+)
            define same_rate as rate = first(rate)
            ) rm
/

RATE  DATE_FROM DATE_TILL
----- --------- ---------
RATE1 01-JAN-15 02-JAN-15
RATE2 03-JAN-15 07-JAN-15
RATE1 08-JAN-15 09-JAN-15

SQL> 


SY.
15 мар 19, 15:17    [21833954]     Ответить | Цитировать Сообщить модератору
 Re: Упростить SQL запрос  [new]
maximand
Member

Откуда:
Сообщений: 117
SY,

Like
18 мар 19, 18:00    [21836355]     Ответить | Цитировать Сообщить модератору
 Re: Упростить SQL запрос  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17157
SY
Проще

...но следует иметь ввиду, что без стартового элемента есть ньюансы matching:
with r as
 (select to_date('2015-01-01', 'yyyy-mm-dd') res_date, 'RATE1' rate   from dual union all
  select to_date('2015-01-02', 'yyyy-mm-dd') res_date, 'RATE1' rate   from dual  union all
  select to_date('2015-01-03', 'yyyy-mm-dd') res_date, 'RATE2' rate  from dual union  all
  select to_date('2015-01-04', 'yyyy-mm-dd') res_date, 'RATE2' rate from dual union all
  select to_date('2015-01-05', 'yyyy-mm-dd') res_date, 'RATE2' rate from dual union all
  select to_date('2015-01-06', 'yyyy-mm-dd') res_date, 'RATE2' rate from dual union all
  select to_date('2015-01-07', 'yyyy-mm-dd') res_date, 'RATE2' rate  from dual union all
  select to_date('2015-01-08', 'yyyy-mm-dd') res_date, 'RATE1' rate from dual union all
  select to_date('2015-01-09', 'yyyy-mm-dd') res_date, 'RATE1' rate  from dual union all
  select to_date('2015-01-10', 'yyyy-mm-dd') res_date, '' rate  from dual union all
  select to_date('2015-01-11', 'yyyy-mm-dd') res_date, '' rate  from dual
)
select rm.*
  from r match_recognize(
            order by res_date
            measures rate as rate
                   , first(res_date) as date_from
                   , last(res_date) as date_till
            pattern(strt same_rate*)
--            pattern(same_rate*)
--            pattern(same_rate+)
            define same_rate as rate = first(rate)
            ) rm
19 мар 19, 15:53    [21837488]     Ответить | Цитировать Сообщить модератору
 Re: Упростить SQL запрос  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1645
andrey_anonymous
SY
Проще

...но следует иметь ввиду, что без стартового элемента есть ньюансы matching:
[/src]

Ньюанс в rate is null, или еще в чем то?

зы
define same_rate as rate = first(rate)
null=null
добавить nvl

.....
stax
19 мар 19, 16:34    [21837533]     Ответить | Цитировать Сообщить модератору
 Re: Упростить SQL запрос  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17157
Stax
Ньюанс в rate is null, или еще в чем то?

В matching жеж :)
Если по очереди раскомментировать оба варианта без стартового элемента и со стартовым, то можно увидеть, что оно работает по-разному, и это следует иметь ввиду.
Был хороший гайд на эту тему - что-то про Deep Dive в этот самый pattern matching, уже не упомню
19 мар 19, 17:28    [21837600]     Ответить | Цитировать Сообщить модератору
 Re: Упростить SQL запрос  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1645
andrey_anonymous
Stax
Ньюанс в rate is null, или еще в чем то?

В matching жеж :)


чет я с ручника никак не снимусь, не вижу (засліпило) разницы для
pattern(strt same_rate*) и pattern(same_rate*)

ps
define same_rate as rate = first(rate)
....
stax
19 мар 19, 17:53    [21837628]     Ответить | Цитировать Сообщить модератору
 Re: Упростить SQL запрос  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17157
Stax
чет я с ручника никак не снимусь, не вижу (засліпило) разницы для
pattern(strt same_rate*) и pattern(same_rate*)

with r as
 (  select to_date('2015-01-10', 'yyyy-mm-dd') res_date, '' rate  from dual union all
  select to_date('2015-01-11', 'yyyy-mm-dd') res_date, '' rate  from dual
)
select rownum, rm.*
  from r match_recognize(
            order by res_date
            measures rate as rate
                   , first(res_date) as date_from
                   , last(res_date) as date_till
            pattern(strt same_rate*)
            define same_rate as rate = first(rate)
            ) rm
;
    ROWNUM RATE DATE_FROM   DATE_TILL
---------- ---- ----------- -----------
         1      10.01.2015  10.01.2015
         2      11.01.2015  11.01.2015
with r as
 (  select to_date('2015-01-10', 'yyyy-mm-dd') res_date, '' rate  from dual union all
  select to_date('2015-01-11', 'yyyy-mm-dd') res_date, '' rate  from dual
)
select rownum, rm.*
  from r match_recognize(
            order by res_date
            measures rate as rate
                   , first(res_date) as date_from
                   , last(res_date) as date_till
            pattern(same_rate*)
            define same_rate as rate = first(rate)
            ) rm
;
    ROWNUM RATE DATE_FROM   DATE_TILL
---------- ---- ----------- -----------
         1                  
         2  
19 мар 19, 18:02    [21837639]     Ответить | Цитировать Сообщить модератору
 Re: Упростить SQL запрос  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1645
andrey_anonymous,

спасибо, не туда смотрел (https://apex.oracle.com)

поведение "примерно" понятно

....
stax
19 мар 19, 18:11    [21837646]     Ответить | Цитировать Сообщить модератору
 Re: Упростить SQL запрос  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17157
Stax
поведение "примерно" понятно

Для улучшения понимания :)
with r as
 (  select to_date('2015-01-10', 'yyyy-mm-dd') res_date, '' rate  from dual union all
  select to_date('2015-01-11', 'yyyy-mm-dd') res_date, '' rate  from dual
)
select rownum, rm.*
  from r match_recognize(
            order by res_date
            measures rate as rate
                   , first(res_date) as date_from
                   , last(res_date) as date_till
                   , MATCH_NUMBER() AS match_no
                   , CLASSIFIER() AS classifier
            pattern(/*strt*/ same_rate*)
            define same_rate as rate = first(rate)
            ) rm
;
19 мар 19, 18:20    [21837653]     Ответить | Цитировать Сообщить модератору
 Re: Упростить SQL запрос  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1645
andrey_anonymous
Stax
поведение "примерно" понятно

Для улучшения понимания :)


я так примерно и понимал
токо раньше ето было связано со *,
а счас и с правилом с null значениями


.....
stax
19 мар 19, 18:41    [21837672]     Ответить | Цитировать Сообщить модератору
 Re: Упростить SQL запрос  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17157
Stax
токо раньше ето было связано со *,
а счас и с правилом с null значениями

не човчем :)
19 мар 19, 18:59    [21837690]     Ответить | Цитировать Сообщить модератору
 Re: Упростить SQL запрос  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1645
andrey_anonymous
не човчем :)

мож и так, я null пріплел к тому что вариант SY не отработает (надо nvl, decode, ...)

а так, со старт выбрано одна строка, без "0" строк

зы
у нас 11-ка, да и ...

....
stax
19 мар 19, 19:09    [21837703]     Ответить | Цитировать Сообщить модератору
 Re: Упростить SQL запрос  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9364
andrey_anonymous
...но следует иметь ввиду, что без стартового элемента есть ньюансы matching:


Нюанс дейсвительно есть:

SQL> with r as
  2   (select to_date('2015-01-01', 'yyyy-mm-dd') res_date, 'RATE1' rate   from dual union all
  3    select to_date('2015-01-02', 'yyyy-mm-dd') res_date, 'RATE1' rate   from dual  union all
  4    select to_date('2015-01-03', 'yyyy-mm-dd') res_date, 'RATE2' rate  from dual union  all
  5    select to_date('2015-01-04', 'yyyy-mm-dd') res_date, 'RATE2' rate from dual union all
  6    select to_date('2015-01-05', 'yyyy-mm-dd') res_date, 'RATE2' rate from dual union all
  7    select to_date('2015-01-06', 'yyyy-mm-dd') res_date, 'RATE2' rate from dual union all
  8    select to_date('2015-01-07', 'yyyy-mm-dd') res_date, 'RATE2' rate  from dual union all
  9    select to_date('2015-01-08', 'yyyy-mm-dd') res_date, 'RATE1' rate from dual union all
 10    select to_date('2015-01-09', 'yyyy-mm-dd') res_date, 'RATE1' rate  from dual union all
 11    select to_date('2015-01-10', 'yyyy-mm-dd') res_date, '' rate  from dual union all
 12    select to_date('2015-01-11', 'yyyy-mm-dd') res_date, '' rate  from dual
 13  )
 14  select rm.*
 15    from r match_recognize(
 16              order by res_date
 17              measures rate as rate
 18                     , first(res_date) as date_from
 19                     , last(res_date) as date_till
 20              pattern(strt same_rate*)
 21  --            pattern(same_rate*)
 22  --            pattern(same_rate+)
 23              define same_rate as rate = first(rate)
 24              ) rm
 25  /

RATE  DATE_FROM DATE_TILL
----- --------- ---------
RATE1 01-JAN-15 02-JAN-15
RATE2 03-JAN-15 07-JAN-15
RATE1 08-JAN-15 09-JAN-15
      10-JAN-15 10-JAN-15
      11-JAN-15 11-JAN-15

SQL> with r as
  2   (select to_date('2015-01-01', 'yyyy-mm-dd') res_date, 'RATE1' rate   from dual union all
  3    select to_date('2015-01-02', 'yyyy-mm-dd') res_date, 'RATE1' rate   from dual  union all
  4    select to_date('2015-01-03', 'yyyy-mm-dd') res_date, 'RATE2' rate  from dual union  all
  5    select to_date('2015-01-04', 'yyyy-mm-dd') res_date, 'RATE2' rate from dual union all
  6    select to_date('2015-01-05', 'yyyy-mm-dd') res_date, 'RATE2' rate from dual union all
  7    select to_date('2015-01-06', 'yyyy-mm-dd') res_date, 'RATE2' rate from dual union all
  8    select to_date('2015-01-07', 'yyyy-mm-dd') res_date, 'RATE2' rate  from dual union all
  9    select to_date('2015-01-08', 'yyyy-mm-dd') res_date, 'RATE1' rate from dual union all
 10    select to_date('2015-01-09', 'yyyy-mm-dd') res_date, 'RATE1' rate  from dual union all
 11    select to_date('2015-01-10', 'yyyy-mm-dd') res_date, '' rate  from dual union all
 12    select to_date('2015-01-11', 'yyyy-mm-dd') res_date, '' rate  from dual
 13  )
 14  select rm.*
 15    from r match_recognize(
 16              order by res_date
 17              measures rate as rate
 18                     , first(res_date) as date_from
 19                     , last(res_date) as date_till
 20  --          pattern(strt same_rate*)
 21              pattern(same_rate+)
 22              define same_rate as rate = first(rate) or (rate is null and first(rate) is null)
 23              ) rm
 24  /

RATE  DATE_FROM DATE_TILL
----- --------- ---------
RATE1 01-JAN-15 02-JAN-15
RATE2 03-JAN-15 07-JAN-15
RATE1 08-JAN-15 09-JAN-15
      10-JAN-15 11-JAN-15

SQL> 


SY.
19 мар 19, 22:43    [21837861]     Ответить | Цитировать Сообщить модератору
 Re: Упростить SQL запрос  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9364
Stax
мож и так, я null пріплел к тому что вариант SY не отработает (надо nvl, decode, ...)


Можно и без nvl/decode:

with r as
 (select to_date('2015-01-01', 'yyyy-mm-dd') res_date, 'RATE1' rate   from dual union all
  select to_date('2015-01-02', 'yyyy-mm-dd') res_date, 'RATE1' rate   from dual  union all
  select to_date('2015-01-03', 'yyyy-mm-dd') res_date, 'RATE2' rate  from dual union  all
  select to_date('2015-01-04', 'yyyy-mm-dd') res_date, 'RATE2' rate from dual union all
  select to_date('2015-01-05', 'yyyy-mm-dd') res_date, 'RATE2' rate from dual union all
  select to_date('2015-01-06', 'yyyy-mm-dd') res_date, 'RATE2' rate from dual union all
  select to_date('2015-01-07', 'yyyy-mm-dd') res_date, 'RATE2' rate  from dual union all
  select to_date('2015-01-08', 'yyyy-mm-dd') res_date, 'RATE1' rate from dual union all
  select to_date('2015-01-09', 'yyyy-mm-dd') res_date, 'RATE1' rate  from dual union all
  select to_date('2015-01-10', 'yyyy-mm-dd') res_date, '' rate  from dual union all
  select to_date('2015-01-11', 'yyyy-mm-dd') res_date, '' rate  from dual
)
select rm.*
  from r match_recognize(
            order by res_date
            measures rate as rate
                   , first(res_date) as date_from
                   , last(res_date) as date_till,
                   classifier() cls
            pattern(same_rate+|null_rate+)
            define same_rate as rate = first(rate),
                   null_rate as rate is null
            ) rm
/

RATE  DATE_FROM DATE_TILL CLS
----- --------- --------- ----------
RATE1 01-JAN-15 02-JAN-15 SAME_RATE
RATE2 03-JAN-15 07-JAN-15 SAME_RATE
RATE1 08-JAN-15 09-JAN-15 SAME_RATE
      10-JAN-15 11-JAN-15 NULL_RATE

SQL> 


SY.
19 мар 19, 22:53    [21837869]     Ответить | Цитировать Сообщить модератору
 Re: Упростить SQL запрос  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1645
SY
Stax
мож и так, я null пріплел к тому что вариант SY не отработает (надо nvl, decode, ...)


Можно и без nvl/decode:

SY.

конечно можно
но ето уже другой запрос (другие правила)
.....
stax
20 мар 19, 09:34    [21838056]     Ответить | Цитировать Сообщить модератору
 Re: Упростить SQL запрос  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17157
Stax
andrey_anonymous
не човчем :)

мож и так, я null пріплел к тому что вариант SY не отработает

Я всего лишь хотел привлечь внимание к тому, что наличие/отсутствие always-true переменной в шаблоне влияет на логику pattern matching и это стоит иметь ввиду, упрощая pattern - exceptions и прочих warning не будет :)
20 мар 19, 17:10    [21838751]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить