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

Откуда: СПб
Сообщений: 101
Доброе время суток
Помогите, пожалуйста, упростить запрос. Суть в следующем. Есть таблица, которая упорядочивается по полям BoreId, from_.
На заданном порядке нужно для всех одинаковых (по ходу просмотра) BoreId,StratId получить начальное (минимальное) значение по полю from_ и конечное (максимальное) значение по полю to_. Пока получилось следующее.
Declare @Strats Table(BoreId Int,StratId Int, from_ Float, to_ Float);
Insert @Strats Values
	(1,1,2,5),	(1,1,0,2), 
	(1,2,5,10), (1,2,10,15),
	(1,1,15,25), (1,1,27,30),
	(1,3,35,40), (1,3,30,35),
	(1,2,40,45), (1,2,50,55),
	(1,4,55,65), (1,1,25,27),
	(2,1,0,22), (2,1,22,32),
	(2,2,32,42), (2,2,42,52),
	(2,1,52,62), (2,1,62,72),
	(2,3,72,84);
--фильтруем результат, убирая строки int_start_row is null
Select BoreId,StratId,from_,to_
From
--записываем в строки где int_start_row и int_end_row содержат 1
--значение начала и конца интервала. Для строк где int_start_row = 1,
--а int_end_row = 0 подтягиваем конец интервала с последующей строки.
--Для остальных Null
(Select BoreId,StratId
,IIf(Int_Start_Row=1,from_,Null) As from_
,IIf(Int_Start_Row=1 And Int_End_Row=1,to_,
IIf(Int_Start_Row=1,lead(to_) Over(Order By BoreId,from_),Null)) as to_
From
--исключаем строки, где и в int_strat_row и в int_end_row 0
(Select BoreId,StratId,from_,to_,Int_Start_Row,Int_End_Row
From
--помечаем начало и конец интервала по BoreId, StratId 1
(Select s.BoreId,s.StratId,s.from_,s.to_
,IIf(lag(s.StratId,1) Over(Order By s.BoreId,s.from_) = s.StratId,0,1) As Int_Start_Row
,IIf(lead(s.StratId,1) Over(Order By s.BoreId,s.from_) = s.StratId,0,1) As Int_End_Row
From @Strats s) def_start_end
Where Int_Start_Row<>0 Or Int_End_Row<>0
) tfinish) remove_null
Where from_ Is Not Null
Order By BoreId,from_;

Заранее спасибо всем откликнувшимся
10 апр 15, 03:06    [17496586]     Ответить | Цитировать Сообщить модератору
 Re: Упростить запрос по выборке начала и конца "группы" по заданному порядку  [new]
Добрый Э - Эх
Guest
anvg,

ну, банальный заезжанный grp_id на разности двух разнооконных row_number-ах, проще некуда:
--
-- Тестовый набор данных:
with
  Strats(BoreId, StratId, from_, to_) as
    (
      select *
        from (
               values
                 (1,1,2,5),(1,1,0,2),(1,2,5,10),(1,2,10,15),(1,1,15,25),(1,1,27,30),(1,3,35,40),
                 (1,3,30,35),(1,2,40,45),(1,2,50,55),(1,4,55,65),(1,1,25,27),(2,1,0,22),
                 (2,1,22,32),(2,2,32,42),(2,2,42,52),(2,1,52,62),(2,1,62,72),(2,3,72,84)
             ) v(BoreId,StratId, from_, to_)
    )
--
-- Основной запрос:
select BoreId, StratId, min(from_) as from_, max(to_) as to_
  from (
         select s.*,
                row_number() over(partition by s.StratId order by s.BoreId, s.from_) -
                row_number() over(order by s.BoreId, s.from_) as grp_id
           from Strats s
       ) v
 group by BoreId, StratId, grp_id
 order by BoreId, min(from_)
on-line проверка на sqlfiddle.com
10 апр 15, 05:22    [17496626]     Ответить | Цитировать Сообщить модератору
 Re: Упростить запрос по выборке начала и конца "группы" по заданному порядку  [new]
aleks2
Guest
Declare @Strats Table(BoreId Int,StratId Int, from_ Float, to_ Float
                      ,id int identity primary key clustered -- а то нема у тя "по ходу просмотра"
);
Insert @Strats Values
	(1,1,2,5),	(1,1,0,2), 
	(1,2,5,10), (1,2,10,15),
	(1,1,15,25), (1,1,27,30),
	(1,3,35,40), (1,3,30,35),
	(1,2,40,45), (1,2,50,55),
	(1,4,55,65), (1,1,25,27),
	(2,1,0,22), (2,1,22,32),
	(2,2,32,42), (2,2,42,52),
	(2,1,52,62), (2,1,62,72),
	(2,3,72,84);

-- поскольку тредстартер нихера не смыслит, то
-- "получить начальное (минимальное) значение по полю from_ и конечное (максимальное) значение по полю to_. "
-- слегка неоднозначно.

-- 1. Если всеж "минимальное и максимальное"
;with
nums as (select *
           , ROW_NUMBER()over(PARTITION by BoreId, StratId order by id) as n 
           , ROW_NUMBER()over(order by id) as n0 from @Strats)
select min(id) id, BoreId, StratId, min(from_) min_from_, max(to_) max_to_ from nums group by BoreId, StratId, (n0 - n)


-- 2. Если всеж "начальное и конечное"
;with
nums as (select *
           , ROW_NUMBER()over(PARTITION by BoreId, StratId order by id) as n 
           , ROW_NUMBER()over(order by id) as n0 from @Strats),
ranges as (select min(id) min_id, max(id) max_id from nums group by BoreId, StratId, (n0 - n))
select r.min_id, r.max_id, s1.BoreId, s1.StratId, s1.from_, s2.to_ 
  from ranges r 
      inner join @Strats s1 on s1.id = r.min_id
      inner join @Strats s2 on s2.id = r.max_id;



PS: Этот вариант с разницей ROW_NUMBERs - самый тормознутый способ отыскания групп.
10 апр 15, 05:37    [17496631]     Ответить | Цитировать Сообщить модератору
 Re: Упростить запрос по выборке начала и конца "группы" по заданному порядку  [new]
anvg
Member

Откуда: СПб
Сообщений: 101
Добрый Э - Эх
Спасибо большое. Ваш вариант отработал чётко.
aleks2
aleks2
id int identity primary key clustered -- а то нема у тя "по ходу просмотра"

А смысл? Данные исходно не упорядочены. Добрый Э - Эх как раз и использует Order By s.BoreId, s.from_ для задания требуемого порядка. И при этом порядке разницы между начальным и минимальным, конечным и максимальным - никакой.
В общем ни тот ни другой вариант не выдал правильного результата, увы.
Прокомментируйте, пожалуйста, поподробнее
aleks2
PS: Этот вариант с разницей ROW_NUMBERs - самый тормознутый способ отыскания групп.

А чем вариант вычисления разницы Row_Numbers как поля запроса хуже, чем вычисляемой разницы при группировке?
10 апр 15, 12:46    [17498162]     Ответить | Цитировать Сообщить модератору
 Re: Упростить запрос по выборке начала и конца "группы" по заданному порядку  [new]
iap
Member

Откуда: Москва
Сообщений: 47197
anvg,

И как только жили до SQL2005, в котором ROW_NUMBER() появился?
Я к тому, что показанными решениями их множество не ограничивается.
Можно, к примеру, с помощью предикатов NOT EXISTS() тоже добиться желаемого.
10 апр 15, 12:54    [17498219]     Ответить | Цитировать Сообщить модератору
 Re: Упростить запрос по выборке начала и конца "группы" по заданному порядку  [new]
anvg
Member

Откуда: СПб
Сообщений: 101
iap
iap
И как только жили до SQL2005, в котором ROW_NUMBER() появился?

Почему жили? До сих пор живёт:) Выгружается в программу
Select * From @Strats Order By BoreI,from_

и пробегается циклом. По смене BoreId,StratId определяется начало и конец интервала. Просто, если всё через SQL, то объем выгрузки со связанными данными будет в 2-3 раза меньше.
iap
Можно, к примеру, с помощью предикатов NOT EXISTS() тоже добиться желаемого.

А не подскажите ли за спасибо - как? И будет ли это эффективно?
10 апр 15, 13:07    [17498301]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить