ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Найти минимальную дырку одной выборкой
48 сообщений из 48, показаны все 2 страниц
  все  
Найти минимальную дырку одной выборкой
    #39921083
PVSpichak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PVSpichak
Гость
Привет всем!
Дано таблица, с полем id number(16).
Данные в ней
15,16,17,20,21,22....
Задача найти одним select-ом первую
Дырку в поле id в диапазоне > 10
Как сделать с помощью двух я в курсе:
Первый select с помощью left join min
Второй с connect by подставляем из первого
Верхнюю границу, нижняя 10.
Чё то не могу их соединить, там затык,
то что в with не даёт подставлять select

Ну это так не к спеху, просто интересно кто как решал.
...
Рейтинг: 0 / 0
31.01.2020, 17:01
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921084
iOracleDev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iOracleDev
Участник
PVSpichak,

Чужой поток сознания никто и никак не решал, ибо не интересно.
...
Рейтинг: 0 / 0
31.01.2020, 17:03
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921086
Фотография AmKad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AmKad
Участник
lag/lead + group by
...
Рейтинг: 0 / 0
31.01.2020, 17:06
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921089
Фотография AmKad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AmKad
Участник
pattern matching
...
Рейтинг: 0 / 0
31.01.2020, 17:12
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921106
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
AmKadlag/lead + group by

А group by там зачем?
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
31.01.2020, 17:56
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921115
chidoriami
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chidoriami
Гость
Dimitry Sibiryakov,

min найти
...
Рейтинг: 0 / 0
31.01.2020, 18:31
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921118
chidoriami
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chidoriami
Гость
PVSpichak,

так, как ты написал, с доп запросом:

Код: 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.
with tipa_real as (
-- массив с дырками 5, 15, 50
    select level as id
      from dual
   connect by level <= 150
    minus
    (             select 5  from dual
        union all select 15 from dual
        union all select 50 from dual
    )
)
, set4search as (
-- цитата: "Второй с connect by подставляем из первого
--Верхнюю границу, нижняя 10"
    select level as id
      from dual
   connect by level <= (select max(id) from tipa_real)
)
select min(set4search.id)  min_lost_id
  from set4search
       left join tipa_real  on tipa_real.id = set4search.id
 where tipa_real.id is null 
   and set4search.id > 10
;



и вариант №2, с аналитикой:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
with tipa_real as (
-- массив с дырками 5, 15, 50
    select level as id
      from dual
   connect by level <= 150
    minus
    (             select 5  from dual
        union all select 15 from dual
        union all select 50 from dual
    )
)
select  min(id+1) min_lost_id
  from (
        select id
               ,lead(id) over (order by id)     next_id
          from tipa_real
       )
 where id+1 != next_id
   and id > 10
;
...
Рейтинг: 0 / 0
31.01.2020, 18:35
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921124
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
chidoriamimin найти

И?.. Автору же нужна одна первая дырка.

Код: sql
1.
select min(id) where lead(id) over (order by id) - id > 1


Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
31.01.2020, 18:47
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921160
oragraf
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
oragraf
Участник
PVSpichak,

Один запрос. Но без дырок он не может)
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
with hole_list as
( select -1 id from dual union all
  select 0 id from dual union all
  select 1 id from dual union all
  select 3 id from dual union all
  select 4 id from dual union all
  select 5 id from dual union all
  select 6 id from dual union all
  select 7 id from dual
)
select id + 1 hole, sign(coalesce(lead(id) over(order by id), id) - id - 1) magic
  from hole_list
 order by magic desc, hole
 fetch first 1 row only;
...
Рейтинг: 0 / 0
31.01.2020, 20:14
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921255
PVSpichak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PVSpichak
Гость
chidoriami спасибо первый вариант работает, второй нет, но понятно как допилить можно по аналогии с первым.

oragraf, ну вот че ты язвишь, на проверь свой вариант на:
create table test(id number(16));
begin
insert into test(id) values(15);
insert into test(id) values(16);
insert into test(id) values(17);
insert into test(id) values(20);
insert into test(id) values(21);
insert into test(id) values(22);
commit;
end;
че ты выдал? 18 а где же 11? Или 18 теперь считать за 11 :)?
может не понял чего я хочу, ну тогда извини.
...
Рейтинг: 0 / 0
01.02.2020, 04:37
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921262
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Вывод минимального значения, которого нет в таблице.
...
Рейтинг: 0 / 0
01.02.2020, 08:30
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921349
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
oragraf

Один запрос. Но без дырок он не может)


Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
with hole_list as
( select -1 id from dual union all
  select 0 id from dual union all
  select 1 id from dual union all
  select 2 id from dual union all
  select 3 id from dual union all
  select 4 id from dual union all
  select 5 id from dual
)
select  flag,
        case flag when 'first hole' then hole end hole
  from  hole_list
  match_recognize(
                  order by id
                  measures id + 1 as hole,
                           match_number() as mn,
                           classifier() flag
                  pattern("no holes"* "first hole"*)
                  define "first hole" as id != next(id) - 1,
                         "no holes" as id = next(id) - 1
                 )
  where mn = 1
/



 

Код: 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.
SQL> with hole_list as
  2  ( select -1 id from dual union all
  3    select 0 id from dual union all
  4    select 1 id from dual union all
  5    select 2 id from dual union all
  6    select 3 id from dual union all
  7    select 4 id from dual union all
  8    select 5 id from dual
  9  )
 10  select  flag,
 11          case flag when 'first hole' then hole end hole
 12    from  hole_list
 13    match_recognize(
 14                    order by id
 15                    measures id + 1 as hole,
 16                             match_number() as mn,
 17                             classifier() flag
 18                    pattern("no holes"* "first hole"*)
 19                    define "first hole" as id != next(id) - 1,
 20                           "no holes" as id = next(id) - 1
 21                   )
 22    where mn = 1
 23  /

FLAG             HOLE
---------- ----------
no holes

SQL> with hole_list as
  2  ( select -1 id from dual union all
  3  --  select 0 id from dual union all
  4  --  select 1 id from dual union all
  5  --  select 2 id from dual union all
  6    select 3 id from dual union all
  7    select 4 id from dual union all
  8    select 5 id from dual
  9  )
 10  select  flag,
 11          case flag when 'first hole' then hole end hole
 12    from  hole_list
 13    match_recognize(
 14                    order by id
 15                    measures id + 1 as hole,
 16                             match_number() as mn,
 17                             classifier() flag
 18                    pattern("no holes"* "first hole"*)
 19                    define "first hole" as id != next(id) - 1,
 20                           "no holes" as id = next(id) - 1
 21                   )
 22    where mn = 1
 23  /

FLAG             HOLE
---------- ----------
first hole          0

SQL>




SY.
...
Рейтинг: 0 / 0
01.02.2020, 19:20
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921413
PVSpichak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PVSpichak
Гость
Привет SY!
Спасибо за наводку по match_recognize,
но у тебя тоже, как ни странно получилось 18 :), повторяю условие найти в диапазоне > 10,
и в общем случае вовсе не обязательно наличие в таблице записи с id <=10 ровно как и наличия вообще записей :)
...
Рейтинг: 0 / 0
02.02.2020, 04:17
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921447
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
PVSpichakв общем случае вовсе не обязательно наличие в таблице записи с id <=10 ровно как и наличия
вообще записей :)

Это решается добавлением в запрос union all фиктивной записи с id = 9.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
02.02.2020, 13:26
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921482
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
забавно, казалось бы lag(id) over(order by id) при наличии первичного ключа по ID должен бы делать window nosort, но нет:
 
тестовые таблички
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
create TABLE hole_list1
as select level id from dual 
   where level!=100
   connect by level<=1e5
/
create TABLE hole_list2
as select level id from dual 
   where level!=20
   connect by level<=1e5
/
alter table hole_list1 add constraint hole_list1_pk primary key (id);
alter table hole_list2 add constraint hole_list2_pk primary key (id);


Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
   select prev_id+1
   from (
     select--+ index(hole_list1 (id))
        id,
        lag(id) over(order by id) prev_id
     from  hole_list1
     where id>10 
     order by id
   )
   where id != prev_id+1
   and rownum=1;

 
