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

Откуда:
Сообщений: 970
Пример записей:

iddatekey
113:3011111
213:3522222
313:4011111
413:4222222
513:5011111
613:5422222
716:0111111
816:1022222
918:2511111
.........


то есть кроме 7,8,9 должны попасть в отбор.

Это вообще реально?
2 фев 16, 16:11    [18762817]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать записи с одинаковым ключом, разница между которыми не больше 30 минут  [new]
Glory
Member

Откуда:
Сообщений: 104760
_Промешан_
Это вообще реально?

а то
Нужно всего лишб для каждой записи найти одну следующую с большим временем и сравнить эти времена
2 фев 16, 16:14    [18762838]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать записи с одинаковым ключом, разница между которыми не больше 30 минут  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
DECLARE @t TABLE (
	id INT IDENTITY PRIMARY KEY,
	[date] SMALLDATETIME,
	[key] VARCHAR(10)
)

INSERT INTO @t ([date], [key])
VALUES
	('13:30', '11111'),
	('13:35', '22222'),
	('13:40', '11111'),
	('13:42', '22222'),
	('13:50', '11111'),
	('13:54', '22222'),
	('16:01', '11111'),
	('16:10', '22222'),
	('18:25', '11111')

SELECT t.id, t.[date], t.[key]
FROM (
	SELECT *, diff = [date] - LAG([date]) OVER (PARTITION BY [key] ORDER BY id)
	FROM @t
) t
WHERE DATEDIFF(MINUTE, '19000101', t.diff) <= 30 OR diff IS NULL
2 фев 16, 16:22    [18762883]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать записи с одинаковым ключом, разница между которыми не больше 30 минут  [new]
_Промешан_
Member

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


OR diff IS NULL

На реальных данных привносит жуткий результат.
А если это условие убрать, то показываются строки без первоначальной, от которой пляска.

PS: про LAG не знал - спасибо!
2 фев 16, 16:44    [18763044]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать записи с одинаковым ключом, разница между которыми не больше 30 минут  [new]
_Промешан_
Member

Откуда:
Сообщений: 970
Как это можно исправить?
2 фев 16, 17:27    [18763389]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать записи с одинаковым ключом, разница между которыми не больше 30 минут  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
А можно вначале показать ожидаемый результат. На словах плохо понимаю формулировку задачи того, что Вы "ожидаете".
2 фев 16, 17:29    [18763420]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать записи с одинаковым ключом, разница между которыми не больше 30 минут  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4807
Вот так?
DECLARE @t TABLE (
	id INT IDENTITY PRIMARY KEY,
	[date] SMALLDATETIME,
	[key] VARCHAR(10)
)

INSERT INTO @t ([date], [key])
VALUES
	('13:30', '11111'),
	('13:35', '22222'),
	('13:40', '11111'),
	('13:42', '22222'),
	('13:50', '11111'),
	('13:54', '22222'),
	('16:01', '11111'),
	('16:10', '22222'),
	('18:25', '11111')

SELECT t2.* FROM 
(
	SELECT TOP 1 WITH TIES 
		t.id, t.[date], t.[key]
	FROM @t t 
	ORDER BY ROW_NUMBER() OVER (PARTITION BY [key] ORDER BY [date])
) t1 
OUTER APPLY 
(
	SELECT 
		t2.id, t2.[date], t2.[key], diff = DATEDIFF(MINUTE, t1.date, t2.date)
	FROM @t t2 
	WHERE t2.[key] = t1.[key] AND DATEDIFF(MINUTE, t1.date, t2.date) <= 30
) t2
2 фев 16, 17:39    [18763509]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать записи с одинаковым ключом, разница между которыми не больше 30 минут  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4807
В Oracle есть такая аналитическая конструкция, в MS SQL её нет.

SELECT TO_CHAR(timepoint,'DD.MM.YYYY HH24:MI:SS.FF3') "Start_bussiest_5sec_interval"
FROM
  (SELECT timepoint,
    DENSE_RANK() over (order by forw_dens_per_5sec DESC,end_time_5sec-timepoint,timepoint) rank
  FROM
    (SELECT timepoint,
      COUNT(*) over (order by timepoint range BETWEEN CURRENT row AND interval '5' second following ) forw_dens_per_5sec,
      MAX(timepoint) over (order by timepoint range BETWEEN CURRENT row AND interval '5' second following ) end_time_5sec
    FROM trt
    )
  )
WHERE rank=1;
2 фев 16, 17:44    [18763542]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать записи с одинаковым ключом, разница между которыми не больше 30 минут  [new]
_Промешан_
Member

