Предположим, у меня есть таблица с именем t
, что-то вроде
id content time
1 'a' 100
1 'a' 101
1 'b' 102
2 'c' 200
2 'c' 201
id
дублируются, и для одного и того же id
контент тоже может дублироваться. Теперь я хочу выбрать для каждого идентификатора строки с максимальной отметкой времени, которые будут
id content time
1 'b' 102
2 'c' 201
И это мое текущее решение:
select t1.id, t1.content, t1.time
from (
select id, content, time from t
) as t1
right join (
select id, max(time) as time from t group by id
) as t2
on t1.id = t2.id and t1.time = t2.time;
Но мне это кажется неэффективным. Потому что теоретически, когда выполняется select id, max(time) as time from t group by id
, нужные мне строки уже находятся. right join
приносит дополнительные затраты времени O (n ^ 2), что кажется ненужным.
Так есть ли более эффективный способ сделать это или что-то, что я неправильно понимаю?
Используйте DISTINCT ON
:
SELECT DISTINCT ON (id) id, content, time
FROM yourTable
ORDER BY id, time DESC;
В Postgres это обычно самый производительный способ написания вашего запроса, и он должен превзойти ROW_NUMBER
и другие подходы.
Следующий индекс может ускорить этот запрос:
CREATE INDEX idx ON yourTable (id, time DESC, content);
Этот индекс, если он используется, позволит Postgres быстро найти для каждого id
запись с самым последним временем. Этот индекс также охватывает столбец content
.
order by
необходимо, учитывая, что я просто хочу макс?
Да ORDER BY
очень необходимо, чтобы DISTINCT ON
работал. Если вам нужен порядок Другие, вы можете выполнить подзапрос и добавить другое предложение order by.
@taotsi без ORDER BY, как он узнает, что вы хотите максимум? Об этом нужно как-то сообщить. Когда DISTINCT ON, упорядочение является механизмом, который информирует об этом.
@TimBiegeleisen Я имею в виду, как правило, сортировка занимает O (nlogn) времени, а поиск максимума занимает только O (n). Мне просто нужен максимум, и мне все равно, второй максимум или третий максимум... Если вы делаете это, используя какую-то библиотеку данных, например pandas, вы можете найти максимум без сортировки. Интересно, может ли SQL это сделать
@taotsi Я думаю, что то, что вы ищете, — это показатель в вашей таблице, что позволит учитывать время поиска журнала (n) для каждого значения id
, qv. мой обновленный ответ выше.
Попробуй это
SELECT a.id, a.content, a.time FROM t AS a
INNER JOIN (
SELECT a.content, MAX(a.time) AS time FROM t
GROUP BY a.content
) AS b ON a.content = b.content AND a.time = b.time
content
могут быть очень длинные строки. Я думаю, что сравнивать content
менее эффективно, чем id