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

Откуда: зачем Вам?
Сообщений: 243
Всем привет,

можно ли пару альтернатив к моему запросу?
Имеется две таблицы total и korr.
Причем, известно, что содержимое таблицы korr - это часть содержимого в total.
Нужно найти к каждой строки korr (partition by part) предыдущую и последующую строку по datum из total.

Select должен возвращать:
1. строку из korr
2. предыдущую стоку (к строке из korr) из total
3. Последующую стоку (к строке из korr) из total

Вот данные вместе с решением:
with
total
as
(select 1 as id, 'ABC' as part, to_date('01.01.2019','dd.mm.yyyy') as datum, 5 as wert_gesamt from dual
union all
select 2 as id, 'ABC' as part,to_date('02.01.2019','dd.mm.yyyy') as datum, 10 as wert_gesamt from dual
union all
select 3 as id,'ABC' as part,to_date('03.01.2019','dd.mm.yyyy') as datum, 15 as wert_gesamt from dual
union all
select 4 as id,'ABC' as part,to_date('04.01.2019','dd.mm.yyyy') as datum, 26 as wert_gesamt from dual
union all
select 5 as id,'ABC' as part,to_date('05.01.2019','dd.mm.yyyy') as datum, 30 as wert_gesamt from dual
union all
select 6 as id,'AAA' as part,to_date('05.01.2019','dd.mm.yyyy') as datum, 8 as wert_gesamt from dual
union all
select 7 as id, 'AAA' as part,to_date('06.01.2019','dd.mm.yyyy') as datum, 12 as wert_gesamt from dual
),
korr
as
(
select 3 as id,'ABC' as part,to_date('03.01.2019','dd.mm.yyyy') as datum, 15 as wert_gesamt from dual
)
,
with_ranking
as
(
    select total.id,
           total.part,
           total.datum,
           total.wert_gesamt ,
           rank() over (partition by total.part order by total.datum desc)as ranking --down
    from korr, total
    where korr.part = total.part
    and korr.datum >= total.datum
    UNION
    select total.id,
           total.part,
           total.datum,
           total.wert_gesamt ,
           rank() over (partition by total.part order by total.datum)as ranking --forward
    from korr, total
    where korr.part = total.part
    and korr.datum <= total.datum
)
select * from with_ranking
where ranking <= 2


        ID PART DATUM    WERT_GESAMT    RANKING
---------- ---- -------- ----------- ----------
2 ABC 02.01.19 10 2
3 ABC 03.01.19 15 1
4 ABC 04.01.19 26 2

3 rows selected.

Заранее спасибо.
1 фев 19, 14:17    [21799703]     Ответить | Цитировать Сообщить модератору
 Re: альтернатива моему запросу  [new]
chidoriami
Member

Откуда: москва
Сообщений: 14
Hans Christian Andersen,

select *
from (
select total.*
,lag(total.datum) over (partition by total.part order by total.datum) as prev_datum
,lead(total.datum) over (partition by total.part order by total.datum) as next_datum
from total
where 1=1
)
total
left join korr
on korr.part = total.part
where 1=1
and (korr.datum = total.datum or korr.datum = total.prev_datum or korr.datum = total.next_datum)
;
1 фев 19, 14:48    [21799762]     Ответить | Цитировать Сообщить модератору
 Re: альтернатива моему запросу  [new]
Hans Christian Andersen
Member

Откуда: зачем Вам?
Сообщений: 243
chidoriami,

спасибо. Оригинально.
1 фев 19, 15:02    [21799781]     Ответить | Цитировать Сообщить модератору
 Re: альтернатива моему запросу  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1597
Hans Christian Andersen,
total.part,total.datum уникальные?

....
stax
1 фев 19, 15:04    [21799782]     Ответить | Цитировать Сообщить модератору
 Re: альтернатива моему запросу  [new]
Hans Christian Andersen
Member

Откуда: зачем Вам?
Сообщений: 243
Stax,

