是否可以在Postgres的左连接上进行"LIMIT 1"？

Question

我有两个表:一个是钱和围绕它的属性(例如谁赚的)，一个是"分类帐"的子表;-这包含一个或多个条目，代表资金流动的历史。

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秒，而不是150ms没有子查询)。我本来希望它更快，因为我们只根据外键(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)

提前感谢您的任何建议。我也愿意听取可以使这种类型的逻辑更容易的模式更改的建议，尽管我更倾向于尝试并使查询工作，因为在我们的生产环境中模式不容易更改。

Answer 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_ledger中最近的条目中显示所有支出。

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

---

顺便说一句:你不需要LEFTJOIN(不向SUM添加任何值不会改变SUM)