Соединение SQL: выбор последних записей в связи "один ко многим"
Предположим, что у меня есть таблица клиентов и таблица покупок. Каждая покупка принадлежит одному клиенту. Я хочу получить список всех клиентов наряду с их последней покупкой в одном операторе SELECT. Какова лучшая практика? Совет относительно создания индексов?
Используйте их таблица/имена столбцов в Вашем ответе:
- клиент: идентификатор, имя
- покупка: идентификатор, customer_id, item_id, дата
И в более сложных ситуациях, это было бы (мудро производительностью) выгодный для денормализовывания базы данных путем помещения последней покупки в потребительскую таблицу?
Если (покупка) идентификатор, как гарантируют, будет отсортирован по дате, могут операторы упрощаться при помощи чего-то как LIMIT 1?
3 ответа
Это пример этой проблемы Perglest-n-Per-Group , которая появилась регулярно на стойке.
Вот как я обычно рекомендую решать его:
SELECT c.*, p1.*
FROM customer c
JOIN purchase p1 ON (c.id = p1.customer_id)
LEFT OUTER JOIN purchase p2 ON (c.id = p2.customer_id AND
(p1.date < p2.date OR (p1.date = p2.date AND p1.id < p2.id)))
WHERE p2.id IS NULL;
Объяснение: Учитывая ряд P1 , не должно быть строки P2 с тем же клиентом и более поздняя дата (или в Случай галстуков, а позже ID ). Когда мы обнаруживаем, что для того, чтобы быть правдой, то P1 - самая последняя покупка для этого клиента.
Что касается индексов, я бы создал сложный индекс в Покупка через столбцы ( CULLING_ID , Дата , ID ). Это может позволить внешнее соединение сделать с использованием индекса покрытия. Обязательно проверьте на своей платформе, потому что оптимизация зависит от реализации. Используйте функции ваших RDBMS для анализа плана оптимизации. Например. Объясните на MySQL.
Некоторые люди используют подзапросы вместо решения, которое я показываю выше, но я нахожу, что мое решение облегчает разрешение связей.
Вы также можете попробовать сделать это с помощью подпункта
SELECT c.*, p.*
FROM customer c INNER JOIN
(
SELECT customer_id,
MAX(date) MaxDate
FROM purchase
GROUP BY customer_id
) MaxDates ON c.id = MaxDates.customer_id INNER JOIN
purchase p ON MaxDates.customer_id = p.customer_id
AND MaxDates.MaxDate = p.date
Выбор должен присоединиться к всем клиентам и их дату покупки .
Вы не указали базу данных. Если это то, что позволяет аналитическими функциями, возможно, может быть бы быстрее использовать этот подход, чем в группу по одному (определенно быстрее в Oracle, скорее всего, быстрее в позднем SQL Server Editions, не знаю о других).
Синтаксис на SQL Server будет:
SELECT c.*, p.*
FROM customer c INNER JOIN
(SELECT RANK() OVER (PARTITION BY customer_id ORDER BY date DESC) r, *
FROM purchase) p
ON (c.id = p.customer_id)
WHERE p.r = 1