Postgres：使用order by和limit 1进行左连接

Question

我有以下情况:

Table1 has a list of companies.
Table2 has a list of addresses.
Table3 is a N relationship of Table1 and Table2, with fields 'begin' and 'end'.

由于公司可能随时间迁移，它们之间的LEFT JOIN结果会为每个公司产生多条记录。

begin和end字段永远不会为空。找到最新地址的解决方案是使用ORDER BY being DESC，并使用LIMIT 1删除旧地址。

如果查询只能带来1个公司，那就很好用。但我需要一个查询，将所有Table1记录与它们当前的Table2地址连接起来。因此，过时数据的删除必须在LEFT JOIN的ON子句中完成（据我所知）。

有什么办法可以构建这个子句，以避免创建重复的Table1公司并带来最新的地址？

- Hikari

Answer 1

在连接条件中使用带有max()函数的依赖子查询。
就像这个例子中的：

SELECT *
FROM companies c
LEFT JOIN relationship r
ON c.company_id = r.company_id
   AND r."begin" = (
        SELECT max("begin")
        FROM relationship r1
        WHERE c.company_id = r1.company_id
     )
INNER JOIN addresses a
ON a.address_id = r.address_id 

演示：http://sqlfiddle.com/#!15/f80c6/2

- krokodilko

---

在sqlfiddle上很不错 - 我之前只见过jsfiddle，但sqlfiddle看起来非常有帮助！ - dwanderson

似乎当 r.begin 可能在一些记录中相等时，它不起作用。 - Joe Half Face

FYI - 你不必使用最大值，你可以在子查询中使用任何排序方式。这是一个很好的解决方案，谢谢。 - hobberwickey

Answer 2

自 PostgreSQL 9.3 版本以来，引入了 JOIN LATERAL（https://www.postgresql.org/docs/9.4/queries-table-expressions.html），它允许使用子查询进行联接，因此以一种优雅的方式解决了您的问题：

SELECT * FROM companies c
JOIN LATERAL (
    SELECT * FROM relationship r
    WHERE c.company_id = r.company_id
    ORDER BY r."begin" DESC LIMIT 1
) r ON TRUE
JOIN addresses a ON a.address_id = r.address_id

这种方法的缺点是 LATERAL 内部表的索引在外部不起作用。

- Fomalhaut

---

你能详细说明一下你的评论“这种方法的缺点是LATERAL内部表的索引在外部无法使用”吗？在你的例子中，如果r.company_id上有一个索引，这是否意味着该索引会在子查询中使用，但如果在外部查询的WHERE子句中引用，则不会使用？ - Eric Dauenhauer

@EricDauenhauer 是的，它确实如此。 - Fomalhaut

Answer 3

我使用Windows函数成功解决了它：

WITH ranked_relationship AS(
    SELECT
        *
        ,row_number() OVER (PARTITION BY fk_company ORDER BY dt_start DESC) as dt_last_addr
    FROM relationship
)

SELECT
    company.*
    address.*,
    dt_last_addr as dt_relationship
FROM
    company
    LEFT JOIN ranked_relationship as relationship
            ON relationship.fk_company = company.pk_company AND dt_last_addr = 1
    LEFT JOIN address ON address.pk_address = relationship.fk_address

row_number()函数会针对每个依据fk_company的窗口创建一个整型计数器。对于每个窗口，最新日期的记录排在第一位，排名为1，然后dt_last_addr = 1 确保只有具有最新地址的记录才会参与JOIN操作，并且每个fk_company只会进行一次JOIN操作。

窗口函数非常强大，但很少有人使用它们，可以避免许多复杂的联接和子查询！

- Hikari