Выведите всех операторов поддержки с числом закрытых обращений, включая тех, у кого закрытых нет
Проверила команда Сопровод · Обновлено
Короткий ответ
Возьмите операторов левой таблицей и присоедините обращения через LEFT JOIN, а фильтр status = 'closed' поставьте в ON. Тогда оператор без закрытых обращений останется в выборке со строкой из NULL справа. Считайте count(t.id): он пропускает NULL и дает 0. count(*) посчитал бы эту пустую строку и вернул 1.
Почему этот вопрос задают
Это самая частая ловушка с внешним соединением: запрос выглядит верно, а операторы без закрытых обращений молча пропадают из отчета. Задача показывает, понимаете ли вы, когда срабатывает условие и что считает COUNT.
Условие задачи
Служба поддержки интернет-провайдера ведет таблицы agents(id, name) и tickets(id, agent_id, status). В agents пять операторов: 1 Алина, 2 Борис, 3 Вадим, 4 Галина, 5 Денис. Обращение закрыто, если status = 'closed'. Выведите каждого оператора и число его закрытых обращений по алфавиту имен. Операторы без закрытых обращений тоже попадают в отчет с нулем: у Вадима обращений нет совсем, у Галины только открытые.
Как подойти к решению
- Возьмите операторов левой таблицей, чтобы в отчет попал каждый из них
- Поставьте условие на статус в
ON: так оно отбирает обращения до соединения - Считайте
count(t.id), чтобы у оператора без пары получился 0 - Сверьте результат с условием: пять строк, у Вадима и Галины нули
Решение
Условие в ON отбирает пары до соединения, и операторы без закрытых обращений получают строку с NULL справа. В WHERE то же условие отбросило бы эти строки после соединения, и Вадим с Галиной пропали бы. count(t.id) не считает NULL, поэтому у них 0. Отчет по всем операторам читает обращения целиком, индекс по tickets(agent_id) пригодится, когда в отчете два-три оператора.
SELECT a.name, count(t.id) AS closed_tickets
FROM agents a
LEFT JOIN tickets t
ON t.agent_id = a.id
AND t.status = 'closed'
GROUP BY a.id, a.name
ORDER BY a.name;name | closed_tickets --------+---------------- Алина | 3 Борис | 1 Вадим | 0 Галина | 0 Денис | 2 (5 rows)
Что спросят следом
Как одним запросом посчитать у каждого оператора и закрытые, и открытые обращения?
Как вывести только операторов, у которых нет ни одного закрытого обращения?
Что будет с обращением, у которого
agent_idпустой?
Доступно по подписке
Ответы на эти вопросы
Частые ошибки
«Отфильтрую закрытые обращения в
WHEREпосле соединения»Фильтр в
WHEREотбросит строки, где справаNULL, иLEFT JOINсработает как внутреннее соединение. Условие на правую таблицу ставьте вON«Посчитаю
count(*), это то же самое»Вадим и Галина получат по 1 за пустую строку справа. Считайте столбец, который в найденной паре всегда заполнен:
t.idилиt.agent_idиз условияON. Столбец с пропусками занизит счет«Добавлю в
WHEREусловиеOR t.status IS NULL, и все починится»Галина все равно пропадет: у ее обращений статус есть, он просто другой. Такое условие спасает только операторов совсем без обращений
«На всякий случай оберну счетчик в
coalesce(count(t.id), 0)»countникогда не возвращаетNULL, обертка лишняя.coalesceнужен дляsumиavg: по группе без значений они даютNULL
Сигналы для интервьюера
Сильный ответ
Уточняет, за какой период нужен отчет, и показывает, что фильтр по дате обращения тоже уходит в ON
Тревожный сигнал
Группирует только по имени и не думает, что два оператора с одинаковым именем сольются в одну строку
Красный флаг
Меняет LEFT JOIN на INNER JOIN и решает, что операторы без закрытых обращений отчету не нужны
Как еще могут спросить
- Почему
LEFT JOINпревращается вINNER JOIN, если добавить условие вWHERE? - Как посчитать записи из второй таблицы так, чтобы строки без пары остались с нулем?
- Выведите всех сотрудников с количеством выполненных задач, включая тех, у кого их нет
- Почему
count(*)послеLEFT JOINдает 1 там, где пары нет?











