PostgreSQL - получить строку, которая имеет значение Max для столбца
Я имею дело с таблицей Postgres (называемой "жизнями"), которая содержит записи со столбцами для time_stamp, usr_id, Transactions_id и Life_remaining. Мне нужен запрос, который даст мне самую последнюю итоговую сумму life_remaining для каждого usr_id
- Есть несколько пользователей (отличные от usr_id)
- time_stamp не является уникальным идентификатором: иногда пользовательские события (по одному в строке в таблице) происходят с одной и той же time_stamp.
- trans_id уникален только для очень маленьких временных диапазонов: со временем он повторяется
- Остальные_живы (для данного пользователя) могут как увеличиваться, так и уменьшаться со временем
пример:
time_stamp | lives_remaining | usr_id | trans_id ----------------------------------------- 07:00 | 1 | 1 | 1 09:00 | 4 | 2 | 2 10:00 | 2 | 3 | 3 10:00 | 1 | 2 | 4 11:00 | 4 | 1 | 5 11:00 | 3 | 1 | 6 13:00 | 3 | 3 | 1
Поскольку мне нужно будет получить доступ к другим столбцам строки с самыми последними данными для каждого заданного usr_id, мне нужен запрос, который дает такой результат:
time_stamp | lives_remaining | usr_id | trans_id ----------------------------------------- 11:00 | 3 | 1 | 6 10:00 | 1 | 2 | 4 13:00 | 3 | 3 | 1
Как уже упоминалось, каждый usr_id может получить или потерять жизни, и иногда эти события с метками времени происходят так близко друг к другу, что имеют одинаковую метку времени! Поэтому этот запрос не будет работать:
SELECT b.time_stamp,b.lives_remaining,b.usr_id,b.trans_id FROM
(SELECT usr_id, max(time_stamp) AS max_timestamp
FROM lives GROUP BY usr_id ORDER BY usr_id) a
JOIN lives b ON a.max_timestamp = b.time_stamp
Вместо этого мне нужно использовать time_stamp (first) и trans_id (second) для определения правильной строки. Затем мне также нужно передать эту информацию из подзапроса в основной запрос, который предоставит данные для других столбцов соответствующих строк. Это взломанный запрос, который я получил на работу:
SELECT b.time_stamp,b.lives_remaining,b.usr_id,b.trans_id FROM
(SELECT usr_id, max(time_stamp || '*' || trans_id)
AS max_timestamp_transid
FROM lives GROUP BY usr_id ORDER BY usr_id) a
JOIN lives b ON a.max_timestamp_transid = b.time_stamp || '*' || b.trans_id
ORDER BY b.usr_id
Итак, это работает, но мне это не нравится. Для этого требуется запрос внутри запроса, самостоятельное соединение, и мне кажется, что это может быть намного проще, если взять строку, в которой MAX обнаружил наибольшую метку времени и trans_id. В таблице "live" нужно проанализировать десятки миллионов строк, поэтому я бы хотел, чтобы этот запрос был максимально быстрым и эффективным. Я новичок в RDBM и Postgres, в частности, поэтому я знаю, что мне нужно эффективно использовать правильные индексы. Я немного растерялся, как оптимизировать.
Я нашел подобное обсуждение здесь. Могу ли я выполнить некоторый тип Postgres, эквивалентный аналитической функции Oracle?
Будем весьма благодарны за любые советы по доступу к информации о связанных столбцах, используемой агрегатной функцией (например, MAX), созданию индексов и созданию более совершенных запросов!
PS Вы можете использовать следующее для создания моего примера:
create TABLE lives (time_stamp timestamp, lives_remaining integer,
usr_id integer, trans_id integer);
insert into lives values ('2000-01-01 07:00', 1, 1, 1);
insert into lives values ('2000-01-01 09:00', 4, 2, 2);
insert into lives values ('2000-01-01 10:00', 2, 3, 3);
insert into lives values ('2000-01-01 10:00', 1, 2, 4);
insert into lives values ('2000-01-01 11:00', 4, 1, 5);
insert into lives values ('2000-01-01 11:00', 3, 1, 6);
insert into lives values ('2000-01-01 13:00', 3, 3, 1);
11 ответов
На таблице с 158k псевдослучайных строк (usr_id равномерно распределены между 0 и 10k, trans_id равномерно распределены между 0 и 30),
Ниже приведена ссылка на стоимость запроса, я имею в виду оценку стоимости оптимизатором Postgres (с настройками Postgres по умолчанию). xxx_cost значения), которая представляет собой взвешенную функцию оценки требуемых ресурсов ввода-вывода и ресурсов ЦП; это можно получить, запустив PgAdminIII и запустив "Query/Explain (F7)" для запроса с "Query/Explain options", установленным в "Analyze"
- Запрос Quassnoy имеет оценку стоимости 745 тыс. (!) И выполняется за 1,3 секунды (с учетом составного индекса на (
usr_id,trans_id,time_stamp)) - Запрос Билла имеет оценку стоимости 93k и выполняется за 2,9 секунды (с учетом составного индекса на (
usr_id,trans_id)) - Запрос № 1, приведенный ниже, оценивается в 16 тыс. И выполняется за 800 мс (с учетом составного индекса на (
usr_id,trans_id,time_stamp)) - Приведенный ниже запрос № 2 оценивается в 14 тыс. Фунтов и выполняется за 800 мс (с учетом индекса составной функции на (
usr_id,EXTRACT(EPOCH FROM time_stamp),trans_id))- это специфично для Postgres
- В приведенном ниже запросе № 3 (Postgres 8.4+) оценочная стоимость и время выполнения сопоставимы с (или лучше) запроса № 2 (с учетом составного индекса на (
usr_id,time_stamp,trans_id)); он имеет преимущество сканированияlivesтаблицу только один раз, и, если вы временно увеличите (если необходимо) work_mem для размещения сортировки в памяти, это будет самый быстрый из всех запросов.
Все вышеперечисленное включает в себя получение полного набора результатов по 10 тысяч строк.
Ваша цель - минимальная оценка стоимости и минимальное время выполнения запроса с акцентом на оценочную стоимость. Выполнение запроса может существенно зависеть от условий выполнения (например, от того, что соответствующие строки уже полностью кэшированы в памяти или нет), тогда как оценка стоимости - нет. С другой стороны, имейте в виду, что смета - это именно оценка.
Наилучшее время выполнения запроса достигается при работе с выделенной базой данных без нагрузки (например, при игре с pgAdminIII на ПК разработчика). Время выполнения запроса может варьироваться в зависимости от фактической загрузки машины / распределения доступа к данным. Когда один запрос выглядит немного быстрее (<20%), чем другой, но имеет гораздо более высокую стоимость, обычно будет разумнее выбрать тот, у которого больше время выполнения, но меньшая стоимость.
Если вы ожидаете, что во время выполнения запроса конкуренция за память на вашем рабочем компьютере не будет (например, кэш СУБД и кэш файловой системы не будут перегружены параллельными запросами и / или операциями с файловой системой), то полученное вами время запроса в автономном режиме (например, pgAdminIII на ПК для разработки) будет представительным. Если в производственной системе возникают конфликты, время запроса будет уменьшаться пропорционально расчетному соотношению затрат, так как запрос с более низкой стоимостью не столько полагается на кэш, тогда как запрос с более высокой стоимостью будет снова и снова возвращаться к одним и тем же данным (запуск дополнительные операции ввода-вывода при отсутствии стабильного кэша), например:
cost | time (dedicated machine) | time (under load) |
-------------------+--------------------------+-----------------------+
some query A: 5k | (all data cached) 900ms | (less i/o) 1000ms |
some query B: 50k | (all data cached) 900ms | (lots of i/o) 10000ms |
Не забывай бегать ANALYZE lives один раз после создания необходимых показателей.
Запрос № 1
-- incrementally narrow down the result set via inner joins
-- the CBO may elect to perform one full index scan combined
-- with cascading index lookups, or as hash aggregates terminated
-- by one nested index lookup into lives - on my machine
-- the latter query plan was selected given my memory settings and
-- histogram
SELECT
l1.*
FROM
lives AS l1
INNER JOIN (
SELECT
usr_id,
MAX(time_stamp) AS time_stamp_max
FROM
lives
GROUP BY
usr_id
) AS l2
ON
l1.usr_id = l2.usr_id AND
l1.time_stamp = l2.time_stamp_max
INNER JOIN (
SELECT
usr_id,
time_stamp,
MAX(trans_id) AS trans_max
FROM
lives
GROUP BY
usr_id, time_stamp
) AS l3
ON
l1.usr_id = l3.usr_id AND
l1.time_stamp = l3.time_stamp AND
l1.trans_id = l3.trans_max
Запрос № 2
-- cheat to obtain a max of the (time_stamp, trans_id) tuple in one pass
-- this results in a single table scan and one nested index lookup into lives,
-- by far the least I/O intensive operation even in case of great scarcity
-- of memory (least reliant on cache for the best performance)
SELECT
l1.*
FROM
lives AS l1
INNER JOIN (
SELECT
usr_id,
MAX(ARRAY[EXTRACT(EPOCH FROM time_stamp),trans_id])
AS compound_time_stamp
FROM
lives
GROUP BY
usr_id
) AS l2
ON
l1.usr_id = l2.usr_id AND
EXTRACT(EPOCH FROM l1.time_stamp) = l2.compound_time_stamp[1] AND
l1.trans_id = l2.compound_time_stamp[2]
Обновление 2013/01/29
Наконец, начиная с версии 8.4, Postgres поддерживает оконную функцию, то есть вы можете написать что-то простое и эффективное, например:
Запрос № 3
-- use Window Functions
-- performs a SINGLE scan of the table
SELECT DISTINCT ON (usr_id)
last_value(time_stamp) OVER wnd,
last_value(lives_remaining) OVER wnd,
usr_id,
last_value(trans_id) OVER wnd
FROM lives
WINDOW wnd AS (
PARTITION BY usr_id ORDER BY time_stamp, trans_id
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);
Я бы предложил чистую версию, основанную на DISTINCT ON (см. документы):
SELECT DISTINCT ON (usr_id)
time_stamp,
lives_remaining,
usr_id,
trans_id
FROM lives
ORDER BY usr_id, time_stamp DESC, trans_id DESC;
В Postgressql 9.5 появилась новая опция, которая называется DISTINCT ON.
SELECT DISTINCT ON (location) location, time, report
FROM weather_reports
ORDER BY location, time DESC;
Он исключает повторяющиеся строки и оставляет только первую строку, как определено в предложении ORDER BY.
смотрите официальную документацию
Вот еще один метод, который не использует коррелированные подзапросы или GROUP BY. Я не эксперт в настройке производительности PostgreSQL, поэтому я предлагаю вам попробовать и это, и решения, предложенные другими людьми, чтобы увидеть, что работает лучше для вас.
SELECT l1.*
FROM lives l1 LEFT OUTER JOIN lives l2
ON (l1.usr_id = l2.usr_id AND (l1.time_stamp < l2.time_stamp
OR (l1.time_stamp = l2.time_stamp AND l1.trans_id < l2.trans_id)))
WHERE l2.usr_id IS NULL
ORDER BY l1.usr_id;
Я предполагаю что trans_id уникален, по крайней мере, по сравнению с любым значением time_stamp,
Мне нравится стиль ответа Майка Вудхауса на другой странице, которую вы упомянули. Это особенно лаконично, когда максимизируемая вещь - это всего лишь один столбец, и в этом случае подзапрос может использовать MAX(some_col) а также GROUP BY другие столбцы, но в вашем случае вам нужно максимизировать количество из 2 частей, вы все равно можете сделать это, используя ORDER BY плюс LIMIT 1 вместо этого (как это сделал Кассной):
SELECT *
FROM lives outer
WHERE (usr_id, time_stamp, trans_id) IN (
SELECT usr_id, time_stamp, trans_id
FROM lives sq
WHERE sq.usr_id = outer.usr_id
ORDER BY trans_id, time_stamp
LIMIT 1
)
Я использую синтаксис конструктора строк WHERE (a, b, c) IN (subquery) хорошо, потому что это сокращает количество необходимых слов.
На самом деле есть хакерское решение этой проблемы. Допустим, вы хотите выбрать самое большое дерево каждого леса в регионе.
SELECT (array_agg(tree.id ORDER BY tree_size.size)))[1]
FROM tree JOIN forest ON (tree.forest = forest.id)
GROUP BY forest.id
Когда вы группируете деревья по лесам, вы увидите несортированный список деревьев, и вам нужно найти самый большой. Первое, что вы должны сделать, это отсортировать строки по размеру и выбрать первую из вашего списка. Это может показаться неэффективным, но если у вас есть миллионы строк, это будет гораздо быстрее, чем решения, которые включают JOINи WHERE условия.
Кстати, обратите внимание, что ORDER_BY за array_agg введен в Postgresql 9.0
Другое решение, которое может оказаться полезным.
SELECT t.*
FROM
(SELECT
*,
ROW_NUMBER() OVER(PARTITION BY usr_id ORDER BY time_stamp DESC) as r
FROM lives) as t
WHERE t.r = 1
SELECT l.*
FROM (
SELECT DISTINCT usr_id
FROM lives
) lo, lives l
WHERE l.ctid = (
SELECT ctid
FROM lives li
WHERE li.usr_id = lo.usr_id
ORDER BY
time_stamp DESC, trans_id DESC
LIMIT 1
)
Creating an index on (usr_id, time_stamp, trans_id) значительно улучшит этот запрос.
You should always, always have some kind of PRIMARY KEY in your tables.
Это забавное рабочее решение:
with t (time_stamp, lives_remaining, usr_id, trans_id) as (
values
(time '07:00', 1, 1, 1),
(time '09:00', 4, 2, 2),
(time '10:00', 2, 3, 3),
(time '10:00', 1, 2, 4),
(time '11:00', 4, 1, 5),
(time '11:00', 3, 1, 6),
(time '13:00', 3, 3, 1)
)
select *
from unnest((
select array_agg(r)
from (
select (max(array[row(time_stamp, lives_remaining, usr_id, trans_id)]))[1] r
from t
group by usr_id
) x
)) as x (time_stamp time, lives_remaining int, usr_id int, trans_id int)
Он использует несколько приемов:
-
MAX(record)не определено, ноMAX(array)есть, и массив может содержать записи - The
UNNEST((SELECT ARRAY_AGG(...) ...))трюк позволяет снова отключить вложенную запись
Это производит:
|time_stamp|lives_remaining|usr_id|trans_id|
|----------|---------------|------|--------|
|11:00:00 |4 |1 |5 |
|13:00:00 |3 |3 |1 |
|10:00:00 |1 |2 |4 |
Если вы не против вложенности, то все гораздо проще:
select (max(array[row(time_stamp, lives_remaining, usr_id, trans_id)]))[1] r
from t
group by usr_id
Производство:
|r |
|----------------|
|(11:00:00,4,1,5)|
|(13:00:00,3,3,1)|
|(10:00:00,1,2,4)|
Наконец, если это реальная таблица, вы можете привестиrowкtчтобы упростить запись:
create table t (time_stamp time, lives_remaining int, usr_id int, trans_id int);
insert into t
values
(time '07:00', 1, 1, 1),
(time '09:00', 4, 2, 2),
(time '10:00', 2, 3, 3),
(time '10:00', 1, 2, 4),
(time '11:00', 4, 1, 5),
(time '11:00', 3, 1, 6),
(time '13:00', 3, 3, 1);
select ((max(array[row(time_stamp, lives_remaining, usr_id, trans_id)::t]))[1]).*
from t
group by usr_id
Производство снова:
|time_stamp|lives_remaining|usr_id|trans_id|
|----------|---------------|------|--------|
|13:00:00 |3 |3 |1 |
|10:00:00 |1 |2 |4 |
|11:00:00 |4 |1 |5 |
Я думаю, у вас есть одна главная проблема: нет монотонно увеличивающегося "счетчика", чтобы гарантировать, что одна строка произошла позже, чем другая. Возьмите этот пример:
timestamp lives_remaining user_id trans_id
10:00 4 3 5
10:00 5 3 6
10:00 3 3 1
10:00 2 3 2
По этим данным нельзя определить, какая запись является самой последней. Это второй или последний? Не существует функции sort или max(), которую можно применить к любым из этих данных, чтобы дать вам правильный ответ.
Увеличение разрешения метки времени будет огромной помощью. Поскольку ядро базы данных сериализует запросы, при достаточном разрешении вы можете гарантировать, что никакие две метки времени не будут одинаковыми.
В качестве альтернативы используйте trans_id, который не будет переворачиваться очень и очень долго. Наличие trans_id, которое переворачивается, означает, что вы не можете сказать (для той же временной метки), является ли trans_id 6 более поздним, чем trans_id 1, если вы не выполните сложную математику.
Я слишком новичок, чтобы комментировать или голосовать, но ответ Марко ниже был именно тем, что я искал. Сработало отлично. Спасибо, @Marco!
ВЫБРАТЬ DISTINCT ON (usr_id) отметка времени, жизнь_ремонта, usr_id, trans_id ИЗ жизней ORDER BY usr_id, time_stamp DESC, trans_id DESC;