Новые аналитические функции MS SQL 2012

С выходом новой версии СУБД Microsoft SQL Server 2012 разработчикам доступны новые аналитические функции, которые позволяют разработчикам писать более эффективные и быстрые запросы, используя гораздо меньший объем кода. Всем известна классическая задача, когда для таблицы требуется написать запрос, который возвращает в текущей строке 2 столбца, первый из которых соответствует столбцу текущей строки, а второй соответствует тому же столбцу предыдущей строки. Пусть нам надо хранить цену за определенный товар на каждый день. Построим таблицу, в которой будет столбец с датой, идентификатором товара и ценой этого товара на день:

create table #goods

(

       dtDate date   not null,

       iGoodId int   not null,

       fPrice float  not null,

       primary key clustered ( iGoodId asc, dtDate asc ) on [PRIMARY]

) on [PRIMARY]

insert into #goods ( dtDate, iGoodId, fPrice )

       values

             ( '2014-01-01', 1, 23 ),

             ( '2014-01-02', 1, 24 ),

             ( '2014-01-03', 1, 25 ),

             ( '2014-01-01', 2, 13 ),

             ( '2014-01-02', 2, 14 ),

             ( '2014-01-03', 2, 15 )


Требуется написать запрос, который для каждой строки таблицы покажет цену на данный товар за предыдущий день, который есть в таблице. Обычно для решения этой задачи использовался джойн таблицы на себя с условием равенства на iGoodId, а также выполнялся поиск максимальной даты из правой таблицы, которая меньше даты из левой таблицы. Либо делалось то же самое, но через связанный подзапрос. Теперь можно просто воспользоваться функцией lag, и сделать задачу одним запросом без всяких джойнов:

select dtDate, iGoodId, fPrice CurPrice,

       isnull ( lag ( fPrice ) over ( partition by iGoodId order by iGoodId asc, dtDate asc ), -1 ) PrevPrice

from #goods

Функция lag может, например, использоваться для поиска тех дней, когда происходили изменения в ценах на товар. Для этого можно написать такой запрос:

;

with Data

as

(

       select dtDate, iGoodId, fPrice CurPrice, isnull ( lag ( fPrice ) over ( partition by iGoodId order by iGoodId asc, dtDate asc ), -1 ) PrevPrice

       from #goods

)

select dtDate, iGoodId, CurPrice

from Data

where CurPrice <> PrevPrice

Если требуется получить цену не за предыдущий, а за следующий день, то используется функция lead:

select dtDate, iGoodId, fPrice CurPrice,

       isnull ( lead ( fPrice ) over ( partition by iGoodId order by iGoodId asc, dtDate asc ), -1 ) NextPrice

from #goods


Добавим теперь к таблице новый столбец с объемом продаж товара за дату:

alter table #goods add mSells money not null default ( 100 )

Теперь посчитаем нарастающий итог. То есть для каждого товара и даты посчитаем сумму продаж с начальной даты в заданной партиции до текущей даты. Это опять-таки можно сделать, используя один запрос, без всяких джойнов. Используется расширение предложения over в агрегирующей функции:

select dtDate, iGoodId, sum ( mSells ) over ( partition by iGoodId order by iGoodId asc, dtDate asc rows between unbounded preceding and current row )

from #goods


Еще можно для каждой строки указать цены за самую первую и за самую последнюю даты, с
которой и до которой торгуется данный товар:

select dtDate, iGoodId, fPrice CurPrice,

       first_value ( fPrice ) over ( partition by iGoodId order by iGoodId asc, dtDate asc ) FirstPrice,

       last_value ( fPrice ) over ( partition by iGoodId order by iGoodId asc, dtDate asc ) LastPrice

from #goods