план
Код: 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.
Plan hash value: 3299310602

----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |      1 |        |       |     2 (100)|          |      1 |00:00:00.02 |     209 |       |       |          |
|*  1 |  COUNT STOPKEY      |               |      1 |        |       |            |          |      1 |00:00:00.02 |     209 |       |       |          |
|*  2 |   VIEW              |               |      1 |      2 |    52 |     2   (0)| 00:00:01 |      1 |00:00:00.02 |     209 |       |       |          |
|   3 |    WINDOW BUFFER    |               |      1 |      2 |    10 |     2   (0)| 00:00:01 |     90 |00:00:00.02 |     209 |  5297K|   950K| 4708K (0)|
|*  4 |     INDEX RANGE SCAN| HOLE_LIST1_PK |      1 |      2 |    10 |     2   (0)| 00:00:01 |  99989 |00:00:00.01 |     209 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$2 / from$_subquery$_001@SEL$1
   3 - SEL$2
   4 - SEL$2 / HOLE_LIST1@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM=1)
   2 - filter("ID"<>"PREV_ID"+1)
   4 - access("ID">10)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "PREV_ID"[NUMBER,22]
   2 - "ID"[NUMBER,22], "PREV_ID"[NUMBER,22]
   3 - (#keys=1) "ID"[NUMBER,22], "HOLE_LIST1".ROWID[ROWID,10], COUNT(*) OVER ( ORDER BY "ID" ROWS  BETWEEN 1 PRECEDING  AND 1 PRECEDING )[22],
       FIRST_VALUE("ID") OVER ( ORDER BY "ID" ROWS  BETWEEN 1 PRECEDING  AND 1 PRECEDING )[22]
   4 - "HOLE_LIST1".ROWID[ROWID,10], "ID"[NUMBER,22]


обратите внимание на секцию проекций
...
Рейтинг: 0 / 0
02.02.2020, 17:12
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921485
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
в общем, в случае если есть индекс и операция сортировки не нужна, то опять же pl/sql вариант будет самым лучшим:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
with function first_hole (x int) return number as 
  prev_id hole_list1.id%type;
begin
   for r in (select id from hole_list1 where id>x order by id) loop
      if prev_id +1 != r.id then
         return prev_id+1;
      end if;
      prev_id:= r.id;
   end loop;
end;
select first_hole(10) from dual;
/
...
Рейтинг: 0 / 0
02.02.2020, 17:18
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921486
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
xtender
window nosort
маленькое уточнение - window buffer, конечно, тоже операция без сортировки, но я имел ввиду, что window buffer не будет требовать всех строк из дочернего row-source
...
Рейтинг: 0 / 0
02.02.2020, 17:23
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921547
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
xtender,

а если дырок нет, что вернет?
...
Рейтинг: 0 / 0
02.02.2020, 22:51
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921550
oragraf
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
oragraf
Участник
andreymx,

ORA-xxxxx Функция без ретурна - деньги на ветер
...
Рейтинг: 0 / 0
02.02.2020, 23:03
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921551
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
andreymx,

А что такое "дырка" при отсутствии верхней границы?
...
Рейтинг: 0 / 0
02.02.2020, 23:07
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921557
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
xtender
andreymx,

А что такое "дырка" при отсутствии верхней границы?
это философский вопрос? даже не знаю, что ответить...
...
Рейтинг: 0 / 0
02.02.2020, 23:32
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921567
oragraf
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
oragraf
Участник
xtender,

это автора бы неплохо спросить.
...
Рейтинг: 0 / 0
03.02.2020, 00:40
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921571
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
andreymx,
oragraf,

Мне это неинтересно. При желании легко допилить как потребуется. Мне тут интересно было только то, что в случае индекса нет идеального sql варианта, те возможности обойтись без полного вычитывания или же нестед лупс с кучей irs/ius. Вкратце, lead/lag не идеальны.
...
Рейтинг: 0 / 0
03.02.2020, 01:15
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921584
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
-- в терминах таблиц, определенных в 22071158
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
With T(id,next_id) as (
  -- фиксируем стартовое значение, искать будем следующее за ним
  Select  10 , 10  From dual
  Union All
  Select T.next_id + 1
  ,(Select Min(h1.id)
     From hole_list1 h1
     Where h1.id> next_id 
    ) 
  From T
  Where 1 = 1 
  And (id - next_id + 1)>  0
)
Select  
   Max(ID) as free_value
from T
;



PS
вроде бы xtender должен был бы сначала такое показать, а потом lead/lag исследовать...
странно это всё...
...
Рейтинг: 0 / 0
03.02.2020, 03:09
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921585
PVSpichak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PVSpichak
Гость
Привет, по верхней границе:
Ну, так, давайте уточним, есть диапазон id number(16) > 10, есть таблица test(id number(16) not null primary key) c записями, или пустая, нужно выдать первую дырку (minHole = минимальное целое, при отсутствие в таблице строки с id=minHole) в диапазоне > 10.
По-моему корректное условие.
...
Рейтинг: 0 / 0
03.02.2020, 03:18
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921613
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
xtender
Мне тут интересно было только то, что в случае индекса нет идеального sql варианта, те возможности обойтись без полного вычитывания или же нестед лупс с кучей irs/ius. Вкратце, lead/lag не идеальны.
Саян, ты видишь академизм там, где он совсем не нужен. Там, где ищут дырки, миллисекунды не важны.
...
Рейтинг: 0 / 0
03.02.2020, 07:47
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921620
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
Elic
xtender
Мне тут интересно было только то, что в случае индекса нет идеального sql варианта, те возможности обойтись без полного вычитывания или же нестед лупс с кучей irs/ius. Вкратце, lead/lag не идеальны.
Саян, ты видишь академизм там, где он совсем не нужен. Там, где ищут дырки, миллисекунды не важны.
а как же концепция, что сервер - это ограниченный ресурс, и снижение каждой миллисекунды нагрузки важно для системы
...
Рейтинг: 0 / 0
03.02.2020, 08:44
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921789
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
Elic,

Дырки не важны, важно лишь то, что lead/lag буферизуют все строки дочернего row source, даже когда не нужно.
...
Рейтинг: 0 / 0
03.02.2020, 13:35
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39921794
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
booby,

Прочти весь топик. Твоя идея очевидна и много раз на форуме уже упоминалась, а в данном случае ещё и очевидно неоптимальна. В моем сообщении выше я уже объяснял почему.
...
Рейтинг: 0 / 0
03.02.2020, 13:37
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39922225
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
xtender
... Твоя идея очевидна...

Ах, ты же знаешь, это не моя идея, и знаешь где именно я её списал.
Я и запросов-то таких писать не умею.

xtender

... а в данном случае ещё и очевидно неоптимальна....

Каком и как именно данном?
Вообще-то "очевидно", что объем ввода/вывода для такого варианта есть функция расстояния до ближайшей дырки.
Он может как выигрывать, так и проигрывать полному линейному просмотру, в зависимости от "данного" распределения дыр.

Но ёж с ужом вполне разумно комбинируется.
Сейчас мне показалась занятной такая идея,
накомбинированная из упомянутого в топике.
(на твоем же примере определения таблицы):

Код: 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.
With T0 as (
  Select 
    cast(10 as Number) as pStart  -- стартовое значение
  , cast(1000 as Number) as pStep -- размер окна подсчета
  From sys.dual
)
, TW(pStart, pStep, nPlan, nTotal, lv)
as (
   -- спасибо xtender-у
  Select 
    pStart, pStep, pStep as nPlan, 
    (Select Count(*) 
     From hole_list1 h1  
     Where h1.id Between T0.Pstart+1 And (T0.Pstart + T0.pStep )
    )
    , 1 lv
  From T0
 Union All  
 Select 
     (TW.Pstart + TW.Pstep ) as pStart
   , TW.pStep
   , TW.pStep
   , (
   Select Count(*)
   From hole_list1 h1  
   Where h1.id Between (TW.Pstart + TW.Pstep + 1) And (TW.Pstart + 2*TW.Pstep)
   ) as nTotal 
   , TW.lv + 1 as lv
 From TW
 Where 1 = 1 
 And nplan = ntotal
)
CYCLE pStart SET cyclemark TO 'X' DEFAULT '-'
, TT as (
  Select *
  From TW
Where nplan != nTotal
)
-------------------
Select  /*+ gather_plan_statistics */
         /* COMBI_B */
   -- спасибо Elic-у
   coalesce(min(case when id <> rn And rn > pstart then rn 
                     when id <> rn And rn = pstart then rn + 1
                end
                )
           , min(rn) + 1) 
From (
    Select 
     TT.pStart - 1 + row_number() over (order by h1.id) as rn
    , TT.* 
    , H1.*
    from TT, Lateral(
      Select h1.id
      From hole_list1 h1
      Where h1.id Between TT.pStart And TT.pStart + TT.pStep
    )(+) H1
) Q
;



благодарности в тексте принесены с целью элиминации возможностью тырцканья в меня "моими идеями".
Нет у меня никаких идей.
...
Рейтинг: 0 / 0
04.02.2020, 12:39
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39922246
iOracleDev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iOracleDev
Участник
oragraf
это автора бы неплохо спросить.

Вы просто не читали, автор уже писал раза два, в диапазоне больше 10, даже при отсутствии записей результат должен быть 11. Решение xtender-а легко допиливается при необходимости, не понимаю зачем вы развели демагогию на ровном месте.
...
Рейтинг: 0 / 0
04.02.2020, 12:59
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39922500
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
booby
идея
боже мой, столько слов... голову бы включил - с лишними IRS/IUS все гораздо легче делается:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
with next as (
  select id 
  from (  
     select  id, rownum+10 rn
     from  hole_list1
     where id>10
     order by id
   )
  where rn<id and rownum=1
 )
select
  max(id)+1 hole
from hole_list1
where id<(select id from next);

 
plan
Код: 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.
Plan hash value: 30339988

-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       4 |      2 |
|   1 |  SORT AGGREGATE              |               |      1 |      1 |     5 |            |          |      1 |00:00:00.01 |       4 |      2 |
|   2 |   FIRST ROW                  |               |      1 |      1 |     5 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |      2 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| HOLE_LIST2_PK |      1 |      1 |     5 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |      2 |
|   4 |     VIEW                     |               |      1 |      1 |    13 |   210   (1)| 00:00:01 |      1 |00:00:00.01 |       2 |      2 |
|*  5 |      COUNT STOPKEY           |               |      1 |        |       |            |          |      1 |00:00:00.01 |       2 |      2 |
|*  6 |       VIEW                   |               |      1 |  99990 |  2538K|   210   (1)| 00:00:01 |      1 |00:00:00.01 |       2 |      2 |
|   7 |        COUNT                 |               |      1 |        |       |            |          |     10 |00:00:00.01 |       2 |      2 |
|*  8 |         INDEX RANGE SCAN     | HOLE_LIST2_PK |      1 |  99990 |   488K|   210   (1)| 00:00:01 |     10 |00:00:00.01 |       2 |      2 |
-------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$3
   3 - SEL$3 / HOLE_LIST2@SEL$3
   4 - SEL$1 / NEXT@SEL$4
   5 - SEL$1
   6 - SEL$2 / from$_subquery$_001@SEL$1
   7 - SEL$2
   8 - SEL$2 / HOLE_LIST2@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ID"<)
   5 - filter(ROWNUM=1)
   6 - filter("RN"<"ID")
   8 - access("ID">10)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) MAX("ID")[22]
   2 - "ID"[NUMBER,22]
   3 - "ID"[NUMBER,22]
   4 - "ID"[NUMBER,22]
   5 - "ID"[NUMBER,22]
   6 - "ID"[NUMBER,22], "RN"[NUMBER,22]
   7 - "ID"[NUMBER,22], ROWNUM[8]
   8 - "ID"[NUMBER,22]

...
Рейтинг: 0 / 0
04.02.2020, 20:29
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39922515
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
xtender,
ты, чем к небу закатывать глаза, лучше бы следил за тем, что пишешь.
результат не соответствует первоначальной постановке,
а) если 10 попадает в середину дырки или
б) весь интервал значений правее 10.

а так текст хорош.
Садись, пять.
...
Рейтинг: 0 / 0
04.02.2020, 21:23
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39922518
iOracleDev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iOracleDev
Участник
booby,

так соответствует первоначальной постановке?
Код: plsql
1.
greatest(nvl(max(id)+1, 0), 11) hole
...
Рейтинг: 0 / 0
04.02.2020, 21:41
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39922519
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
booby
лучше бы следил за тем, что пишешь
вот пристал как банный лист... Я показал идею, допиливать и проверять граничные условия мне лень и абсолютно неинтересно.
Даже страшно подумать, если ты считаешь что твоя портянка чуть выше "проверена"...
...
Рейтинг: 0 / 0
04.02.2020, 21:46
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39922522
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
xtender
booby
лучше бы следил за тем, что пишешь
вот пристал как банный лист... Я показал идею, допиливать и проверять граничные условия мне лень и абсолютно неинтересно.
Даже страшно подумать, если ты считаешь что твоя портянка чуть выше "проверена"...

ой, ты уж не пугайся так.
мне-то и включать нечего.
я вот твоему "совету" последовал, и весь топик прочитал.
а было бы чем считать - и читать бы не стал - зачем читать чужие идеи, неинтересные их собственным авторам.
...
Рейтинг: 0 / 0
04.02.2020, 22:08
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39922523
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
booby,

Исправь для начала собственную портянку...
...
Рейтинг: 0 / 0
04.02.2020, 22:11
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39922535
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
xtender
booby,

Исправь для начала собственную портянку...

исправил:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
[SRC oracle]   
coalesce(
        min(case when id <> rn And rn > pstart then rn 
                      when id <> rn And rn = pstart then rn + 1
               end
             )
         , max(rn) + 1
          )

[/SRC]
а за "для начала" - ... тебе, а не уважуха и респект.
хотя за указание спасибо.
...
Рейтинг: 0 / 0
04.02.2020, 23:03
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39922558
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
booby,

посмотрел еще раз, признаю, что вариант не годится.
левая граница за шаг до её начала не ловится, воспринимается как дырка.
исправление в этом варианте требует явного знания о её начале, и не стоит тех усилий.
...
Рейтинг: 0 / 0
05.02.2020, 01:14
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39922610
Фотография AmKad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AmKad
Участник
Данная тема набрала уже тысячу просмотров. Я всегда говорил, что правильно подобрать название темы - это искусство.
...
Рейтинг: 0 / 0
05.02.2020, 09:09
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39922651
merch
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
merch
Участник
AmKadДанная тема набрала уже тысячу просмотров.


Изучив статистику по другим темам, можно сделать вывод, что люди больше любят курить и смеяться, чем исследовать дыры...
...
Рейтинг: 0 / 0
05.02.2020, 10:15
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39922929
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
AmKad
правильно подобрать название темы
...
Рейтинг: 0 / 0
05.02.2020, 15:14
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39923565
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
PVSpichak
Привет SY!
Спасибо за наводку по match_recognize,
но у тебя тоже, как ни странно получилось 18 :), повторяю условие найти в диапазоне > 10,
и в общем случае вовсе не обязательно наличие в таблице записи с id <=10 ровно как и наличия вообще записей :)


Код: 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.
with hole_list_adjusted as (
                            select * from hole_list union
                            select 10 from dual
                           )
select  case
          when flag = 'first hole' then flag
          when hole = 11 then 'first hole'
          else flag
        end flag,
        case
          when flag = 'first hole' then hole
          when hole = 11 then 11
        end hole
  from  hole_list_adjusted
  match_recognize(
                  order by id
                  measures "no holes".id + 1 as hole,
                           match_number() as mn,
                           classifier() flag
                  pattern("no holes"* "first hole"?)
                  define "first hole" as id != prev(id) + 1,
                         "no holes" as id <= 10 or id = greatest(nvl(prev(id),10),10) + 1
                 ) x
  where mn = 1
/



SY.
...
Рейтинг: 0 / 0
06.02.2020, 17:20
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39926156
andrey odegov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey odegov
Участник
Код: 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.
with
  id_holes as (
    select -1 id from dual union all
    select 0 from dual union all
    select 1 from dual union all
    select 2 from dual union all
    select 3 from dual union all
    select 4 from dual union all
    select 14 from dual union all
    select 15 from dual union all
    select 16 from dual union all
    select 17 from dual union all
    select 21 from dual union all
    select 22 from dual union all
    select 5 from dual
  )
select hole
from id_holes
where id > 10
model return updated rows
  dimension by (id)
  measures (id hole)
  rules iterate (1e9) until hole[iteration_number + 11] is null (
    hole[1] = iteration_number + 11
  )
/
...
Рейтинг: 0 / 0
13.02.2020, 11:14
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39926348
Фотография Кобанчег
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Кобанчег
Участник
andrey odegov
Код: plsql
1.
... model ... iterate ...

Этот подход полностью читает таблицу в память и потом еще итерирует над прочитанным набором.
Стыдно должно быть показывать такое в культурном обществе когда тут борьба за миллисекунды.

Единственным плюсом является то, что в левой части правил ссылка на одну конкрутную ячейку поэтому в плане MODEL FAST.
Но после full scan'a это уже имеет мало значения.

Ну и решение вообще неверное.
...
Рейтинг: 0 / 0
13.02.2020, 16:49
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39926664
andrey odegov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey odegov
Участник
Кобанчег
andrey odegov
Код: plsql
1.
... model ... iterate ...

... Ну и решение вообще неверное.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
select nvl2(nxt, 'hole', 'no holes') flag,
       nvl2(nxt, hole, nxt) hole
from id_holes
where id > 10
model return updated rows
  dimension by (id)
  measures (id hole, id nxt)
  rules iterate (1e9) until hole[iteration_number + 11] is null (
    hole[1] = iteration_number + 11,
    nxt[1] = min(hole)[id > iteration_number + 11]
  )
/
...
Рейтинг: 0 / 0
14.02.2020, 12:57
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39926777
clipper1995
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
clipper1995
Гость
PVSpichak,
много интересного тут написали, всем спасибо, надо будет посмотреть внимательно.

Если чисто по исходной задаче - я бы сделал более примитивно :)

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
SELECT * from (
  SELECT seq.id FROM (
              SELECT level as id FROM dual CONNECT BY rownum <= (SELECT max(id) FROM sometable)
         ) seq
  WHERE not exists (SELECT id FROM sometable where sometable.id = seq.id)
  AND seq.id > 10  
  ORDER BY seq.id
) 
WHERE rownum = 1
...
Рейтинг: 0 / 0
14.02.2020, 16:10
| Ответить | Цитировать | Написать  
Найти минимальную дырку одной выборкой
    #39926778
clipper1995
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
clipper1995
Гость
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
SELECT * from (
  SELECT seq.id FROM (
              SELECT level as id FROM dual CONNECT BY rownum <= (SELECT max(id) FROM sometable)
         ) seq
  WHERE not exists (SELECT id FROM sometable where sometable.id = seq.id)
  AND seq.id > 10  
  ORDER BY seq.id
) 
WHERE rownum = 1
...
Рейтинг: 0 / 0
14.02.2020, 16:16
| Ответить | Цитировать | Написать  
48 сообщений из 48, показаны все 2 страниц
  все  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Найти минимальную дырку одной выборкой
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=52&tid=1881560]:
 0ms
get settings:
 9ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 62ms
get topic data:
 14ms
get forum data:
 3ms
get page messages:
 87ms
get tp. blocked users:
 2ms
others: 269ms
total:  468ms
созд. / загр.: 468ms
x
x
Закрыть


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