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

Откуда:
Сообщений: 5
Помогите с запросом.
Имеется вот такая вюха, которая тенет данные из многих таблиц:
SELECT TOP 1000
[event_datetime]
,[event_date]
,[Expr1]
,[object_id]
,[object_name]
,[pass_nomer]
,[event_time]
FROM [VISITOR].[dbo].[vw_Visitor_vw]
order by event_datetime, pass_nomer DESC

часть результата запроса:

event_datetime event_date Expr1 object_id object_name pass_nomer event_time
2014-08-18 21:16:28.000 18.08.2014 Столовая Г/З 4224 Дяк Александ Поликович 1068 21
2014-08-18 21:19:03.000 18.08.2014 Операторская 4224 Дяк Александ Поликович 1068 21
2014-08-18 21:19:55.000 18.08.2014 Диспетчерская 4224 Дяк Александ Поликович 1068 21
2014-08-18 21:20:01.000 18.08.2014 Столовая Г/З 4224 Дяк Александ Поликович 1068 21
2014-08-18 21:20:18.000 18.08.2014 Столовая Г/З 4224 Дяк Александ Поликович 1068 21 Дел <17
2014-08-18 21:20:26.000 18.08.2014 Столовая Г/З 4224 Дяк Александ Поликович 1068 21 Дел <27
2014-08-18 21:20:35.000 18.08.2014 Операторская 4224 Дяк Александ Поликович 1068 21
2014-08-18 21:21:21.000 18.08.2014 Операторская 4224 Дяк Александ Поликович 1068 21
2014-09-01 09:19:59.000 01.09.2014 Столовая Ч/З 24490 Супрун Алексей Йосипович 1007 9
2014-09-01 09:20:02.000 01.09.2014 Столовая Ч/З 24490 Супрун Алексей Йосипович 1007 9 Дел <3
2014-09-01 09:20:07.000 01.09.2014 Столовая Ч/З 24490 Супрун Алексей Йосипович 1007 9 Дел <8
2014-09-01 09:20:10.000 01.09.2014 Столовая Ч/З 24490 Супрун Алексей Йосипович 1007 9 Дел <11
2014-09-01 09:20:15.000 01.09.2014 Столовая Ч/З 24490 Супрун Алексей Йосипович 1007 9 Дел <16
2014-09-01 10:56:57.000 01.09.2014 Столовая Г/З 4628 Полева Ольга Юриевна 2563 10

Нужно чтобы строки, в которых время между посещением ((if(Expr1 = Expr1) then (if(event_datetime < (event_datetime + 30))) меньше 30 секунд – не отображалтсь во вюхе.

К сообщению приложен файл. Размер - 131Kb
25 май 16, 12:14    [19217100]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с заданным параметром  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21042
Sparda
Нужно чтобы строки, в которых время между посещением ((if(Expr1 = Expr1) then (if(event_datetime < (event_datetime + 30))) меньше 30 секунд – не отображалтсь во вюхе.

Ну так и допиши в неё соответствующий WHERE...
25 май 16, 12:33    [19217266]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с заданным параметром  [new]
Sparda
Member

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

Не получится)
Например строка(забыл пронумеровать) под номером 4 имеет дату посещения 2014-08-18 21:20:01.000, следующая строка имеет
дату 2014-08-18 21:20:18.000. Вот ее мне уже отображать не надо, так как интервал 30 сек между ними еще не прошел.
25 май 16, 12:59    [19217501]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с заданным параметром  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21042
WHERE DATEDIFF('s', event_datetime,LEAD(event_datetime) OVER ... ) < 30
25 май 16, 13:23    [19217705]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с заданным параметром  [new]
Rankatan
Member

Откуда:
Сообщений: 250
Akina
WHERE DATEDIFF('s', event_datetime,LEAD(event_datetime) OVER ... ) < 30

нельзя использовать оконные функции в where.
25 май 16, 13:52    [19217943]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с заданным параметром  [new]
Sparda
Member

Откуда:
Сообщений: 5
Не совсем понятно.
Надо пример так:
Вывелась строка под номером 4, следующая(5строка) проверяется с предыдущей(4-той строкой), если не прошло 30 секунд, то мы 5-тую не выводим. Берем 6-ую строку, сравниваем с той что у нас выведена (это 4-тая), прошло – нет, берем 7-мую сравниваем, если прошло 30 сек, то выводим ее.
Идем далее, берем 8-ую строку и сравниваем ее уже с той что выведена (это 7). И так далее.
В таблице более миллиона записей, и все они хаотично записаны. Сравнивать надо время и номер пропуска. Короче хз))))
Прошу помощи))
25 май 16, 13:54    [19217963]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с заданным параметром  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21042
Rankatan, это идея, а не готовый код. Получи, например, нужные для отбора значения в подзапросе, а потом отсей нужное - и уже получишь то, что требуется.
25 май 16, 15:13    [19218568]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с заданным параметром  [new]
Sparda
Member

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

