Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Запрос на разделение интервалов (дырки)  [new]
HResult
Member

Откуда:
Сообщений: 132
Задачка, кто поможет?

Есть труба, длинная, длинная - километров 70.

Есть таблица интервалов длин, там оборудование какое, или кожуха, к примеру:
Pipeline Start(m) End(m)
3 3815.755 3844.558477
3 6599.593 6607.176916
3 8526.033 8527.847989
3 16865.97 16868.7632
3 25544.64 25550.58006

Нужно найти все промежутки между ними.
Типа:
Pipeline Start(m) End(m)
3 0 3815.755
3 3844.558477 6599.593
3 6607.176916 8526.033
3 8527.847989 16865.97
3 16868.7632 25544.64
3 25550.58006 70000

И все хорошо бы запросом, и хорошо бы одним.
Я тут видел красивые решение по объединению, а вот по разделению?
6 окт 13, 15:22    [14930164]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разделение интервалов (дырки)  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6123
;with cte as (
  select t.*, row_number() over(order by start desc) ord from (
    values
      (3, 3815.755, 3844.558477),
      (3, 6599.593, 6607.176916),
      (3, 8526.033, 8527.847989),
      (3, 16865.97, 16868.7632),
      (3, 25544.64, 25550.58006)
  ) t(pipeline, start, [end])
)
select pipeline, lag([end], 1, 0) over(order by start), start from cte
union all
select pipeline, [end], 70000 from cte where ord=1
6 окт 13, 16:16    [14930237]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разделение интервалов (дырки)  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6123
Лучше вот так:
;with cte as (
  select t.* from (
    values
      (3, 3815.755, 3844.558477),
      (3, 6599.593, 6607.176916),
      (3, 8526.033, 8527.847989),
      (3, 16865.97, 16868.7632),
      (3, 25544.64, 25550.58006)
  ) t(pipeline, start, [end])
  union all
  select 3, 70000, -1
)
select pipeline, lag([end], 1, 0) over(order by start), start from cte
6 окт 13, 16:29    [14930271]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разделение интервалов (дырки)  [new]
HResult
Member

Откуда:
Сообщений: 132
Ага, спасибо, попробовал.
Только у меня ругается <'lag' is not a recognized built-in function name.>
У меня 2012 сервер. Подсказку про Lag дает, но ругается.
И хотелось бы, чтобы, как минимум, работало на MSSQL и ORACLE.
Что-то такое, чисто каноническое. Как-то можно, я надеюсь, только сам я не в теме.
Что-то там прячется в джоинах...
6 окт 13, 16:47    [14930301]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разделение интервалов (дырки)  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6123
HResult
У меня 2012 сервер.

Тогда LAG вполне должен работать, он именно в 2012 и был введен. В оракле, кстати, LAG тоже есть.
Но если уж так хочется с джойнами - вот два варианта:
;with cte as (
  select t.* from (
    values
      (3, 3815.755, 3844.558477),
      (3, 6599.593, 6607.176916),
      (3, 8526.033, 8527.847989),
      (3, 16865.97, 16868.7632),
      (3, 25544.64, 25550.58006)
  ) t(pipeline, start, [end])
  union all
  select 3, 70000, -1
), cte2 as (
  select *, row_number() over(order by start) ord
  from cte
)
select e1.pipeline, isnull(e2.[end],0), e1.start
from cte2 e1
left join cte2 e2 on e2.ord=e1.ord-1

и
;with cte as (
  select t.* from (
    values
      (3, 3815.755, 3844.558477),
      (3, 6599.593, 6607.176916),
      (3, 8526.033, 8527.847989),
      (3, 16865.97, 16868.7632),
      (3, 25544.64, 25550.58006)
  ) t(pipeline, start, [end])
  union all
  select 3, 70000, -1
), cte2 as (
  select *, row_number() over(order by start) ord
  from cte
), cte3 as (
  select pipeline, start, [end], convert(numeric(11,6),0) [from], start [to], ord from cte2 where ord=1
  union all
  select e1.pipeline, e1.start, e1.[end], convert(numeric(11,6),e2.[end]), e1.start, e1.ord
  from cte2 e1
  join cte3 e2 on e2.ord=e1.ord-1
)
select pipeline, [from], [to] from cte3 option(maxrecursion 0)
6 окт 13, 16:56    [14930318]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разделение интервалов (дырки)  [new]
HResult
Member

Откуда:
Сообщений: 132
Спасибо, это заработало!

Сейчас пробую прикрутить к своей базе.
Там еще засада, длины труб в другой таблице (то место, где 70000 и -1).
То есть, кожуха в одной таблице по всем трубам, трубы с длинами в другой, они связаны по этому индексу (3), и надо найти все дырки во всех трубах.
Так сходу не получилось, там что-то со связью в UNION.
Я ж говорю, не специалист.

А вообще спасибо, что-то закрутилось.
6 окт 13, 18:00    [14930438]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разделение интервалов (дырки)  [new]
HResult
Member

Откуда:
Сообщений: 132
Уважаемый Сон Веры Павловны,
Не могу найти, как отправить личное сообщение на этом форуме, но хотелось бы, как минимум, отблагодарить за помощь.
Куда засылать вискарь, и как?
7 окт 13, 22:28    [14936520]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разделение интервалов (дырки)  [new]
Gribbs
Member

Откуда: Липецк
Сообщений: 1
sqrt
9 окт 13, 22:04    [14947605]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разделение интервалов (дырки)  [new]
HResult
Member

Откуда:
Сообщений: 132
Gribbs
sqrt

Нужны пояснения.
9 окт 13, 22:35    [14947705]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разделение интервалов (дырки)  [new]
`
Guest
HResult
Gribbs
sqrt

Нужны пояснения.

это СЕО-спамер , так что не берите в голову - прийдёт модератор и "пристрелит" удалит
9 окт 13, 22:41    [14947726]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разделение интервалов (дырки)  [new]
Jaffar
Member

Откуда:
Сообщений: 633
дайте ка и я предложу свой олдфажный вариант:
;with PIPELINE as
(
          select ROW_NUMBER() over(order by V1 asc, V2 asc)NN,       V1,       V2 from TT
union all select                                            0,     null,        0
union all select                                   COUNT(*)+1,    70000,    NULL  from TT )

select t1.NN, t1.V2, t2.V1
from PIPELINE T1
join PIPELINE T2 on T2.NN = T1.NN+1
where
      t1.V2 != t2.V1 -- Если отрезки идут сразу
order by t1.NN asc
10 окт 13, 08:43    [14948374]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разделение интервалов (дырки)  [new]
HResult
Member

Откуда:
Сообщений: 132
Ну, если уже развивать, то есть две таблицы:

1. Pipelines с полями id, Start, Finish. Это все трубопроводы с длинами
2. Segments с полями idPipeline, Start, Finish. Это перечень сегментов на тех трубопроводах.

Сегменты не покрывают всей трубы.
Надо построить запрос, который бы находил все места в трубах, непокрытые сегментами.
В самом деле, я уже прикрутил первую подсказку.
Но хотелось бы без "With", так как некоторые системы могут работать на серверах 2000, 2005. С Ораклом легче, уже ни у кого меньше девятки нету.
Мы когда-то уже делали подобную вестч с интерполяцией, и она работает.
Такое, универсальное решение. Уверен, что оно есть, только не хватает терпения разбираться с джоинами.

В самом деле, есть более интересная задача по динамической сегментации, но это уже за рамками дружеской помощи на форуме.
10 окт 13, 09:47    [14948624]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разделение интервалов (дырки)  [new]
Jaffar
Member

Откуда:
Сообщений: 633
Ну если не нравиться Cte - создай вид в который запихни текст из CTE и так же делай join.

если не создавать вид - то просто сделай 2 подзапроса и сджойни их по вышеприведенному условию.
10 окт 13, 10:10    [14948713]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разделение интервалов (дырки)  [new]
HResult
Member

Откуда:
Сообщений: 132
View - не решение. Запрос полностью генерируется на лету.
Пробовал подставлять тексты запросов вместо имен, но там не все так просто с алиасами. У самого не получается.
10 окт 13, 10:15    [14948741]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разделение интервалов (дырки)  [new]
Jaffar
Member

Откуда:
Сообщений: 633
HResult
только не хватает терпения разбираться с джоинами.


Извините меня за мой хамский и безтактный вопрос, НО - на каком уровне вы знаете t-sql?
2 таблички сджойнить можете?

если нет, то тогда я не знаю чем вам помочь кроме как предложить платную помощь......
11 окт 13, 11:06    [14955219]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разделение интервалов (дырки)  [new]
HResult
Member

Откуда:
Сообщений: 132
Jaffar
HResult
только не хватает терпения разбираться с джоинами.


Извините меня за мой хамский и безтактный вопрос, НО - на каком уровне вы знаете t-sql?
2 таблички сджойнить можете?

если нет, то тогда я не знаю чем вам помочь кроме как предложить платную помощь......

Я знаю о чем SQL, могу разобраться, наверное, в любом SQL предложении, но это не то, чем я занимаюсь обычно.
Насчет платной помощи - я уже вижу кому бы мог ее заказать, но это, пока, не Вы.
11 окт 13, 11:25    [14955377]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разделение интервалов (дырки)  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Jaffar
олдфажный вариант
ROW_NUMBER()

Да ладно, какой же это олдфажный, он даже на MSSQL 2000 не запустится ;-)

Если нужен 2000, то можно сделать так:
-- таблица труб
declare @pipe table (pipeline int, [from] numeric(20,8), [to] numeric(20,8));

insert @pipe values (1, 12345.678, 56789.012);
insert @pipe values (3, 0, 70000);

-- таблица отрезков
declare @t table (pipeline int, [from] numeric(20,8), [to] numeric(20,8));

insert @t values (3, 3815.755, 3844.558477);
insert @t values (3, 6599.593, 6607.176916);
insert @t values (3, 8526.033, 8527.847989);
insert @t values (3, 16865.97, 16868.7632);
insert @t values (3, 25544.64, 25550.58006);

insert @t values (1, 13000.64, 18000.58006);
insert @t values (1, 19000.55, 20000.68009);

if object_id('tempdb..#tmp') is not null drop table #tmp;

select identity(int,1,1) as id, pipeline, [from], [to]
into #tmp
from
(
  select pipeline, [from], [to]
  from @t
  union all
  select pipeline, [from], [from]
  from @pipe
  union all
  select pipeline, [to], [to]
  from @pipe
) t
order by t.pipeline, t.[from];

alter table #tmp add primary key (id);

select t1.pipeline, t1.[to] as [from], t2.[from] as [to]
from #tmp t1
     inner join #tmp t2 on t2.id = t1.id + 1 and t2.pipeline = t1.pipeline
where t1.[to] <> t2.[from];
11 окт 13, 11:58    [14955650]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разделение интервалов (дырки)  [new]
HResult
Member

Откуда:
Сообщений: 132
Да, это тоже работает. Спасибо.
Немного смущают временные таблицы, но если без них для 2000 нельзя... Будем заказчика отваживать от 2000.

Что я увидел, ни один пример не работает напрямую в ORACLE.
ORACLE точно не мой выбор, но приходится о нем тоже думать.
Там и SELECT FROM VALUES не поддерживается, и на собаку ругается. Собака...

Идеально было бы универсальный запрос, чтобы и там, и там работал.
Но можно и ветку зарядить.
11 окт 13, 14:29    [14956824]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разделение интервалов (дырки)  [new]
Гость333
Member

Откуда:
Сообщений: 3683
HResult
универсальный запрос, чтобы и там, и там работал

"Нет, сынок, это фантастика" (С)
11 окт 13, 14:33    [14956865]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разделение интервалов (дырки)  [new]
HResult
Member

Откуда:
Сообщений: 132
Гость333
HResult
универсальный запрос, чтобы и там, и там работал

"Нет, сынок, это фантастика" (С)

Какая фантастика - комедия (вариант - трагедия).
Все, что связано с прикосновением к ORACLE, у меня лично вызывает острое чувство необходимости вымыть руки. Немедленно.
11 окт 13, 15:02    [14957074]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разделение интервалов (дырки)  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2396
HResult
Все, что связано с прикосновением к ORACLE, у меня лично вызывает острое чувство необходимости вымыть руки. Немедленно.


скорей всего, вы просто не умеет его готовить, а то полмира (мужиков) и не знают

(если что я сам, из стана MS)
11 окт 13, 16:07    [14957451]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разделение интервалов (дырки)  [new]
HResult
Member

Откуда:
Сообщений: 132
StarikNavy
HResult
Все, что связано с прикосновением к ORACLE, у меня лично вызывает острое чувство необходимости вымыть руки. Немедленно.


скорей всего, вы просто не умеет его готовить, а то полмира (мужиков) и не знают

(если что я сам, из стана MS)

У нас много заказчиков и с одной байдой и с другой.
Так, что сугубо личное мнение: то, что можно сделать мизинцем с использованием спинного мозга в MSSQL, для того же самого надо прилагать громадные усилия, искать аутичных ораклоидов, платить им сумасшедшие деньги только для того, чтобы они подсказали совершенно неочевидную, нелогичную и противоестественную комбинацию ключей в запрятанной под матрацем форме.
И это начиная от бакапа и заканчивая синтаксисом и командами.
Это мое мнение.
Да и по производительности я лично не вижу большой разницы.
А даже вижу разницу большую в пользу MS SQL. При чем ORACLE настраивает и сопровождает заказчик. Настоящие, шчырые спецылисты. Грудь у медалях по пояс.
Но такие тормоза...
11 окт 13, 19:06    [14958187]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разделение интервалов (дырки)  [new]
Добрый Э - Эх
Guest
HResult
то, что можно сделать мизинцем с использованием спинного мозга в MSSQL, для того же самого надо прилагать громадные усилия, искать аутичных ораклоидов, платить им сумасшедшие деньги только для того, чтобы они подсказали совершенно неочевидную, нелогичную и противоестественную комбинацию ключей в запрятанной под матрацем форме.
Без конкретных примеров для двух систем - не более чем пустое словоблудие

HResult
И это начиная от бакапа и заканчивая синтаксисом и командами
Ты, конечно, мне не поверишь, но большинство синтаксических конструкций стандартизовано институтом ANSI, а потому - совпадает в обоих системах.
Что касается бэк-апа. Лень учить команды - используй OEM. Там как раз всё для ленивых - на кнопочках и в визуальном оконном интерфейсе, по аналогии с MS SQL-ной менеджмент студией.

HResult
Грудь у медалях по пояс.
У Брежнева медалей тоже по самые яйца были, но в Оракле он был не бум-бум. Да и генсек из него был так себе - неважнецкий, хоть медали именно за генсекство и получал

HResult
с использованием спинного мозга в MSSQL
в общем случае считается, что мыслительная деятельность должна осуществляться всё же головным мозгом...

HResult
Это мое сугубо личное мнение.
Любой человек может ошибаться, но свято верить в свою правоту ;)
11 окт 13, 20:27    [14958384]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разделение интервалов (дырки)  [new]
HResult
Member

Откуда:
Сообщений: 132
Добрый Э - Эх
HResult
то, что можно сделать мизинцем с использованием спинного мозга в MSSQL, для того же самого надо прилагать громадные усилия, искать аутичных ораклоидов, платить им сумасшедшие деньги только для того, чтобы они подсказали совершенно неочевидную, нелогичную и противоестественную комбинацию ключей в запрятанной под матрацем форме.
Без конкретных примеров для двух систем - не более чем пустое словоблудие

HResult
И это начиная от бакапа и заканчивая синтаксисом и командами
Ты, конечно, мне не поверишь, но большинство синтаксических конструкций стандартизовано институтом ANSI, а потому - совпадает в обоих системах.
Что касается бэк-апа. Лень учить команды - используй OEM. Там как раз всё для ленивых - на кнопочках и в визуальном оконном интерфейсе, по аналогии с MS SQL-ной менеджмент студией.

HResult
Грудь у медалях по пояс.
У Брежнева медалей тоже по самые яйца были, но в Оракле он был не бум-бум. Да и генсек из него был так себе - неважнецкий, хоть медали именно за генсекство и получал

HResult
с использованием спинного мозга в MSSQL
в общем случае считается, что мыслительная деятельность должна осуществляться всё же головным мозгом...

HResult
Это мое сугубо личное мнение.
Любой человек может ошибаться, но свято верить в свою правоту ;)

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

А уж во что он там верит, и как свято - это его личное дело.

Я смотрю со своей колокольни. (И всем рекомендую).
Что, установка быстрее или медленнее, удобно или неудобно, легче-тяжелее, дороже-дешевле, итд.

Я точно знаю, что нам было бы намного легче, если бы Оракла не было. Нам лично.
На сейчас стало намного проще, у большинство наших заказчиков все IT железо и дата-центры в оутсорсе и стало не проблемой под наши новые системы брать MS SQL.
Но несколько мастодонтов осталось в сопровождении и это напрягает.
11 окт 13, 21:48    [14958657]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить