Задачка на знание SQL для принимаемых на работу программистов

[dil]

Для DBA её тоже можно применять. На самом начальном этапе, потому что если кандидат не смог навскидку ответить, то он никакой не DBA, а чмо какое-то. А вот программист такого может сразу не знать, но придумать должен, если умный.

Итак. Есть табличка foo, в которой содержатся данные об игроках по дням в следующем виде: date, user_id, nickname и кучка других данных за указанный день, которые нас не интересуют. Игрок идентифицируется по user_id и участвует в игре под псевдонимом nickname. В теории nickname у игрока меняться не должен, а на практике может, поэтому в разные дни у одного user_id может быть разный nickname. Для каждого user_id за каждую дату может быть не более одной записи, потому что nickname в течение дня меняться не может, к счастью.

Задача: выбрать одним запросом (можно с подзапросами) полный список всех user_id и для каждого из них один-единственный nickname – тот, под которым игрок играл последний раз.

Классический ответ под катом, но если кто знает другие варианты – welcome.

SELECT a.userid, a.nickname
FROM foo a
WHERE a.date = (SELECT MAX(b.date) FROM foo b WHERE b.user_id = a.user_id)

Оригинал этой записи. Комментировать можно тут или там.

Любые материалы из этого блога запрещается использовать на сайте livejournal.ru в любой форме и любом объёме

Comments

[gornal.livejournal.com]

Вроде сработает так, не проверял, но если и опечатался, то идею можно довести до ума.

select t1.user_id, t1.nickname from t as t1 left join t t2 on t1.user_id=t2.user_id and t1.date<t2.date where t2.user_id is null

[gornal.livejournal.com]

Классический лучше моего.

[dil.livejournal.com]

Не, так не получится. join on t1.user_id=t2.user_id никак не сочетается с where t2.user_id is null

[gornal.livejournal.com]

В mysql такая конструкция работает для left join и я всю жизнь думал, что это по стандарту SQL.

[dil.livejournal.com]

Да, работает. Мне никогда не приходило в голову делать join по неравенству

[kranov.livejournal.com]

это стандартная практика заставить субд сделать semi-join http://www.sql.ru/forum/actualthread.aspx?tid=575673#5934896 что бы найти записи из t1, отсутствующие в t2

почему?

[kranov.livejournal.com]

where t2.user_id is null это post-join предикат. Все будет хорошо.

[kranov.livejournal.com]

select a.userid, (SELECT first 1 b.nickname FROM foo b WHERE b.user_id = a.user_id order by b.date desc)
from
(SELECT distinct userid FROM foo) a

[dil.livejournal.com]

Тоже вариант, но по-моему, тут многовато select'ов. Нельзя его как-нибудь сократить?

[kranov.livejournal.com]

зачем?
Для абстрактной красоты и пуризма рулит твой вариант.
Для практики, я управлюсь в проход/заход в foo, оракле с помощью olap синтаксиса создается окно partition by nickname, в окне записи упорядочиваются order by date desc, нумируются, берется только первая.

[kranov.livejournal.com]

в один проход

[duke-igthorn.livejournal.com]

Есть мысля - попробовать вместо второго селекта сделать group by как-нибудь

не люблю подзапросы...

[npocmu.livejournal.com]

SELECT a.userid, b.nickname
FROM
(
SELECT userid, MAX(date)
FROM foo
GROUP BY userid
) a
INNER JOIN foo b
ON a.userid = b.userid AND a.date = b.date

Re: не люблю подзапросы...

[dil.livejournal.com]

и тем не менее, он тут есть :)
В целом работает, только к MAX(date) надо подписать AS date, иначе ругается на отсутсвие a.date

Re: не люблю подзапросы...

[npocmu.livejournal.com]

Я имел в виду коррелирующие подзапросы, конечно. Предполагаю, что при тупом оптимизаторе, этот запрос будет гораздо быстрее выполнятся, чем классический вариант.

[some41.livejournal.com]

не стандартно, зато 1 запрос:
select userid, LAST(nickname) from foo group by userid order by date

[some41.livejournal.com]

это я наврал, конечно, нужно
select userid,last(nickname) from (select userid, nickname from foo order by date) group by userid
а это уже с подзапросом

[wesel.livejournal.com]

А куда на работу берут с такими задачками? :) Если не секрет.

[dil.livejournal.com]

Ну вот ежели у нас будет очередная вакансия прикладного программиста со знанием БД, так я ее туда включу :)