SQL Join on Ближайшее меньше даты
Обычно я просто делал это в самом коде, но мне любопытно, можно ли это эффективно выполнить в TSQL.
Table 1
Date - Value
Table 2
Date - Discount
Таблица 1 содержит записи для каждого дня. Таблица 2 содержит записи только при изменении скидки. Скидка, применяемая к стоимости, считается действительной до тех пор, пока не будет введена новая скидка.
Пример данных:
Table 1
1/26/2010 - 10
1/25/2010 - 9
1/24/2010 - 8
1/24/2010 - 9
1/23/2010 - 7
1/22/2010 - 10
1/21/2010 - 11
Table 2
1/26/2010 - 2
1/23/2010 - 1
1/20/2010 - 0
Мне нужно вернуть следующее: T1 Date - T1 Value - T2 Discount
Пример данных:
1/26/2010 - 10 - 2
1/25/2010 - 9 - 1
1/24/2010 - 8 - 1
1/24/2010 - 9 - 1
1/23/2010 - 7 - 1
1/22/2010 - 10 - 0
1/21/2010 - 11 - 0
Возможно или мне лучше просто продолжать делать это в коде?
Ответы
Ответ 1
Я считаю, что этот подзапрос будет делать это (не проверено).
select *,
(select top 1 Discount
from table2
where table2.Date <= t.Date
order by table2.Date desc) as Discount
from Table1 t
Возможно, не самый результативный.
Edit:
Тестовый код:
create table #table1 ([date] datetime, val int)
create table #table2 ([date] datetime, discount int)
insert into #table1 ([date], val) values ('1/26/2010', 10)
insert into #table1 ([date], val) values ('1/25/2010', 9)
insert into #table1 ([date], val) values ('1/24/2010', 8)
insert into #table1 ([date], val) values ('1/24/2010', 9)
insert into #table1 ([date], val) values ('1/23/2010', 7)
insert into #table1 ([date], val) values ('1/22/2010', 10)
insert into #table1 ([date], val) values ('1/21/2010', 11)
insert into #table2 ([date], discount) values ('1/26/2010', 2)
insert into #table2 ([date], discount) values ('1/23/2010', 1)
insert into #table2 ([date], discount) values ('1/20/2010', 0)
select *,
(select top 1 discount
from #table2
where #table2.[date] <= t.[date]
order by #table2.[date] desc) as discount
from #table1 t
drop table #table1
drop table #table2
Результаты:
2010-01-26 00:00:00.000 10 2
2010-01-25 00:00:00.000 9 1
2010-01-24 00:00:00.000 8 1
2010-01-24 00:00:00.000 9 1
2010-01-23 00:00:00.000 7 1
2010-01-22 00:00:00.000 10 0
2010-01-21 00:00:00.000 11 0
Ответ 2
Нет "ближайшего" запроса будет таким же эффективным, как запрос "равно", но это другое задание для надежного ROW_NUMBER
:
;WITH Discounts_CTE AS
(
SELECT
t1.[Date], t1.[Value], t2.Discount,
ROW_NUMBER() OVER
(
PARTITION BY t1.[Date]
ORDER BY t2.[Date] DESC
) AS RowNum
FROM Table1 t1
INNER JOIN Table2 t2
ON t2.[Date] <= t1.[Date]
)
SELECT *
FROM Discounts_CTE
WHERE RowNum = 1
Ответ 3
В ответ на добавление в Joels... если в обеих таблицах присутствуют идентификаторы, то это повысит производительность:
select *,
(select top 1 Discount
from Table2 t2
where t2.Date <= t1.Date
and t2.ID = t1.ID
order by t2.Date desc) as Discount
from Table1 t1
Ответ 4
Это работает на oracle XE. Поскольку сервер sql имеет аналитические функции, его не должно быть сложно переносить.
create table one (
day date,
value integer
);
create table two (
day date,
discount integer
);
insert into one values (trunc(sysdate), 10);
insert into one values (trunc(sysdate-1), 8);
insert into one values (trunc(sysdate-2), 1);
insert into one values (trunc(sysdate-3), 23);
insert into one values (trunc(sysdate-4), 3);
insert into one values (trunc(sysdate-5), 4);
insert into one values (trunc(sysdate-6), 8);
insert into one values (trunc(sysdate-7), 5);
insert into one values (trunc(sysdate-8),8);
insert into one values (trunc(sysdate-9), 8);
insert into one values (trunc(sysdate-10), 5);
insert into two values (trunc(sysdate), 2);
insert into two values (trunc(sysdate-3), 1);
insert into two values (trunc(sysdate-5), 3);
insert into two values (trunc(sysdate-8), 1);
select day, value, discount, cnt,
nvl(max(discount) over (partition by cnt)
,0) as calc_discount
from (
select day, value, discount,
count(discount) over (order by day) as cnt
from one
left outer join two
using(day)
)