Откуда:
Сообщений: 970
a_voronin
Вот так?
SELECT t2.* FROM 
(
	SELECT TOP 1 WITH TIES 
		t.id, t.[date], t.[key]
	FROM @t t 
	ORDER BY ROW_NUMBER() OVER (PARTITION BY [key] ORDER BY [date])
) t1 
OUTER APPLY 
(
	SELECT 
		t2.id, t2.[date], t2.[key], diff = DATEDIFF(MINUTE, t1.date, t2.date)
	FROM @t t2 
	WHERE t2.[key] = t1.[key] AND DATEDIFF(MINUTE, t1.date, t2.date) <= 30
) t2


Да.
А как-то можно совместить это с первым ответом? Уж очень мне LAG понравился.))
3 фев 16, 14:39    [18767602]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать записи с одинаковым ключом, разница между которыми не больше 30 минут  [new]
_Промешан_
Member

Откуда:
Сообщений: 970
C LAG работает гораздо быстрее, чем OUTER APPLY.
На миллионе записей ранжирование и апплай это ад :(
3 фев 16, 14:45    [18767660]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать записи с одинаковым ключом, разница между которыми не больше 30 минут  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4807
_Промешан_
C LAG работает гораздо быстрее, чем OUTER APPLY.
На миллионе записей ранжирование и апплай это ад :(



На ORACLE можно было бы записать LAG (....) OVER (....BETWEEN interval '30' minutes preceding and CURRENT row ) в одну строку, а на SQL SERVER придется через OUTER APPLY

при правильных индексах не должно тормозить
3 фев 16, 15:03    [18767777]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать записи с одинаковым ключом, разница между которыми не больше 30 минут  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20960
SELECT id, [date], [key]
FROM
(
SELECT id, [date], [key],
DATEDIFF(mi
       , LAG([date]) OVER (PARTITION BY [key] ORDER BY id)
       , [date]
       ) AS [LAG],
DATEDIFF(mi
       , [date]
       , LEAD([date]) OVER (PARTITION BY [key] ORDER BY id)
       ) AS [LEAD]
FROM test
) AS dummy
WHERE [LEAD] <= 30 OR [LAG] <= 30


_Промешан_
OR diff IS NULL

На реальных данных привносит жуткий результат.
А если это условие убрать, то показываются строки без первоначальной, от которой пляска.

Сдаётся мне ещё, что с ним первоначальная запись будет выведена даже в случае, если до следующей более 30 минут.
3 фев 16, 15:27    [18767962]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать записи с одинаковым ключом, разница между которыми не больше 30 минут  [new]
aleks2
Guest
К чему эти плоды воспаленного воображения?

exists(...) - вот истинный дао.
3 фев 16, 17:00    [18768780]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать записи с одинаковым ключом, разница между которыми не больше 30 минут  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4807
_Промешан_
C LAG работает гораздо быстрее, чем OUTER APPLY.
На миллионе записей ранжирование и апплай это ад :(


попробуйте вот так

DECLARE @t TABLE (
	id INT IDENTITY PRIMARY KEY,
	[date] SMALLDATETIME,
	[key] VARCHAR(10)
)

INSERT INTO @t ([date], [key])
VALUES
	('13:30', '11111'),
	('13:35', '22222'),
	('13:40', '11111'),
	('13:42', '22222'),
	('13:50', '11111'),
	('13:54', '22222'),
	('16:01', '11111'),
	('16:10', '22222'),
	('18:25', '11111')

SELECT t2.* FROM 
(
	SELECT TOP 1 WITH TIES 
		t.id, t.[date], t.[key]
	FROM @t t 
	ORDER BY ROW_NUMBER() OVER (PARTITION BY [key] ORDER BY [date])
) t1 
OUTER APPLY 
(
	SELECT 
		t2.id, t2.[date], t2.[key], diff = DATEDIFF(MINUTE, t1.date, t2.date)
	FROM @t t2 
	WHERE t2.[key] = t1.[key] AND t1.date > DATEADD(MINUTE, -30, t2.date)
) t2
3 фев 16, 17:15    [18768920]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать записи с одинаковым ключом, разница между которыми не больше 30 минут  [new]
_Промешан_
Member

Откуда:
Сообщений: 970
a_voronin
_Промешан_
C LAG работает гораздо быстрее, чем OUTER APPLY.
На миллионе записей ранжирование и апплай это ад :(


попробуйте вот так

SELECT t2.* FROM 
(
	SELECT TOP 1 WITH TIES 
		t.id, t.[date], t.[key]
	FROM @t t 
	ORDER BY ROW_NUMBER() OVER (PARTITION BY [key] ORDER BY [date])
) t1 
OUTER APPLY 
(
	SELECT 
		t2.id, t2.[date], t2.[key], diff = DATEDIFF(MINUTE, t1.date, t2.date)
	FROM @t t2 
	WHERE t2.[key] = t1.[key] AND t1.date > DATEADD(MINUTE, -30, t2.date)
) t2
Я в конце добавил ORDER BY key

И за 3 с половиной часа чуда не случилось.
Благо данные - это логи.
3 фев 16, 21:02    [18770024]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать записи с одинаковым ключом, разница между которыми не больше 30 минут  [new]
_Промешан_
Member

Откуда:
Сообщений: 970
C LAG запрос "просирается" за 12 секунд, но без первоначальной строки.
3 фев 16, 21:03    [18770025]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать записи с одинаковым ключом, разница между которыми не больше 30 минут  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20960
_Промешан_, а мой вариант?
4 фев 16, 09:10    [18771017]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать записи с одинаковым ключом, разница между которыми не больше 30 минут  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
если я правильно понял что надо

DECLARE @t TABLE (
	id INT IDENTITY PRIMARY KEY,
	[date] SMALLDATETIME,
	[key] VARCHAR(10)
)

INSERT INTO @t ([date], [key])
VALUES
	('13:30', '11111'),
	('13:35', '22222'),
	('13:40', '11111'),
	('13:42', '22222'),
	('13:50', '11111'),
	('13:54', '22222'),
	('16:01', '11111'),
	('16:10', '22222'),
	('18:25', '11111')


select t1.*
from @t t1
where not exists (
  select 1
  from @t
  where [key] =t1.[key]
    and [date] < DATEADD(MINUTE, -30, t1.[date])
)
order by t1.[key]
4 фев 16, 10:03    [18771235]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать записи с одинаковым ключом, разница между которыми не больше 30 минут  [new]
_Промешан_
Member

Откуда:
Сообщений: 970
Akina
_Промешан_, а мой вариант?


12 секунд. Отличный результат. Спасибо.

А монстрообразные запросы с CROSS APPLY вчера базу повесили на 3 часа. Аукнулось только сегодня. Так что бдите.
4 фев 16, 13:18    [18772354]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать записи с одинаковым ключом, разница между которыми не больше 30 минут  [new]
_Промешан_
Member

Откуда:
Сообщений: 970
Maxx
если я правильно понял что надо

DECLARE @t TABLE (
	id INT IDENTITY PRIMARY KEY,
	[date] SMALLDATETIME,
	[key] VARCHAR(10)
)

INSERT INTO @t ([date], [key])
VALUES
	('13:30', '11111'),
	('13:35', '22222'),
	('13:40', '11111'),
	('13:42', '22222'),
	('13:50', '11111'),
	('13:54', '22222'),
	('16:01', '11111'),
	('16:10', '22222'),
	('18:25', '11111')


select t1.*
from @t t1
where not exists (
  select 1
  from @t
  where [key] =t1.[key]
    and [date] < DATEADD(MINUTE, -30, t1.[date])
)
order by t1.[key]


За 40 секунд не вышло результата.
Hash Match (Right Anti Semi Join) - 99%
4 фев 16, 13:22    [18772384]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать записи с одинаковым ключом, разница между которыми не больше 30 минут  [new]
_Промешан_
Member

Откуда:
Сообщений: 970
С LAG и LEAD - ну очень красивый план получается на 2012 скуле.
4 фев 16, 13:24    [18772398]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать записи с одинаковым ключом, разница между которыми не больше 30 минут  [new]
А на фига там LEAD?
Guest
Можно план еще улучшить.
LAG([date],1,[date])
4 фев 16, 13:57    [18772622]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать записи с одинаковым ключом, разница между которыми не больше 30 минут  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20960
А на фига там LEAD?

А чтобы через год не втыкать, за каким там создана такая конструкция. С LEAD же всё понятно практически без слов.
4 фев 16, 14:05    [18772658]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать записи с одинаковым ключом, разница между которыми не больше 30 минут  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
_Промешан_
Hash Match (Right Anti Semi Join) - 99%

а что вы еще хотели от нот екзист ?
4 фев 16, 14:14    [18772702]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать записи с одинаковым ключом, разница между которыми не больше 30 минут  [new]
Ну если раз в год...
Guest
...пользоваться лагами, то да, вы правы
4 фев 16, 14:15    [18772718]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить