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

Откуда:
Сообщений: 12
Имеется таблица

+-------------+------------------------------------+--------------------------------------+
+-----Id------+--------data_start--------------+---------data_stop-------------+
|18828035 | 2011-01-15 14:15:43.267 | 2011-01-15 14:15:43.267 |
|18828036 | 2011-01-15 14:15:43.267 | 2011-01-15 16:37:24.990 |
|18828899 | 2011-01-15 16:37:24.993 | 2011-01-15 17:27:27.003 |
|18829163 | 2011-01-15 17:27:27.007 | 2011-01-17 15:29:38.907 |
|18841878 | 2011-01-17 15:29:38.930 | 2011-01-19 08:28:55.863 |
|18854355 | 2011-01-19 08:28:56.203 | 2011-01-19 08:32:53.597 |
|18854666 | 2011-01-19 08:57:24.407 | 2011-01-19 09:01:14.910 |
|18854723 | 2011-01-19 09:01:14.913 | 2011-01-19 11:02:28.927 |
|18856675 | 2011-01-19 11:02:28.930 | 2011-01-19 12:08:00.490 |
|18857683 | 2011-01-19 12:08:00.490 | 2011-01-19 12:36:26.267 |
--------------+-------------------------------------+-------------------------------------+

Необходимо выбрать ID у которых id.data_stop отличается от (ID+1).data_start на определенную величину, к примеру на 1сек.

Смотрел в сторону ROW_NUMBER().
18 апр 12, 13:09    [12432603]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение 2х последовательных записей в одной таблице  [new]
iljy
Member

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

зачем вам ROW_NUMBER, если надо просто по ID+1 соединять?
18 апр 12, 13:19    [12432670]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение 2х последовательных записей в одной таблице  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
SELECT *
FROM T
WHERE EXISTS(SELECT * FROM T TT WHERE TT.ID=T.ID+1 AND ABS(DATEDIFF(SECOND,T.data_stop,TT.data_stop))=1);
18 апр 12, 13:21    [12432688]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение 2х последовательных записей в одной таблице  [new]
Yasha123
Member

Откуда:
Сообщений: 1973
declare @dif int = 1
declare @t table (Id int, data_start datetime, data_stop datetime)
insert into @t values
(18828035, '2011-01-15 14:15:43.267', '2011-01-15 14:15:43.267'),
(18828036, '2011-01-15 14:15:43.267', '2011-01-15 16:37:24.990'),
(18828899, '2011-01-15 16:37:24.993', '2011-01-15 17:27:27.003'),
(18829163, '2011-01-15 17:27:27.007', '2011-01-17 15:29:38.907'),
(18841878, '2011-01-17 15:29:38.930', '2011-01-19 08:28:55.863'),
(18854355, '2011-01-19 08:28:56.203', '2011-01-19 08:32:53.597'),
(18854666, '2011-01-19 08:57:24.407', '2011-01-19 09:01:14.910'),
(18854723, '2011-01-19 09:01:14.913', '2011-01-19 11:02:28.927'),
(18856675, '2011-01-19 11:02:28.930', '2011-01-19 12:08:00.490'),
(18857683, '2011-01-19 12:08:00.490', '2011-01-19 12:36:26.267')

select t1.id
from @t t1 join @t t2 on t1.Id = t2.Id + 1
where abs(datediff(second, t1.data_stop, t2.data_start)) = @dif


?
18 апр 12, 13:25    [12432705]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение 2х последовательных записей в одной таблице  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
iap
SELECT *
FROM T
WHERE EXISTS(SELECT * FROM T TT WHERE TT.ID=T.ID+1 AND ABS(DATEDIFF(SECOND,T.data_stop,TT.data_stop))=1);
А если надо чтобы разница была абсолютно точно ±1 секунда, то
SELECT *
FROM T
WHERE EXISTS
(
 SELECT *
 FROM T TT
 WHERE TT.ID=T.ID+1 AND TT.data_stop IN(DATEADD(SECOND,-1,T.data_stop),DATEADD(SECOND,1,T.data_stop))
);
18 апр 12, 13:49    [12432846]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение 2х последовательных записей в одной таблице  [new]
neiron2
Member

Откуда:
Сообщений: 12
iljy
neiron2,

зачем вам ROW_NUMBER, если надо просто по ID+1 соединять?


С SQL по роду деятельности много не приходилось работать, поэтому коллеги-программисты посоветовали смотреть в ту сторону


iap
SELECT *
FROM T
WHERE EXISTS(SELECT * FROM T TT WHERE TT.ID=T.ID+1 AND ABS(DATEDIFF(SECOND,T.data_stop,TT.data_stop))=1);


Тут, мне кажется, проблема будет в T.ID+1, т.к. ID'шники упорядочены, но не последовательны. Таблица уже представляет из себя выборку по определенным условиям
18 апр 12, 14:02    [12432915]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение 2х последовательных записей в одной таблице  [new]
Yasha123
Member

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

в смысле надо не между соседними с разницей в 1, а просто между соседними, если упорядочить по возрастанию?
18 апр 12, 14:07    [12432937]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение 2х последовательных записей в одной таблице  [new]
iljy
Member

Откуда:
Сообщений: 8711
neiron2
Тут, мне кажется, проблема будет в T.ID+1, т.к. ID'шники упорядочены, но не последовательны. Таблица уже представляет из себя выборку по определенным условиям

Ну так надо четче задачу формулировать. Тут конечно надо использовать ROW_NUMBER.
18 апр 12, 14:10    [12432953]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение 2х последовательных записей в одной таблице  [new]
_ч_
Member

Откуда:
Сообщений: 1446
neiron2
Тут, мне кажется, проблема будет в T.ID+1, т.к. ID'шники упорядочены, но не последовательны. Таблица уже представляет из себя выборку по определенным условиям


Ну так зачем Вы пишете так запутанно условие в первом посту (про ID+1)?

Если в значениях поля Id есть разрывы, то как то так должно работать:

select * from (select row_number() over (order by id) as id2,* from @t) as t1
left join(select row_number() over (order by id) as id2,* from @t)t2 on t2.id2 = t1.id2+1
where DATEDIFF(SECOND, t1.data_stop, t2.data_start)=1
18 апр 12, 14:11    [12432963]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение 2х последовательных записей в одной таблице  [new]
neiron2
Member

Откуда:
Сообщений: 12
Yasha123,
Т.е. это процессы событий с датой начала и конца. Необходимо по таблице проследить, что бы цепь не прерывалась более чем n-секунд, часов, дней. Т.е. время окончания одного события, должно практически полностью совпадать со времени следующего за ним события.
ID.data_stop=(ID+1).data_start


iap
SELECT *
FROM T
WHERE EXISTS(SELECT * FROM T TT WHERE TT.ID=T.ID+1 AND ABS(DATEDIFF(SECOND,T.data_stop,TT.data_stop))=1);

Не совсем понял суть данного запроса, поэтому не смог определить где у Вас опечатка, полностью отсутствует поле data_start
18 апр 12, 14:14    [12432986]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение 2х последовательных записей в одной таблице  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
_ч_
neiron2
Тут, мне кажется, проблема будет в T.ID+1, т.к. ID'шники упорядочены, но не последовательны. Таблица уже представляет из себя выборку по определенным условиям


Ну так зачем Вы пишете так запутанно условие в первом посту (про ID+1)?

Если в значениях поля Id есть разрывы, то как то так должно работать:

select * from (select row_number() over (order by id) as id2,* from @t) as t1
left join(select row_number() over (order by id) as id2,* from @t)t2 on t2.id2 = t1.id2+1
where DATEDIFF(SECOND, t1.data_stop, t2.data_start)=1
Но там же ещё сомнительное место есть. Даже два.
"отличаются на 1 секунду" - это же как в большую, так и в меньшую сторону?
Ровно на одну секунду или на одну секунду без учёта миллисекунд и более мелких единиц измерения?
18 апр 12, 14:15    [12433001]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение 2х последовательных записей в одной таблице  [new]
Yasha123
Member

Откуда:
Сообщений: 1973
declare @dif int = 1

