Как проверить базовое знание SQL

Written by elwood

На днях вспомнил об одной задаче, которая мне попалась на работе года полтора назад.
В то время я еще не знал об sql-ex.ru и мои познания в SQL были достаточно слабыми, на уровне
простейших селектов с inner join’ами. И, как я помню тогда, я не смог решить эту задачу без
дополнительного подзапроса. Сейчас, после нескольких десятков решенных задач на sql-ex.ru я
без проблем с ней справился, что навело меня на мысль о том, что это – неплохой тест на знание
азов SQL. В отличие от более сложных задач, эта задачка не требует большого количества времени, но
покрывает почти всё, что требуется для среднего программера в вопросе знания SQL, особенно если решать её в уме. Сразу отпадают вопросы наподобие “что такое JOIN, чем LEFT JOIN отличается от INNER JOIN, как работает группировка” итд.

Итак, формулировка такова. Есть 2 таблицы – Collection и Item.

Необходимо сделать выборку
Collection.ID Collection.Name Collection.Count
тех коллекций, которые содержат меньше 5 элементов.
Вот и всё.

ДАЛЬШЕ СПОЙЛЕР, НЕ ЧИТАТЬ !!!111 (Я не научился пока скрывать в вордпрессе куски постов, но как только научусь – оформлю соответствующе).

Алгоритм решения задачи.
Ну ясно же – надо сделать JOIN, делаем JOIN, группируем по Collection.ID, выводим ID, Name, Count(*).
Черт. При группировке по ID нельзя вывести Name. Значит, надо либо завернуть полученную выборку в еще 1 запрос, либо дописать в группировку этот Name. Ок, работает. Только пустые коллекции не выводятся. Почему ? Ну ясно же – они не попадают в результат INNER JOIN’a, надо использовать LEFT JOIN. О, теперь получилось. Только коллекции-то пустые, а count(*) выводит единицу вместо нуля. Ах да, это же LEFT JOIN, он джойнит Collection.ID Collection.Name NULL NULL и count(*) дает единицу. Как поправить-то. Вычесть 1 нельзя, поскольку так можно запороть непустые коллекции. Надо как-то проверить, является ли коллекция пустой, и для нее вывести 0, а для остальных – count(*). Да у нас же справа NULL, можно их в какую-нибудь агрегатную функцию запихнуть, и сравнить с NULL’ом. Если NULL, значит, коллекция пустая, и для нее выводим 0, иначе – count(*). Эврика! Ну и добавляем HAVING. Пишем запрос:

SELECT
  c.ID, c.Name, CASE WHEN COUNT(*) = 1 AND MAX(i.CollectionID) IS NULL THEN 0 ELSE COUNT(*) END
FROM Collection c
LEFT JOIN Item i ON c.ID = i.CollectionID
GROUP BY c.ID, c.Name
HAVING COUNT(*) < 5
  • Артур Фатхуллин

    Клевый пример, возьму на заметку для собеседований. Понравилось что фурмулировка задания простая:)

  • Михалыч

    Вставить разделитель поста в редакторе – кнопочка с пунктирной линией (“- – -“), насколько я помню.

    По теме – разве без CASE в данном случае нельзя обойтись дистинктом?

    SELECT c.ID, c.Name, COUNT(DISTINCT i.ID)
    FROM Collection c
    LEFT JOIN Item i ON c.ID = i.CollectionID
    GROUP BY c.ID, c.Name
    HAVING COUNT(*) < 5

    COUNT(DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, nonnull values.

  • elwood

    Можно ! Очень в тему тут. Как раз считает то, что нужно. Правда хз есть ли он где-нибудь помимо MS SQL.. UPD: Да, есть, ура, радуемся )

  • elwood

    Так, тут был комент Анджея (который был утерян во время SQL-борьбы со спамом) про то, что DISTINCT и не нужен (поскольку если * – то NULL-строки учитываются, а если конкретный столбец указан – то нет). Поэтому в принципе задача была поставлена зря. Достаточно написать COUNT(i.ID).

  • John Williams

    WITH CollectionItems AS
    (
    SELECT c.ID AS ID_coll, c.Name, i.ID AS ID_item, i.value
    FROM Collection AS c
    LEFT OUTER JOIN Item AS i ON c.ID = i.CollectionID
    )
    ,
    ItemsCount AS
    (
    SELECT c.ID, c.Name
    , (SELECT COUNT(ci.ID_item)
    FROM CollectionItems ci
    WHERE ci.ID_coll = c.ID) AS cnt
    FROM Collection AS c
    )

    SELECT a.ID, a.Name, a.cnt
    FROM ItemsCount AS a
    WHERE a.cnt < 5