Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Пересечение диапазонов номеров / 25 сообщений из 27, страница 1 из 2
  1  все
21.05.2021, 16:28
    #40071987
ARRay001
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:41
    #40071990
ARRay001
ARRay001
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Пересечение диапазонов номеров
Можно результат вывести так:
Группа / Список с разделителями пересечений по группе
1/21,22,23
3/33,35
и т.д.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2021, 17:58
    #40072021
ARRay001
ARRay001
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Пересечение диапазонов номеров
Сделал другим способом. Этот можно оставить как вариант для тренировки, если интересно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2021, 18:01
    #40072023
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Пересечение диапазонов номеров
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, 23:30
    #40072098
ARRay001
ARRay001
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Пересечение диапазонов номеров
Stax,
listagg - забыл совсем про него) Спасибо, здОрово!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2021, 23:50
    #40072104
ARRay001
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
| Ответить | Цитировать | Написать  
22.05.2021, 02:23
    #40072120
ARRay001
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, 12:03
    #40072154
freecozoid
freecozoid
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Пересечение диапазонов номеров
Тысячекратно извиняюсь. НО! Вам вряд ли сможет кто-то помочь даже если захочет. Плохо сформулированная задача. Ее каждый для себя может понять по своему и дать ответ который вам не подойдет ! Вы написали что у вас есть таблица с определенными полями, затем вы пишите "определить вхождения номера". Какого номера ? Порядкового ? У вас в таблице есть id, если про него то так и пишите id. Далее, на небольшом примере покажите исходные данные и что должно получиться, и постарайтесь что бы не было многозначности в формулировках.
Подготовьте наборы данных. Один исходный, другой с результатом, из которых ОДНОЗНАЧНО понятно что должно получиться ! Если не хватает скиллов, то просто опишите на пальцах проблему, которую решаете (if !NDA ).

Как всем известно без ТЗ результат ... соответсвующий. Потрудитесь сформулировать, а не выкидывать на форум свой слепок мозга.
Удачи !
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.05.2021, 15:43
    #40072182
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Пересечение диапазонов номеров
Код: 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
| Ответить | Цитировать | Написать  
23.05.2021, 15:15
    #40072286
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Пересечение диапазонов номеров
SY,

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

......
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.05.2021, 15:30
    #40072288
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Пересечение диапазонов номеров
Stax
SY,
listagg, between &beg and &end сразу под lateral

stax


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

SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.05.2021, 23:27
    #40072344
ARRay001
ARRay001
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Пересечение диапазонов номеров
SY,
Спасибо - то, что нужно! Мне ещё надо разобраться с применением lateral.
Можно ещё расширить задачу: на вход подается не интервал или число, а список целых чисел с разделителем ';'. Ищем так же вхождение каждого из этих чисел со всеми интервалами в таблице с таким же выводом.
В pl/sql делал коллекциями multiset intersect distinct, думаю можно проще сделать, расширив данный запрос.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.05.2021, 01:41
    #40072355
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Пересечение диапазонов номеров
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, 02:16
    #40072356
ARRay001
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, 03:04
    #40072357
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Пересечение диапазонов номеров
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:34
    #40072358
ARRay001
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, 04:03
    #40072359
ARRay001
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
| Ответить | Цитировать | Написать  
09.06.2021, 15:11
    #40076647
ARRay001
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:50
    #40076655
ARRay001
ARRay001
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Пересечение диапазонов номеров
Уточню что нужно: чтобы по каждой группе выводилось не более 5 номеров.
Т.е.: Группа1 | 1,2,5,6,9 (максимум 5 первых номеров при сортировке от меньшего к большему или случайно - лишь бы 5)
Группа2 | 34,55,3
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.06.2021, 16:02
    #40076662
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Пересечение диапазонов номеров
.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.06.2021, 16:17
    #40076668
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Пересечение диапазонов номеров
ARRay001,

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

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.06.2021, 16:37
    #40076675
ARRay001
ARRay001
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Пересечение диапазонов номеров
Stax,
в данном примере ищется пересечение интервала between 1 and 100 с интервалами в полях таблицы t.no_beg и t.no_end в рамках группы (столбец gc.name). И надо чтобы вывод результата ограничивался первыми 5 значениями как по группам (уже есть), так и по номерам в каждой группе. Проблема в том, что может быть несколько строк по каждой из групп. Т.е. надо брать первые 5 значений с начала каждой группы и потом уже применять listagg. Без ограничения оно работает...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.06.2021, 16:50
    #40076681
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Пересечение диапазонов номеров
ARRay001,

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

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


.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.06.2021, 17:18
    #40076690
ARRay001
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:47
    #40076699
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Пересечение диапазонов номеров
ARRay001,

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

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Пересечение диапазонов номеров / 25 сообщений из 27, страница 1 из 2
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 291ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1880117]:
 0ms
get settings:
 10ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 166ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 59ms
get tp. blocked users:
 2ms
others: 18ms
total:  291ms
x
x
Закрыть


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