SQL

Google «А как сделать, чтобы все необходимое данные были размещены в одной строке, и можно было провести математическое действие с ними?» Или маленькими шагами к желаемому результату…

Время прочтения: 6 мин.

Продолжая тему анализа транзакционных операций, о которой было упомянуто в моей прошлой статье, от заказчика была поставлена задача по получению следующей информации:

  1. По шести счетам необходимо получить баланс на отчетную дату.
  2. Добавить информацию о канале проведения операции по открытию счета.
  3. Посчитать сумму остатков по счетам, в разрезе канала открытия.

Возьмем за дату отчета 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 руб.

Задание было успешно выполнено.

Советуем почитать