ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Найти минимальную дырку одной выборкой
25 сообщений из 48, страница 1 из 2
  1  все
Найти минимальную дырку одной выборкой
    #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
| Ответить | Цитировать | Написать  
25 сообщений из 48, страница 1 из 2
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Найти минимальную дырку одной выборкой
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39921557&tid=1881560]:
 0ms
get settings:
 9ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 41ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 61ms
get tp. blocked users:
 1ms
others: 268ms
total:  415ms
созд. / загр.: 415ms
x
x
Закрыть


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