Пересечение диапазонов номеров / Oracle

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Пересечение диапазонов номеров

25 сообщений из 27, страница 1 из 2

все

Пересечение диапазонов номеров

#40071987

ARRay001

Гость

Добрый день!

Прошу помощи в составлении запроса.
Условие:
Есть таблица NumData(id, num_beg, num_end, num_gr)

Надо определить вхождение(пересечение) номера с любым из интервалов в строках от num_beg до num_end и вывести в таком виде:

Номер 22 уже входит в диапазон группы (num_gr) 1,

Основной запрос уже есть (достает пересечение номеров), но как вывести группу не прибегая к pl_sql - пока не понял (может быть не одно число на вход, а интервал). В случае интервала результат такой:
Номера 21, 22, 23 уже входят в диапазон группы 1,
Номера 33, 35 уже входят в диапазон группы 3.

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

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

select distinct t.start_range + level - 1 val_range
from (select 20 start_range, 22 end_range 
        from dual 
     ) t
connect by level <= (t.end_range - t.start_range + 1)
intersect 
select distinct t.start_range + level - 1 val_range
from (select h.num_beg start_range, h.num_end end_range, h.num_gr 
        from NumData h 
       where h.num_beg is not null and h.num_end is not null) t
connect by level <= (t.end_range - t.start_range + 1)
order by 1;

Если добавить группу к внешним запросам, то не сработает сквозной поиск пересечения.
Подскажите, как сделать? В идеале бы ещё отсортировать результат по группам...

...

Рейтинг:

0 / 0

21.05.2021, 16:28

| Ответить | Цитировать | Написать

Пересечение диапазонов номеров

#40071990

ARRay001

Гость

Можно результат вывести так:
Группа / Список с разделителями пересечений по группе
1/21,22,23
3/33,35
и т.д.

...

Рейтинг:

0 / 0

21.05.2021, 16:41

| Ответить | Цитировать | Написать

Пересечение диапазонов номеров

#40072021

ARRay001

Гость

Сделал другим способом. Этот можно оставить как вариант для тренировки, если интересно.

...

Рейтинг:

0 / 0

21.05.2021, 17:58

| Ответить | Цитировать | Написать

Пересечение диапазонов номеров

#40072023

Stax

Участник

Откуда: Ukraine,Lviv

Сообщения: 4 370

Рейтинг: 0 / 0

ARRay001,

пересечение з - по

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

SQL> ed
Wrote file afiedt.buf

  1  with t as (
  2  select 1 gr,1 z, 5 po from dual union all
  3  select 2 gr,7 z, 10 po from dual union all
  4  select 3 gr,3 z, 6 po from dual union all
  5  select 4 gr,2 z, 9 po from dual union all
  6  select 5 gr,11 z, 13 po from dual)
  7  select t1.gr,listagg(t2.gr, ',') within group(order by t2.gr) lst
  8  from t t1,t t2 where t1.gr<>t2.gr and greatest(t1.z,t2.z)<=least(t1.po,t2.po)
  9  group by t1.gr
 10* order by 1
SQL> /

        GR LST
---------- ------------------------------
         1 3,4
         2 4
         3 1,4
         4 1,2,3

.....
stax

...

Рейтинг:

0 / 0

21.05.2021, 18:01

| Ответить | Цитировать | Написать

Пересечение диапазонов номеров

#40072098

ARRay001

Гость

Stax,
listagg - забыл совсем про него) Спасибо, здОрово!

...

Рейтинг:

0 / 0

21.05.2021, 23:30

| Ответить | Цитировать | Написать

Пересечение диапазонов номеров

#40072104

ARRay001

Гость

Stax, правда я наверное не совсем так написал постановку. Диапазоны в таблице должны сравниваться с входящим числом или входящим интервалом. Примерно так:

Интервал от 12 до 22 (входящий):

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

SELECT b.num_gr, b.num_beg, b.num_end FROM NumData b
WHERE (
(b.num_beg <= 22 AND b.num_end >= 12) --пересечение если интервал задан и там и там
OR
(b.num_beg <= 22 AND b.num_beg >= 12 AND b.num_end IS NULL) -- сравнение с одиночными записями
OR
(12 >= b.num_beg AND 12 <= b.num_end AND 22 IS NULL) -- сравнение одиночной записи с интервалом
OR
(12 = b.num_beg AND 22 IS NULL AND b.num_end IS NULL ) -- проверять на точку тоже надо
) 
order by b.num_gr;

Только тут не делается в строку...

...

Рейтинг:

0 / 0

21.05.2021, 23:50

| Ответить | Цитировать | Написать

Пересечение диапазонов номеров

#40072120

ARRay001

Гость

Кроме этого, последний пример не выводит конкретных начальных и конечных номеров по группам, по которым происходит пересечение.
В приведенном выше примере должно получиться, если входящие beg=1 и end=3:
with t as (
2 select 1 gr,1 z, 5 po from dual union all
3 select 2 gr,7 z, 10 po from dual union all
4 select 3 gr,3 z, 6 po from dual union all
5 select 4 gr,2 z, 9 po from dual union all
6 select 5 gr,11 z, 13 po from dual
)

Вывод:
Группа/Пересечения
1 1,2,3
4 2,3

Но в реале так не может быть, т.к. в таблице уже есть пересечение, чего быть не может. Будет только одна строка в выводе.
Несколько строк могут получиться, если входящий диапазон перекрывает диапазоны нескольких групп.
Например, 4-8:

Вывод:
Группа/Пересечения
1 4,5
2 7,8
3 4,5,6
4 4,5,6,7,8

Моё решение не подошло, так что вопрос пока остается открытым.

...

Рейтинг:

0 / 0

22.05.2021, 02:23

| Ответить | Цитировать | Написать

Пересечение диапазонов номеров

#40072154

freecozoid

Гость

Тысячекратно извиняюсь. НО! Вам вряд ли сможет кто-то помочь даже если захочет. Плохо сформулированная задача. Ее каждый для себя может понять по своему и дать ответ который вам не подойдет ! Вы написали что у вас есть таблица с определенными полями, затем вы пишите "определить вхождения номера". Какого номера ? Порядкового ? У вас в таблице есть id, если про него то так и пишите id. Далее, на небольшом примере покажите исходные данные и что должно получиться, и постарайтесь что бы не было многозначности в формулировках.
Подготовьте наборы данных. Один исходный, другой с результатом, из которых ОДНОЗНАЧНО понятно что должно получиться ! Если не хватает скиллов, то просто опишите на пальцах проблему, которую решаете (if !NDA ).

Как всем известно без ТЗ результат ... соответсвующий. Потрудитесь сформулировать, а не выкидывать на форум свой слепок мозга.
Удачи !

...

Рейтинг:

0 / 0

22.05.2021, 12:03

| Ответить | Цитировать | Написать

Пересечение диапазонов номеров

#40072182

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

Код: 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.

with t as (
           select 1 gr,1 z, 5 po from dual union all
           select 2 gr,7 z, 10 po from dual union all
           select 3 gr,3 z, 6 po from dual union all
           select 4 gr,2 z, 9 po from dual union all
           select 5 gr,11 z, 13 po from dual
          )
select  gr,
        listagg(p,',') within group(order by p) crosspoints
  from  t,
        lateral(
                select  z + level - 1 p
                  from  dual
                  connect by z + level - 1 <= po
               )
  where p between &beg and &end
  group by gr
/
Enter value for beg: 1
Enter value for end: 3
old  16:   where p between &beg and &end
new  16:   where p between 1 and 3

        GR CROSSPOINT
---------- ----------
         1 1,2,3
         3 3
         4 2,3

