Время прочтения: 6 мин.
Продолжая тему анализа транзакционных операций, о которой было упомянуто в моей прошлой статье, от заказчика была поставлена задача по получению следующей информации:
- По шести счетам необходимо получить баланс на отчетную дату.
- Добавить информацию о канале проведения операции по открытию счета.
- Посчитать сумму остатков по счетам, в разрезе канала открытия.
Возьмем за дату отчета 20.05.2021 и один счет, на примере которого проведем все необходимые действия.
Воспользовавшись функциями из прошлой статьи, проводим выборку всех имеющихся операций до отчетной даты:
SELECT
[Номер счета]
, [Дата операции]
, [Сумма операции]
, [Баланс после операции]
, [Порядковый номер операции по счету]
,max([DEPOHIST_OpNo]) over (partition by [Номер счета]) [Номер операции_max]
,min([DEPOHIST_OpNo]) over (partition by [Номер счета]) [Номер операции_min]
, [Канал проведения операции]
FROM [Транзакции по счетам]
Where [Номер счета] in ('067CD797D381C896E3','4F2474CED164E1B211','63BD1C3C8D17407E9C'
,'8342A37E860392AF0F','A3BA4ABF09E3502764','DC9E352E0B983BB143')
and [Дата операции] < '2021-05-20'
До отчетной даны у нас получилось 93 операции:


Информация о канале открытия размещена в строке первой операции, а баланс на отчетную дату находится в последней операции за дату, предшествующей отчетной.
Для вывода искомого значения обернем наш запрос в подзапрос с применением фильтра:
Where (s. [Порядковый номер операции по счету] = s. [Номер операции_max]) or (s. [Порядковый номер операции по счету] = s. [Номер операции_min]).
Для более наглядного представления с помощью функции CASE добавим две дополнительных графы: ‘Канал открытия’ и ‘Баланс на отчетную дату’.

То, что мы получили на выходе нам не подходит, т.к. выводимая информация спровоцировала дубликацию строк, что может негативно сказаться при дальнейшей статистической обработке, при работе с большим массивом.
На этом этапе и возник вопрос: «А как сделать так, чтобы все необходимое данные были размещены в одной строке?»
Немного покопавшись в предлагаемых Google материалах, я обратил внимание на функцию STRING_AGG, позволяющую конкатенировать строки. Для этой функции присущи два обязательных аргумента, в виде строкового выражения, используемого для сцепления и разделителя. В результате получился следующий запрос:
select
J. [Номер счета]
, [Канал открытия] = STRING_AGG ([Канал открытия],'|')
, [Баланс на отчетную дату] = STRING_AGG ([Баланс на отчетную дату],'|')
from
(select
s. [Номер счета]
, s. [Баланс после операции]
, s. [Порядковый номер операции по счету]
, s. [Номер операции_max]
, s. [Номер операции_min]
, s. [Канал проведения операции]
, case
when s. [Порядковый номер операции по счету] = s. [Номер операции_max]
then s. [Баланс после операции]
else null
end 'Баланс на отчетную дату'
, case
when s. [Порядковый номер операции по счету] = s. [Номер операции_min]
then s. [Канал проведения операции]
else null
end 'Канал открытия'
(SELECT
[Номер счета]
, [Дата операции]
, [Сумма операции]
, [Баланс после операции]
, [Порядковый номер операции по счету]
, max([DEPOHIST_OpNo]) over (partition by [Номер счета]) [Номер операции_max]
, min([DEPOHIST_OpNo]) over (partition by [Номер счета]) [Номер операции_min]
, [Канал проведения операции]
FROM [Транзакции по счетам]
Where [Номер счета] in ('067CD797D381C896E3','4F2474CED164E1B211','63BD1C3C8D17407E9C'
,'8342A37E860392AF0F','A3BA4ABF09E3502764','DC9E352E0B983BB143')
and [Дата операции] < '2021-05-20'
) S
where (s. [Порядковый номер операции по счету] = s. [Номер операции_max])
or (s. [Порядковый номер операции по счету] = s. [Номер операции_min])
) J
group by J. [Номер счета]

Данная команда поможет вывести в одну строку информацию за разные даты по каждому счету.
Далее проведем подсчет количества счетов и сумму остатков по ним, в разрезе канала открытия:
select
G. [Канал открытия]
, count (G. [Номер счета]) [Количество открытых счетов]
, sum (G. [Баланс на отчетную дату]) [Общая сумма балансов по открытым счетам]
Но запустив выполнение данной функции я получил ошибку «Operand data type nvarchar is invalid for sum operator».
И как же я мог забыть один важный момент!!!
При использовании функции STRING_AGG соединяемые значения преобразуются в тип данных VARCHAR (NVARCHAR)– текстовые данные. Чтобы в дальнейшем с этими данными проводить математические действия, необходимо их конвертировать в нужный тип данных (money – денежное выражение), с применением функции CONVERT, позволяющей преобразовать выражение из одного типа данных в другой.
select
G. [Канал открытия]
, count (G. [Номер счета]) [Количество открытых счетов]
, sum (convert (money, G. [Баланс на отчетную дату])) [Общая сумма балансов по открытым счетам]
From (select
J. [Номер счета]
, [Канал открытия] = STRING_AGG ([Канал открытия],'|')
, [Баланс на отчетную дату] = STRING_AGG ([Баланс на отчетную дату],'|')
from
(select
s. [Номер счета]
, s. [Баланс после операции]
, s. [Порядковый номер операции по счету]
, s. [Номер операции_max]
, s. [Номер операции_min]
, s. [Канал проведения операции]
, case
when s. [Порядковый номер операции по счету] = s. [Номер операции_max]
then s. [Баланс после операции]
else null
end 'Баланс на отчетную дату'
, case
when s. [Порядковый номер операции по счету] = s. [Номер операции_min]
then s. [Канал проведения операции]
else null
end 'Канал открытия'
FROM
(SELECT
[Номер счета]
, [Дата операции]
, [Сумма операции]
, [Баланс после операции]
, [Порядковый номер операции по счету]
, max([DEPOHIST_OpNo]) over (partition by [Номер счета]) [Номер операции_max]
, min([DEPOHIST_OpNo]) over (partition by [Номер счета]) [Номер операции_min]
, [Канал проведения операции]
FROM [Транзакции по счетам]
Where [Номер счета] in ('067CD797D381C896E3','4F2474CED164E1B211','63BD1C3C8D17407E9C'
,'8342A37E860392AF0F','A3BA4ABF09E3502764','DC9E352E0B983BB143')
and [Дата операции] < '2021-05-20'
) S
where (s. [Порядковый номер операции по счету] = s. [Номер операции_max])
or (s. [Порядковый номер операции по счету] = s. [Номер операции_min])
) J
group by J. [Номер счета]
) G
group by
G. [Канал открытия]
Благодаря функции CONVERT у меня получился желаемый результат:

Видно, что один счет с остатком на отчетную дату 0,00 руб. был открыт в «Личном кабинете на сайте банка», а остальные 5 в «Офисе банка», с суммой остатков 530 125,21 руб.
Задание было успешно выполнено.