Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Помощь в написании запроса / 9 сообщений из 9, страница 1 из 1
26.07.2018, 16:11
    #39679737
assmsk
assmsk
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помощь в написании запроса
Добрый день. Сотрудники придумали одну хитрость которую нужно отловить.(подробно описывать не буду)
К сути:
Нужно найти серверы и сотрудников к которым один и тот же сотрудник подключался подряд 5 и более раз и время первого логирования и последненго и сколько времени прошло между каждыми подключениями в секундах


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
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 раз подряд.


Нужно получить :
Код: plaintext
1.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2018, 16:13
    #39679738
assmsk
assmsk
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помощь в написании запроса
Неправильно написал


Нужно получить :
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2018, 16:23
    #39679741
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помощь в написании запроса
Схематично
Код: sql
1.
2.
3.
SELECT server, user, MIN(dt), MAX(dt), MAX(dt)-MIN(dt)
FROM table
GROUP BY server, user


Если надо за сутки - добавь вычисляемое поле в выход и группировку.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2018, 16:28
    #39679745
assmsk
assmsk
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помощь в написании запроса
AkinaСхематично
Код: sql
1.
2.
3.
SELECT server, user, MIN(dt), MAX(dt), MAX(dt)-MIN(dt)
FROM table
GROUP BY server, user


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


Нужно найти серверы и сотрудников к которым один и тот же сотрудник подключался подряд 5 и более раз
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2018, 16:31
    #39679747
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помощь в написании запроса
assmsk,

Код: sql
1.
HAVING COUNT(*) >= 5
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2018, 16:32
    #39679748
court
court
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помощь в написании запроса
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 ...
но как-то так :
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
;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(Отсутствует имя столбца)(Отсутствует имя столбца)xzServer2S_T02002018-01-07 14:08:23.0002018-01-07 14:14:23.0007Server2S_T0A1662018-01-07 14:01:23.0002018-01-07 14:05:23.0000
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2018, 16:33
    #39679750
assmsk
assmsk
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помощь в написании запроса
assmskAkinaСхематично
Код: sql
1.
2.
3.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2018, 16:42
    #39679756
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помощь в написании запроса
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
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;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2018, 16:43
    #39679757
assmsk
assmsk
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помощь в написании запроса
court,
благодарю, идеально!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Помощь в написании запроса / 9 сообщений из 9, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]