Предположим у нас есть таблица, в которой хранятся данные о лицевых счетах. В таблице есть идентификатор счета, лицевой номер. Также счета сгруппированы в группы и каждый счет имеет несколько атрибутов, например, остаток и некий коэффициент. Рассмотрим такой пример:
Вставим несколько строк:
Теперь сформулируем задачу. Для каждой группы счетов требуется вычислить какую-то ее числовую характеристику, которая определяется столбцами 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 должен быть нулем для применения формулы первый раз для очередной группы:
Теперь сделаем такой update:
Видно что в ходе update происходит не только обновление поля fChar, но циклическое вычисление для переменной @fChar, которая затем используется для обновления fChar следующей строки. При этом на таблице упорядоченность по кластерному индексу должна соответствовать тому порядку, в котором происходит вычисление. Столбец iMinAccId используется для того, чтобы сбросить @fChar в ноль при переходе к начальному iAccountId для новой группы. Теперь для получения атрибутов групп достаточно выполнить такой запрос:
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
Комментариев нет:
Отправить комментарий