Is it possible to do a "LIMIT 1" on a left join in Postgres?-CodePudding

I have two tables: one for money and attributes surrounding it (e.g. who earnt it) and a child table for the "ledger" - this contains one or more entries that represent the history of money that has moved.

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

This query works well when there is only one ledger item, but when more are added the SUM will increase. I want to join only the latest row. So hypothetically:

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 ...

(which sadly doesn't work)

What I have tried:

Using a subquery works, but is painfully slow given the size of the data set (and other omitted properties and where clauses etc.):

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)

Incidentally, I'm unsure why this subquery is so slow (~12 seconds, as opposed to 150ms with no subquery). I would have expected it to be quicker given that we're only selecting based on the foreign key (payout_id).

Another thing I tried was to do a select from the join - my logic being that if we select from small joined dataset instead of the whole table it would be quicker. However I was met with relation "pl" does not exist error:

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)

Thank you in advance for any suggestions. I am also open to suggestions for schema changes that could make this type of logic easier, although my preference would be to try and get the query working since the schema is not easy to change on our production environment.

CodePudding user response：

If you're on Postgres 9.4 , you can use a 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

This will return the sum of the "achieved" field in the most recent entry in payout_ledgers for all payouts.

CodePudding user response：

window functions:

-- 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
        ;

BTW: you do not need the LEFT JOIN (adding no value to the SUM does not change the sum)