Увы((( на 2008 сервере LEAD не работает
а вот на тестовом 2012 кое что получилось, но опять таки с LEAD
Может есть еще какие варианты?
27 май 16, 11:02    [19226974]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с заданным параметром  [new]
CrazHunt
Member

Откуда:
Сообщений: 40
SELECT  t1.[event_datetime] ,
        t1.[event_date] ,
        t1.[Expr1] ,
        t1.[object_id] ,
        t1.[object_name] ,
        t1.[pass_nomer] ,
        t1.[event_time]
FROM    [dbo].[vw_Visitor_vw] t1
JOIN    [dbo].[vw_Visitor_vw] t2 ON t1.pass_nomer = t2.pass_nomer
                                    AND t1.event_datetime > t2.event_datetime
GROUP BY t1.[event_datetime] ,
        t1.[event_date] ,
        t1.[Expr1] ,
        t1.[object_id] ,
        t1.[object_name] ,
        t1.[pass_nomer] ,
        t1.[event_time]
HAVING  DATEDIFF(ss, t1.event_datetime, MAX(t2.event_datetime)) > 30


Как-то так. Не проверял, но идея надеюсь ясна
27 май 16, 14:10    [19228007]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с заданным параметром  [new]
CrazHunt
Member

Откуда:
Сообщений: 40
Не, наврал по-ходу.

Вот так будет лучше:
SELECT  t1.[event_datetime] ,
        t1.[event_date] ,
        t1.[Expr1] ,
        t1.[object_id] ,
        t1.[object_name] ,
        t1.[pass_nomer] ,
        t1.[event_time]
FROM    [dbo].[vw_Visitor_vw] t1

except

SELECT  t1.[event_datetime] ,
        t1.[event_date] ,
        t1.[Expr1] ,
        t1.[object_id] ,
        t1.[object_name] ,
        t1.[pass_nomer] ,
        t1.[event_time]
FROM    [dbo].[vw_Visitor_vw] t1
JOIN    [dbo].[vw_Visitor_vw] t2 ON t1.pass_nomer = t2.pass_nomer
                                    AND t1.event_datetime > t2.event_datetime
GROUP BY t1.[event_datetime] ,
        t1.[event_date] ,
        t1.[Expr1] ,
        t1.[object_id] ,
        t1.[object_name] ,
        t1.[pass_nomer] ,
        t1.[event_time]
HAVING  DATEDIFF(ss, t1.event_datetime, MAX(t2.event_datetime)) < 30
27 май 16, 14:14    [19228042]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с заданным параметром  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Sparda
Помогите с запросом.
Имеется вот такая вюха, которая тенет данные из многих таблиц:
SELECT TOP 1000
[event_datetime]
,[event_date]
,[Expr1]
,[object_id]
,[object_name]
,[pass_nomer]
,[event_time]
FROM [VISITOR].[dbo].[vw_Visitor_vw]
order by event_datetime, pass_nomer DESC

Нужно чтобы строки, в которых время между посещением ((if(Expr1 = Expr1) then (if(event_datetime < (event_datetime + 30))) меньше 30 секунд – не отображалтсь во вюхе.

+ если вьюху материализовать
set dateformat ymd
if object_id('tempdb..#vis') is null 
begin
  ;with vw_Visitor_vw as (
	  select * from( values
  (cast('2014-08-18 21:16:28.000' as datetime),'18.08.2014','Столовая Г/З','4224' ,'Дяк Александ Поликович','1068' ,'21',''),
  ('2014-08-18 21:19:03.000','18.08.2014','Операторская','4224' ,'Дяк Александ Поликович','1068' ,'21',''),
  ('2014-08-18 21:19:55.000','18.08.2014','Диспетчерская','4224','Дяк Александ Поликович','1068' ,'21',''),
  ('2014-08-18 21:20:01.000','18.08.2014','Столовая Г/З','4224' ,'Дяк Александ Поликович','1068' ,'21',''),
  ('2014-08-18 21:20:18.000','18.08.2014','Столовая Г/З','4224' ,'Дяк Александ Поликович','1068' ,'21','Дел <17'),
  ('2014-08-18 21:20:26.000','18.08.2014','Столовая Г/З','4224' ,'Дяк Александ Поликович','1068' ,'21','Дел <27'),
  ('2014-08-18 21:20:35.000','18.08.2014','Операторская','4224' ,'Дяк Александ Поликович','1068' ,'21',''),
  ('2014-08-18 21:21:21.000','18.08.2014','Операторская','4224' ,'Дяк Александ Поликович','1068' ,'21',''),
  ('2014-09-01 09:19:59.000','01.09.2014','Столовая Ч/З','24490','Супрун Алексей Йосипович','1007','9',''),
  ('2014-09-01 09:20:02.000','01.09.2014','Столовая Ч/З','24490','Супрун Алексей Йосипович','1007','9','  Дел <3   '),
  ('2014-09-01 09:20:07.000','01.09.2014','Столовая Ч/З','24490','Супрун Алексей Йосипович','1007','9','  Дел <8'),
  ('2014-09-01 09:20:10.000','01.09.2014','Столовая Ч/З','24490','Супрун Алексей Йосипович','1007','9','  Дел <11'),
  ('2014-09-01 09:20:15.000','01.09.2014','Столовая Ч/З','24490','Супрун Алексей Йосипович','1007','9','  Дел <16'),
  ('2014-09-01 10:56:57.000','01.09.2014','Столовая Г/З','4628' ,'Полева Ольга  Юриевна','2563'  ,'10','')
	  )values_vw_Visitor_vw(event_datetime,event_date,Expr1,object_id,object_name,pass_nomer,event_time,note))
  select * into #vis from vw_Visitor_vw
end

то у меня есть 2 варианта:
первый создает очень много лишних промежуточных записей, которые потом отсеиваются
declare @period_sec int=30
;with rec as (
	select lvl=1, v.Expr1, v.event_datetime, v.object_id -- , ...
    from #vis v
    where not exists(select * from #vis e where e.Expr1=v.Expr1 and e.event_datetime<v.event_datetime)
  union all
  select lvl+1, v.Expr1, v.event_datetime, v.object_id -- , ...
    from rec r
    join #vis v on v.Expr1=r.Expr1 and v.event_datetime>DATEADD(SECOND,@period_sec,r.event_datetime)
  )
, rez as(
  select row_number()over(partition by r.Expr1, r.lvl  order by r.event_datetime)npp, *
  from rec r
  )
select * 
from rez r
where r.npp=1
order by r.event_datetime, r.Expr1 


второй сразу создает только нужные строки, но - с помощью курсора (вроде как долго)...
if object_id('tempdb..#cursor') is not null drop table #cursor
create table #cursor(
    lvl int
  , Expr1 varchar(100) not null
  , event_datetime datetime not null
  , object_id int
--, ...
--, primary key (Expr1,event_datetime)
  )
insert #cursor
select 1, v.Expr1, v.event_datetime, v.object_id
  from #vis v
  where not exists(select * from #vis e where e.Expr1=v.Expr1 and e.event_datetime<v.event_datetime)
--select * from #cursor

declare crs cursor dynamic read_only
  for select lvl, Expr1, event_datetime, object_id from #cursor
declare @lvl int, @Expr1 varchar(256), @event_datetime datetime, @object_id int
open crs
while 1=1 
begin
  fetch next from crs into @lvl, @Expr1, @event_datetime, @object_id
if @@FETCH_STATUS<>0 break;
  insert #cursor
  select top(1) @lvl+1, v.Expr1, v.event_datetime, v.object_id
    from #vis v
    where v.Expr1=@Expr1 and v.event_datetime>dateadd(SECOND,@period_sec,@event_datetime)
    order by v.Expr1, v.event_datetime
end
close crs
deallocate crs

select * from #cursor c order by c.event_datetime, c.Expr1

lvlExpr1event_datetimeobject_id
1Столовая Г/З2014-08-18 21:16:28.0004224
1Операторская2014-08-18 21:19:03.0004224
1Диспетчерская2014-08-18 21:19:55.0004224
2Столовая Г/З2014-08-18 21:20:01.0004224
2Операторская2014-08-18 21:20:35.0004224
3Операторская2014-08-18 21:21:21.0004224
1Столовая Ч/З2014-09-01 09:19:59.00024490
3Столовая Г/З2014-09-01 10:56:57.0004628
31 май 16, 17:19    [19242366]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с заданным параметром  [new]
Sparda
Member

Откуда:
Сообщений: 5
CrazHunt - Ваши варианты не помогли.


Cygapb-007 - сегодня буду побывать
2 июн 16, 14:45    [19250410]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить