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

Откуда:
Сообщений: 169
Добрый день. Сотрудники придумали одну хитрость которую нужно отловить.(подробно описывать не буду)
К сути:
Нужно найти серверы и сотрудников к которым один и тот же сотрудник подключался подряд 5 и более раз и время первого логирования и последненго и сколько времени прошло между каждыми подключениями в секундах


Create table #t (Server_id varchar(255) , User_login varchar(255) , login_Dt datetime )
Insert #t 
Select * from (values ('Server1','S_T0A135','2018-07-01 13:56:23.000'),
('Server1','S_T0A256','2018-07-01 13:56:23.000'),
('Server1','S_T0A319','2018-07-01 13:57:23.000'),
('Server1','S_T0A135','2018-07-01 13:58:23.000'),
('Server1','S_T0A135','2018-07-01 13:59:23.000'),
('Server1','S_T0A001','2018-07-01 14:00:23.000'),

('Server2','S_T0A166','2018-07-01 14:01:23.000'),
('Server2','S_T0A166','2018-07-01 14:02:23.000'),
('Server2','S_T0A166','2018-07-01 14:03:23.000'),
('Server2','S_T0A166','2018-07-01 14:04:23.000'),
('Server2','S_T0A166','2018-07-01 14:05:23.000'),
('Server2','S_T0A188','2018-07-01 14:06:23.000'),
('Server2','S_T0A193','2018-07-01 14:07:23.000'),
('Server2','S_T0200','2018-07-01 14:08:23.000'),
('Server2','S_T0200','2018-07-01 14:09:23.000'),
('Server2','S_T0200','2018-07-01 14:10:23.000'),
('Server2','S_T0200','2018-07-01 14:11:23.000'),
('Server2','S_T0200','2018-07-01 14:12:23.000'),
('Server2','S_T0200','2018-07-01 14:13:23.000'),
('Server2','S_T0200','2018-07-01 14:14:23.000'),

('Server3','S_T0A007','2018-07-01 14:15:23.000'),
('Server3','S_T0A111','2018-07-01 14:16:23.000'),
('Server3','S_T0A123','2018-07-01 14:17:23.000'),
('Server3','S_T0A109','2018-07-01 14:18:23.000')
) x(Server_id, User_id_, login_Dt)


На примере данных выше на сервере 1 не было сотрудников которые пытались подключиться 5 и более раз подряд.
Сервер 3 - не было
На сервере 2 сотрудник S_T0A166 подключился 5 раз подряд и S_T0200 7 раз подряд.


Нужно получить :
Server1 | S_T0A166  | 2018-07-01 14:01:23.000 | 2018-07-01 14:05:23.000 | 240
Server2 | S_T0200 | 2018-07-01 14:08:23.000 | 2018-07-01 14:14:23.000 | 360


Сообщение было отредактировано: 26 июл 18, 16:40
26 июл 18, 16:11    [21605156]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в написании запроса  [new]
assmsk
Member

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


Нужно получить :
Server2 | S_T0A166 | 2018-07-01 14:01:23.000 | 2018-07-01 14:05:23.000 | 240
Server2 | S_T0200 | 2018-07-01 14:08:23.000 | 2018-07-01 14:14:23.000 | 360
26 июл 18, 16:13    [21605161]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в написании запроса  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20208
Схематично
SELECT server, user, MIN(dt), MAX(dt), MAX(dt)-MIN(dt)
FROM table
GROUP BY server, user

Если надо за сутки - добавь вычисляемое поле в выход и группировку.
26 июл 18, 16:23    [21605203]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в написании запроса  [new]
assmsk
Member

Откуда:
Сообщений: 169
Akina
Схематично
SELECT server, user, MIN(dt), MAX(dt), MAX(dt)-MIN(dt)
FROM table
GROUP BY server, user

Если надо за сутки - добавь вычисляемое поле в выход и группировку.