SQL> /
Enter value for beg: 4
Enter value for end: 8
old  16:   where p between &beg and &end
new  16:   where p between 4 and 8

        GR CROSSPOINT
---------- ----------
         1 4,5
         2 7,8
         3 4,5,6
         4 4,5,6,7,8

SQL>

SY.

...

Рейтинг:

0 / 0

22.05.2021, 15:43

| Ответить | Цитировать | Написать

Пересечение диапазонов номеров

#40072286

Stax

Участник

Откуда: Ukraine,Lviv

Сообщения: 4 370

Рейтинг: 0 / 0

SY,

listagg, between &beg and &end сразу под lateral

......
stax

...

Рейтинг:

0 / 0

23.05.2021, 15:15

| Ответить | Цитировать | Написать

Пересечение диапазонов номеров

#40072288

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

Stax

SY,
listagg, between &beg and &end сразу под lateral

stax

IMHO добавит сложности в понимании/читабельности и вряд ли даст ощутимый прирост производительности разве что у ТС очень большие интервалы z - po и очень маленькие интервалы &beg - &end.

SY.

...

Рейтинг:

0 / 0

23.05.2021, 15:30

| Ответить | Цитировать | Написать

Пересечение диапазонов номеров

#40072344

ARRay001

Гость

SY,
Спасибо - то, что нужно! Мне ещё надо разобраться с применением lateral.
Можно ещё расширить задачу: на вход подается не интервал или число, а список целых чисел с разделителем ';'. Ищем так же вхождение каждого из этих чисел со всеми интервалами в таблице с таким же выводом.
В pl/sql делал коллекциями multiset intersect distinct, думаю можно проще сделать, расширив данный запрос.

...

Рейтинг:

0 / 0

23.05.2021, 23:27

| Ответить | Цитировать | Написать

Пересечение диапазонов номеров

#40072355

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

ARRay001

В pl/sql делал коллекциями multiset intersect distinct, думаю можно проще сделать, расширив данный запрос.

Код: 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.

with t as (
           select 1 gr,1 z, 5 po from dual union all
           select 2 gr,7 z, 10 po from dual union all
           select 3 gr,3 z, 6 po from dual union all
           select 4 gr,2 z, 9 po from dual union all
           select 5 gr,11 z, 13 po from dual
          ),
driver as (
           select 1 b,3 e from dual union all
           select 4,8 from dual
          )
select  d.b,
        d.e,
        t.gr,
        listagg(p,',') within group(order by p) crosspoints
  from  t,
        lateral(
                select  t.z + level - 1 p
                  from  dual
                  connect by t.z + level - 1 <= t.po
               ) l,
        driver d
  where l.p between d.b and d.e
  group by d.b,
           d.e,
           t.gr
  order by d.b,
           d.e,
           t.gr
/

         B          E         GR CROSSPOINTS
---------- ---------- ---------- -----------
         1          3          1 1,2,3
         1          3          3 3
         1          3          4 2,3
         4          8          1 4,5
         4          8          2 7,8
         4          8          3 4,5,6
         4          8          4 4,5,6,7,8

7 rows selected.

SQL>

SY.

...

Рейтинг:

0 / 0

24.05.2021, 01:41

| Ответить | Цитировать | Написать

Пересечение диапазонов номеров

#40072356

ARRay001

Гость

SY, Почти то, только вместо табличного представления:
driver as (
select 1 b,3 e from dual union all
select 4,8 from dual
)
на входе:
driver as (
select '1;3;4;8' from dual;
)
Либо тогда надо преобразовать это в таблицу либо как-то иначе...

...

Рейтинг:

0 / 0

24.05.2021, 02:16

| Ответить | Цитировать | Написать

Пересечение диапазонов номеров

#40072357

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

ARRay001

на входе:
driver as (
select '1;3;4;8' from dual;
)
Либо тогда надо преобразовать это в таблицу либо как-то иначе...

Код: 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.

