Самый эффективный способ выбрать повторяющиеся строки с максимальной отметкой времени

Предположим, у меня есть таблица с именем 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), что кажется ненужным.

Так есть ли более эффективный способ сделать это или что-то, что я неправильно понимаю?

stackoverflow.com/questions/3800551/…
Mark 17.03.2022 07:01
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
1
100
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 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 необходимо, учитывая, что я просто хочу макс?

taotsi 17.03.2022 10:05

Да ORDER BY очень необходимо, чтобы DISTINCT ON работал. Если вам нужен порядок Другие, вы можете выполнить подзапрос и добавить другое предложение order by.

Tim Biegeleisen 17.03.2022 10:07

@taotsi без ORDER BY, как он узнает, что вы хотите максимум? Об этом нужно как-то сообщить. Когда DISTINCT ON, упорядочение является механизмом, который информирует об этом.

jjanes 17.03.2022 16:52

@TimBiegeleisen Я имею в виду, как правило, сортировка занимает O (nlogn) времени, а поиск максимума занимает только O (n). Мне просто нужен максимум, и мне все равно, второй максимум или третий максимум... Если вы делаете это, используя какую-то библиотеку данных, например pandas, вы можете найти максимум без сортировки. Интересно, может ли SQL это сделать

taotsi 18.03.2022 03:41

@taotsi Я думаю, что то, что вы ищете, — это показатель в вашей таблице, что позволит учитывать время поиска журнала (n) для каждого значения id, qv. мой обновленный ответ выше.

Tim Biegeleisen 18.03.2022 03:55

Попробуй это

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
taotsi 17.03.2022 10:07

Другие вопросы по теме