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

Откуда: Рахів
Сообщений: 841
Это очередная вариация на тему интервалов. Мне подобное на форуме не попадалось, но сорри если баян.

Есть интервалы двух цветов и требуется получить результирующую разбивку как показано ниже.
with t (x1, x2, c) as
(
select 1, 4, 'red' from dual
union all select 7, 10, 'red' from dual
union all select 13, 16, 'red' from dual
union all select 3, 14, 'black' from dual
union all select 16, 19, 'black' from dual
union all select 18, 22, 'red' from dual
union all select 22, 25, 'black' from dual
union all select 26, 28, 'red' from dual
union all select 29, 30, 'black' from dual
union all select 32, 33, 'black' from dual
)
select ...
/

        X1         X2 RESULT
---------- ---------- -------
         1          2 red
         3          4 overlap
         5          6 black
         7         10 overlap
        11         12 black
        13         14 overlap
        15         15 red
        16         16 overlap
        17         17 black
        18         19 overlap
        20         21 red
        22         22 overlap
        23         25 black
        26         28 red
        29         30 black
        31         31 none
        32         33 black

17 rows selected.
Входные интервалы каждого отдельного цвета не пересекаются и не соприкасаются.

Картинка вероятно нагляднее покажет как получен результат.

К сообщению приложен файл. Размер - 24Kb
13 ноя 20, 14:42    [22231442]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задача: Красное и черное  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
Кобанчег,

Верхняя и нижняя граница всегда определены имеющимися интервалами?
13 ноя 20, 15:09    [22231455]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задача: Красное и черное  [new]
Кобанчег
Member

Откуда: Рахів
Сообщений: 841
env,

Не совсем понял вопрос. На выходе должен быть диапазон от начала первого до конца последнего.
13 ноя 20, 15:21    [22231468]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задача: Красное и черное  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
Кобанчег,

Напрашивается решение через непрерывный список от min до max и start of group, но это видимо слишком простое и очевидное.

+
with t (x1, x2, c) as
(
select 1, 4, 'red' from dual
union all select 7, 10, 'red' from dual
union all select 13, 16, 'red' from dual
union all select 3, 14, 'black' from dual
union all select 16, 19, 'black' from dual
union all select 18, 22, 'red' from dual
union all select 22, 25, 'black' from dual
union all select 26, 28, 'red' from dual
union all select 29, 30, 'black' from dual
union all select 32, 33, 'black' from dual
),
minmax as (
    select
        min(x1)     mn,
        max(x2)     mx
    from
        t
), 
nums as (
    select
        mn + level - 1 n
    from
        minmax
    connect by
        level <= mx - mn + 1
), 
vect as (
    select
        n,
        decode(count(distinct t.c), 1, max(c), 0, 'none', 'overlap') c
    from
        nums,
        t
    where
        nums.n between t.x1 (+) and t.x2 (+)
    group by
        nums.n
), 
sog as (
    select
        n,
        c,
        decode(c, lag(c, 1, c) over(order by n), 0, 1) g
    from
        vect
), 
grp as (
    select
        n,
        c,
        sum(g) over(order by n) gr
    from
        sog
)
select
    min(n),
    max(n),
    max(c)
from
    grp
group by
    gr
order by
    gr;

    MIN(N)     MAX(N) MAX(C)
---------- ---------- -------
         1          2 red
         3          4 overlap
         5          6 black
         7         10 overlap
        11         12 black
        13         14 overlap
        15         15 red
        16         16 overlap
        17         17 black
        18         19 overlap
        20         21 red
        22         22 overlap
        23         25 black
        26         28 red
        29         30 black
        31         31 none
        32         33 black


Сообщение было отредактировано: 13 ноя 20, 15:49
13 ноя 20, 15:51    [22231486]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задача: Красное и черное  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2788
Кобанчег,

red =1
black=2
суммируем с перекрытием
3-overlap
0-прозрачный


.....
stax
13 ноя 20, 15:56    [22231491]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задача: Красное и черное  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18484
model + overlap ?
13 ноя 20, 15:58    [22231492]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задача: Красное и черное  [new]
Elic
Member

Откуда:
Сообщений: 29980
Кобанчег
если баян
join с подинтервальчиками.
13 ноя 20, 16:00    [22231497]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задача: Красное и черное  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18484
Кстати, насколько помню была какая-то не[полностью]документированная функция типа именно overlap для работы с датами и числами
Или только датами/диапазонами(?)
13 ноя 20, 16:01    [22231499]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задача: Красное и черное  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2788
Stax
Кобанчег,