with t as (
           select 1 gr,1 z, 5 po from dual union all
           select 2 gr,7 z, 10 po from dual union all
           select 3 gr,3 z, 6 po from dual union all
           select 4 gr,2 z, 9 po from dual union all
           select 5 gr,11 z, 13 po from dual
          ),
driver as (
           select '1;3;4;8' list from dual
          )
select  d.b,
        d.e,
        t.gr,
        listagg(p,',') within group(order by p) crosspoints
  from  t,
        lateral(
                select  t.z + level - 1 p
                  from  dual
                  connect by t.z + level - 1 <= t.po
               ) l,
        driver dr,
        lateral(
                select  to_number(regexp_substr(dr.list,'\d+',1,2 * level - 1)) b,
                        to_number(regexp_substr(dr.list,'\d+',1,2 * level)) e
                  from  dual
                  connect by level <= (regexp_count(dr.list,';') + 1) / 2
               ) d
  where l.p between d.b and d.e
  group by d.b,
           d.e,
           t.gr
  order by d.b,
           d.e,
           t.gr
/

         B          E         GR CROSSPOINTS
---------- ---------- ---------- -----------
         1          3          1 1,2,3
         1          3          3 3
         1          3          4 2,3
         4          8          1 4,5
         4          8          2 7,8
         4          8          3 4,5,6
         4          8          4 4,5,6,7,8

7 rows selected.

SQL>

SY.

...

Рейтинг:

0 / 0

24.05.2021, 03:04

| Ответить | Цитировать | Написать

Пересечение диапазонов номеров

#40072358

ARRay001

Гость

SY,
Нечто такое предполагал, но как-то не осознал.
А как будет выражение

Код: plsql

1.
2.
3.
4.

select  to_number(regexp_substr(dr.list,'\d+',1,2 * level -1)) b/*,
                        to_number(regexp_substr(dr.list,'\d+',1,2 * level)) e*/
                  from  dual
                  connect by level <= (regexp_count(dr.list,';') + 1) / 2

если все числа запихиваем в один столбец b (интервал between d.b and d.b) ?

...

Рейтинг:

0 / 0

24.05.2021, 03:34

| Ответить | Цитировать | Написать

Пересечение диапазонов номеров

#40072359

ARRay001

Гость

Понял:

Код: plsql

1.
2.
3.

select  to_number(regexp_substr(dr.list,'\d+',1, level)) b
                  from  dual
                  connect by level <= (regexp_count(dr.list,';') + 1)

...

Рейтинг:

0 / 0

24.05.2021, 04:03

| Ответить | Цитировать | Написать

Пересечение диапазонов номеров

#40076647

ARRay001

Гость

Подскажите, пож, как в данном запросе (сделал по образцу выше) ограничить количество выводимых pp в каждой группе (gname). Что-то никак не пойму. Предполагаю аналитику с интервалом в подзапросе, но не уверен. Жаль у listagg нет ограничителя по подгруппам.

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

select g1 as gname, listagg(pp,',' on overflow truncate '...' with count) within group(order by pp) as crosspoints, max(cc) as ccc 
    from (select g as g1, p as pp, max(c) as cc        
            from (select gc.name g, p, count(distinct gc.name) over () as c,l
                    from tbl_list t,
                         lateral(
                                  select  t.no_beg + level - 1 p, level as l
                                  from  dual
                                  where t.no_beg is not null and t.no_end is not null
                                  connect by t.no_beg + level - 1 <= t.no_end
                                 ) l,
                         tbl_group gc
                   where t.group = gc.id and
                         l.p between 1 and 100
                   group by gc.name, p, l
                 )  
          group by g, p      
          union all      
          select '', null, 0 from dual        
         )
  group by g1
  fetch first 5 rows only;

...

Рейтинг:

0 / 0

09.06.2021, 15:11

| Ответить | Цитировать | Написать

Пересечение диапазонов номеров

#40076655

ARRay001

Гость

