Postgres: left join with order by and limit 1

У меня сложилась ситуация:

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 поля никогда не имеют NULL. Решение найти последний адрес - использовать ORDER BY being DESC, а для удаления старых адресов - LIMIT 1.

Это отлично работает, если запрос может принести только 1 компанию. Но мне нужен запрос, который приносит все записи Table1, объединенные с их текущими адресами Table2. Поэтому удаление устаревших данных должно быть сделано (AFAIK) в разделе LEFT JOIN ON.

Любая идея, как я могу построить предложение, чтобы не создавать дублированные компании Table1 и приносить последний адрес?

Ответ 1

Мне удалось решить эту проблему с помощью функции 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() создает счетчик int для каждой записи, внутри каждого окна на основе fk_company. Для каждого окна запись с последней датой сначала начинается с ранга 1, тогда dt_last_addr = 1 гарантирует, что JOIN будет происходить только один раз для каждого fk_company, с записью с последним адресом.

Функции окна очень мощные, и несколько ppl используют их, они избегают множества сложных объединений и подзапросов!

Ответ 2

Используйте зависимый подзапрос с функцией 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

demo: http://sqlfiddle.com/#!15/f80c6/2