SQL — произведения, издававшиеся более 5 раз (библиотека)

Условие

Дан софт, автоматизирующий работу библиотеки. 3НФ-схема такая:

• Произведение(id, название)
• Издание(id, id_произведения, год_издания, кол-во_страниц)
• Экземпляр(id, id_издание, инвентарный_номер)
• Лог_операций(id, id_user, id_экземпляр, дата_взяли, дата_вернули)

Найти произведения, которые издавались более 5 раз. Решение должно быть оптимальным: если можно без вложенного запроса/CTE — нужно без них.

Решение

Идея

«Издаваемость» — это количество строк в таблице Издание для данного произведения. Достаточно одной агрегации GROUP BY id_произведения + фильтра HAVING COUNT(*) > 5.

JOIN c таблицей Произведение нужен только если хотим вывести название (без него возвращаем id_произведения).

Код

Минимальный вариант (только id):

SELECT id_произведения,
       COUNT(*) AS editions_count
FROM Издание
GROUP BY id_произведения
HAVING COUNT(*) > 5;

С названием:

SELECT p.id,
       p.название,
       COUNT(i.id) AS editions_count
FROM Произведение p
JOIN Издание i ON i.id_произведения = p.id
GROUP BY p.id, p.название
HAVING COUNT(i.id) > 5
ORDER BY editions_count DESC;

Сложность / проверка

• Один проход по Издание + хеш-агрегация. На больших данных — O(N).
• Если есть индекс на id_произведения — план будет «Index Scan + GroupAggregate».

Подводные камни

1. WHERE COUNT(*) > 5 — частая ошибка. COUNT агрегатная и фильтруется через HAVING, не WHERE.
2. Дублирование строк при JOIN. Если по неосторожности приджойнить Экземпляр или Лог_операций — COUNT посчитает уже не издания, а экземпляры/выдачи. Считаем только по таблице Издание.
3. > 5 vs >= 5. Условие говорит «более 5» — это строго > 5, то есть от 6 включительно.
4. Произведения без изданий. Они не попадут в результат — это корректно: «не издавалось» != «издавалось ≤5 раз».
5. Подзапрос вместо HAVING. В стандартах ANSI можно через WITH + фильтр, но это лишняя работа: задание прямо указывает, что вложенные запросы здесь не оптимальны.

Эталонный ответ

SELECT id_произведения, COUNT(*) FROM Издание GROUP BY id_произведения HAVING COUNT(*) > 5. Если нужно название — JOIN Произведение.