Недавно столкнулся с такой интересной задачей, о которой не могу не написать. Пусть через базу были оформлены заявки на займы определенных сумм. Эти суммы должны периодически погашаться. Причем сначала идут гашения для более ранних заявок. А потом для более поздних (в соответствии с номерами заявок). Итак у нас есть талица заявок:
То есть сначала заняли 100 через первую заявку, затем 200 через вторую и т. д. В определенные дни заемщик вносит деньги для погашения. Сначала они идут на погашение первой заявки. Если периодически вносить деньги для погашения, то в какой-то момент их общая сумма станет больше либо равна чем сумма в первой заявке. Остаток и последующие гашения идут уже на погашение второй заявки. Создадим таблицу с погашениями:
По тем данным, что были загружены в новые таблицы видно, что 01.01.2014 было гашение на сумму 110. Эта величина больше чем 100 - сумма первой заявки. Значит на 01.01.2014 погашена первая заявка, при этом есть остаток: 110 - 100 = 10, который идет на следующее погашение второй заявки. Для даты 02.01.2014 имеем погашение на сумму 20, с остатком 10, то есть погашение реально на 30. Сумма 30 меньше чем сумма второй заявки: 200. Значит на 02.01.2014 заявка не погашена. На 03.01.2014 погашение на сумму: 10 + 20 + 90 = 120 - неполное. На 04.01.2014 сумма погашения составляет 10 + 20 + 90 + 150 = 270. Это погашение полностью погашает долг по заявке 2, при этом остается остаток: 270 - 150 = 120, который идет на погашение следующей заявки. Задача состоит в том, чтобы реляционными методами вывести список всех дат из таблицы гашений и напротив каждой даты указать было ли гашение полным или нет.
Задача похожа на расчет нарастающего итога, который можно подсчитать таким образом:
Однако суммировать в порядке возрастания дат надо до тех пор пока сумма не станет больше чем сумма текущей заявки. К тому же надо учитывать возможный остаток от расчета гашений предыдущей заявки.
Напишем запрос, который вернет первые гашения, которых хватит на полное гашение первой заявки (с остатком или без):
В этом запросе сначала строится CTE, которое дает номер для каждого гашения. Эти номера понадобятся в дальнейшем, когда мы сделаем из этого запроса рекурсивный CTE. Идея состоит в следующем. Мы пишем в подзапросе repays нарастающий итог. Для этого подзапроса делаем cross join на заявки, чтобы в наборе записей с суммой гашений был столбец для суммы заявки. Указываем условие на номер заявки, чтобы получить сумму только от одной, первой, заявки.
Дальше надо ограничить вывод, так, чтобы вывелись те строки, где гашение неполное и, возможно, еще одну строку, которая "догасила" первую заявку, возможно, с ненулевым остатком. Для этого мы считаем знак разности между суммой заявки и суммой гашения. Если накопленнная сумма гашения больше либо равна чем сумма заявки, то такие строки через dense_rank нумеруются как два, иначе как 1. То есть условие case when data.signum = 1 then 0 else data.num end = 1 дает гарантию на вывод гашений, которые не полностью гасят первую заявку.
Группы строк, которые ранжируются через dense_rank нумеруются через row_number. Запись, которая имеет row_number, равный единице, и находится в группе с dense_rank, соответствующей большим значениям RepaySum, будет первой записью которая догасит первую заявку. Эта запись гарантированно будет выведена благодаря условию newnum = 1.
Теперь будем считать, что этот запрос является базой рекурсивного CTE. Для простоты будем считать, что номера заявок идут последовательно. Этого всегда можно легко добиться за счет CTE и функции row_number. Напишем общий запрос:
В результате получаем такой красивый ответ:
База рекурсии осталась без изменений. В рекурсивном члене надо начать считать накопленную сумму от той строки в Repayments, на которой остановились. Для этого надо заполучить максимальный RowId на предыдущем шаге. Для этого надо обратиться к набору записей CTE (по его имени DataAll) и получить последнюю строку (в сортировке по RowId). Однако кроме RowId еще требуется и остаток от предыдущего шага. Кроме того, по правилам обращение в CTE, в его рекурсивной части, по имени может происходить только один раз. Поэтому для извлечения последней строки предыдущего шага используем функцию lead (она будет давать null для последней строки). Получив последнюю строку DataAll просто задаем условие, что рассмартиваются гашения, у которых RowId больше чем последний (а значит и максимальный) в DataAll. Также по номеру заявки, который также получается через обращение к DataAll задаем условие, что мы рассматриваем новую заявку с номером на единицу большим. К накопленной сумме прибавляем остаток предыдущего полного гашения и сравниваем ее с суммой в очередной заявке. Дальше следуют те же условия на dense_rank и row_number, что и в базе рекурсии. При окончательном выводе убираем лишние записи через top.
if
object_id ( 'tempdb..#Orders', 'U' ) is not null
drop table #Orders
create
table #Orders
(
OrdNum int not null,
OrdSum float not null,
primary key clustered ( OrdNum asc) on [PRIMARY]
)
on [PRIMARY]
insert
into #Orders ( OrdNum, OrdSum )
values
( 1,
100 ),
( 2,
200 ),
( 3,
500 ),
( 4, 90 )То есть сначала заняли 100 через первую заявку, затем 200 через вторую и т. д. В определенные дни заемщик вносит деньги для погашения. Сначала они идут на погашение первой заявки. Если периодически вносить деньги для погашения, то в какой-то момент их общая сумма станет больше либо равна чем сумма в первой заявке. Остаток и последующие гашения идут уже на погашение второй заявки. Создадим таблицу с погашениями:
if
object_id ( 'tempdb..#Repayments', 'U' ) is not null
drop table #Repayments
create
table #Repayments
(
RepayDate date not null,
RepaySum float not null,
primary key clustered ( RepayDate asc ) on [PRIMARY]
)
on [PRIMARY]
insert
into #Repayments ( RepayDate, RepaySum )
values
(
'2014-01-01',
110 ),
(
'2014-01-02', 20
),
(
'2014-01-03', 90
),
(
'2014-01-04',
150 ),
(
'2014-01-05', 20
),
(
'2014-01-06', 10
),
(
'2014-01-07', 34
),
( '2014-01-08', 123),
( '2014-01-09', 400 ),
( '2014-01-10',
40 )По тем данным, что были загружены в новые таблицы видно, что 01.01.2014 было гашение на сумму 110. Эта величина больше чем 100 - сумма первой заявки. Значит на 01.01.2014 погашена первая заявка, при этом есть остаток: 110 - 100 = 10, который идет на следующее погашение второй заявки. Для даты 02.01.2014 имеем погашение на сумму 20, с остатком 10, то есть погашение реально на 30. Сумма 30 меньше чем сумма второй заявки: 200. Значит на 02.01.2014 заявка не погашена. На 03.01.2014 погашение на сумму: 10 + 20 + 90 = 120 - неполное. На 04.01.2014 сумма погашения составляет 10 + 20 + 90 + 150 = 270. Это погашение полностью погашает долг по заявке 2, при этом остается остаток: 270 - 150 = 120, который идет на погашение следующей заявки. Задача состоит в том, чтобы реляционными методами вывести список всех дат из таблицы гашений и напротив каждой даты указать было ли гашение полным или нет.
Задача похожа на расчет нарастающего итога, который можно подсчитать таким образом:
select
RepayDate, sum ( RepaySum ) over ( order by RepayDate
rows between unbounded preceding and current row ) RepaySum
from #RepaymentsОднако суммировать в порядке возрастания дат надо до тех пор пока сумма не станет больше чем сумма текущей заявки. К тому же надо учитывать возможный остаток от расчета гашений предыдущей заявки.
Напишем запрос, который вернет первые гашения, которых хватит на полное гашение первой заявки (с остатком или без):
declare
@minOrdNum int = ( select min ( OrdNum ) from #Orders )
;
with
Repayments
as
(
select RepayDate, RepaySum, row_number () over ( order by RepayDate ) RowId
from #Repayments
)
select
*
from
(
select data.*, row_number () over ( partition by num order by num ) as newnum
from
(
select
repays.RowId, ord.OrdNum, ord.OrdSum, repays.RepayDate, repays.RepaySum,
dense_rank () over ( order by -sign ( case when ord.OrdSum - repays.RepaySum = 0 then -1
else ord.OrdSum - repays.RepaySum end ) ) num,
ord.OrdSum -
repays.RepaySum diff,
-sign
( case
when ord.OrdSum - repays.RepaySum = 0 then -1 else ord.OrdSum - repays.RepaySum end ) signum
from
#Orders ord
cross
join
(
select RowId, RepayDate,
sum ( RepaySum ) over
( order by RepayDate rows between unbounded preceding and current row ) RepaySum
from Repayments
)
repays
where
ord.OrdNum = @minOrdNum
) data
)
data
where
case when data.signum = 1 then 0 else data.num end = 1 or
data.newnum = 1В этом запросе сначала строится CTE, которое дает номер для каждого гашения. Эти номера понадобятся в дальнейшем, когда мы сделаем из этого запроса рекурсивный CTE. Идея состоит в следующем. Мы пишем в подзапросе repays нарастающий итог. Для этого подзапроса делаем cross join на заявки, чтобы в наборе записей с суммой гашений был столбец для суммы заявки. Указываем условие на номер заявки, чтобы получить сумму только от одной, первой, заявки.
Дальше надо ограничить вывод, так, чтобы вывелись те строки, где гашение неполное и, возможно, еще одну строку, которая "догасила" первую заявку, возможно, с ненулевым остатком. Для этого мы считаем знак разности между суммой заявки и суммой гашения. Если накопленнная сумма гашения больше либо равна чем сумма заявки, то такие строки через dense_rank нумеруются как два, иначе как 1. То есть условие case when data.signum = 1 then 0 else data.num end = 1 дает гарантию на вывод гашений, которые не полностью гасят первую заявку.
Группы строк, которые ранжируются через dense_rank нумеруются через row_number. Запись, которая имеет row_number, равный единице, и находится в группе с dense_rank, соответствующей большим значениям RepaySum, будет первой записью которая догасит первую заявку. Эта запись гарантированно будет выведена благодаря условию newnum = 1.
Теперь будем считать, что этот запрос является базой рекурсивного CTE. Для простоты будем считать, что номера заявок идут последовательно. Этого всегда можно легко добиться за счет CTE и функции row_number. Напишем общий запрос:
declare
@minOrdNum int = ( select min ( OrdNum ) from #Orders )
;
with
Repayments
as
(
select RepayDate, RepaySum, row_number () over ( order by RepayDate ) RowId
from #Repayments
),
DataAll
as
(
select *
from
(
select
data.*, row_number () over ( partition by num order by num ) as newnum
from
(
select
repays.RowId, ord.OrdNum, ord.OrdSum, repays.RepayDate, repays.RepaySum,
dense_rank () over ( order by -sign ( case when ord.OrdSum - repays.RepaySum = 0 then -1
else ord.OrdSum - repays.RepaySum end ) ) num,
ord.OrdSum -
repays.RepaySum diff,
-sign
( case
when ord.OrdSum - repays.RepaySum = 0 then -1 else ord.OrdSum - repays.RepaySum end ) signum
from
#Orders ord
cross join
(
select RowId, RepayDate,
sum ( RepaySum ) over
( order by RepayDate rows between unbounded preceding and current row ) RepaySum
from Repayments
) repays
where
ord.OrdNum = @minOrdNum
) data
) data
where
case
when data.signum = 1 then 0 else data.num end = 1 or
data.newnum = 1
union all
select *
from
(
select
data.*, row_number () over ( partition by num order by num ) as newnum
from
(
select
data.RowId, data.OrdNum, data.OrdSum, data.RepayDate, data.RepaySum,
dense_rank () over ( order by -sign ( case when data.OrdSum - data.RepaySum = 0 then -1
else data.OrdSum - data.RepaySum end ) ) num,
data.OrdSum - data.RepaySum diff,
-sign ( case when data.OrdSum - data.RepaySum = 0 then -1 else data.OrdSum - data.RepaySum end ) signum
from
(
select new.RowId, new.RepayDate,
sum ( new.RepaySum ) over
( order by new.RepayDate rows between unbounded preceding and current row ) + abs ( old.diff ) RepaySum, ord.OrdNum, ord.OrdSum
from
Repayments new
cross join
( select qq.* from ( select RowId, OrdNum, diff, lead ( RowId ) over ( order by RowId ) as numadd from DataAll ) qq where qq.numadd is null ) old
cross join
#Orders ord
where
new.RowId > old.RowId and
ord.OrdNum = old.OrdNum + 1
)
data
) data
) data
where
case
when data.signum = 1 then 0 else data.num end = 1 or
data.newnum = 1
)
select
top ( select count (*) from #Repayments ) OrdNum, RepayDate, case when OrdSum > RepaySum then 'НЕ
погашено' else 'погашено (остаток:
' + cast ( abs ( diff ) as nvarchar ( 100 ) ) + ')' end LogMessage
from DataAllВ результате получаем такой красивый ответ:
База рекурсии осталась без изменений. В рекурсивном члене надо начать считать накопленную сумму от той строки в Repayments, на которой остановились. Для этого надо заполучить максимальный RowId на предыдущем шаге. Для этого надо обратиться к набору записей CTE (по его имени DataAll) и получить последнюю строку (в сортировке по RowId). Однако кроме RowId еще требуется и остаток от предыдущего шага. Кроме того, по правилам обращение в CTE, в его рекурсивной части, по имени может происходить только один раз. Поэтому для извлечения последней строки предыдущего шага используем функцию lead (она будет давать null для последней строки). Получив последнюю строку DataAll просто задаем условие, что рассмартиваются гашения, у которых RowId больше чем последний (а значит и максимальный) в DataAll. Также по номеру заявки, который также получается через обращение к DataAll задаем условие, что мы рассматриваем новую заявку с номером на единицу большим. К накопленной сумме прибавляем остаток предыдущего полного гашения и сравниваем ее с суммой в очередной заявке. Дальше следуют те же условия на dense_rank и row_number, что и в базе рекурсии. При окончательном выводе убираем лишние записи через top.
Комментариев нет:
Отправить комментарий