declare @t table (Id int, data_start datetime, data_stop datetime)
insert into @t values
(18828035, '2011-01-15 14:15:43.267', '2011-01-15 14:15:43.267'),
(18828036, '2011-01-15 14:15:43.267', '2011-01-15 16:37:24.990'),
(18828899, '2011-01-15 16:37:24.993', '2011-01-15 17:27:27.003'),
(18829163, '2011-01-15 17:27:27.007', '2011-01-17 15:29:38.907'),
(18841878, '2011-01-17 15:29:38.930', '2011-01-19 08:28:55.863'),
(18854355, '2011-01-19 08:28:56.203', '2011-01-19 08:32:53.597'),
(18854666, '2011-01-19 08:57:24.407', '2011-01-19 09:01:14.910'),
(18854723, '2011-01-19 09:01:14.913', '2011-01-19 11:02:28.927'),
(18856675, '2011-01-19 11:02:28.930', '2011-01-19 12:08:00.490'),
(18857683, '2011-01-19 12:08:00.490', '2011-01-19 12:36:26.267')

;with tt as
( select ROW_NUMBER() over(order by id) as rn,
         *
from @t
)

select t1.id
from tt t1 join tt t2 on t1.rn = t2.rn + 1
where abs(datediff(second, t1.data_stop, t2.data_start)) = @dif


(а почему надо left join? вроде если пары нет, то и разницы в @dif секунд не может быть, не?)
18 апр 12, 14:17    [12433020]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение 2х последовательных записей в одной таблице  [new]
neiron2
Member

Откуда:
Сообщений: 12
_ч_,
Согласен, некорректно сформулировал
18 апр 12, 14:17    [12433023]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение 2х последовательных записей в одной таблице  [new]
neiron2
Member

Откуда:
Сообщений: 12
iap
_ч_
пропущено...


Ну так зачем Вы пишете так запутанно условие в первом посту (про ID+1)?

Если в значениях поля Id есть разрывы, то как то так должно работать:

select * from (select row_number() over (order by id) as id2,* from @t) as t1
left join(select row_number() over (order by id) as id2,* from @t)t2 on t2.id2 = t1.id2+1
where DATEDIFF(SECOND, t1.data_stop, t2.data_start)=1
Но там же ещё сомнительное место есть. Даже два.
"отличаются на 1 секунду" - это же как в большую, так и в меньшую сторону?
Ровно на одну секунду или на одну секунду без учёта миллисекунд и более мелких единиц измерения?


Про секунду это я к примеру сказал, по идее предполагается просто указать любой временной промежуток. Будет проведено ряд испытаний, например: событий с разницей в окончании одного этапа и начале следующего в 1 сек-100шт, в 1час- 10шт, в 24часа- 2 шт
18 апр 12, 14:20    [12433057]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение 2х последовательных записей в одной таблице  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
neiron2
Yasha123,
Т.е. это процессы событий с датой начала и конца. Необходимо по таблице проследить, что бы цепь не прерывалась более чем n-секунд, часов, дней. Т.е. время окончания одного события, должно практически полностью совпадать со времени следующего за ним события.
ID.data_stop=(ID+1).data_start


iap
SELECT *
FROM T
WHERE EXISTS(SELECT * FROM T TT WHERE TT.ID=T.ID+1 AND ABS(DATEDIFF(SECOND,T.data_stop,TT.data_stop))=1);

Не совсем понял суть данного запроса, поэтому не смог определить где у Вас опечатка, полностью отсутствует поле data_start
Да. Упустил.
Надо TT.data_stop заменить на TT.data_start.
Если TT.data_start всегда >= T.data_stop, то убрать ABS() и поменять даты местами (меньшая - второй аргумент, большая - третий)
18 апр 12, 14:21    [12433058]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение 2х последовательных записей в одной таблице  [new]
_ч_
Member

Откуда:
Сообщений: 1446
iap
Но там же ещё сомнительное место есть. Даже два.
"отличаются на 1 секунду" - это же как в большую, так и в меньшую сторону?

Согласен, в таком случае автору может помочь abs().

iap
Ровно на одну секунду или на одну секунду без учёта миллисекунд и более мелких единиц измерения?


Я исходил из первого поста (описания задачи). Так что этот вопрос лучше задать автору, он написал разница 1 секунда (а не 1000 мс, например).
18 апр 12, 14:25    [12433104]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение 2х последовательных записей в одной таблице  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3752
row_number!
НО !! Сортировать надо по дате а не по id !!!
18 апр 12, 15:22    [12433624]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить