Форум пользователей MySQL

deadka · 21.10.2010 15:07:54

Доброго времени суток!

Есть таблица intervals(id,first,last), содержащая интервалы чисел. Интервал определен нижней и верхней границей. Интервалы не пересекаются, но один интервал может включать другой (то есть не может быть двух пар типа (1,5) и (3,7), потому что эти интервалы пересекаются ), но могут быть пары (1,7), (1,6), (3,5), (4,4).

Пытаюсь составить запрос, который выбросит все внутренние интервалы, а оставит только внешние.

Пробовал путем

SELECT
distinct t2.id
FROM
intervals t1,intervals t2
where
t2.first<=t1.first and
t2.last>=t1.last and
t1.id!=t2.id

но такой запрос срабатывает лишь если "уровень вложенности" равен 1, а на содержимом, например

(1,10)
(2,6)
(3,4)

выбрасывает только (3,4), а по идее должен выбросить интервал (2,6) тоже.

Подскажите как это можно сделать (если можно) кто знает, плиз.

vasya · 21.10.2010 19:45:05

test >select * from a;

+----+----+----+

| id | f  | l  |

+----+----+----+

|  1 |  1 | 10 |

|  2 |  2 |  6 |

|  3 | 11 | 14 |

|  4 | 11 | 15 |

|  5 |  3 |  4 |

+----+----+----+

5 rows in set (0.00 sec)

test >select id,f,l from (select t.*,@i i from (select * from a order by f asc, l desc) t,

(select @i:=0,@l:=0) y where if(f<@l,@i:=@i+1,(@i:=0) or (@l:=l))) x where i=0;

+----+----+----+

| id | f  | l  |

+----+----+----+

|  1 |  1 | 10 |

|  4 | 11 | 15 |

+----+----+----+

2 rows in set (0.00 sec)

или что тоже самое:

test >set @i=0, @l=0;

Query OK, 0 rows affected (0.00 sec)

test >select id,f,l from (select t.*,@i i from (select * from a order by f asc, l desc) t w

here if(f<@l,@i:=@i+1,(@i:=0) or (@l:=l))) x where i=0;

+----+----+----+

| id | f  | l  |

+----+----+----+

|  1 |  1 | 10 |

|  4 | 11 | 15 |

+----+----+----+

2 rows in set (0.25 sec)

deadka · 21.10.2010 22:24:07

Спасибо огромное!!! Осталось расшифровать :-), но это уж как-нибудь.

vasya · 21.10.2010 22:56:14

Сначала сортируем данные так чтобы шел внешний интервал и все вложенные в него

test >select * from a order by f asc, l desc;

+----+----+----+

| id | f  | l  |

+----+----+----+

|  1 |  1 | 10 |

|  2 |  2 |  6 |

|  5 |  3 |  4 |

|  4 | 11 | 15 |

|  3 | 11 | 14 |

+----+----+----+

Отсортированный результат имеет алиас `t`

Фактически задача свелась к тому, чтобы выбрать по одному экземпляру из группы http://sqlinfo.ru/forum/viewtopic.php?id=1742

@i - счетчик. Считает кол-во вложенных интервалов, при новом внешнем интервале обнуляется.
@l - переменная в которую записывается верхняя граница внешнего интервала

Изначально переменные обнулены
test >set @i=0, @l=0;

where if(f<@l,@i:=@i+1,(@i:=0) or (@l:=l))
если интервал вложенный, т.е. f<@l, то счетчик увеличивается на 1
в противном случае счетчик обнуляется, данный интервал считается новым внешним и переменной @l присваевается новое значение. Результат (@i:=0) or (@l:=l) истина, т.е. условие не выбрасывает никаких строк, а лишь считает интервалы по уровням вложенности.

test >set @i:=0,@l:=0;

Query OK, 0 rows affected (0.42 sec)

test >select t.*,@i,@l from (select * from a order by f asc, l desc) t where if(f<@l,@i:=@i+1,(@i:=0) or (@l:=l));

+----+----+----+------+------+

| id | f  | l  | @i   | @l   |

+----+----+----+------+------+

|  1 |  1 | 10 |    0 |   10 |

|  2 |  2 |  6 |    1 |   10 |

|  5 |  3 |  4 |    2 |   10 |

|  4 | 11 | 15 |    0 |   15 |

|  3 | 11 | 14 |    1 |   15 |

+----+----+----+------+------+

5 rows in set (0.00 sec)

Данный результат имеет алиас `x` и из него нам нужно выбрать строки где счетчик имеет значение 0. Т.е. ещё один внешний select.
Обратите внимание, что в последней выборке (внешний select) используется не переменная @i, её алиас `i`, так как сама переменная @i к моменту внешней выборки будет иметь другое значение.

deadka · 21.10.2010 23:11:38

Спасибо! Теперь всё понятно.

vasya · 21.10.2010 23:20:50

Кстати, третий селект лишний. Проверку уровня вложенности можно проводить сразу во втором.

test >set @i:=0,@l:=0;

Query OK, 0 rows affected (0.00 sec)

test >select t.* from (select * from a order by f asc, l desc) t where if(f<@l,@i:=@i+1,(@i

:=0) or (@l:=l)) and @i=0;

+----+----+----+

| id | f  | l  |

+----+----+----+

|  1 |  1 | 10 |

|  4 | 11 | 15 |

+----+----+----+

2 rows in set (0.00 sec)

vasya · 21.10.2010 23:26:33

Я сначала просто поставил в условии проверку на нулевой уровень вложенности @i=0 перед if().. и получил закономерный результат.

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

Форум пользователей MySQL

#1 21.10.2010 15:07:54

Как выбросить внутренние интервалы (в таблице, содержащей интервалы)

#2 21.10.2010 19:45:05

Re: Как выбросить внутренние интервалы (в таблице, содержащей интервалы)

#3 21.10.2010 22:24:07

Re: Как выбросить внутренние интервалы (в таблице, содержащей интервалы)

#4 21.10.2010 22:56:14

Re: Как выбросить внутренние интервалы (в таблице, содержащей интервалы)

#5 21.10.2010 23:11:38

Re: Как выбросить внутренние интервалы (в таблице, содержащей интервалы)

#6 21.10.2010 23:20:50

Re: Как выбросить внутренние интервалы (в таблице, содержащей интервалы)

#7 21.10.2010 23:26:33

Re: Как выбросить внутренние интервалы (в таблице, содержащей интервалы)

Board footer