Выбирая последнюю строку при агрегировании результатов в mysql

Question

Мне было поручено сгенерировать несколько отчетов об использовании нашего трекера запросов. Request Tracker - это система тикетов, которую мы используем для нескольких отделов, где я работаю. Для этого я делаю ночной снимок информации о билетах, измененных за день, в другую базу данных. Этот подход отделяет мои отчеты от внутренней схемы базы данных, которую использует RT.

Среди многих других вопросов для отчета, я должен сообщить, сколько билетов было решено в каждом месяце за отдел. В RT отдел хранится как CustomField, и мое моделирование следует этой тенденции, как вы можете видеть в моем запросе ниже. Однако из-за того, что я получаю снимки каждую ночь, у меня есть несколько строк для заявки, и поле "Отдел" может меняться в течение месяца. Я заинтересован только в самой последней области Департамента. Я не знаю, как получить это в запросе.

Я знаю, что могу использовать "GROUP BY", чтобы сократить результаты моего запроса до одного за тикет, но когда я это делаю, я не знаю, как получить последние настройки отдела. Поскольку все отделы являются строками, MAX() не получает последний. MySQL не требует, чтобы вы использовали агрегирующую функцию для полей, которые вы выбираете, но результаты являются неопределенными (из моего тестирования похоже, что он может получить первую в моей версии MySQL).

Чтобы проиллюстрировать это, вот результаты запроса, который показывает мне два билета и все его настройки поля отдела:

"ticket_num","date","QueueName","CF","CFValue","closed"
35750,"2009-09-22","IT_help","Department","",""
35750,"2009-09-23","IT_help","Department","",""
35750,"2009-09-24","IT_help","Department","",""
35750,"2009-09-25","IT_help","Department","",""
35750,"2009-09-26","IT_help","Department","",""
35750,"2009-10-02","IT_help","Department","",""
35750,"2009-10-03","IT_help","Department","",""
35750,"2009-10-12","IT_help","Department","",""
35750,"2009-10-13","IT_help","Department","",""
35750,"2009-10-26","IT_help","Department","Conference/Visitors","2009-10-26 10:10:32"
35750,"2009-10-27","IT_help","Department","Conference/Visitors","2009-10-26 10:10:32"
36354,"2009-10-20","IT_help","Department","",""
36354,"2009-10-21","IT_help","Department","",""
36354,"2009-10-22","IT_help","Department","FS Students",""
36354,"2009-10-23","IT_help","Department","FS Students",""
36354,"2009-10-26","IT_help","Department","FS Students","2009-10-26 12:23:00"
36354,"2009-10-27","IT_help","Department","FS Students","2009-10-26 12:23:00"

Как мы видим, оба билета были закрыты 26-го, и оба билета имели пустое поле Департамента в течение нескольких дней, когда они впервые появились. Я включил свой запрос ниже, вы можете видеть, что я искусственно ограничил число столбцов, возвращаемых во второй половине оператора where:

SELECT d.ticket_num, d.date, q.name as QueueName, cf.name as CF, cfv.value as CFValue, d.closed
FROM daysCF dcf
INNER JOIN daily_snapshots d on dcf.day_id = d.id
INNER JOIN Queues q on d.queue_id = q.id
INNER JOIN CustomFieldValues cfv on dcf.cfv_id = cfv.id
INNER JOIN CustomFields cf on cf.id = cfv.field_id
WHERE cf.name = 'Department' and (d.ticket_num = 35750 or d.ticket_num = 36354)
ORDER by d.ticket_num, d.date

Как я могу изменить этот запрос, чтобы получить набор результатов, который говорит мне, что в октябре был закрыт один билет для "Студентов FS" и один билет для "Конференции / Посетителей"?

Answer 1

Это проблема "наибольший по количеству групп", которая часто возникает при переполнении стека.

Вот как бы я решил это в вашем случае:

SELECT d1.ticket_num, d1.date, q.name as QueueName, 
  cf.name as CF, cfv.value as CFValue, d1.closed
FROM daysCF dcf
INNER JOIN daily_snapshots d1 ON (dcf.day_id = d1.id)
INNER JOIN Queues q ON (d1.queue_id = q.id)
INNER JOIN CustomFieldValues cfv ON (dcf.cfv_id = cfv.id)
INNER JOIN CustomFields cf ON (cf.id = cfv.field_id)
LEFT OUTER JOIN daily_snapshots d2 ON (d1.ticket_num = d2.ticket_num AND d1.date < d2.date)
WHERE d2.id IS NULL AND cf.name = 'Department'
ORDER by d1.ticket_num, d1.date;

Answer 2

Mysql не имеет оператора LAST, поэтому вам действительно нужно сделать это, используя временную таблицу.

CREATE TEMPORARY TABLE last_dates SELECT ticket_num, MAX(date) AS date
  FROM daily_snapshots GROUP BY ticket_num

это дает вам таблицу с последней датой для каждого билета. Затем в вашем основном запросе присоединитесь к этой таблице с полями ticket_num и date. Это отфильтрует все строки, для которых дата не самая последняя для соответствующего номера заявки.

Вам может понадобиться индекс для этой временной таблицы, я оставлю это вам.