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

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

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) только...

Спасибо!


mysql aggregate
person cars    schedule 08.12.2011    source источник
comment
Для какого значения метки времени ожидать только результат (1,4)?   -  person Ben Lee    schedule 09.12.2011
comment
то есть SELECT * FROM t WHERE timestamp < :date -- для чего :date ваш пример?   -  person Ben Lee    schedule 09.12.2011
comment
Я бы использовал 5 в качестве :date для этого примера! (отредактировано)   -  person cars    schedule 09.12.2011
comment
Итак, просто для ясности: он не возвращает никаких строк с идентификатором 2, потому что есть один после метки времени 5 (2,6), и он не возвращает никаких строк с идентификатором 3, потому что 3 находится в таблице y, верно?   -  person Ben Lee    schedule 09.12.2011
comment
да, сэр, это правильно   -  person cars    schedule 09.12.2011

Ответы (1)


arrow_upward
2
arrow_downward

Вам нужно выбрать с помощью MAX чтобы получить последнее время (вместо сортировки), выполните LEFT JOIN для сравнения данных в другой таблице и 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. Таким образом, вы LEFT JOIN table y и выбираете только строки, в которых соответствующая строка в таблице y не существует (т.е. этот идентификатор не существует в таблице y); вы делаете это, используя обычный WHERE.

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

person Ben Lee    schedule 08.12.2011
comment
Элегантно, спасибо! Есть ли место, на которое вы могли бы обратить внимание, чтобы сделать его более эффективным? - person cars; 09.12.2011
comment
Чтобы сделать его эффективным, все, что вам нужно сделать, это добавить индексы к соответствующим столбцам (в этом случае вы захотите добавить индексы для t.id, t.timestamp и y.id). - person Ben Lee; 09.12.2011