Задачка на решение аномалий. / MySQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / MySQL [игнор отключен] [закрыт для гостей] / Задачка на решение аномалий.

9 сообщений из 9, страница 1 из 1

Задачка на решение аномалий.

#39822981

Sarmas

Гость

Доброго времени суток. Ищу чуда. Помогите составить запрос.
Очень нужно решить одну задачку, я в ней окончательно запоролся, а сроки сильно жмут.
Запрос на составление таблицы:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

CREATE TABLE table1 ( id INTEGER PRIMARY KEY AUTO_INCREMENT, uid INTEGER, point_uid integer,  event_ts timestamp, event_type VARCHAR(2) );

insert into table1 (uid, point_uid,event_ts,event_type) values (255, 1255, '2019-01-01 21:13:45', 's');
insert into table1 (uid, point_uid,event_ts,event_type) values (211, 1255, '2019-01-01 21:18:31', 's');
insert into table1 (uid, point_uid,event_ts,event_type) values (233, 1255, '2019-01-01 21:17:40', 's');
insert into table1 (uid, point_uid,event_ts,event_type) values (255, 1255, '2019-01-01 23:15:15', 'e');
insert into table1 (uid, point_uid,event_ts,event_type) values (211, 1255, '2019-01-01 22:10:05', 'e');
insert into table1 (uid, point_uid,event_ts,event_type) values (334, 1233, '2019-01-01 20:25:45', 'e');
insert into table1 (uid, point_uid,event_ts,event_type) values (333, 1211, '2019-01-01 23:13:45', 'e');
insert into table1 (uid, point_uid,event_ts,event_type) values (255, 1255, '2019-01-01 21:19:45', 's');
insert into table1 (uid, point_uid,event_ts,event_type) values (266, 1251, '2019-01-01 22:19:45', 's');
insert into table1 (uid, point_uid,event_ts,event_type) values (266, 1251, '2019-01-01 23:19:45', 's');

Задача:
написать SQL-запрос, который выведет все аномальные сессии устройств (без завершения или завершение без начала).
Формат вывода: uid - идентификатор устройства; point_id - точка подключения устройства; event_ts – timestamp случившегося события;
Ожидается ответ.: (255, 1255, '2019-01-01 21:13:45'),(233, 1255, '2019-01-01 21:17:40'),(334, 1233, '2019-01-01 20:25:45'),(333, 1211, '2019-01-01 23:13:45'),(266, 1251, '2019-01-01 22:19:45'),(266, 1251, '2019-01-01 23:19:45') *

...

Рейтинг:

0 / 0

05.06.2019, 17:22

| Ответить | Цитировать | Написать

Задачка на решение аномалий.

#39822982

Sarmas

Гость

Пробовал:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.

select uid, point_uid, event_ts from table1 where id not in (

  select t2.id from (
    select * from table1
  ) as t2
   join table1 t1 on 
     t1.uid = t2.uid and 
     t1.point_uid = t2.point_uid and
     t1.event_type = (
       CASE t2.event_type
         WHEN 's' THEN 'e'
         WHEN 'e' THEN 's'
       end
     )
     
     and (
       case t2.event_type
         when 's' then  t2.event_type>=t1.event_type
         when 'e' then  t2.event_type<=t1.event_type
       end
       )
  
  )

Проблема - не выводит (255, 1255, '2019-01-01 21:13:45')

...

Рейтинг:

0 / 0

05.06.2019, 17:24

| Ответить | Цитировать | Написать

Задачка на решение аномалий.

#39823049

Sarmas

Гость

Код: sql

1.
2.
3.
4.
5.

select
  uid, point_uid, event_ts
  From table1
 Group by uid
 Having sum(case when event_type='e' THEN 1 ELSE 0 END) <> sum(case when event_type='s' then 1 ELSE 0 end)

не вывело (266, 1251, '2019-01-01 23:19:45')

...

Рейтинг:

0 / 0

05.06.2019, 18:31

| Ответить | Цитировать | Написать

Задачка на решение аномалий.

#39823099

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.

WITH cte AS (SELECT *,
             LEAD(event_type) OVER (PARTITION BY uid ORDER BY event_ts) event_lead,
             LAG(event_type) OVER (PARTITION BY uid ORDER BY event_ts) event_lag
             FROM table1)
SELECT * 
FROM cte 
WHERE (event_type = 's' AND event_lead != 'e')
   OR (event_type = 'e' AND event_lag != 's')

...

Рейтинг:

0 / 0

05.06.2019, 21:19

| Ответить | Цитировать | Написать

Задачка на решение аномалий.

#39823100

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

или (PARTITION BY uid, point_uid) ...

...

Рейтинг:

0 / 0

05.06.2019, 21:20

| Ответить | Цитировать | Написать

Задачка на решение аномалий.

#39823351

Sarmas

Гость

Akina, сложность в том, что нужен только MySQL.

SQL Fiddle:
DDL and DML statements are not allowed in the query panel for MySQL; only SELECT statements are allowed. Put DDL and DML in the schema panel.

Paiza.io:
ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ERROR 1064 (42000) at line 14: You have an error in your SQL syntax' at line 1

...

Рейтинг:

0 / 0

06.06.2019, 12:03

| Ответить | Цитировать | Написать

Задачка на решение аномалий.

#39823356

Sarmas

Гость

Простите, замотался немного.
структура данных:
uid – идентификатор устройства;
point_uid - идентификатор точки подключения устройства;
event_ts – timestamp случившегося события;
event_type – тип события ([s, e]). Где s – начало сессии устройства, e – завершение сессии устройства;

...

Рейтинг:

0 / 0

06.06.2019, 12:07

| Ответить | Цитировать | Написать

Задачка на решение аномалий.

#39823422

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.

WITH cte AS (SELECT *,
             COALESCE(LEAD(event_type) OVER (PARTITION BY uid, point_uid ORDER BY event_ts), 'f') event_lead,
             COALESCE(LAG(event_type) OVER (PARTITION BY uid, point_uid ORDER BY event_ts), 'f') event_lag
             FROM table1)
SELECT * 
FROM cte 
WHERE (event_type = 's' AND event_lead != 'e')
   OR (event_type = 'e' AND event_lag != 's')

Sarmasнужен только MySQL.Показанный запрос прекрасно воспринимается и моим MySQL, и fiddle .
SarmasSQL Fiddle:
DDL and DML statements are not allowed in the query panel for MySQL; only SELECT statements are allowed. Put DDL and DML in the schema panel.
Если Вы не умеете правильно работать с fiddle - при чём тут мой запрос?

...

Рейтинг:

0 / 0

06.06.2019, 13:27

| Ответить | Цитировать | Написать

Задачка на решение аномалий.

#39823550

Sarmas

Гость

Akina,

И не заметил, как вышла 8.0.
Пробовались на 5.7

...

Рейтинг:

0 / 0

06.06.2019, 15:41

| Ответить | Цитировать | Написать

9 сообщений из 9, страница 1 из 1

Форумы / MySQL [игнор отключен] [закрыт для гостей] / Задачка на решение аномалий.

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=47&fpage=35&tid=1829113]:	0ms
get settings:	8ms
get forum list:	14ms
check forum access:	3ms
check topic access:	3ms
track hit:	38ms
get topic data:	10ms
get forum data:	2ms
get page messages:	41ms
get tp. blocked users:	1ms
others:	315ms

total:	435ms