Уточню что нужно: чтобы по каждой группе выводилось не более 5 номеров.
Т.е.: Группа1 | 1,2,5,6,9 (максимум 5 первых номеров при сортировке от меньшего к большему или случайно - лишь бы 5)
Группа2 | 34,55,3

...

Рейтинг:

0 / 0

09.06.2021, 15:50

| Ответить | Цитировать | Написать

Пересечение диапазонов номеров

#40076662

Stax

Участник

Откуда: Ukraine,Lviv

Сообщения: 4 370

Рейтинг: 0 / 0

...

Рейтинг:

0 / 0

09.06.2021, 16:02

| Ответить | Цитировать | Написать

Пересечение диапазонов номеров

#40076668

Stax

Участник

Откуда: Ukraine,Lviv

Сообщения: 4 370

Рейтинг: 0 / 0

ARRay001,

запутали Вы меня, у Вас список('1;3;4;8' ), или з/по (no_beg/t.no_end)?

.....
stax

...

Рейтинг:

0 / 0

09.06.2021, 16:17

| Ответить | Цитировать | Написать

Пересечение диапазонов номеров

#40076675

ARRay001

Гость

Stax,
в данном примере ищется пересечение интервала between 1 and 100 с интервалами в полях таблицы t.no_beg и t.no_end в рамках группы (столбец gc.name). И надо чтобы вывод результата ограничивался первыми 5 значениями как по группам (уже есть), так и по номерам в каждой группе. Проблема в том, что может быть несколько строк по каждой из групп. Т.е. надо брать первые 5 значений с начала каждой группы и потом уже применять listagg. Без ограничения оно работает...

...

Рейтинг:

0 / 0

09.06.2021, 16:37

| Ответить | Цитировать | Написать

Пересечение диапазонов номеров

#40076681

Stax

Участник

Откуда: Ukraine,Lviv

Сообщения: 4 370

Рейтинг: 0 / 0

ARRay001,

('1;3;4;8' ) ето два диапазона 1-3 и 4-8, или четыре елемента?

ps
приведите пример таблиц, и что плучить

.....
stax

...

Рейтинг:

0 / 0

09.06.2021, 16:50

| Ответить | Цитировать | Написать

Пересечение диапазонов номеров

#40076690

ARRay001

Гость

Stax,

with tbl_list as (
select 1 group,21 no_beg,21 no_end from dual union all
select 1 ,22 ,22 from dual union all
select 1 ,1 , 4 from dual union all
select 2 ,12 ,20 from dual union all
select 2 ,45 ,47 from dual
)

а из tbl_group gc достается имя группы gc.name
=> tbl_list.group = gc.id
Интервал 1..100

Ожидаемый результат (числа на пересечении диапазонов:):
Группа | Сrosspoints
1 | 1, 2, 3, 4, 21 (далее отрезано)
2 | 12, 13, 14, 15, 16 (далее отрезано)

...

Рейтинг:

0 / 0

09.06.2021, 17:18

| Ответить | Цитировать | Написать

Пересечение диапазонов номеров

#40076699

Stax

Участник

Откуда: Ukraine,Lviv

Сообщения: 4 370

Рейтинг: 0 / 0

ARRay001,

если я правильно понял
1) для каждого select 1 gr,21 no_beg,21 no_end from генерить не больше 5-ти елементов
2) дистинкт в рамках gr
3) берем <=5 строк в пределах группы
4) listagg

.....
stax

...

Рейтинг:

0 / 0

09.06.2021, 17:47

| Ответить | Цитировать | Написать

25 сообщений из 27, страница 1 из 2

все

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Пересечение диапазонов номеров

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=52&fpage=17&tid=1880117]:	0ms
get settings:	8ms
get forum list:	12ms
check forum access:	3ms
check topic access:	3ms
track hit:	54ms
get topic data:	11ms
get forum data:	2ms
get page messages:	63ms
get tp. blocked users:	2ms
others:	12ms

total:	170ms