powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Форумы / Oracle [игнор отключен] [закрыт для гостей] / альтернатива моему запросу
24 сообщений из 24, страница 1 из 1
альтернатива моему запросу
    #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
альтернатива моему запросу
    #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
альтернатива моему запросу
    #39768243
chidoriami,

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

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

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

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

Oracle 12.1.0.2.0
...
Рейтинг: 0 / 0
альтернатива моему запросу
    #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
альтернатива моему запросу
    #39768267
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Hans Christian Andersen,

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

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

конечно UNIQUE.
...
Рейтинг: 0 / 0
альтернатива моему запросу
    #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
альтернатива моему запросу
    #39768309
Stax,

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

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

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

....
stax
...
Рейтинг: 0 / 0
альтернатива моему запросу
    #39768354
Фотография -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
альтернатива моему запросу
    #39768458
-2-,

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

???
...
Рейтинг: 0 / 0
альтернатива моему запросу
    #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
альтернатива моему запросу
    #39768825
Stax,


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

Sorry. Вернее вы.
Сегодня попробую
...
Рейтинг: 0 / 0
альтернатива моему запросу
    #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
альтернатива моему запросу
    #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
альтернатива моему запросу
    #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
альтернатива моему запросу
    #39769358
Stax,

сорри. Была ошибка в моем решение. То, что ты предложил - правильно.
...
Рейтинг: 0 / 0
альтернатива моему запросу
    #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
24 сообщений из 24, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / альтернатива моему запросу
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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