да
1 фев 19, 15:05    [21799788]     Ответить | Цитировать Сообщить модератору
 Re: альтернатива моему запросу  [new]
Hans Christian Andersen
Member

Откуда: зачем Вам?
Сообщений: 243
Hans Christian Andersen,

P.S. нужно, что бы запрос быстро работал.

Oracle 12.1.0.2.0
1 фев 19, 15:06    [21799790]     Ответить | Цитировать Сообщить модератору
 Re: альтернатива моему запросу  [new]
Hans Christian Andersen
Member

Откуда: зачем Вам?
Сообщений: 243
chidoriami,

Проблеиа в вашем решение в том, что из за 1 строки в korr вы должы всю таблицу total читать.

select total.*
,lag(total.datum) over (partition by total.part order by total.datum) as prev_datum
,lead(total.datum) over (partition by total.part order by total.datum) as next_datum
from total
;
1 фев 19, 15:17    [21799805]     Ответить | Цитировать Сообщить модератору
 Re: альтернатива моему запросу  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1597
Hans Christian Andersen,

индекс по total.part,total.datum есть?

.....
stax
1 фев 19, 15:28    [21799815]     Ответить | Цитировать Сообщить модератору
 Re: альтернатива моему запросу  [new]
Hans Christian Andersen
Member

Откуда: зачем Вам?
Сообщений: 243
Stax,

конечно UNIQUE.
1 фев 19, 15:31    [21799819]     Ответить | Цитировать Сообщить модератору
 Re: альтернатива моему запросу  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1597
Hans Christian Andersen
chidoriami,

Проблеиа в вашем решение в том, что из за 1 строки в korr вы должы всю таблицу total читать.


я так понимаю Вы хотите избежать полного чтения тотал

не вариант решения
так для попробовать, в надежде на STOPKEY
select * from korr k
,lateral (select * from total t where t.part=k.part and t.datum<k.datum order by t.datum desc FETCH NEXT 1 ROWS ONLY)(+)
,lateral (select * from total t where t.part=k.part and t.datum>k.datum order by t.datum asc  FETCH NEXT 1 ROWS ONLY)(+)


.....
stax
1 фев 19, 16:00    [21799859]     Ответить | Цитировать Сообщить модератору
 Re: альтернатива моему запросу  [new]
Hans Christian Andersen
Member

Откуда: зачем Вам?
Сообщений: 243
Stax,

А я полного чтения в моем решение избежал. Просто коду получилось многовато. А мне из этого решение нужно ODI-Mapping создать.
1 фев 19, 16:16    [21799875]     Ответить | Цитировать Сообщить модератору
 Re: альтернатива моему запросу  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1597
Hans Christian Andersen
Stax,

А я полного чтения в моем решение избежал. Просто коду получилось многовато. А мне из этого решение нужно ODI-Mapping создать.


UNION - тяжелая операция
я б заменил на UNION ALL (>= | <= на >|<)

....
stax
1 фев 19, 16:25    [21799894]     Ответить | Цитировать Сообщить модератору
 Re: альтернатива моему запросу  [new]
-2-
Member

Откуда:
Сообщений: 14350
Hans Christian Andersen
полного чтения в моем решение избежал
Если korr попадает в более определенного процента блоков total, последовательный фулскан может быть выгоднее поштучного заглядывания через индекс. Хотя к этому еще сортировка по полному объему.
select *
from (
    select
       v.*, count(v.kid) over(partition by v.part order by v.datum rows between 1 preceding and 1 following) f
    from (
        select t.*, k.id kid
        from total t
        left join korr k on k.part=t.part and k.datum = t.datum
    ) v
) w
where w.f > 0
;
1 фев 19, 16:53    [21799940]     Ответить | Цитировать Сообщить модератору
 Re: альтернатива моему запросу  [new]
Hans Christian Andersen
Member

Откуда: зачем Вам?
Сообщений: 243
-2-,