red =1
black=2
суммируем с перекрытием
3-overlap
0-прозрачный


зі
нашел
https://www.sql.ru/forum/1297132/razdelit-na-neperesekaushhiesya-intervaly-dat-otrezki-s-ssumirovaniem-summy-v-peresecheniyah

.....
stax
13 ноя 20, 16:08    [22231507]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задача: Красное и черное  [new]
Кобанчег
Member

Откуда: Рахів
Сообщений: 841
env
Кобанчег,

Напрашивается решение через непрерывный список от min до max и start of group, но это видимо слишком простое и очевидное.

+
with t (x1, x2, c) as
(
select 1, 4, 'red' from dual
union all select 7, 10, 'red' from dual
union all select 13, 16, 'red' from dual
union all select 3, 14, 'black' from dual
union all select 16, 19, 'black' from dual
union all select 18, 22, 'red' from dual
union all select 22, 25, 'black' from dual
union all select 26, 28, 'red' from dual
union all select 29, 30, 'black' from dual
union all select 32, 33, 'black' from dual
),
minmax as (
    select
        min(x1)     mn,
        max(x2)     mx
    from
        t
), 
nums as (
    select
        mn + level - 1 n
    from
        minmax
    connect by
        level <= mx - mn + 1
), 
vect as (
    select
        n,
        decode(count(distinct t.c), 1, max(c), 0, 'none', 'overlap') c
    from
        nums,
        t
    where
        nums.n between t.x1 (+) and t.x2 (+)
    group by
        nums.n
), 
sog as (
    select
        n,
        c,
        decode(c, lag(c, 1, c) over(order by n), 0, 1) g
    from
        vect
), 
grp as (
    select
        n,
        c,
        sum(g) over(order by n) gr
    from
        sog
)
select
    min(n),
    max(n),
    max(c)
from
    grp
group by
    gr
order by
    gr;

    MIN(N)     MAX(N) MAX(C)
---------- ---------- -------
         1          2 red
         3          4 overlap
         5          6 black
         7         10 overlap
        11         12 black
        13         14 overlap
        15         15 red
        16         16 overlap
        17         17 black
        18         19 overlap
        20         21 red
        22         22 overlap
        23         25 black
        26         28 red
        29         30 black
        31         31 none
        32         33 black
Да, это весьма неэффективно и завязано на натуральные числа.
Интервалы могут быть произвольной длины и не обязательно с целыми границами.
13 ноя 20, 16:19    [22231521]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задача: Красное и черное  [new]
Кобанчег
Member

Откуда: Рахів
Сообщений: 841
Stax
Stax
Кобанчег,

red =1
black=2
суммируем с перекрытием
3-overlap
0-прозрачный


зі
нашел
https://www.sql.ru/forum/1297132/razdelit-na-neperesekaushhiesya-intervaly-dat-otrezki-s-ssumirovaniem-summy-v-peresecheniyah

.....
stax
Идея понятна, но можно без джойнов и подзапросов.
13 ноя 20, 16:23    [22231526]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задача: Красное и черное  [new]
Кобанчег
Member

Откуда: Рахів
Сообщений: 841
Elic
Кобанчег
если баян
join с подинтервальчиками.
Наиболее эффективно без джойнов.
Но некоторая баянистость просматривается, да.
13 ноя 20, 16:25    [22231530]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задача: Красное и черное  [new]
Кобанчег
Member

Откуда: Рахів
Сообщений: 841
Вячеслав Любомудров
model + overlap ?
В тяжелой артиллерии нет особой необходимости.
Вячеслав Любомудров
Кстати, насколько помню была какая-то не[полностью]документированная функция типа именно overlap для работы с датами и числами
Или только датами/диапазонами(?)
overlaps

Но недокументированное это неспортивно (и для данной задачи нет надобности).
13 ноя 20, 16:29    [22231536]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задача: Красное и черное  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2788
Кобанчег

Наиболее эффективно без джойнов.
Но некоторая баянистость просматривается, да.


unpivot можно?

для
union all select 7, 10, 'red' from dual
union all select 10, 14, 'black' from dual

10 10 overlap?

.....
stax
13 ноя 20, 16:36    [22231545]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задача: Красное и черное  [new]
Кобанчег
Member

Откуда: Рахів
Сообщений: 841
Stax
unpivot можно?
Да всё что угодно можно.
Stax
10 10 overlap?
Да.

PS. На самом деле мне стоило состряпать более адекватный пример с real numbers
(тогда бы 10 10 было касание а не перекрытие на единицу) но уже есть как есть.
13 ноя 20, 16:43    [22231550]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задача: Красное и черное  [new]
Кобанчег
Member

Откуда: Рахів
Сообщений: 841
Кобанчег
завязано на натуральные числа
Должен признать что моя постановка именно это и подразумевает, но всё равно генерить диапазоны connect by - не самый удачный вариант.
13 ноя 20, 16:47    [22231555]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задача: Красное и черное  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2788
Кобанчег,

была идейка red full outer join black on пересекаются
но плюнул перебирать случаи пересечений
.....
stax
14 ноя 20, 12:11    [22231976]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задача: Красное и черное  [new]
graycode
Member

Откуда:
Сообщений: 461
Stax
Кобанчег,

red =1
black=2
суммируем с перекрытием
3-overlap
0-прозрачный


.....
stax

Суммировать хорошая идея, правда как без трансформации запроса это делать я не знаю.

Вижу примерно следующий алгоритм, x1 и x2 в один столбец, каждый признак (red, black) отдельным столбцом, для начала периода (x1) признаку +1, там где кончается период (x2) признаку -1, если накопительная сумма с учетом периода больше нуля значит признак в периоде (в вертикальном виде, период это две ближайшие строки) включен, если нет значит выключен, потом преобразовать обратно в периоды.

PS: реализовывать лень)))
14 ноя 20, 12:41    [22231997]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задача: Красное и черное  [new]
Кобанчег
Member

Откуда: Рахів
Сообщений: 841
Stax
Кобанчег,

была идейка red full outer join black on пересекаются
но плюнул перебирать случаи пересечений
.....
stax
Да, при том подходе надо скурпулёзно все перебирать и солжно для понимания и поддержки имхо.

Более просто и эффективно развернуть все отрезки в один ряд (cross join/pivot) и определить что есть что в результате (аналитика/pattern matching).

В общем мое решение выглядит так
select x1, x2, result
from t unpivot (x for type in (x1, x2))
match_recognize
(
  order by x, type
  measures
    case when type = 'X2' and next(type) = 'X1' and next(x) - x =1 then 1 end touch,
    case when next(x) = x and next(type) = type then 1 end same_bound,
    x + decode(type, 'X2', 1, 0) x1,
    next(x) - decode(next(type), 'X1', 1, 0) x2,
    decode(sum(decode(c, 'red', 1, 'black', 2) * decode(type, 'X1', 1, 'X2', -1)),
           1, 'red', 2, 'black', 3, 'overlap', 'none') result    
  all rows per match
  pattern (x+)
  define x as next(x) is not null
)
where touch is null and same_bound is null


touch используется для фильтра когда конец одного соприкасается с началом другого,
а same_bound для исключения из результата первой из точек когда начала либо концы совпадают.
14 ноя 20, 15:38    [22232069]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задача: Красное и черное  [new]
Кобанчег
Member

Откуда: Рахів
Сообщений: 841
В общем эта была разминка, теперь предлагается задачка посложнее.

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

with t (x1, x2, c, flag) as
(
select 1, 4, 'red', 'src' from dual
union all select 7, 10, 'yellow', 'src' from dual
union all select 13, 16, 'red', 'src' from dual
--union all select 3, 14, 'black' from dual
union all select 3, 7, 'black', 'tgt' from dual
union all select 9, 11, 'black', 'tgt' from dual
union all select 13, 14, 'black', 'tgt' from dual
--
union all select 16, 19, 'blue', 'tgt' from dual
union all select 18, 22, 'green', 'src' from dual
union all select 22, 25, 'black', 'tgt' from dual
union all select 26, 28, 'red', 'src' from dual
union all select 29, 30, 'red', 'tgt' from dual
union all select 32, 33, 'black', 'tgt' from dual
)
select ...
/

        X1         X2 RESULT
---------- ---------- ------
         1          4 red
         5          6 black
         7         10 yellow
        11         11 black
        12         12
        13         16 red
        17         17 blue
        18         22 green
        23         25 black
        26         30 red
        31         31
        32         33 black

12 rows selected.
Здесь не удастся выкрутиться с decode + sum ибо цветов потенциально неограниченное множество.

И снова картинка для наглядности.

К сообщению приложен файл. Размер - 36Kb
14 ноя 20, 16:06    [22232077]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задача: Красное и черное  [new]
Stax
Member

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

PS: реализовывать лень)))


https://www.sql.ru/forum/1297132/razdelit-na-neperesekaushhiesya-intervaly-dat-otrezki-s-ssumirovaniem-summy-v-peresecheniyah

ета задачка даж проще, токо одно пересечение

.....
stax
14 ноя 20, 16:17    [22232081]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задача: Красное и черное  [new]
graycode
Member

Откуда:
Сообщений: 461
del

Сообщение было отредактировано: 14 ноя 20, 19:50
14 ноя 20, 19:53    [22232123]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задача: Красное и черное  [new]
graycode
Member

Откуда:
Сообщений: 461
Кобанчег
В общем мое решение выглядит так

Это называется без тяжелой артиллерии?

Я думал без тяжелой артиллерии выглядит примерно так:
with t (x1, x2, c) as
(
select 1, 4, 'red' from dual
union all select 7, 10, 'red' from dual
union all select 13, 16, 'red' from dual
union all select 3, 14, 'black' from dual
union all select 16, 19, 'black' from dual
union all select 18, 22, 'red' from dual
union all select 22, 25, 'black' from dual
union all select 26, 28, 'red' from dual
union all select 29, 30, 'black' from dual
union all select 32, 33, 'black' from dual
)
, t1 (x, red, black) as
(
select x1 as x, decode(c, 'red', 1, 0), decode(c, 'black', 1, 0) from t
union all
select x2 + 1 as x, decode(c, 'red', -1, 0), decode(c, 'black', -1, 0) from t
)
, t2 (x, red, black) as
(
select distinct x
     , sum(red) over (order by x range unbounded preceding)
     , sum(black) over (order by x range unbounded preceding)
  from t1
)
, t3 (x1, x2, c) as
(
select x
     , lead(x) over (order by x) - 1
     , case when red > 0 and black > 0 then 'overlap'
            when red > 0 then 'red'
            when black > 0 then 'black'
            else 'none' end
  from t2
)
select * from t3 where x2 is not null order by x1
15 ноя 20, 00:53    [22232231]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задача: Красное и черное  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
Одно из решений.

p(r,clr,cln) as
(
select 0, null, null from dual union all
select r+1,
       nvl((select max(c) from t where flag='src' and r+1 between x1 and x2),
           (select max(c) from t where flag='tgt' and r+1 between x1 and x2)),
       nvl((select max(c) from t where flag='src' and r+2 between x1 and x2),
           (select max(c) from t where flag='tgt' and r+2 between x1 and x2))
  from p where r < (select max(x2) from t)
)
select nvl(lag(r) over (order by r),1) as y1,
       r as y2,
       clr
from p where sys_op_map_nonnull(clr) != sys_op_map_nonnull(cln)
15 ноя 20, 01:01    [22232233]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задача: Красное и черное  [new]
graycode
Member

Откуда:
Сообщений: 461
Кобанчег
Здесь не удастся выкрутиться с decode + sum ибо цветов потенциально неограниченное множество.

не так изящно конечно и наверное можно упростить, но выкрутиться можно))
, t1 (x, c_src, c_tgt, gr_src, gr_tgt) as
(
select x1 as x
     , decode(flag, 'src', c, ''), decode(flag, 'tgt', c, '')
     , decode(flag, 'src', 1, 0), decode(flag, 'tgt', 1, 0)
from t
union all
select x2 + 1 as x
     , '', ''
     , decode(flag, 'src', 1, 0), decode(flag, 'tgt', 1, 0)
  from t
)
, t2 (x, c_src, c_tgt, gr_src, gr_tgt) as
(
select x, c_src, c_tgt
     , sum(gr_src) over (order by x range unbounded preceding)
     , sum(gr_tgt) over (order by x range unbounded preceding)
  from t1
)
, t3 (x, c) as
(
select x
     , coalesce(min(c_src) keep (dense_rank first order by x) over (partition by gr_src),
                min(c_tgt) keep (dense_rank first order by x) over (partition by gr_tgt))
  from t2
)
, t4 (x, c) as
(
select min(x), min(c) from (
    select x, c, sum(sog) over (order by x) as gr from (
        select x, c
             , case nvl(c, 'none') when nvl(lag(c) over (order by x), 'none') then 0 else 1 end as sog
          from t3))
group by gr
)
, t5 (x1, x2, c) as
(
select x
     , lead(x) over (order by x) - 1
     , c
  from t4
)
select * from t5 where x2 is not null order by x1
15 ноя 20, 01:04    [22232236]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3 4 5 6 7 8   вперед  Ctrl      все
Все форумы / Oracle Ответить