Регистрация на основе максимальной даты <= данной даты

Question

У меня есть две таблицы, которые выглядят примерно так:

Таблица 1:

Unit      Date
A         9/30/17
B         9/30/17

Таблица 2:

Unit     Date      Metric
A        9/30/17   100
A        6/30/17   250
A        5/31/17   150
B        8/15/17   200
B        4/30/17   150
B        4/15/17   100

Для каждой комбинации Единицы и Даты из Таблицы 1 я хочу получить соответствующее значение Метрики из Таблицы 2. Если в Таблице 2 нет совпадений, я хочу отозвать значение Метрики для следующей ближайшей даты, которая < дата из таблицы 1. Используя приведенные выше примеры, я ожидаю получить 100 для блока A и 200 для блока B.

Как я могу получить это в форме запроса? Мне было трудно оборачиваться вокруг него. Было бы легко, если бы я имел дело только с датами, но компонент Unit - это то, что меня отталкивает.

Answer 1

Получить точное совпадение относительно просто, используя левое соединение

SELECT t1.Unit, t2.Metric
FROM Table1 t1 
LEFT JOIN Table2 t2 ON t2.Unit = t1.Unit AND t2.Date = t1.Date

С этим запросом t2.Metric будет NULL, если нет точного соответствия. Получить метрику без точного соответствия сложнее. В MS SQL Server вы могли бы написать пользовательскую функцию (UDF), чтобы вернуть ближайшее значение.

create function dbo.udfGetMetric(@Unit char(1), @Date datetime) returns int
as 
BEGIN
declare @Metric smallint;
SELECT TOP(1) @Metric = Metric
FROM Table2 
WHERE Unit = @Unit
    and [Date] < @Date
ORDER BY [Date] DESC;

RETURN @Metric;
END
GO

Затем вы можете вызвать эту функцию в своем запросе, когда значение равно NULL.

SELECT t1.Unit, ISNULL(t2.Metric, dbo.udfGetMetric(t1.Unit, t1.[Date])) as Metric
FROM Table1 t1 
LEFT JOIN Table2 t2 ON t2.Unit = t1.Unit AND t2.[Date] = t1.[Date];

Вы можете сделать то же самое, используя подзапрос вместо функции:

SELECT t1.Unit
, ISNULL(t2.Metric, (SELECT TOP(1) Metric
                    FROM Table2 
                    WHERE Unit = t1.Unit
                        and [Date] < t1.[Date]
                    ORDER BY [Date] DESC)) as Metric
FROM Table1 t1 
LEFT JOIN Table2 t2 ON t2.Unit = t1.Unit AND t2.[Date] = t1.[Date];