SQL Server - создайте представление, извлекающее самое последнее значение до указанной даты

Question

Предположим, у меня есть следующая таблица в SQL Server (2012):

MyTable:
Date1:     Col1:    Val:
1/1/2016   c1       Val1
1/2/2016   c1       Val2
1/3/2016   c2       Val3
1/4/2016   c2       Val4
1/5/2016   c2       Val5
1/6/2016   c3       Val6
1/7/2016   c3       Val7
1/8/2016   c3       Val8

И я хотел бы создать представление, которое для каждого значения в Col1 возвращает, для данной даты, последнее значение в Val

Так что мой взгляд будет следующим:

MyView:
Date1:     Col1:    Val:
1/2/2016   c1       Val2
1/5/2016   c2       Val5
1/8/2016   c3       Val8

И, более конкретно, я мог бы запросить мое мнение, например:

SELECT * FROM MyView WHERE Date1 < '1/6/2016'

Result:
Date1:     Col1:    Val:
1/2/2016   c1       Val2
1/5/2016   c2       Val5
1/6/2016   c3       Val6

---

SELECT * FROM MyView WHERE Date1 <= '1/4/2016'

Result:
Date1:     Col1:    Val:
1/2/2016   c1       Val2
1/4/2016   c2       Val4

---

Я могу написать вложенный запрос / cte, чтобы сделать это следующим образом:

SELECT
    Date1
    , Col1
    , Val
FROM (
        SELECT
            Date1
            , Col1
            , Val
            , ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col1, Date1 DESC) as version_num
        FROM 
            MyTable
        WHERE 
            Date1 <= '1/6/2016' 
      ) orderedtable
WHERE 
    version_num = 1

Но я понятия не имею, как преобразовать этот точный запрос в представление, в котором результаты изменяются в зависимости от запрашиваемой даты или просто возвращают последние / последние значения, если в запросе представления не указана дата.

Я искал LAST_VALUE (), но я не нахожу, что работать тоже.

Есть идеи? Можно ли это сделать?

Answer 1

Похоже, вы ищете параметризованный вид. В SQL Server вы не можете передать переменную для просмотра, но вы можете использовать табличную функцию:

CREATE FUNCTION dbo.my_func(@d DATE)
RETURNS TABLE
AS
RETURN(
SELECT  Date1
       ,Col1
       ,Val
FROM (SELECT 
        Date1
        ,Col1
        ,Val
        ,ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Date1 DESC) as version_num
       FROM MyTable
       WHERE Date1 <= @d 
      ) orderedtable
WHERE version_num = 1)
GO


SELECT *
FROM dbo.my_func('1/6/2016')  -- it behaves as normal table
-- JOIN/WHERE ...

LiveDemo

Выход:

╔═════════════════════╦══════╦══════╗
║        Date1        ║ Col1 ║ Val  ║
╠═════════════════════╬══════╬══════╣
║ 02.01.2016 00:00:00 ║ c1   ║ Val2 ║
║ 05.01.2016 00:00:00 ║ c2   ║ Val5 ║
║ 06.01.2016 00:00:00 ║ c3   ║ Val6 ║
╚═════════════════════╩══════╩══════╝

РЕДАКТИРОВАТЬ:

Если вы хотите, чтобы все значения пользователь мог передать NULL:

SELECT *
FROM dbo.my_func(NULL);

Chnage: WHERE Date1 <= @d в WHERE Date1 <=ISNULL(@d, '2099-01-01T00:00:00')

LiveDemo2

или использовать DEFAULT:

CREATE FUNCTION dbo.my_func(@d DATE = '2099-01-01T00:00:00')
...

SELECT *
FROM dbo.my_func(default);

LiveDemo3