Присоединение к самой последней записи на основе даты в другой записи
У меня есть две таблицы — назовем их week и contract, вот так:
Week Contract
emp_id | starting | data1 | ... emp_id | from_date | data2 | ...
-------|------------|-------|-- -------|------------|-------|--
12 | 2019-01-08 | abcd | 12 | 2018-08-01 | efgh |
12 | 2019-01-15 | abcd | 13 | 2018-10-02 | efgh |
12 | 2019-01-22 | abcd | 13 | 2019-01-15 | ijkl |
13 | 2019-01-08 | abcd | 13 | 2019-03-19 | mnop |
13 | 2019-01-15 | abcd | 14 | 2017-02-02 | efgh |
13 | 2019-01-22 | abcd | 15 | 2018-01-19 | efgh |
Поле week.starting представляет собой дату (дата-время со временем, установленным на полночь) с довольно регулярными интервалами. Конкретная комбинация (emp_id, start) уникальна. Поле from_date также является датой, в которой записан начальный период, к которому применяется запись contract. Это может быть в будущем, поэтому мы не можем просто сделать MAX(from) и получить правильный контракт для каждого сотрудника. В настоящее время (emp_id, from_date) уникален, но я не хочу на это полагаться. week.starting и contract.from_date могут совпадать.
Мне нужен запрос, который возвращает всю запись week и для каждой записи для того, что contract было активным в то время, то есть запись, в которой from_date является наибольшим, но все же меньше или равно week.starting. Получение этого контракта, если я имею в виду конкретную неделю, является довольно простой проблемой для n на группу:
SELECT * FROM contract
WHERE contract.emp_id = @emp_id AND contract.from_date <= @starting
ORDER BY contract.from_date DESC
LIMIT 1
Но я не могу понять, как это сделать как часть запроса, чтобы получить каждую запись в week. Моя конкретная комбинация препятствий означает, что я не смог найти ответ, несмотря на то, что это общий набор проблем. Кажется, я не могу передать week.starting в подзапрос, и я также не могу использовать LIMIT в соединении. Моя лучшая попытка до сих пор заканчивалась тем, что я присоединился ко всем контрактам, которые были меньше данной недели.
Какой запрос вернет результат, который я ищу?
emp_id | starting | from_date | data1 | data2 | ...
-------|------------|------------|-------|-------|--
12 | 2019-01-08 | 2018-08-01 | abcd | efgh |
12 | 2019-01-15 | 2018-08-01 | abcd | efgh |
12 | 2019-01-22 | 2018-08-01 | abcd | efgh |
13 | 2019-01-08 | 2018-10-02 | abcd | efgh |
13 | 2019-01-15 | 2019-01-15 | abcd | ijkl |
13 | 2019-01-22 | 2019-01-15 | abcd | ijkl |
извините, я сделал опечатку в таблице примеров. Некоторые даты должны были быть 2019, а не 2018.
Ответы 2
Вы должны иметь возможность использовать оконную функцию для упорядочения контрактов во времени после фильтрации будущих контрактов и присвоения ранга. Затем вы можете выбрать последнюю с высшим рангом.
Не проверял это, но должно выглядеть примерно так:
select * from (
select w.*, c.from_date, c.data2,
row_number() over (partition by c.emp_id, w.starting order by c.from_date desc) as latest
from week w
join contract c on c.emp_id = w.emp_id and c.from_date <= w.starting
) as sub where latest = 1
Вы можете использовать first_value(data2) вместо row_number(), чтобы исключить необходимость в предложении внешнего выбора, см. официальная документация.
First_value применяет этот результат к каждой строке в рамке окна. Тогда вам все равно придется проверить, где first_value=from_date. Он не делает фильтрацию за вас. Это также может привести к обману, поскольку может совпадать более одного раза.
Ты прав. Если вы используете partition by c.emp_id, w.starting, вы получаете точный результат, который ожидал OP (контракт сотрудника на каждую неделю).
Хотел отметить, что в некоторых случаях может быть желательным возврат всех совпадающих строк с использованием first_value.
Я только что попробовал это в нашей базе данных разработчиков, и она прекрасно работает.
В Postgres вы можете использовать боковое соединение:
select w.*, c.*
from weeks w left join lateral
(select c.*
from contract c
where c.emp_id = w.emp_id and
c.from_date <= w.starting
order by c.from_date desc
fetch first 1 row only
) c;
На каком основании вы указываете условие, например: for emp_id = 13 13 --> 2019-01-08 ---> 2018-10-02 13 --> 2019-01-15 --> 2019-01-15 13 --> 22.01.2019 --> 15.01.2019