SQL, Анализ данных

Сопоставление данных из разных источников, не имеющих ключевых признаков

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

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

Вот и в этот раз нам было необходимо выгрузить из базы транзакций все операции, подлежащие обязательному контролю, и сравнить с базой по контролю указанных операций на предмет полноты и достоверности отправленных в Росфинмониторинг сообщений (в рамках исполнения требований федерального закона 115-ФЗ).

При первоначальном изучении обрабатываемой информации стало понятно, что задача будет не простая. Выгрузку из базы транзакций предварительно нужно было преобразовать, так как каждое значение одной операции отображалось в базе отдельными строками и необходимо было вывести эти значения по столбцам в одну строку. Кроме того, в представленных для выгрузки источниках не было единичного и уникального значения, которое бы являлось одинаковым для сопоставляемых операций. Более детально проанализировав структуру базовых таблиц, у нас возникла идея идентифицировать операции путем набора характерных значений, которые в совокупности могли бы обеспечить уникальность данной операции. При этом, набор должен быть оптимальным, чтобы проверкой были охвачены все операции. Для этого первоначально нужно было осуществить объединенную выгрузку операций, применив для сопоставления минимальный набор характерных значений. На следующем шаге необходимо было проверить полученную выгрузку на уникальность записей, и, если этого не достигнуто, то добавлять в набор новое значение и повторять процедуру заново. Однако, для выполнения таких задач с применением ручных методов, требующих рутинных и повторяющихся действий, пришлось бы затратить значительные временные ресурсы.

И тут нам на помощь пришли простые возможности SQL и Python.

Для выгрузки операций из базы транзакций и преобразования полученной информации в таблицу с необходимой структурой мы использовали SQL и его встроенный оператор PIVOT:

SELECT [AUDIT_ID], [EVENT_DAY], [ЗНАЧЕНИЕ1], [ЗНАЧЕНИЕ2], [ЗНАЧЕНИЕ3], [ЗНАЧЕНИЕ4], [ЗНАЧЕНИЕ5], [ЗНАЧЕНИЕ6], [ЗНАЧЕНИЕ7], [ЗНАЧЕНИЕ8], [ЗНАЧЕНИЕ9]
FROM [dbo].[TRANZAK] AS tt
PIVOT
(MAX([ATTR_VALUE])
 FOR [ATTR_NAME] IN [ЗНАЧЕНИЕ1], [ЗНАЧЕНИЕ2], [ЗНАЧЕНИЕ3], [ЗНАЧЕНИЕ4], [ЗНАЧЕНИЕ5], [ЗНАЧЕНИЕ6], [ЗНАЧЕНИЕ7], [ЗНАЧЕНИЕ8], [ЗНАЧЕНИЕ9])
) AS pvt
ORDER BY pvt.[AUDIT_ID], pvt.[EVENT_DAY]

Получили ТАБЛИЦА_1. Для большего понимания приводим пример результата.

Было:

Стало:

point = 1234567890  # уникальное значение (индекс записи) из столбца AUDIT_ID (ТАБЛИЦА_1)  
select = '''select * from
ТАБЛИЦА_1 t1
left join 
ТАБЛИЦА_2 t2
on ({0})'''
where = '''where t1. AUDIT_ID = {0}'''.format(point) # создаем шаблон оптимального набора #характерных значений на примере одной операции (строки) для дальнейшей обработки всех #операций

col_names_1 =['t1. EVENT_DAY', 't1.ЗНАЧЕНИЕ1', 't1.ЗНАЧЕНИЕ3', 't1.ЗНАЧЕНИЕ5', 't1.ЗНАЧЕНИЕ8']
col_names_2 =['t2. DATA', 't2.ЗНАЧЕНИЕ2', 't2.ЗНАЧЕНИЕ5', 't2.ЗНАЧЕНИЕ7', 't2.ЗНАЧЕНИЕ9']
all_names = []

for name_1,name_2 in zip(col_names_1,col_names_2):
    text = 't1.{0} = t2.{1}'.format(name_1,name_2)
    all_names.append(text)
    end_text = ' and '.join(all_names) if all_names else text
    new_select = select.format(end_text)+where
    tab = pd.read_sql(new_select)
    if len(tab)==1:
        break
select_all =  select.format(end_text)

Вот так с помощью IT инструментов мы смогли выявить отклонения и своевременно проинформировать руководство.

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