mysql - выбрать самое последнее по значению до даты x, если не после даты x и не в другой таблице

Question

У меня есть таблица т:

id, timestamp

Существует несколько значений идентификаторов, и несколько строк могут иметь один и тот же идентификатор.

Я хочу выбрать самую последнюю строку для каждого идентификатора, до даты x, только если идентификатор не найден после даты x, а идентификатор также не найден в таблице y.

Я могу выбрать все до x даты, в этом примере:date=5:

SELECT * FROM t WHERE timestamp < :date

Я пытался получить только самый последний идентификатор, не получая самый последний - но возвращая 1 строку на идентификатор:

SELECT * FROM t WHERE timestamp < :date GROUP BY id ORDER BY timestamp DESC

Я обеспокоен тем, что GROUP BY будет замедлять работу с большим количеством данных.

Вот некоторые примеры данных БД:

CREATE TABLE IF NOT EXISTS `t` (
  `id` int(2) NOT NULL,
  `timestamp` int(2) NOT NULL
) 
INSERT INTO `t` (`id`, `timestamp`) VALUES
(1, 1),
(1, 4),
(2, 3),
(2, 1),
(2, 6),
(3, 4),
(3, 2);

CREATE TABLE IF NOT EXISTS `y` (
  `id` int(2) NOT NULL,
  `timestamp` int(2) NOT NULL
) 
INSERT INTO `y` (`id`, `timestamp`) VALUES
(3, 1);

Нужно вернуть только строку (1,4)...

Спасибо!

Answer 1

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

SELECT t.id, MAX(t.timestamp) AS latest_timestamp
FROM t
LEFT JOIN y on t.id = y.id
WHERE y.id IS NULL
GROUP BY t.id
HAVING latest_timestamp <= :date

Когда вы делаете GROUP BY, вы можете выбирать с помощью агрегатных функций. Здесь MAX возвращает максимальное значение для этого столбца во всех строках группы (поскольку вы группируете по идентификатору, это будет возвращать максимальную временную метку для каждого идентификатора). Но вы хотите выбрать только те элементы, у которых нет метки времени после:date - вот где начинается HAVING (HAVING - это, по сути, WHERE для агрегатов GROUP BY). Наконец, вы не хотите выбирать элементы из таблицы y. Таким образом, вы оставляете таблицу YIN JOIN и выбираете только те строки, в которых не существует соответствующая строка в таблице y (то есть этот идентификатор не существует в таблице y); Вы делаете это с помощью обычного ГДЕ.

ОБНОВЛЕНИЕ: чтобы сделать это эффективным, все, что вам нужно сделать, это добавить индексы в соответствующие столбцы. В этом случае вы хотели бы добавить индексы для t.id, t.timestamp, а также y.id, См. http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html.