Эффективный последний рекордный запрос с Postgresql
Я не требую никакого кредита на ответ, потому что я нашел его после некоторого поиска:
то, Что я не знал, - то, что PostgreSQL позволяет, Вы для определения собственных агрегатных функций с СОЗДАЕТЕ АГРЕГАТ
Это сообщение на шоу списка PostgreSQL, как тривиальный это должно создать функцию, чтобы сделать то, что требуется:
CREATE AGGREGATE textcat_all(
basetype = text,
sfunc = textcat,
stype = text,
initcond = ''
);
SELECT company_id, textcat_all(employee || ', ')
FROM mytable
GROUP BY company_id;
4 ответа
Если вы не хотите изменять свою модель данных, вы можете использовать DISTINCT ON , чтобы получить самую новую запись из таблицы «b» для каждой записи в «a» :
SELECT DISTINCT ON (a.id) *
FROM a
INNER JOIN b ON a.id=b.id
ORDER BY a.id, b.date DESC
Если вы хотите избежать «сортировки» в запросе, добавление такого индекса может помочь вам, но я не уверен:
CREATE INDEX b_id_date ON b (id, date DESC)
SELECT DISTINCT ON (b.id) *
FROM a
INNER JOIN b ON a.id=b.id
ORDER BY b.id, b.date DESC
В качестве альтернативы, если вы хотите отсортировать записи из таблица "a" каким-то образом:
SELECT DISTINCT ON (sort_column, a.id) *
FROM a
INNER JOIN b ON a.id=b.id
ORDER BY sort_column, a.id, b.date DESC
Альтернативные подходы
Однако все вышеперечисленные запросы по-прежнему должны читать все ссылочные строки из таблицы "b", поэтому, если у вас много данных, он может быть слишком медленным.
Вы можете создать новую таблицу, которая будет содержать только самую новую запись «b» для каждого a.id - или даже переместить эти столбцы в таблицу «a»
Если у вас много строк на каждый идентификатор, вам определенно нужен коррелированный подзапрос. Он выполнит 1 поиск по индексу для каждого идентификатора, но это быстрее, чем сортировка всей таблицы.
Примерно так:
SELECT a.id,
(SELECT max(t.date) FROM table t WHERE t.id = a.id) AS lastdate
FROM table2;
'table2', который вы будете использовать, не та таблица, которую вы упомянули в своем запросе выше, потому что здесь вам нужно список отдельных идентификаторов для хорошей производительности. Поскольку ваши идентификаторы, вероятно, являются FK в другой таблице, используйте эту.
это могло бы быть более эффективным. Разница: запрос для таблицы b выполняется только 1 раз, ваш коррелированный подзапрос выполняется для каждой строки:
SELECT *
FROM table a
JOIN (SELECT ID, max(date) maxDate
FROM table
GROUP BY ID) b
ON a.ID = b.ID AND a.date = b.maxDate
WHERE ID IN $LIST
Метод On - создать небольшую производную таблицу, содержащую самое последнее время обновления / вставки в таблице a - вызвать эту таблицу a_latest. Таблица a_latest потребует достаточной детализации для удовлетворения ваших конкретных требований к запросу. В вашем случае должно быть достаточно использовать
CREATE TABLE
a_latest
( id INTEGER NOT NULL,
date TSTAMP NOT NULL,
PRIMARY KEY (id, max_time) );
Затем используйте запрос, аналогичный предложенному najmeddine:
SELECT a.*
FROM TABLE a, TABLE a_latest
USING ( id, date );
Уловка тогда заключается в том, чтобы поддерживать a_latest в актуальном состоянии. Сделайте это с помощью триггера для вставок и обновлений. Триггер, написанный на plppgsql, довольно легко написать. Если хотите, я с радостью приведу пример.
Дело в том, что вычисление времени последнего обновления выполняется во время самих обновлений. Это снимает большую часть нагрузки с запроса.