Агрегатные функции в предложении WHERE в SQLite
Проще говоря, у меня есть таблица, в которой, помимо прочего, есть столбец для временных меток. Я хочу получить строку с самой последней меткой времени (т. Е. С наибольшим значением). Сейчас делаю вот что:
SELECT * FROM table ORDER BY timestamp DESC LIMIT 1
Но я бы предпочел сделать что-то вроде этого:
SELECT * FROM table WHERE timestamp=max(timestamp)
Однако SQLite отклоняет этот запрос:
SQL error: misuse of aggregate function max()
документация подтверждает это поведение (внизу страницы):
Aggregate functions may only be used in a SELECT statement.
Мой вопрос: можно ли написать запрос, чтобы получить строку с наибольшей отметкой времени, не упорядочивая выбор и не ограничивая количество возвращаемых строк до 1? Кажется, это должно быть возможно, но я полагаю, что мой SQL-fu не в порядке.
Ответы 4
SELECT * from foo where timestamp = (select max(timestamp) from foo)
или, если SQLite настаивает на обработке подзапросов как наборов,
SELECT * from foo where timestamp in (select max(timestamp) from foo)
Просто любопытно, заставляет ли подзапрос sqlite обрабатывать таблицу дважды или это оптимизировано?
Это заставляет его обрабатывать таблицу дважды - один раз для определения максимума и еще раз для поиска совпадений. Я не уверен, что вы можете получить однопроходный алгоритм для этого из SQL.
Думаю, я отвечал на этот вопрос 5 раз за последнюю неделю, но я слишком устал, чтобы найти ссылку на один из них прямо сейчас, так что вот он снова ...
SELECT
*
FROM
table T1
LEFT OUTER JOIN table T2 ON
T2.timestamp > T1.timestamp
WHERE
T2.timestamp IS NULL
Вы в основном ищете строку, в которой не соответствует ни одна другая строка, более поздняя, чем она.
ПРИМЕЧАНИЕ: Как указано в комментариях, этот метод не будет работать так же хорошо в такой ситуации. Обычно это работает лучше (по крайней мере, для SQL Server) в ситуациях, когда вам нужна последняя строка для каждого клиента (в качестве примера).
Это может сработать, но разве выполнение соединения не влечет за собой снижение производительности?
Это работает, но поражает своей неэффективностью. Предполагая, что в исходной таблице 10 строк, это дает (и отбрасывает) 55 строк. Для 100 строк размер создаваемого вами набора составляет 5050. Растет очень быстро. Лучше просто сделать два сканирования.
Да, и это не работает, когда у вас есть две строки с одинаковой максимальной отметкой времени.
@Dmitriy - ваше собственное решение дает тот же точный результат, если две строки имеют одинаковую максимальную временную метку. Что касается эффективности, то в этом упрощенном примере использование подзапроса работает лучше, но не в тех случаях, когда вы хотите, чтобы последняя строка была сгруппирована по другому столбцу.
Том, ты прав, я снимаю свой комментарий о том, что твое решение не работает. Но я считаю, что это очень неэффективно. Что до группировки по другому столбцу - а? Просто добавьте "группу по x". Может я не понимаю сценарий? Как правило, из соображений философии я стараюсь избегать почти декартовых соединений.
Есть много способов снять шкуру с кошки.
Если у вас есть столбец идентификаторов, который имеет функцию автоматического увеличения, более быстрый запрос будет результатом, если вы вернете последнюю запись по идентификатору из-за индексации столбца, если, конечно, вы не хотите поместить индекс в столбец с меткой времени.
SELECT * FROM TABLE ORDER BY ID DESC LIMIT 1
Красиво работает, спасибо. Я знал, что это должно быть что-то простое.