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

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

Есть отсортированая таблица (вью)
IDFLAG
10
20
31
41
51
60
71
81


Нужен запрос, возвращающий "ссылку" на первый элемент непрерывной последовательности

IDFLAGFirstID
10null
20null
313
413
513
60null
717
817


Подскажите куда копать? (Данных очень много, курсор или что-то типа нарастающего итога не подходит)

Спасибо
8 фев 16, 22:09    [18790383]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом, найти ссылка на первый элемент в "последовательности"  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
Givv,
DECLARE @t TABLE (ID int,FLAG int) 
INSERT INTO @t(ID,FLAG)
SELECT 1, 0 
UNION SELECT 2, 0 
UNION SELECT 3, 1 
UNION SELECT 4, 1 
UNION SELECT 5, 1 
UNION SELECT 6, 0 
UNION SELECT 7, 1 
UNION SELECT 8, 1 

;WITH 
 t1(ID,FLAG,N) AS(SELECT ID,FLAG,CASE WHEN FLAG <> 0 THEN ID - ROW_NUMBER()OVER(PARTITION BY FLAG ORDER BY ID) ELSE NULL END AS N FROM @t)
,t2(N,ID) AS(SELECT N, MIN(ID) AS ID FROM t1 WHERE N IS NOT NULL GROUP BY N)
 SELECT t1.ID, t1.FLAG, t2.ID AS FIRSTID
 FROM t1
 LEFT JOIN t2 ON t2.N = t1.N
 ORDER BY t1.ID
9 фев 16, 07:18    [18790935]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом, найти ссылка на первый элемент в "последовательности"  [new]
Givv
Member

Откуда:
Сообщений: 70
LexusR,

спасибо, мой пример немного кривой, вводит в заблуждение, ID там не должно совпадать с номером строки (и вообще это дата)
Т.е. так не работает

DECLARE @t TABLE (ID int,FLAG int) 
INSERT INTO @t(ID,FLAG)
SELECT 5, 0 
UNION SELECT 6, 0 
UNION SELECT 9, 1 
UNION SELECT 22, 1 
UNION SELECT 25, 1 
UNION SELECT 26, 0 
UNION SELECT 37, 1 
UNION SELECT 42, 1 

;WITH t1 (ID, FLAG, N) AS
(SELECT ID, FLAG, CASE WHEN FLAG <> 0 THEN ID - ROW_NUMBER() OVER (PARTITION BY FLAG ORDER BY ID) ELSE NULL END AS N FROM @t)
,t2(N,ID) AS (SELECT N, MIN(ID) AS ID FROM t1 WHERE N IS NOT NULL GROUP BY N)
 SELECT t1.ID, t1.FLAG, t2.ID AS FIRSTID
 FROM t1
 LEFT JOIN t2 ON t2.N = t1.N
 ORDER BY t1.ID
9 фев 16, 08:51    [18791056]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом, найти ссылка на первый элемент в "последовательности"  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8316
Givv,

по-моему уже было что-то подобное. Вам нудно пронумеровать группы и присвоить значение первого элемента всем элементам группы.
"Лёгкого" нет решения.
9 фев 16, 11:41    [18791972]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом, найти ссылка на первый элемент в "последовательности"  [new]
iap
Member

Откуда: Москва
Сообщений: 47047
Владислав Колосов
Givv,

по-моему уже было что-то подобное. Вам нудно пронумеровать группы и присвоить значение первого элемента всем элементам группы.
"Лёгкого" нет решения.
Да ладно!
Весь запрос можно построить на вложенных NOT EXISTS().
Даже и без нумерации.

Ведь "первый элемент непрерывной последовательности" - это такая строка,
до которой (по полям, определяющим порядок записей) не существует (NOT EXISTS)
записи с теми же "признаками непрерывности", после которой, но до исходной строки,
не существует (NOT EXISTS) записи с другими "признаками непрерывности".
9 фев 16, 11:50    [18792066]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом, найти ссылка на первый элемент в "последовательности"  [new]
iap
Member

