Гость
Целевая тема:
Создать новую тему:
Автор:
Форумы / Oracle [игнор отключен] [закрыт для гостей] / альтернатива моему запросу / 24 сообщений из 24, страница 1 из 1
01.02.2019, 14:17
    #39768200
альтернатива моему запросу
Всем привет,

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

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

Вот данные вместе с решением:
Код: 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.
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



Код: plaintext
1.
2.
3.
4.
5.
6.
7.
        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.

Заранее спасибо.
...
Рейтинг: 0 / 0
01.02.2019, 14:48
    #39768232
chidoriami
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
альтернатива моему запросу
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)
;
...
Рейтинг: 0 / 0
01.02.2019, 15:02
    #39768243
альтернатива моему запросу
chidoriami,

спасибо. Оригинально.
...
Рейтинг: 0 / 0
01.02.2019, 15:04
    #39768244
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
альтернатива моему запросу
Hans Christian Andersen,
total.part,total.datum уникальные?

....
stax
...
Рейтинг: 0 / 0
01.02.2019, 15:05
    #39768249
альтернатива моему запросу
Stax,

да
...
Рейтинг: 0 / 0
01.02.2019, 15:06
    #39768251
альтернатива моему запросу
Hans Christian Andersen,

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

Oracle 12.1.0.2.0
...
Рейтинг: 0 / 0
01.02.2019, 15:17
    #39768258
альтернатива моему запросу
chidoriami,

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

Код: plsql
1.
2.
3.
4.
5.
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
;
...
Рейтинг: 0 / 0
01.02.2019, 15:28
    #39768267
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
альтернатива моему запросу
Hans Christian Andersen,

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

.....
stax
...
Рейтинг: 0 / 0
01.02.2019, 15:31
    #39768270
альтернатива моему запросу
Stax,

конечно UNIQUE.
...
Рейтинг: 0 / 0
01.02.2019, 16:00
    #39768297
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
альтернатива моему запросу
Hans Christian Andersenchidoriami,

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


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

не вариант решения
так для попробовать, в надежде на STOPKEY
Код: plsql
1.
2.
3.
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
...
Рейтинг: 0 / 0
01.02.2019, 16:16
    #39768309
альтернатива моему запросу
Stax,

А я полного чтения в моем решение избежал. Просто коду получилось многовато. А мне из этого решение нужно ODI-Mapping создать.
...
Рейтинг: 0 / 0
01.02.2019, 16:25
    #39768324
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
альтернатива моему запросу
Hans Christian AndersenStax,

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

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

....
stax
...
Рейтинг: 0 / 0
01.02.2019, 16:53
    #39768354
-2-
-2-
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
альтернатива моему запросу
Hans Christian Andersenполного чтения в моем решение избежалЕсли korr попадает в более определенного процента блоков total, последовательный фулскан может быть выгоднее поштучного заглядывания через индекс. Хотя к этому еще сортировка по полному объему.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
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
;
...
Рейтинг: 0 / 0
01.02.2019, 19:08
    #39768458
альтернатива моему запросу
-2-,

думаю, что селект по индексу будет на много быстрее.
total 10 M строк
korr 100 строк
...
Рейтинг: 0 / 0
01.02.2019, 19:17
    #39768466
-2-
-2-
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
альтернатива моему запросу
Hans Christian Andersenkorr 100 строкНу, тогда попробуй мой вариант, но с inner join только по part. count тогда поменять на условный.
...
Рейтинг: 0 / 0
01.02.2019, 22:53
    #39768553
альтернатива моему запросу
-2-,

???
...
Рейтинг: 0 / 0
02.02.2019, 21:47
    #39768768
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
альтернатива моему запросу
Hans Christian Andersen,

У Вас с двойкой несколько разные запросы
сравните напр на
Код: plsql
1.
2.
3.
4.
5.
6.
7.
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
...
Рейтинг: 0 / 0
03.02.2019, 09:13
    #39768825
альтернатива моему запросу
Stax,


Не пробовал. Очень уж он не решительно этот запрос описал.
...
Рейтинг: 0 / 0
03.02.2019, 09:53
    #39768830
альтернатива моему запросу
Hans Christian Andersen,

Sorry. Вернее вы.
Сегодня попробую
...
Рейтинг: 0 / 0
04.02.2019, 10:00
    #39769081
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
альтернатива моему запросу
Hans Christian Andersen,

на заветах -2-
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
 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
...
Рейтинг: 0 / 0
04.02.2019, 11:49
    #39769129
MaximaXXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
альтернатива моему запросу
Stax,

А чего не так?
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
    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
...
Рейтинг: 0 / 0
04.02.2019, 12:15
    #39769145
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
альтернатива моему запросу
MaximaXXL,

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

напр для

Код: 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.
57.
58.
59.
60.
61.
62.
63.
64.
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
более правильно
Код: plsql
1.
2.
3.
4.
5.
6.
        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
...
Рейтинг: 0 / 0
04.02.2019, 17:53
    #39769358
альтернатива моему запросу
Stax,

сорри. Была ошибка в моем решение. То, что ты предложил - правильно.
...
Рейтинг: 0 / 0
04.02.2019, 18:04
    #39769365
альтернатива моему запросу
Hans Christian Andersen,
вот это должен выдать селект

Код: plaintext
1.
2.
3.
4.
5.
        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
...
Рейтинг: 0 / 0
Форумы / Oracle [игнор отключен] [закрыт для гостей] / альтернатива моему запросу / 24 сообщений из 24, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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