Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: 1 2      [все]
 Пересечение диапазонов номеров  [new]
ARRay001
Member

Откуда:
Сообщений: 95
Добрый день!

Прошу помощи в составлении запроса.
Условие:
Есть таблица 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.

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

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;


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

Сообщение было отредактировано: 21 май 21, 16:23
21 май 21, 16:28    [22325378]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов номеров  [new]
ARRay001
Member

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

Сообщение было отредактировано: 21 май 21, 16:32
21 май 21, 16:41    [22325385]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов номеров  [new]
ARRay001
Member

Откуда:
Сообщений: 95
Сделал другим способом. Этот можно оставить как вариант для тренировки, если интересно.
21 май 21, 17:58    [22325443]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов номеров  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 3070
ARRay001,

пересечение з - по
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
21 май 21, 18:01    [22325446]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов номеров  [new]
ARRay001
Member

Откуда:
Сообщений: 95
Stax,
listagg - забыл совсем про него) Спасибо, здОрово!
21 май 21, 23:30    [22325577]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов номеров  [new]
ARRay001
Member

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

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

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; 


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

Сообщение было отредактировано: 21 май 21, 23:42
21 май 21, 23:50    [22325583]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов номеров  [new]
ARRay001
Member

Откуда:
Сообщений: 95
Кроме этого, последний пример не выводит конкретных начальных и конечных номеров по группам, по которым происходит пересечение.
В приведенном выше примере должно получиться, если входящие 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

Моё решение не подошло, так что вопрос пока остается открытым.
22 май 21, 02:23    [22325609]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов номеров  [new]
freecozoid
Member

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

Как всем известно без ТЗ результат ... соответсвующий. Потрудитесь сформулировать, а не выкидывать на форум свой слепок мозга.
Удачи !
22 май 21, 12:03    [22325652]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов номеров  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10215
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.
22 май 21, 15:43    [22325700]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов номеров  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 3070
SY,

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

......
stax
23 май 21, 15:15    [22325864]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов номеров  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10215
Stax
SY,
listagg, between &beg and &end сразу под lateral

stax


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

SY.
23 май 21, 15:30    [22325866]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов номеров  [new]
ARRay001
Member

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

Сообщение было отредактировано: 23 май 21, 23:19
23 май 21, 23:27    [22325995]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов номеров  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10215
ARRay001

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


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.
24 май 21, 01:41    [22326014]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов номеров  [new]
ARRay001
Member

Откуда:
Сообщений: 95
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;
)
Либо тогда надо преобразовать это в таблицу либо как-то иначе...
24 май 21, 02:16    [22326015]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов номеров  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10215
ARRay001

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


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.
24 май 21, 03:04    [22326017]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов номеров  [new]
ARRay001
Member

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

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) ?

Сообщение было отредактировано: 24 май 21, 03:26
24 май 21, 03:34    [22326018]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов номеров  [new]
ARRay001
Member

Откуда:
Сообщений: 95
Понял:
select  to_number(regexp_substr(dr.list,'\d+',1, level)) b
                  from  dual
                  connect by level <= (regexp_count(dr.list,';') + 1) 
24 май 21, 04:03    [22326022]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов номеров  [new]
ARRay001
Member

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

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;
9 июн 21, 15:11    [22333350]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов номеров  [new]
ARRay001
Member

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

Сообщение было отредактировано: 9 июн 21, 15:43
9 июн 21, 15:50    [22333367]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов номеров  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 3070
.

Сообщение было отредактировано: 9 июн 21, 15:56
9 июн 21, 16:02    [22333382]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов номеров  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 3070
ARRay001,

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

.....
stax
9 июн 21, 16:17    [22333396]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов номеров  [new]
ARRay001
Member

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

Сообщение было отредактировано: 9 июн 21, 16:30
9 июн 21, 16:37    [22333407]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов номеров  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 3070
ARRay001,

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

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


.....
stax
9 июн 21, 16:50    [22333420]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов номеров  [new]
ARRay001
Member

Откуда:
Сообщений: 95
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 (далее отрезано)
9 июн 21, 17:18    [22333436]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов номеров  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 3070
ARRay001,

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

.....
stax
9 июн 21, 17:47    [22333452]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов номеров  [new]
ARRay001
Member

Откуда:
Сообщений: 95
Stax,
Да, почти:

1) для каждого select 1 gr, 21 no_beg, 21 no_end from генерить не больше 5-ти элементов (да, но в рамках каждой группы, т.е. собрать все числа из гр.1 (может быть из нескольких строк) и выдать название группы и 5 первых чисел по ней)
2) дистинкт в рамках gr - да (по собранному из разных строк с этой группой)
3) берем <=5 строк в пределах группы (да, см. п1)
4) берём <=5 групп
4) listagg (Группа 1: номера по ней (до 5 номеров)
Группа 2: номера по ней (до 5 номеров)
...
Группа 8: номера по ней (до 5 номеров)
до 5 групп (группа 8 - не по порядку а по названию)
Ещё там во внутреннем запросе считается count без обрезания (для того, чтобы потом добавить обработку исключения и слово ' и другие').
Было бы идеально, если бы count считался при любом из 2-х переполнений (>=5). Либо выводился какой-то признак когда больше 5.
9 июн 21, 18:05    [22333465]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение диапазонов номеров  [new]
ARRay001
Member

Откуда:
Сообщений: 95
В общем, немного упростив (без группировки по группам) сделал так:

with req as (
               select p as pp, gc.name d
                 from tbl_list t,
                       lateral(
                                 select  t.no_beg + level - 1 p
                                 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
                               ),
                       tbl_group gc       
                where t.group = gc.id and 
                      p between 1 and 100
                order by gc.name, p      
              )   
  select  listagg(case 
                    when ROWNUM = 6 then 'и другие'
                    when ROWNUM > 6 then null  
                  else  
                    req.pp ||'('||req.d||')'
                  end,
                  ', ') within group(order by to_number(req.pp)) 
  from req;  
10 июн 21, 20:59    [22334096]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Oracle Ответить