Откуда: Москва
Сообщений: 47047
iap
Ведь "первый элемент непрерывной последовательности" - это такая строка,
до которой (по полям, определяющим порядок записей) не существует (NOT EXISTS)
записи с теми же "признаками непрерывности", после которой, но до исходной строки,
не существует (NOT EXISTS) записи с другими теми же "признаками непрерывности".
Поправил слегка...
9 фев 16, 11:51    [18792076]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом, найти ссылка на первый элемент в "последовательности"  [new]
Givv
Member

Откуда:
Сообщений: 70
iap
с другими теми же "признаками непрерывности".

Так ведь признаки непрерывности для всех последовательностей одинаковы..
Или я что-то не понял?
9 фев 16, 12:00    [18792154]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом, найти ссылка на первый элемент в "последовательности"  [new]
o-o
Guest
@@version >= 2012:
declare @t table (id int,flag int) 
insert into @t(id,flag)
values (5, 0), (6, 0), (9, 1), (22, 1), 
(25, 1), (26, 0), (37, 1), (42, 1); 

with cte as
(
select *, 
       case 
          when lag(flag) over(order by id) is null 
             then null -- no first
          when (flag <> lag(flag) over(order by id)) and 
               (lead(flag) over(order by id) = lag(flag) over(order by id)) -- the only one
             then null 
          when flag <> lag(flag) over(order by id) 
             then id 
          else -1
       end as val
from @t
)

select id, 
       flag, 
	   nullif( 
	   case 
	      when val IS null then NULL
	      else max(val) over(order by id)
	   end, -1) 
	   as first_el    
from cte;
9 фев 16, 12:00    [18792157]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом, найти ссылка на первый элемент в "последовательности"  [new]
iap
Member

Откуда: Москва
Сообщений: 47047
Givv
iap
с другими теми же "признаками непрерывности".

Так ведь признаки непрерывности для всех последовательностей одинаковы..
Или я что-то не понял?
Признаки непрерывности - это одинаковые значения у заданного набора полей.
Я так понимаю.
Если у этой же группы полей значение хоть в одном поле отличается,
то это другая непрерывная последовательность. Разве не так?
9 фев 16, 12:10    [18792231]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом, найти ссылка на первый элемент в "последовательности"  [new]
Glory
Member

Откуда:
Сообщений: 104760
Givv
Нужен запрос, возвращающий "ссылку" на первый элемент непрерывной последовательности

Последовательность это строго больше одной записи ?
Почему для 1 и 2 получились null-ы ?
9 фев 16, 12:13    [18792254]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом, найти ссылка на первый элемент в "последовательности"  [new]
iap
Member

Откуда: Москва
Сообщений: 47047
Что-то я запутался словами описывать, да ещё и общий случай.
Проще запрос написать:
WITH T AS
(
 SELECT * FROM
 (
  VALUES
  (1,0)
 ,(2,0)
 ,(3,1)
 ,(4,1)
 ,(5,1)
 ,(6,0)
 ,(7,1)
 ,(8,1)
 )T(ID,FLAG)
)
SELECT *
FROM T
WHERE NOT EXISTS
(
 SELECT *
 FROM T TT
 WHERE TT.FLAG=T.FLAG AND TT.ID<T.ID
   AND NOT EXISTS
   (
    SELECT *
	FROM T TTT
	WHERE TTT.FLAG<>TT.FLAG AND TTT.ID>TT.ID AND TTT.ID<T.ID
   )
);
9 фев 16, 12:25    [18792334]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом, найти ссылка на первый элемент в "последовательности"  [new]
o-o
Guest
iap
Что-то я запутался словами описывать, да ещё и общий случай.
Проще запрос написать:
WITH T AS
(
 SELECT * FROM
 (
  VALUES
  (1,0)
 ,(2,0)
 ,(3,1)
 ,(4,1)
 ,(5,1)
 ,(6,0)
 ,(7,1)
 ,(8,1)
 )T(ID,FLAG)
)
SELECT *
FROM T
WHERE NOT EXISTS
(
 SELECT *
 FROM T TT
 WHERE TT.FLAG=T.FLAG AND TT.ID<T.ID
   AND NOT EXISTS
   (
    SELECT *
	FROM T TTT
	WHERE TTT.FLAG<>TT.FLAG AND TTT.ID>TT.ID AND TTT.ID<T.ID
   )
);