Нужно найти серверы и сотрудников к которым один и тот же сотрудник подключался подряд 5 и более раз
26 июл 18, 16:28    [21605225]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в написании запроса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
assmsk,

HAVING COUNT(*) >= 5
26 июл 18, 16:31    [21605237]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в написании запроса  [new]
court
Member

Откуда:
Сообщений: 1956
assmsk
Нужно получить :
Server1 | S_T0A166 | 2018-07-01 14:01:23.000 | 2018-07-01 14:05:23.000 | 240
Server2 | S_T0200 | 2018-07-01 14:08:23.000 | 2018-07-01 14:14:23.000 | 360


хз, что значат твои 240 и 360 ...
но как-то так :
;with cte as (
	select 
		*
		,rn1	=row_number()over(partition by Server_id order by login_Dt)
		,rn2	=row_number()over(partition by Server_id,User_login order by login_Dt)    
		,xz		=row_number()over(partition by Server_id order by login_Dt)-row_number()over(partition by Server_id,User_login order by login_Dt) 
	from #t
)

select 
	Server_id
	,User_login
	,min(login_Dt)
	,max(login_Dt)
	,xz
from cte 
group by 
	Server_id
	,User_login
	,xz 
having count(*)>=5

Server_idUser_login(Отсутствует имя столбца)(Отсутствует имя столбца)xz
Server2S_T02002018-01-07 14:08:23.0002018-01-07 14:14:23.0007
Server2S_T0A1662018-01-07 14:01:23.0002018-01-07 14:05:23.0000
26 июл 18, 16:32    [21605240]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в написании запроса  [new]
assmsk
Member

Откуда:
Сообщений: 169
assmsk
Akina
Схематично
SELECT server, user, MIN(dt), MAX(dt), MAX(dt)-MIN(dt)
FROM table
GROUP BY server, user

Если надо за сутки - добавь вычисляемое поле в выход и группировку.



Нужно найти серверы и сотрудников к которым один и тот же сотрудник подключался подряд 5 и более раз



+ к тому если 1 сотрудник подключился 5 раз подряд, потом подключился другой сотрудник и потом опять этот же 5 раз, то это 2 записи



('Server2','S_T0A166','2018-07-01 14:01:23.000'),
('Server2','S_T0A166','2018-07-01 14:02:23.000'),
('Server2','S_T0A166','2018-07-01 14:03:23.000'),
('Server2','S_T0A166','2018-07-01 14:04:23.000'),
('Server2','S_T0A166','2018-07-01 14:05:23.000'),

('Server2','S_T0A188','2018-07-01 14:06:23.000'),

('Server2','S_T0A166','2018-07-01 14:07:23.000'),
('Server2','S_T0A166','2018-07-01 14:08:23.000'),
('Server2','S_T0A166','2018-07-01 14:09:23.000'),
('Server2','S_T0A166','2018-07-01 14:10:23.000'),
('Server2','S_T0A166','2018-07-01 14:11:23.000'),


server2 | S_T0A166 | 2018-07-01 14:01:23.000 | 2018-07-01 14:05:23.000 | 300
server2 | S_T0A166 | 2018-07-01 14:07:23.000 | 2018-07-01 14:11:23.000 | 300
26 июл 18, 16:33    [21605242]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в написании запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9123
with a as
(
 select
  Server_id, User_Login, login_Dt,
  row_number() over (partition by Server_id order by login_Dt) - row_number() over (partition by Server_id, User_Login order by login_Dt) as g
 from
  #t
)
select
 Server_id, User_Login, min(login_Dt), max(login_Dt), datediff(s, min(login_Dt), max(login_Dt))
from
 a
group by
 Server_id, User_Login, g
having
 count(*) >= 5;
26 июл 18, 16:42    [21605267]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в написании запроса  [new]
assmsk
Member

Откуда:
Сообщений: 169
court,
благодарю, идеально!
26 июл 18, 16:43    [21605268]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить