Нарастающий итог - обратная задача

  • Article

В постах "Нарастающий итог в Денали", "Нарастающий итог - сравнение производительности" мы посмотрели, какие удобные возможности появились в SQL Server 11 CTP3 для вычисления скользящих агрегатов. Здесь мы продолжим эту тему и разберем решение обратной задачи - как из колонки, где ведется учет нарастающим итогом получить дельту прироста между двумя соседними записями. Пусть имеем таблицу

use tempdb

 

if OBJECT_ID('#t', 'U') is not null drop table #t

 

create table #t (id int identity primary key, grouping_id int, dt date, x int)

insert #t (grouping_id, dt, x) values (1, '2011-01-14', 5), (1, '2011-03-06', 8), (1, '2011-05-20', 15),

                                                      (2, '2011-01-18', 2), (2, '2011-02-27', 6), (2, '2011-04-02', 9), (2, '2011-05-07', 10),

                                                      (3, '2011-02-09', 3), (3, '2011-04-19', 9),

                                                      (4, '2011-01-06', 2), (4, '2011-01-08', 5), (4, '2011-02-10', 7), (4, '2011-03-21', 12), (4, '2011-05-12', 21),

                                                      (5, '2011-03-17', 8)

 

select * from #t

 

image

Рис.1

 

Колонка x прирастает в пределах каждой группы grouping_id, так что в каждый в момент времени dt мы видим ее нарастающее значение. Чтобы узнать, сколько составила дельта прироста в момент времени dt, надо, очевидно, получить рядом значение за предыдущий момент dt и из значения за текущий момент вычесть значение за предыдущий. До Денали СТР3 это можно было сделать джойном таблицы самой на себя подобно Скрипту 6 в сумме нарастающим итогом:

 

with cte (id1, gr1, dt1, x1, id2, gr2, dt2, x2, n) as

