Итак состоялась наша встреча, посвященная базам данных.
Присутствовало около 40 человек и,не смотря на некоторые технические заминки в начале, все остались довольны.
Для затравки
Давайте напомню, что происходило на нашем заседании. Сами презентации будут выложены чуть позже. Необходимо время на обработку видео и сведения звука.
Открыл встречу Сергей Мудрецов(Skype) с доклада «Мир баз данных, его тенденции и тонкости» о Sybase и сравнении его с некторыми другими популярными и не очень OLAP решениями. Из презентации мы узнали о том что есть такая замечательная база как Sybase, получили представление о фишках приятных и не особо в MS SQL, MySQL, SAS и Progress. Доклад получился интересным, хотя и слегка затянутым. Было много вопросов из зала, в частности «что же такое OLAP?» и «с чем его едят?». Надеюсь эта тема ещё будет подниматься на следующих встречах.
После перерыва Мартин Киуру(Swedbank) продолжил заседание с презентацией «Oracle Secrets» о секретах Oracle.Тёмных тайн нам не открыли, однако поделились опытом в оптимизации запросов. Надеюсь теперь все имеют представление о построении запросов, операторах, которых не стоит использовать в конкретных случаях, хинтах, и собственно как посмотреть, что движок вытворяет с запросом и на что уходят драгоценные секунды. Из зала были также слышны ценные дополнения.
Завершил нашу встречу Андрей Солнцев(Hireright) с докладом «Evolutionary database design» о Эволюционном дизайне баз данных, проще говоря, об Agile разработке применительно к базам данных и о инструментах, которые позволяют внести порядок в процедуру разработки, установки и отката скриптов, версионирование. В частности Андрей посоветовал литературу по рефакторингу баз данных и вкратце ознакомил с использованием библиотек DB Deploy и LiquiBase, построенных на принципах, изложенных в этой книге.
В конце я вскольз рассказал о линейке продуктов Redgate для синхронизации структуры и данных между базами.
В перерывах участники активно общались между собой. Кто-то встретил старых знакомых, кто-то завёл новых. Мы рады, что встреча оказалась настолько интересной и вы нашли в себе силы прийти, несмотря на летнюю погоду :). Заполненные анкеты и собранные пожертвования помогут нам успешно провести следующее заседание.
Пользуясь случаем хотелось бы выразить благодарность
— Арсению Григорьеву(Aqris) и команде Aqris’a за гостепреимство
— Кириллу Линнику(Skype) за модераторство и конкурс с призами
— Захару Кириллову за неоценимую помощь с призами
— Александру Моченову(Tallink) за предоставление Devclub-у в пользование мега-пульта дял презентаций с лазерной указкой
— Евгению Холодкову(Ericsson) за диктофон с head-set’ом.
Домашнее задание.
Дабы поддержать добрую традицию приятных призов за вопросы, Кирилл предлагает вашему вниманию простую, но интересную задачку.
Итак, имеется следующая бизнес-логика:
Пользователь блокируется различными системами, которые находят его поведение небезопасным. Если тревога ложная, то разблокируется он один раз.
В итоге имеем простую табличку:
status_log: id (int)
user (varchar)
reason (varchar)
is_blocked (int 0-1)
change_time (datetime).
Проблема: в табличке записей крайне много.
Вопрос: как одним запросом найти всех пользователей, с временем и причиной первой блокировки, а так же временем и причиной разблокировки.
Ремарка: блок-разблок может происходить несколько раз.
Диалект решения — стандартный 92. Если вы знаете, что на другом диалекте можно это сделать еще эффективней — можете добавить и это решение, что добавит вам призовых баллов. Победитель будет объявлен на следующей встрече и без приза ему будет не уйти 😉 Ответы можно постить в комменты, или отправлять на e-mail — kirill точка linnik собака mail точка ee.
P. S. для тех, кто так и не уловил смысл ремарки, показываю на данных. допустим, у нас такая временная дата по одному юзеру (число — в блок?):
1 — 1
2 — 1
3 — 0
4 — 0
6 — 1
7 — 1
8 — 0
9 — 0
скрипт в конечном итоге по этому юзеру должен выдать 2(!!) записи:
даты 1 и 3 + даты 6 и 8. т.е. те даты, в период которых пользователь был заблочен. в период 3-6 и другие он в блоке не был 😉
Ждем комментариев, вопросов, предложений.
Юрий Муленко, a.k.a Казначей.
Anton Arhipov
хорошее видео. но больше как то про BI и анализ данных, чем про СУБД
slacker
"а так же временем и причиной разблокировки"
тоже первой?
если учесть, что в логах обычно на ID стоит праймари ключ и автоинкремент и ручками его никто не задает и найти надо первую блокировку и первую разблокировку то я бы сделал как-то так:
select *
from status_log where id in (
select min(id) from status_log group by user, is_blocked
);
В мускуле можно даже не делать субселект:
select * from status_log group by user, is_blocked;
ПС. кстати в логи лучше писать user_id. и вообще назвнаие таблицы не правильное. надо либо status_logs либо status_log_list.
Anton Arhipov
skacker, по-моему название не принципиально. назови хоть "зюзя" — сути задачки это не поменяет.
Kirill Linnik
в задаче главное — ремарка 😉
в реальной базе, естесственно, все названия иные. здесь дан упрощенный вид
Juri Mulenko
Добавлены презентации.
Anton Arhipov
будете смеяться, но в мускуле работает вот такое (при условии, что первая запись появляется только при блокировке) 🙂
SELECT * FROM `status_log` WHERE `is_blocked`=1 group by user
union
SELECT * FROM `status_log` WHERE `is_blocked`=0 group by user
Kirill Linnik
но это решение не работает корректно (смотрите ремарку 😉
Anton Litvinenko
получаем для каждого пользователя его первый блок:
select * from status_log s
where s.change_time = (
select min(change_time)
from status_log s2
where s2.user = s.user and is_blocked = 1)
а тепер на основе этого — решение:
select s1.user, s1.change_time, s1.reason, s2.change_time, s2.reason
from (select * from status_log s where s.change_time = (select min(change_time) from status_log s2 where s2.user = s.user and is_blocked = 1)) s1
join (select * from status_log s where s.change_time = (select min(change_time) from status_log s2 where s2.user = s.user and is_blocked = 0)) s2
on s1.user = s2.user
MiamiBC
Прошу прощения, не в тот пост добавил свой комментарий, в итоге оказался вне дискуссии. Переношу сюда свой запрос…
SELECT
blocked.user,
blocked.reason,
blocked.change_time,
unblocked.reason,
unblocked.change_time
FROM
status_log blocked,
status_log unblocked
WHERE
blocked.user = unblocked.user
AND blocked.is_blocked = 1
AND unblocked.is_blocked = 0
Если такой вид не подходит (тут про ремарки что-то говорилось) то:
SELECT
blocked.user,
blocked.reason blockedreason,
blocked.change_time blockedtime,
unblocked.reason unblockedreason,
unblocked.change_time unblockedtime
FROM status_log blocked
JOIN status_log unblocked
ON blocked.user = unblocked.user
WHERE blocked.is_blocked = 1
AND unblocked.is_blocked = 0
JOIN отсекает тех кто не блокирован или не разблокирован, т.е. показывает челов которые побывали в обоих состояниях. Если нужно полностью то LEFT JOIN…
Anton Arhipov
MiamiBC,
ремарка как раз в том чтоб найти первую блокировку и разблокировку записи, как я понимаю
slacker
мне кажется в задании немного не хватает данных 🙂
"первую блокировку и разблокировку" …. это значит первую блокировку и ПЕРВУЮ разблокировку, так?
Если не использовать айди как было сделано в моем примере выше (а это глупо ибо это логи), то можно сделать так:
select status_log.* from status_log join
(
select user, min(change_time) change_time from status_log group by user, is_blocked
) sub
on status_log.user=sub.user and status_log.change_time=sub.change_time;
Вы ещё скажите, что change_time в пределах user не уникален 🙂
Kirill Linnik
для тех, кто так и не уловил смысл ремарки, показываю на данных. допустим, у нас такая временная дата по одному юзеру (число — в блок?):
1 — 1
2 — 1
3 — 0
6 — 1
7 — 1
8 — 0
скрипт в конечном итоге по этому юзеру должен выдать 2(!!) записи:
даты 1 и 3 + даты 6 и 8. т.е. те даты, в период которых пользователь был заблочен. в период 3-6 и другие он в блоке не был 😉
Anton Arhipov
Кирилл, так в ремарке написано только первая блокировка и разблокировка. А не все блокировки-разблокировки. Дополни может ремарку тогда — ато она реально misleading
slacker
Я правильно понял, что полная таблица:
1 — 1
2 — 1
3 — 0
4 — 0
5 — 0
6 — 1
7 — 1
8 — 0
???
Или разблокирующая запись только одна?
Kirill Linnik
на практике статусов больше, поэтому:
да, пускай разблокировок тоже будет несколько.
з.ы. сейчас поправлю ремарку.
Anton Litvinenko
select b.user, b.change_time, b.reason, u.change_time, u.reason
from (select * from status_log s where s.change_time = (
select min(s2.change_time) from status_log s2
where s2.user = s.user and s2.is_blocked = 1 and s2.change_time <= s.change_time and not exists (
select * from status_log s3 where s3.user = s.user and s3.is_blocked = 0
and s3.change_time > s2.change_time and s3.change_time < s.change_time))) b
join status_log u on u.user = b.user
where u.is_blocked = 0 and u.change_time = (
select min(s4.change_time) from status_log s4
where s4.user = u.user and s4.is_blocked = 0 and s4.change_time > b.change_time)
Kirill Linnik
Для проверки себя, вот простенький скрипт для данных:
CREATE TABLE IF NOT EXISTS `status_log` (
`id` int(11) NOT NULL auto_increment,
`user` varchar(10) NOT NULL,
`reason` varchar(100) NOT NULL,
`is_blocked` tinyint(1) NOT NULL,
`change_time` datetime NOT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `status_log` (`id`, `user`, `reason`, `is_blocked`, `change_time`) VALUES
(1, 'test1', 'block by system 1', 1, '2009-07-01 12:58:12'),
(2, 'test1', 'block by system 2', 1, '2009-07-02 12:58:35'),
(3, 'test1', 'ublocked by user 1', 0, '2009-07-03 12:59:03'),
(4, 'test1', 'unblocked by user 2', 0, '2009-07-04 12:59:21'),
(5, 'test1', 'blocked by system 3', 1, '2009-07-06 12:59:41'),
(6, 'test1', 'blocked by system 4', 1, '2009-07-07 13:00:01'),
(7, 'test1', 'unblocked by user 3', 0, '2009-07-08 13:00:28'),
(8, 'test1', 'unblocked by user 2', 0, '2009-07-09 13:00:45');
Антон, кто тебя учил так форматировать код? З.Ы. Кстати, это первое верное решение. Но это не значит, что оно — самое оптимальное и единственно верное 😉
Anton Arhipov
Кирилл, чтобы снять все сомнения, ты могбы ещё написать ЧТО должно получиться из селекта по этим данным 🙂
Kirill Linnik
test1 2009-07-01 12:58:12 block by system 1 2009-07-03 12:59:03 ublocked by user 1
test1 2009-07-06 12:59:41 blocked by system 3 2009-07-08 13:00:28 unblocked by user 3
Anton Litvinenko
у меня было 10 минут на придумать решение и отослать 😉 поэтому форматирование было cancelled впрочем как и объяснение
govorun_ee
Вот мой вариант.
Правда при большом объёме данных он дулет работать долго из-за JOIN-а со вложенным SELECT-ом.
—————————
select
a.user
,max(case a.id when b.blocked_first_id then a.reason else null end) as 'blocked_reason'
,max(case a.id when b.blocked_first_id then a.change_time else null end) as 'blocked_time'
,max(case a.id when b.unblocked_first_id then a.reason else null end) as 'unblocked_reason'
,max(case a.id when b.unblocked_first_id then a.change_time else null end) as 'unblocked_time'
from
status_log as a
inner join
(
select
user
,min(case is_blocked when 1 then id else null end) as 'blocked_first_id'
,min(case is_blocked when 0 then id else null end) as 'unblocked_first_id'
from
status_log
group by
user
) as b
on b.user = a.user
group by
user
—————————
govorun_ee
P.S.
Есл я правильно понял задание, то нам нужны время и причина ТОЛЬКО первой блокировки и сответствено ТОЛЬКО первой последовавшей после этого разблокировки. Т.е. на выходе имеем по одной строке на каждого пользователя.
Мой вариант работает по этой логике. Он выводит в т.ч. и пользователей, которые были на данный момент забоникованы, но пока ещё не разблокированы.
govorun_ee
P.P.S.
Кстати, вот мои тестовые данные дял той же таблицы. Тут на 4-х пользователей, если кому интересно поупражняться. 🙂
——————
insert into status_log (user, reason, is_blocked, change_time)
values
('User A','Blocked 1',1,'2009-06-01'),
('User A','Unblocked 1',0,'2009-06-02'),
('User A','Blocked 2',1,'2009-06-04'),
('User A','Unblocked 2',0,'2009-06-06'),
('User B','Blocked 1',1,'2009-06-02'),
('User B','Unblocked 1',0,'2009-06-02'),
('User B','Blocked 1',1,'2009-06-12'),
('User B','Unblocked 2',0,'2009-06-15'),
('User B','Blocked 2',1,'2009-06-20'),
('User B','Unblocked 2',0,'2009-06-25'),
('User C','Blocked 2',1,'2009-06-03'),
('User C','Unblocked 2',0,'2009-06-04'),
('User C','Blocked 1',1,'2009-06-06'),
('User C','Unblocked 1',0,'2009-06-08'),
('User C','Blocked 3',1,'2009-06-12'),
('User D','Blocked 3',1,'2009-06-04')
——————
MiamiBC
Простите, ремарка в начале была совершенно недоходчива. Вот уверенно работающий вариант при условии что данные из таблицы не удаляются, а только накапливаются.
SELECT
blocked.user,
blocked.change_time,
blocked.reason,
unblocked.change_time,
unblocked.reason
FROM
status_log unblocked,
( SELECT
MIN( id ) first, user, change_time, reason
FROM status_log
WHERE is_blocked = 1
GROUP BY user
) blocked
WHERE unblocked.id >= blocked.first
AND blocked.user = unblocked.user
AND unblocked.is_blocked = 0
GROUP BY user
Если данные удаляются, то можно добавить для уверенности в самый конец:
ORDER BY unblocked.change_time
Результат проверил, всё чики-пук, но оптимальными подобные решения назвать язык не поворачивается.
MiamiBC
SELECT
blocked.user,
MIN(blocked.change_time) blocked_time,
blocked.reason,
unblocked.change_time unblocked_time,
unblocked.reason
FROM
status_log blocked,
status_log unblocked
WHERE
blocked.is_blocked = 1
AND unblocked.is_blocked = 0
AND blocked.user = unblocked.user
GROUP BY blocked.user
HAVING unblocked_time >= blocked_time
Этот вариант элегантнее, но что-то у меня подозрения в достоверности результатов.
Kirill Linnik
2 MiamiBC
а на тест-данных самому проверить? 😉
govorun_ee
А вот вариант, работающий по уточнённой логике. На выходе даёт одну строчку для каждой группы, состоящей из неских последовательных строк BLOCK и следующей непосредственно за ними UNBLOCK для каждого пользователя.
———————————
select
sb.user
,sb.id as 'blocked_id'
,sb.reason as 'blocked_reason'
,sb.change_time as 'blocked_time'
,sunb.id as 'unblocked_id'
,sunb.reason as 'unblocked_reason'
,sunb.change_time as 'unblocked_time'
from
(
select
min(b.blocked_id) as blocked_id
,b.unblocked_id
from
(
select
a.id as 'blocked_id'
,min(u.id) as 'unblocked_id'
from
status_log as a
left join
(
select
id
,user
,reason
,change_time
from
status_log
where
is_blocked = 0
) as u
on u.user = a.user and u.change_time > a.change_time
where
a.is_blocked = 1
group by
a.id
) as b
group by
b.unblocked_id
) as d
inner join status_log as sb
on d.blocked_id = sb.id
inner join status_log as sunb
on d.unblocked_id = sunb.id
order by
sb.user
,sb.id
———————————
Логика следующая (по шагам):
1) начала выбираем все ID строк событий разблокировки (результат в таблице "u");
2) затем для каждого полученного BLOCK_ID выбираем первый следующий после неё UNBLOCK_ID для этого USER-а (результат в таблице "b").
3) поскольку BLOCK_ID м.б. несколько в каждой группе, то берём только первый (результат в таблице "d").
4) и теперь к имеющейся таблице с парами (BLOCK_ID — UNBLOCK_ID) приклеиваем детали из STATUS_LOG двумя JOIN-ами (для BLOCK и UNBLOCK соответственно).
MiamiBC
Kirill Linnik said…
а на тест-данных самому проверить? 😉
На тест-данных все в порядке. Но в практике оказывается в порядке не всегда. Тут и тестовых данных должно быть побольше и разнообразие комбинаций. Почти уверен что всё будет работать как нужно, но децл сомнений всёже имеется.
Oleg
Как насчет заблокированного, но еще не разблокированного юзера? Следует ли с точки зрения бизнес логики выводить записи следующего вида:
user: user1
blocked: 2009-07-02 12:58:35
block_reason: 'bad, bad user'
unblocked: null
unblock_reason: null
Oleg
Так и не дождавшись ответа, допускаю, что основным все таки является событие блокировки, а не разблокировки. Исходя из того, что отображение неразблокированных пользователей является существенным, предлагаю свой вариант.
select
row.user as user,
blk.change_time as block_time,
blk.reason as block_reason,
unblk.change_time as unblock_time,
unblk.reason as unblock_reason
from (
select b.user as user,
min(b.change_time) as block_time,
(select min(un.change_time) from status_log un where un.user = b.user and un.change_time > b.change_time and un.is_blocked = 0) as unblock_time
from status_log b
where b.is_blocked = 1
group by b.user, unblock_time
) row
inner join status_log blk on blk.user = row.user and blk.change_time = row.block_time
left join status_log unblk on unblk.user = row.user and unblk.change_time = row.unblock_time
;
1. Для каждого события блокировки ищем ближайшее следующее за ним событие разблокировки для данного пользователя. От события разблокировки берем время — подзапрос unblock_time в выборке row
2. Группируем основную выборку, находя первое время блокировки в группе по пользователю и времени разблокировки, получаем интересующие нас строки т.н. "периодов заблокированности" пользователей (user, block_time, unblock_time) — row
3. Джойним к основной выборке информацию о причине блокировки/разблокировки (blk, unblk), поскольку разблокировки может не быть — второй джойн — левый. При джойне предполагается уникальность timestamp-а status_log.change_time в пределах группы событий конкретного пользователя.
4. В целях оптимизации обязательно проиндексировать status_log.user и status_log.change_time
Kirill Linnik
не был у компа. если написано "разблокировки", то разблокировка важна. если ее не было — ничего выводить не надо.
Oleg
ну тогда решение то же самое, но второй джойн — не левый 🙂
select
row.user as user,
blk.change_time as block_time,
blk.reason as block_reason,
unblk.change_time as unblock_time,
unblk.reason as unblock_reason
from (
select b.user as user,
min(b.change_time) as block_time,
(select min(un.change_time) from status_log un where un.user = b.user and un.change_time > b.change_time and un.is_blocked = 0) as unblock_time
from status_log b
where b.is_blocked = 1
group by b.user, unblock_time
) row
inner join status_log blk on blk.user = row.user and blk.change_time = row.block_time
inner join status_log unblk on unblk.user = row.user and unblk.change_time = row.unblock_time
;