Обзор встречи 30.06

Итак состоялась наша встреча, посвященная базам данных.
Присутствовало около 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 Казначей.

Назад

Видео. Евгений Холодков. JavaFX.

Далее

Видео. Сергей Мудрецов. Обзор СУБД.

  1. Anton Arhipov

    хорошее видео. но больше как то про BI и анализ данных, чем про СУБД

  2. 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.

  3. Anton Arhipov

    skacker, по-моему название не принципиально. назови хоть "зюзя" — сути задачки это не поменяет.

  4. Kirill Linnik

    в задаче главное — ремарка 😉

    в реальной базе, естесственно, все названия иные. здесь дан упрощенный вид

  5. Juri Mulenko

    Добавлены презентации.

  6. 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

  7. Kirill Linnik

    но это решение не работает корректно (смотрите ремарку 😉

  8. 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

  9. 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…

  10. Anton Arhipov

    MiamiBC,

    ремарка как раз в том чтоб найти первую блокировку и разблокировку записи, как я понимаю

  11. 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 не уникален 🙂

  12. Kirill Linnik

    для тех, кто так и не уловил смысл ремарки, показываю на данных. допустим, у нас такая временная дата по одному юзеру (число — в блок?):
    1 — 1
    2 — 1
    3 — 0
    6 — 1
    7 — 1
    8 — 0
    скрипт в конечном итоге по этому юзеру должен выдать 2(!!) записи:
    даты 1 и 3 + даты 6 и 8. т.е. те даты, в период которых пользователь был заблочен. в период 3-6 и другие он в блоке не был 😉

  13. Anton Arhipov

    Кирилл, так в ремарке написано только первая блокировка и разблокировка. А не все блокировки-разблокировки. Дополни может ремарку тогда — ато она реально misleading

  14. slacker

    Я правильно понял, что полная таблица:

    1 — 1
    2 — 1
    3 — 0
    4 — 0
    5 — 0
    6 — 1
    7 — 1
    8 — 0

    ???
    Или разблокирующая запись только одна?

  15. Kirill Linnik

    на практике статусов больше, поэтому:
    да, пускай разблокировок тоже будет несколько.

    з.ы. сейчас поправлю ремарку.

  16. 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)

  17. 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');

    Антон, кто тебя учил так форматировать код? З.Ы. Кстати, это первое верное решение. Но это не значит, что оно — самое оптимальное и единственно верное 😉

  18. Anton Arhipov

    Кирилл, чтобы снять все сомнения, ты могбы ещё написать ЧТО должно получиться из селекта по этим данным 🙂

  19. 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

  20. Anton Litvinenko

    у меня было 10 минут на придумать решение и отослать 😉 поэтому форматирование было cancelled впрочем как и объяснение

  21. 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
    —————————

  22. govorun_ee

    P.S.
    Есл я правильно понял задание, то нам нужны время и причина ТОЛЬКО первой блокировки и сответствено ТОЛЬКО первой последовавшей после этого разблокировки. Т.е. на выходе имеем по одной строке на каждого пользователя.

    Мой вариант работает по этой логике. Он выводит в т.ч. и пользователей, которые были на данный момент забоникованы, но пока ещё не разблокированы.

  23. 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')
    ——————

  24. 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

    Результат проверил, всё чики-пук, но оптимальными подобные решения назвать язык не поворачивается.

  25. 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

    Этот вариант элегантнее, но что-то у меня подозрения в достоверности результатов.

  26. Kirill Linnik

    2 MiamiBC

    а на тест-данных самому проверить? 😉

  27. 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 соответственно).

  28. MiamiBC

    Kirill Linnik said…
    а на тест-данных самому проверить? 😉

    На тест-данных все в порядке. Но в практике оказывается в порядке не всегда. Тут и тестовых данных должно быть побольше и разнообразие комбинаций. Почти уверен что всё будет работать как нужно, но децл сомнений всёже имеется.

  29. Oleg

    Как насчет заблокированного, но еще не разблокированного юзера? Следует ли с точки зрения бизнес логики выводить записи следующего вида:

    user: user1
    blocked: 2009-07-02 12:58:35
    block_reason: 'bad, bad user'
    unblocked: null
    unblock_reason: null

  30. 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

  31. Kirill Linnik

    не был у компа. если написано "разблокировки", то разблокировка важна. если ее не было — ничего выводить не надо.

  32. 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
    ;

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

*

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.

создано с помощью WordPress & Автор темы: Anders Norén