(select *, ROW_NUMBER() over (partition by t1.id order by t2.dt desc) from #t t1 left join #t t2 on t1.grouping_id = t2.grouping_id and t1.dt > t2.dt)

select * from cte where n = 1 order by id1

Скрипт 1

 

Замечательная функция LAG(x, n) позволяет непосредственно получить значение из колонки х, отступив n записей назад:

 

select *, lag(x, 1) over (partition by grouping_id order by dt) from #t

 

image

Рис.2

 

NULL означает, что более ранних записей внутри этой группы нет. Для удобства дальнейших вычислений хотелось бы в этом случае получить не NULL, а 0. Третий аргумент функции LAG позволяет задать значение по умолчанию, если отступ назад принес NULL:

 

select *, lag(x, 1, 0) over (partition by grouping_id order by dt) from #t

 

image

Рис.3

 

Окончательно имеем:

 

select *, (x - lag(x, 1, 0) over (partition by grouping_id order by dt)) as delta from #t

 

image

Рис.4

 

Рассмотрим чуть более хитрую ситуацию, когда нарастающий итог учитывается с запозданием, т.е. дельта за текущую запись идет в зачет нарастающего итога не в ней, а в следующей записи. Например,

 

if OBJECT_ID('#t', 'U') is not null drop table #t

 

create table #t (id int identity primary key, grouping_id int, dt date, x int)

insert #t (grouping_id, dt, x) values (1, '2011-01-14', 0), (1, '2011-03-06', 5), (1, '2011-05-20', 8),

                                                      (2, '2011-01-18', 0), (2, '2011-02-27', 2), (2, '2011-04-02', 6), (2, '2011-05-07', 9),

                                                      (3, '2011-02-09', 0), (3, '2011-04-19', 3),

                                                      (4, '2011-01-06', 0), (4, '2011-01-08', 2), (4, '2011-02-10', 4), (4, '2011-03-21', 7), (4, '2011-05-12', 12),

                                                      (5, '2011-03-17', 0)

select * from #t

 

image

Рис.5

 

Понятно, что дельту за последнюю запись в группе мы в этом случае не узнаем, но как ее посчитать для остальных? Надо взять х не из предыдущей, а из следующей записи, т.е. lag(-1). Функция LAG не воспринимает отрицательное смещение:

 

select *, lag(x, -1, 0) over (partition by grouping_id order by dt) from #t

 

Msg 8730, Level 16, State 1, Line 1

Offset parameter for Lag and Lead functions cannot be a negative value.

 

Этим она невыгодно отличается от MDX. Чтобы продвинуться вперед, следует использовать функцию LEAD:

 

select *, lead(x, 1) over (partition by grouping_id order by dt) from #t

 

image

Рис.6

 

Соответственно, прирост в этом случае получается как

 

with cte as (select *, lead(x, 1) over (partition by grouping_id order by dt) next_x from #t)

select *, delta = next_x - x from cte

 

image

Рис.7

 

Заодно давайте посмотрим родственные функции first_value() и last_value(). Как нетрудно догадаться из названий, они возвращают первое и последнее значение в группе.

 

select *, first_value(x) over (partition by grouping_id order by dt), last_value(x) over (partition by grouping_id order by dt) from #t

 

image

Рис.8

 

Но что это? Функция last_value возвращает не последнее значение х в группе, а просто берет его из текущей записи. Неужели ее не доделали в СТР3, ужаснулся я и в отчаянии поинтересовался у старших товарищей. Спасибо, Боб Бушмен, дай Бог ему здоровья, разъяснил, что когда внутри over() стоит order by, то по умолчанию диапазон действия функции - не целиком группа, а от ее начала и по текущую запись. См. OVER Clause в BOL, ROW or RANGE clause. Например, подсчет суммы нарастающим итогом по таблице Рис.4:

 

use tempdb

 

if OBJECT_ID('#t', 'U') is not null drop table #t

 

create table #t (id int identity primary key, grouping_id int, dt date, delta int)

insert #t (grouping_id, dt, delta) values (1, '2011-01-14', 5), (1, '2011-03-06', 3), (1, '2011-05-20', 7),

                                                      (2, '2011-01-18', 2), (2, '2011-02-27', 4), (2, '2011-04-02', 3), (2, '2011-05-07', 1),

                                                      (3, '2011-02-09', 3), (3, '2011-04-19', 6),

                                                      (4, '2011-01-06', 2), (4, '2011-01-08', 3), (4, '2011-02-10', 2), (4, '2011-03-21', 5), (4, '2011-05-12', 9),

                                                      (5, '2011-03-17', 8)

Скрипт 2

 

можно осуществить как

 

select *, sum(delta) over (partition by grouping_id order by dt) as x from #t

Скрипт 3

 

и мы получим таблицу с колонкой х нарастающим итогом - см. Рис.1, с которой начался данный пост. Этот запрос эквивалентен

 

select *, sum(delta) over (partition by grouping_id order by dt rowsbetweenunboundedprecedingandcurrentrow) as x from #t

Скрипт 4

 

как если бы мы явно указали, что сумма считается от начала группы по текущую запись. Чтобы получить таблицу Рис.5, где нарастающий итог считается от начала группы не по, а до текущей записи, не включая ее, надо изменить запрос как

 

select *, sum(delta) over (partition by grouping_id order by dt rows between unbounded preceding and 1 preceding) as x from #t

Скрипт 5

 

Чтобы посчитать скользящее среднее в окне, состоящем, например, из трех записей, предыдущей, текущей и следующей, надо написать запрос

select *, avg(delta * 1.0) over (partition by grouping_id order by dt rows between 1 precedingand 1 following) as x from #t

 

image

Рис.9

Чтобы получить сумму по всей группе, надо указать весь диапазон действия группы - rows between unbounded preceding and unbounded following или не указывать order by, т.к. порядок при подсчете суммы по всей группе неважен. Последнее эквивалентно поведению в SQL Server 2008.

 

select *, sum(delta) over (partition by grouping_id order by dt rows between unbounded preceding and unbounded following), sum(delta) over (partition by grouping_id) from #t

 

image

Рис.10

 

Возвращаясь к функции last_value(). Порядок здесь, очевидно, важен, поэтому надо модифицировать запрос Рис.8 так:

 

select *, first_value(x) over (partition by grouping_id order by dt) first, last_value(x) over (partition by grouping_id order by dt rowsbetweenunboundedprecedingandunboundedfollowing) last from #t

 

image

Рис.11

 

 

Алексей Шуленин