Цикл в атомарной операции

Предположим у нас есть таблица, в которой хранятся данные о лицевых счетах. В таблице есть идентификатор счета, лицевой номер. Также счета сгруппированы в группы и каждый счет имеет несколько атрибутов, например, остаток и некий коэффициент. Рассмотрим такой пример:

create table dbo.Accounts

(

       iAccountId   int identity ( 1, 1 ) not null,

       iGroupId     int                   not null,

       vcAccount    char ( 20 )           not null,

       fRest        float                 not null,

       fCoef        float                 not null,

       constraint PK_Accounts_iGroupId_iAccountId primary key clustered ( iGroupId asc, iAccountId asc ) on [PRIMARY]

) on [PRIMARY]

Вставим несколько строк:

insert into dbo.Accounts ( iGroupId, vcAccount, fRest, fCoef )

       values

             ( 1, '20202810896983470198', 15, 1.4 ),

             ( 1, '20202810736329201930', 14, 1.5 ),

             ( 1, '20202810176070804078', 56, 1.9 ),

             ( 1, '20202810210923822168', 23, 2.4 ),

             ( 2, '40702840636152513621', 11, 0.5 ),

             ( 2, '40702840774312017863', 7, 0.9 ),

             ( 2, '40702840704759073191', 87, 2.8 )

Теперь сформулируем задачу. Для каждой группы счетов требуется вычислить какую-то ее числовую характеристику, которая определяется столбцами fRest и fCoef счетов этой группы и вычисляется по определенной формуле. Для примера рассмотрим такую реккурентную формулу, записанную псевдокодом: fChar_Cur = ( fChar_Prev + fRest_Cur * fCoef_Cur ) / ( 1 + fCoef_Cur ).
То есть характеристика группы рассчитывается по этой формуле поочереди для каждого счета. Счета берутся по порядку (по полю iAccountId). Имея определенный iAccountId мы берем его fRest и fCoef и подставляем их в формулу вместе со значением характеристики, вычисленной для предыдущего iAccountId в данной группе. Когда мы работаем с самым первым iAccountId данной группы, то в качестве fChar_Prev берем ноль.

По всему видно, что операция циклическая и посчитать fChar для каждой группы можно было бы с помощью курсора на таблице. Можно ли придумать алгоритм решения задачи без использования циклов, а только с помощью операций над множествами? Если бы в формуле не было умножения и деления, то все сводилось бы к расчету нарастающего итога и сумма для последнего iAccountId давала бы характеристику группы. Нарастающий итог можно посчитать одним запросом, используя оконные функции, как указано в одной из моих предыдущих работ: Новые аналитические функции MS SQL 2012.

Если бы у нас в формуле не было знаменателя, то fChar для каждой группы равнялся бы произведению fRest для первого iAccountId в группе на произведения величин ( 1 + fCoef ) по всем fCoef от первого до текущего iAccountId группы. В этом случае можно было бы создать агрегирующую функцию на базе clr-сборки, которая вычисляет произведение ( 1 + fCoef ) по всем элементам группы. Однако наличие знаменателя все портит: общая формула становится сложнее и что самое критичное значение начинает зависеть от порядка, в котором берутся iAccountId. Поэтому агрегирующие функции на базе CLR не помогут.

Ниже я приведу решение, которое позволяет избежать применения курсоров и решить задачу сделав один update и один select. Для начала надо добавить к таблице столбец fChar, в котором будет храниться атрибут группы. Его значение будет искомым для максимального iAccountId в пределах группы:
alter table dbo.Accounts add fChar float not null default ( 0 )

Также необходимо добавить столбец в минимальным iAccountId для данной группы. Он необходим для контроля над тем когда в fChar должен быть нулем для применения формулы первый раз для очередной группы:

alter table dbo.Accounts add iMinAccId int null

go

update acc

       set acc.iMinAccId = minacc.iAccountId

       from

             dbo.Accounts acc

                    inner join

             (

                    select iGroupId, min ( iAccountId ) iAccountId

                    from dbo.Accounts

                    group by iGroupId

             ) minacc on acc.iGroupId = minacc.iGroupId

Теперь сделаем такой update:

declare @fChar float = 0

update dbo.Accounts

       set @fChar = fChar = (

           case when iAccountId = iMinAccId then 0 else @fChar end +

           fRest * fCoef ) / ( 1 + fCoef )

Видно что в ходе update происходит не только обновление поля fChar, но циклическое вычисление для переменной @fChar, которая затем используется для обновления fChar следующей строки. При этом на таблице упорядоченность по кластерному индексу должна соответствовать тому порядку, в котором происходит вычисление. Столбец iMinAccId используется для того, чтобы сбросить @fChar в ноль при переходе к начальному iAccountId для новой группы. Теперь для получения атрибутов групп достаточно выполнить такой запрос:

select acc.iGroupId, acc.fChar

from

       dbo.Accounts acc

             inner join

       (

             select iGroupId, max ( iAccountId ) iAccountId

             from dbo.Accounts

             group by iGroupId

       ) maxacc on

             acc.iGroupId = maxacc.iGroupId and

             acc.iAccountId = maxacc.iAccountId