думаю, что селект по индексу будет на много быстрее.
total 10 M строк
korr 100 строк
1 фев 19, 19:08    [21800077]     Ответить | Цитировать Сообщить модератору
 Re: альтернатива моему запросу  [new]
-2-
Member

Откуда:
Сообщений: 14350
Hans Christian Andersen
korr 100 строк
Ну, тогда попробуй мой вариант, но с inner join только по part. count тогда поменять на условный.
1 фев 19, 19:17    [21800085]     Ответить | Цитировать Сообщить модератору
 Re: альтернатива моему запросу  [new]
Hans Christian Andersen
Member

Откуда: зачем Вам?
Сообщений: 243
-2-,

???
1 фев 19, 22:53    [21800209]     Ответить | Цитировать Сообщить модератору
 Re: альтернатива моему запросу  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1597
Hans Christian Andersen,

У Вас с двойкой несколько разные запросы
сравните напр на
korr
as
(
select 2 as id, 'ABC' as part,to_date('02.01.2019','dd.mm.yyyy') as datum, 10 as wert_gesamt from dual
union all
select 3 as id,'ABC' as part,to_date('03.01.2019','dd.mm.yyyy') as datum, 15 as wert_gesamt from dual
)


зи
с латералом бистродействие не сравнивали?

....
stax
2 фев 19, 21:47    [21800565]     Ответить | Цитировать Сообщить модератору
 Re: альтернатива моему запросу  [new]
Hans Christian Andersen
Member

Откуда: зачем Вам?
Сообщений: 243
Stax,


Не пробовал. Очень уж он не решительно этот запрос описал.
3 фев 19, 09:13    [21800680]     Ответить | Цитировать Сообщить модератору
 Re: альтернатива моему запросу  [new]
Hans Christian Andersen
Member

Откуда: зачем Вам?
Сообщений: 243
Hans Christian Andersen,

Sorry. Вернее вы.
Сегодня попробую
3 фев 19, 09:53    [21800687]     Ответить | Цитировать Сообщить модератору
 Re: альтернатива моему запросу  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1597
Hans Christian Andersen,

на заветах -2-
 47  select *
 48  from (
 49      select
 50         v.*, count((select 1 from korr k where k.part=v.part and k.datum = v.datum))
 51              over(partition by v.part order by v.datum rows between 1 preceding and 1 following) f
 52      from (
 53          select t.*
 54          from total t
 55          where exists (select 1 from korr k where k.part=t.part)
 56      ) v
 57  ) w
 58* where w.f > 0
SQL> /

SQL> /

        ID PAR DATUM    WERT_GESAMT          F
---------- --- -------- ----------- ----------
         1 ABC 01.01.19           5          1
         2 ABC 02.01.19          10          2
         3 ABC 03.01.19          15          2
         4 ABC 04.01.19          26          1


зы
важно что надо получить
если 21799703 правильний
то возможно достаточно count(decode(t.datum,k_datum,1)) over ...


.....
stax
4 фев 19, 10:00    [21801056]     Ответить | Цитировать Сообщить модератору
 Re: альтернатива моему запросу  [new]
MaximaXXL
Member

Откуда: Киев
Сообщений: 652
Stax,

А чего не так?
    select w.*
   from (
           select t.*,
           count(decode(t2.datum,t.datum,1))
               over(partition by t.part order by t.datum rows between 1 preceding and 1 following) f
           from korr t2, total t 
           where t2.part=t.part 
   ) w
  where w.f > 0
4 фев 19, 11:49    [21801139]     Ответить | Цитировать Сообщить модератору
 Re: альтернатива моему запросу  [new]
Stax
Member

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

не знаю каков правильный ответ (как должен выглядеть результат)

напр для

with
total
as
(select 1 as id, 'ABC' as part, to_date('01.01.2019','dd.mm.yyyy') as datum, 5 as wert_gesamt from dual
union all
select 2 as id, 'ABC' as part,to_date('02.01.2019','dd.mm.yyyy') as datum, 10 as wert_gesamt from dual
union all
select 3 as id,'ABC' as part,to_date('03.01.2019','dd.mm.yyyy') as datum, 15 as wert_gesamt from dual
union all
select 4 as id,'ABC' as part,to_date('04.01.2019','dd.mm.yyyy') as datum, 26 as wert_gesamt from dual
union all
select 5 as id,'ABC' as part,to_date('05.01.2019','dd.mm.yyyy') as datum, 30 as wert_gesamt from dual
union all
select 6 as id,'AAA' as part,to_date('05.01.2019','dd.mm.yyyy') as datum, 8 as wert_gesamt from dual
union all
select 7 as id, 'AAA' as part,to_date('06.01.2019','dd.mm.yyyy') as datum, 12 as wert_gesamt from dual
),
korr
as
(
select 2 as id, 'ABC' as part,to_date('02.01.2019','dd.mm.yyyy') as datum, 10 as wert_gesamt from dual
union all
select 3 as id,'ABC' as part,to_date('03.01.2019','dd.mm.yyyy') as datum, 15 as wert_gesamt from dual
)
,
with_ranking
as
(
    select total.id,
           total.part,
           total.datum,
           total.wert_gesamt ,
           rank() over (partition by total.part order by total.datum desc)as ranking --down
    from korr, total
    where korr.part = total.part
    and korr.datum >= total.datum
    UNION
    select total.id,
           total.part,
           total.datum,
           total.wert_gesamt ,
           rank() over (partition by total.part order by total.datum)as ranking --forward
    from korr, total
    where korr.part = total.part
    and korr.datum <= total.datum
)
    select w.*
   from (
           select t.*,
           count(decode(t2.datum,t.datum,1))
               over(partition by t.part order by t.datum rows between 1 preceding and 1 following) f
           from korr t2, total t 
           where t2.part=t.part 
   ) w
  where w.f > 0
/
SQL> /

        ID PAR DATUM    WERT_GESAMT          F
---------- --- -------- ----------- ----------
         2 ABC 02.01.19          10          1
         2 ABC 02.01.19          10          2
         3 ABC 03.01.19          15          2
         3 ABC 03.01.19          15          1


с учетом
автор
Нужно найти к каждой строки korr (partition by part) предыдущую и последующую строку по datum из total.

имхо
должна быть строка с ид 1 и 4
более правильно
        ID PAR DATUM    WERT_GESAMT        KID          F
---------- --- -------- ----------- ---------- ----------
         1 ABC 01.01.19           5                     1
         2 ABC 02.01.19          10          2          2
         3 ABC 03.01.19          15          3          2
         4 ABC 04.01.19          26                     1



но с учетом "к каждой строки korr" тож терзают сомнения
зы
латерал какраз исчет для каждой строки в корр перыдущую и следующую
но ето не вписывается в "альтернатива моему запросу", если считать что запрс правильный



.....
stax
4 фев 19, 12:15    [21801163]     Ответить | Цитировать Сообщить модератору
 Re: альтернатива моему запросу  [new]
Hans Christian Andersen
Member

Откуда: зачем Вам?
Сообщений: 243
Stax,

сорри. Была ошибка в моем решение. То, что ты предложил - правильно.
4 фев 19, 17:53    [21801489]     Ответить | Цитировать Сообщить модератору
 Re: альтернатива моему запросу  [new]
Hans Christian Andersen
Member

Откуда: зачем Вам?
Сообщений: 243
Hans Christian Andersen,
вот это должен выдать селект


ID PAR DATUM WERT_GESAMT KID F
---------- --- -------- ----------- ---------- ----------
1 ABC 01.01.19 5 1
2 ABC 02.01.19 10 2 2
3 ABC 03.01.19 15 3 2
4 ABC 04.01.19 26 1
4 фев 19, 18:04    [21801497]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить