Возможно ли выполнить "ОГРАНИЧЕНИЕ 1" для левого соединения в Postgres?

ProgramBox

Возможно ли выполнить «ОГРАНИЧЕНИЕ 1» для левого соединения в Postgres?

Post author:admin
Запись опубликована:8 июля, 2022
Post category:Вопросы по программированию

postgresql

#postgresql

Вопрос:

У меня есть две таблицы: одна для денег и окружающих ее атрибутов (например, кто их заработал) и дочерняя таблица для «бухгалтерской книги» — она содержит одну или несколько записей, представляющих историю перемещений денег.

 SELECT SUM(pl.achieved)
FROM payout p
LEFT JOIN payout_ledgers pl ON pl.payout_id = p.id

Этот запрос хорошо работает, когда есть только один элемент книги, но при добавлении большего SUM количества будет увеличиваться. Я хочу присоединиться только к последней строке. Итак, гипотетически:

 SELECT SUM(pl.achieved)
FROM payout p
LEFT JOIN payout_ledgers pl ON pl.payout_id = p.id ORDER BY pl.ts DESC LIMIT 1
WHERE ...
ORDER BY ...
LIMIT ...

(что, к сожалению, не работает)

Что я пробовал:

Использование подзапроса работает, но мучительно медленно, учитывая размер набора данных (и другие пропущенные свойства, предложения where и т. Д.):

 SELECT SUM(pl.achieved)
FROM payout p
LEFT JOIN payout_ledgers pl ON pl.payout_id = p.id AND pl.id = (SELECT id FROM payout_ledgers WHERE payout_id = p.id ORDER BY ts DESC LIMIT 1)

Кстати, я не уверен, почему этот подзапрос такой медленный (~ 12 секунд, в отличие от 150 мс без подзапроса). Я бы ожидал, что это будет быстрее, учитывая, что мы выбираем только на основе внешнего ключа ( payout_id ) .

Еще одна вещь, которую я пробовал, это сделать выбор из соединения — моя логика заключается в том, что если мы выбираем из небольшого объединенного набора данных вместо всей таблицы, это будет быстрее. Однако я столкнулся с relation "pl" does not exist ошибкой:

 SELECT SUM(pl.achieved)
FROM payouts p
LEFT JOIN payout_ledgers pl ON pl.payout_id = p.id
WHERE pl.id = (SELECT id FROM pl ORDER BY ts DESC LIMIT 1)

Заранее благодарю вас за любые предложения. Я также открыт для предложений по изменениям схемы, которые могли бы упростить этот тип логики, хотя я бы предпочел попытаться заставить запрос работать, поскольку схему нелегко изменить в нашей производственной среде.

1. Вы пытаетесь преобразовать запрос MySQL 5 в PostgreSQL? В MySQL 8 и PosrgreSQL вы можете использовать LAST_VALUE функцию для возврата последнего значения в наборе, например SUM(LAST_VALUE(pl.achieved) OVER(ORDER BY pl.ts desc)) . Запросы выполняются медленно, потому что внутренний запрос должен выполняться для каждой внешней строки

2. Используйте оконную функцию, например, row_number() BTW: почему вы хотите SUM() использовать один элемент?

3. @PanagiotisKanavos Спасибо за ваше предложение. Это казалось многообещающим, но, к сожалению, я получаю сообщение об ошибке aggregate function calls cannot contain window function calls .

4. @wildplasser Спасибо за ваше предложение. Я знаком с row_number() , но где именно это будет вписываться в запрос? Мне нужно суммировать только один элемент, поскольку дочернее отношение payout_ledgers имеет одну или несколько записей, где только последняя является текущим репрезентативным состоянием денег (так что у нас, по сути, есть история перемещения денег)

Ответ №1:

Если вы используете Postgres 9.4 , вы можете использовать LEFT JOIN LATERAL (docs)

 SELECT SUM(sub.achieved)
FROM payout p
LEFT JOIN LATERAL (SELECT achieved 
     FROM payout_ledgers pl 
     WHERE pl.payout_id = p.id 
     ORDER BY pl.ts DESC LIMIT 1) sub ON true

Это вернет сумму поля «достигнуто» в самой последней записи в payout_ledgers для всех выплат.

1. Спасибо за ваш ответ. Я только что попробовал это сделать, и, к сожалению, это кажется одинаково медленным из-за наличия подзапроса в where . Скорость запроса меняется только тогда, когда я добавляю order by и limit .

2. Медлительность не вызвана подзапросом (возможно, это есть / было в mysql, но не в Postgres) Это вызвано отсутствием индекса на (pl.payout_id, pl.ts)

3. @ollie у вас есть подходящий индекс?

4. Моя ошибка, я считаю, что это связано с индексацией. Итак, для этого подзапроса какой индекс был бы наиболее подходящим? Должен ли я делать какой-то комбинированный индекс?

5. Я согласен с @wildplasser, если этот запрос выполняется очень медленно, это, скорее всего, из-за отсутствия подходящего индекса, попробуйте определить его на (pl.payout_id, pl.ts), postgres обычно довольно хорошо оптимизирует БОКОВЫЕ запросы

Ответ №2:

функции окна:

 -- using row_number()
SELECT SUM(sss.achieved)
FROM (SELECT pl.achieved
        , row_number() OVER (PARTITION BY pl.payout_id, ORDER BY pl.ts DESC)
        FROM payouts p
        JOIN payout_ledgers pl ON pl.payout_id = p.id
        ) sss
WHERE sss.rn =1
        ;
-- using last_value()
SELECT SUM(sss.achieved)
FROM (SELECT 
        , last_value(achieved) OVER (PARTITION BY pl.payout_id, ORDER BY pl.ts ASC) AS achieved
        FROM payouts p
        JOIN payout_ledgers pl ON pl.payout_id = p.id
        ) sss
        ;

КСТАТИ: вам не нужно ЛЕВОЕ СОЕДИНЕНИЕ (добавление никакого значения к СУММЕ не изменяет сумму)

Метки: Можно ли сделать «ОГРАНИЧЕНИЕ 1» для левого соединения в Postgres?