Случайности не случайны

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

Для отработки гипотез, поиска отклонений, формирования разного рода индикаторов и отчётов аудитору, использующему в работе digital-инструменты и приёмы DS/DA/ML, необходимо выгружать информацию из баз данных и обрабатывать её программными средствами. Однако, иногда в рабочем процессе, связанном с получением необходимых данных, могут возникнуть проблемы, например:

1. Сотрудник (очень некстати) оказался «на удалёнке» (ну, или дома ему захотелось поработать в отпуске, после работы или на выходных). По требованиям безопасности он не имеет доступа к рабочим базам данных, содержащим конфиденциальную информацию, персональные данные клиентов и т.п. При этом структура БД ему известна; требуется создать и отладить запрос, делающий выборку записей из этих баз;
2. Другой возможный случай, аналогичный первому. Известна только структура результирующего набора, который нужно получить в итоге. Требуется создать и отладить алгоритм, который этот набор данных обрабатывает;
3. Или еще один вариант: доступ к БД есть, но записей в них настолько много, что их выборка может идти слишком долго либо структура хранения данных в исходных БД неизвестна и нужно долго в этом разбираться, получать необходимые доступы и т.д. При этом известна структура результирующего набора данных, но, как и в предыдущем случае, требуется создать и отладить алгоритм, который этот набор обрабатывает;

и т.п.

Частично обойти эти проблемы можно, создав тестовые таблицы со структурой, аналогичной структуре исходных таблиц рабочих БД или результирующих таблиц. А затем отладить на них алгоритмы обработки или SQL-скрипты выборки. Естественно, что для наполнения таких таблиц пользоваться реальными данными нельзя, поэтому рассмотрим: как в Transact-SQL можно сделать наполнение таблицы случайно сгенерированными данными. Для создания случайных значений в Transact-SQL используются широко известная функция RAND и менее известная NEWID.

Например, следующий скрипт генерирует и выводит четыре случайных числа:

DECLARE @counter SMALLINT;
SET @counter = 1;
WHILE @counter < 5
BEGIN
PRINT RAND()
SET @counter = @counter + 1
END;
GO

В результате получим следующие числа:

0.69311
0.771596
0.920466
0.582494

(у вас, разумеется, эти числа будут другими и при каждом новом запуске скрипта будут постоянно меняться)

К сожалению, RAND не годится для генерации случайных значений поля для нескольких записей таблицы в операторе SELECT, т.к. в одном операторе она будет вызвана один раз. Случайное число тоже будет сгенерировано только одно и применено ко всем выбираемым записям.

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

select top (10) newid() as random_number
from sysobjects

Соответственно, если мы будем выбирать записи из sysobjects, то всегда будем что-то получать. Но в данном случае нас эти записи сами по себе не интересуют, мы для каждой выбранной строки выводим уникальное случайное значение, сгенерированное функцией NEWID. Всего мы извлечём 10 записей и выведем их в виде таблицы, состоящей из поля «random_number», заполненного случайными строками.

Итак, получен набор уникальных случайных значений (правда, довольно специфического вида). Теперь можем преобразовать их к нужной форме, чтобы они стали нормальными числами в необходимом диапазоне. Для этого воспользуемся функцией CHECKSUM, которая по алгоритму MD5 рассчитывает числовое значение хэш-функции любого набора символов. Проще говоря, работает по принципу «уникальная строка на входе – уникальное число на выходе». Вместо newid() запишем checksum(newid()) и в результате получим:

Для того чтобы преобразовать результат работы данной функции в положительное число в диапазоне от A до B, используем следующее выражение: ABS(CHECKSUM(NEWID())) % (B – A + 1) + A. Также воспользуемся функцией, которую можно использовать в процессе генерации случайных данных — это SIGN(n).

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

• общее количество строк с данными – от 10 до 20;
• 1-й столбец: дата совершения операции от 01.06.2020 до 30.06.2020 (для этого получим число от 43981 до 44010 (число дней до нужной даты, считая от 01.01.1900) и преобразуем его в тип date);
• 2-й: время совершения операции от 09:00 до 18:00 (для этого получим число от 32400 до 64800 (число секунд до нужного времени, считая от полуночи), разделим на 86400 (число секунд в сутках) и преобразуем в тип time полученную в результате десятичную дробь);
• 3-й: сумма операции от 1 до 10000;
• 4-й: номер оператора, совершившего операцию, от 1 до 3;
• 5-й: признак — приход (1) или расход (-1).

Скрипт, выполняющий заполнение таблицы и результат его выполнения:

SELECT TOP (ABS(CHECKSUM(NEWID())) % 11 + 10)
cast(cast((ABS(CHECKSUM(NEWID())) % 30 + 43981) as datetime) as date) as oper_date,
cast(cast((ABS(CHECKSUM(NEWID())) % 32401 + 32400)/86400. as datetime) as time) as oper_time,
(ABS(CHECKSUM(NEWID())) % 999999. + 1) / 100. as amount,
ABS(CHECKSUM(NEWID())) % 3 + 1 as operator_num,
SIGN(CHECKSUM(NEWID())) as turn_code
FROM sysobjects

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

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

CROSS JOIN sysobjects

Она «умножает» на себя количество извлекаемых из sysobjects записей. Если этого будет мало, то можно добавить ещё один CROSS JOIN, тогда мы получим число записей в sysobjects в кубе, и т.д. (по мере необходимости).