но ему же еще надо признак "первого",
что он ни на что не ссылается (NULL),
и для "одиночных" тоже нужен NULL.
так что на этом работа не закончена...

К сообщению приложен файл. Размер - 17Kb
9 фев 16, 12:43    [18792498]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом, найти ссылка на первый элемент в "последовательности"  [new]
Givv
Member

Откуда:
Сообщений: 70
Glory
Последовательность это строго больше одной записи ?
Почему для 1 и 2 получились null-ы ?

Нет, может быть одна, ссылка у единичного элемента соответствено просто на себя.
По задаче, для элементов маркировнный как 0 не нужна ссылка на первый элемент, хотя не проблема, если там тоже будет ссылка.
Она не мешает как минимум.
9 фев 16, 12:48    [18792530]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом, найти ссылка на первый элемент в "последовательности"  [new]
Givv
Member

Откуда:
Сообщений: 70
o-o
@@version >= 2012:
declare @t table (id int,flag int) 
insert into @t(id,flag)
values (5, 0), (6, 0), (9, 1), (22, 1), 
(25, 1), (26, 0), (37, 1), (42, 1); 

with cte as
(
select *, 
       case 
          when lag(flag) over(order by id) is null 
             then null -- no first
          when (flag <> lag(flag) over(order by id)) and 
               (lead(flag) over(order by id) = lag(flag) over(order by id)) -- the only one
             then null 
          when flag <> lag(flag) over(order by id) 
             then id 
          else -1
       end as val
from @t
)

select id, 
       flag, 
	   nullif( 
	   case 
	      when val IS null then NULL
	      else max(val) over(order by id)
	   end, -1) 
	   as first_el    
from cte;


Да, 2014, большое спасибо, выглядит перспективно
Сейчас попробую, что там со скоростью на огромных массивах.
9 фев 16, 12:50    [18792551]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом, найти ссылка на первый элемент в "последовательности"  [new]
o-o
Guest
Givv
Нет, может быть одна, ссылка у единичного элемента соответствено просто на себя.
По задаче, для элементов маркировнный как 0 не нужна ссылка на первый элемент, хотя не проблема, если там тоже будет ссылка.
Она не мешает как минимум.

тогда у меня совсем не та логика.
у меня выставлялся не нулям, а одиночным либо первой группе.
вот так надо:
declare @t table (id int,flag int) 
insert into @t(id,flag)
values (5, 0), (6, 0), (9, 1), (22, 1), 
(25, 1), (26, 0), (37, 1), (42, 1); 

with cte as
(
select *,	
       case 
          when flag <> lag(flag, 1, id) over(order by id) 
             then id 
          else -1
       end as val
from @t
)

select id, 
       flag, 
       case flag when 0 then null
                 else max(val) over(order by id)
	   end as first_el    
from cte;

и у iap тогда "ссылки" найдены, приджойнить осталось.
замерьте оба варианта, меньше кода не показатель..
9 фев 16, 13:08    [18792714]     Ответить | Цитировать Сообщить модератору
 Re: Помощь с запросом, найти ссылка на первый элемент в "последовательности"  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Givv,

пять копеек
create table #t (id int identity, flag int)
insert #t values (0), (0),(1), (1),(1), (0),(1), (1)

select id, flag, case when flag = 0 then null else min(id) over(partition by  flag1) end
from 
	(select id, flag, case when flag = 0 then null else sum(1 - flag) over(order by id) end flag1
	from #t) a
 order by id

 drop table #t
9 фев 16, 13:53    [18793097]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить