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

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

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

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

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

и т.п.

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

Для справки:
RAND возвращает десятичную дробь от 0 до 1, её можно использовать для разового создания случайного числа.

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

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
random_number
64C7CD39-0533-49AC-BB2F-D353BCB1D415
2A3658F3-207F-4F4B-B845-21AADD0B72D8
D95E9589-BD1B-4DF8-90A6-4B57029CF7E2
1D4802C7-777B-4C28-B449-2540339534EF
2CC04649-032F-4AD3-9EBA-98681DDF67F9
A02C1420-BFCA-4457-AE9C-8047E3B54790
AE451AB9-5369-4796-9063-1AFA2E93B1C0
4A7FCA46-5ADE-4FB0-BFA9-83BD5AB5042D
1A6D8F60-155D-44D4-A026-0FB91962299A
29C3BB97-7A90-4799-B6AF-0C56A4D75A8B
Пару слов от том, что такое sysobjects:
SYSOBJECTS — это служебная таблица MS SQL Server, содержащая информацию о структуре баз данных и доступная пользователю на чтение. Она всегда есть на сервере и в ней всегда есть некоторое количество записей (обычно несколько десятков).

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

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

random_number
2025367116
296330166
-386093759
-1832849159
1761369500
-1086028261
-434652561
-118245324
2135398500
1922864082

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

Для справки:
SIGN(n) — возвращает значение «1» типа integer, если число n положительное, или «-1», если отрицательное  

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

  • общее количество строк с данными – от 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
oper_dateoper_timeamountoperator_numturn_code
2020-06-1111:39:04.9533333252.12000021
2020-06-1009:38:49.00000002620.9300002-1
2020-06-0209:22:30.00000002943.0500003-1
2020-06-2517:10:06.93000003810.81000021
2020-06-3011:55:28.96333337325.20000021
2020-06-1013:29:35.98000005089.6500003-1
2020-06-0812:09:13.99666671494.68000031
2020-06-1216:48:55.98666672946.06000021
2020-06-0317:55:46.93333339001.55000031
2020-06-0713:30:27.99333334767.3100001-1
2020-06-1409:56:00.96000006011.8000002-1
2020-06-1917:47:20.97666676888.7500001-1
2020-06-0513:18:19.98666678314.5900001-1
2020-06-2210:47:34.94333331360.79000031
2020-06-1210:34:15.91666677257.0900002-1
2020-06-1015:53:29.93333336427.0600003-1
2020-06-0414:19:41.92333337704.28000031
2020-06-0916:01:26.94666671482.88000031

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

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

CROSS JOIN sysobjects

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

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