§ Generate test data in PostgreSQL.

Generate test data in PostgreSQL.

Время от времени мне приходится проводить с PostgreSQL различные опыты или эксперименты. Довольно часто для этих задач мне нужны определенные данные. В зависимости от целей могут понадобиться самые разные данные. Конечно можно вручную задать необходимый набор строк с данными, но когда речь заходит о десятках, сотнях, тысячах строк... не о каком ручном труде речи быть не может. Итак под катом сотни и сотни сгенерированных данных.

По сути пост задумывался как шпаргалка и то место где у меня воедино собраны рецепты по генерации данных. Итак начнем с самых простых вариантов и будем постепенно усложнять задачу.
Самый первый и самый просто способ сгенерировать данные это  создание последовательности. Здесь мы прибегнем к функции generate_series(). Это довольно удобная функция которая позволяет генерировать как непрерывные последовательности чисел, так и с определенным шагом. Вдобавок можно генерировать и временные последовательности. Подробности здесь. Cгенерируем же скорей последовательность простых чисел от 1 до 3:
# select generate_series(1,3) as id;
 id
----
  1
  2
  3

В дальнейшем мы будем использовать generate_series() для определения того сколько нам нужно данных.
Теперь сгенерируем случайные дробные числа. Здесь нам понадобится функция random() которая генерирует случайное число от 0 до 1 которое мы тут же умножаем на 1000 (или любое другой множитель) и приводим к типу numeric для точных чисел.
# select (random()*1000)::numeric(10,2) as price from generate_series(1,3);
 price
--------
 321.72
 657.41
 552.61

Теперь предположим нам нужны числа из определённого диапазона, добавляем немного математики к функции random() и генерируем случайное число в желаемом диапазоне, например с 22 до 43. Полученные числа приводим к целочисленному типу integer.
# select (random() * 21 + 22)::int as size from generate_series(1,3);
 size
------
   39
   34
   24

Возможно, сейчас кто-то спросит, а как удостовериться что наши значения не выходят за границы диапазона? Чтож, давайте проверим... возьмем минимальное и максимальное значение из достаточно большого диапазона, например из одного миллиона строк:
# select min(size), max(size) from (select (random() * 21 + 22)::int as size from generate_series(1,1000000)) q;
 min | max
-----+-----
  22 |  43

Как видим минимальное и максимальное значения соответствуют границам диапазона.
Идем дальше, генерируем случайный текст. Здесь мы воспользуемся функцией md5() и уже известной random(). 
# select md5(random()::text) as product_name from generate_series(1,3);
           product_name          
----------------------------------
 ae6bab1e2975acfc7d13ac3b5e0853fd
 9934902c18cb0666ac2f0261d2d41ba4
 3065d96aa8419564bcaa3a2e83a024f7

Теперь такой случай, сгенерируем случайную последовательность из уже заданного набора. Допустим у нас есть 3 цвета и 5 автомобилей, раскрасим автомобили случайным образом. Здесьмы воспользуемся массивов и функцией ceil():
# select (array['red', 'green', 'blue'])[ceil(random()*3)] as color from generate_series(1,5);
 color 
-------
 green
 blue
 red
 blue
 green

Чтож с числами и текстом все понятно. Теперь генерируем время. В общем случае, время можно представить датой, временем в часах минутах или временной отметкой (timestamp). Здесь мы будем использовать функции now() для получения текущего времени, round() для округления результатов random() и оператор interval. Либо мы можем привести результат умножения random() к типу integer как это было в примере выше (числа из заданного диапазона).
Для начала генерируем случайную временную отметку:
# select now() - interval '1 day' * round(random() * 100) as timestamp;
          timestamp          
------------------------------
 2014-02-13 18:20:15.11048+06

Далее полученную временную отметку можно перевести в дату:
# select (now() - interval '1 day' * round(random() * 100))::date as date;
    date  
------------
 2014-01-10

или время:
# select (now() - interval '1 day' * round(random() * 100))::time as time;
      time      
-----------------
 18:20:45.827767

К полученному рецепту подмешиваем generate_series() и генерируем набор случайных временных отметок:
# select now() - interval '1 day' * round(random()*100) as updated_at from generate_series(1,3);
          updated_at          
-------------------------------
 2013-12-13 22:02:06.521724+06
 2013-12-23 22:02:06.521724+06
 2013-11-28 22:02:06.521724+06

Теперь усложним задачу и предположим что нужно сгенерировать даты в определенном диапазоне. Например возьмем диапазон дат между сегодняшним днем и датой 20 дней назад. Решается задача изменением интервала.
# select (now() - interval '20 day' * random())::date as date from generate_series(1,3);
    date  
------------
 2014-02-19
 2014-02-18
 2014-02-20

Для уверенности можно проверить значения на большой последовательности значения, проверим значения от сегодняшнего (на момент написания 28 февраля 2014) дня до 20 дней (8 февраля 2014).
# select min(date),max(date) from (select (now() - interval '20 day' * random())::date as date from generate_series(1,1000000)) q;
    min     |    max    
------------+------------
 2014-02-08 | 2014-02-28

Сходится. Добавляем еще математики и генерируем время в заданном диапазоне, например дату между 10 дней назад и 20 дней назад. На первый взгляд, кажется немного запутанно, не удивлюсь если найдется более простой вариант.
# select (now() - interval '20 day' + interval '10 day' * random())::date as date from generate_series(1,5);
    date  
------------
 2014-02-16
 2014-02-16
 2014-02-17

И снова можем проверить верность дат в придуманном диапазоне
# select min(date),max(date) from (select (now() - interval '20 day' + interval '10 day' * random())::date as date from generate_series(1,1000000)) q;
    min     |    max    
------------+------------
 2014-02-08 | 2014-02-18

Ну и напоследок сгенерируем случайные boolean. Тут все просто, мы получаем случайное число, приводим к integer и затем к boolean типу.
# select random()::int::bool from generate_series(1,3);
 random
--------
 f
 t
 t

Итак теперь когда мы можем генерировать разные виды данных, сгенерируем тестовые данные, например последовательность, наименование товара, цену, характеристику товара в диапазоне с 22 до 43 например размер, цвет товара, время последнего обновления товара, дату изготовления и наличие в продаже:
# select 
  generate_series(1,5) as id,
  md5(random()::text)::char(10) as name,
  (random()*1000)::numeric(10,2) as price,
  (random() * 21 + 22)::int as size,
  (array['cyan','magenta'])[ceil(random()*2)] as color,
  (now() - interval '1 day' * round(random()*100))::timestamp(0) as updated_at,
  (now() - interval '2 year' + interval '1 year' * random())::date as built,
  random()::int::bool as avail;
 id |    name    | price  | size |  color  |     updated_at      |   built    | avail 
----+------------+--------+------+---------+---------------------+------------+-------
  1 | 93a7cb08f3 | 317.16 |   41 | magenta | 2014-01-11 13:13:31 | 2013-01-27 | t
  2 | 815aa27c83 | 893.48 |   39 | magenta | 2013-12-27 13:13:31 | 2012-04-26 | t
  3 | 953569162e |  72.74 |   31 | magenta | 2014-02-24 13:13:31 | 2013-02-11 | t
  4 | 0b14c9ef4a | 617.05 |   26 | cyan    | 2014-01-02 13:13:31 | 2012-09-07 | t
  5 | f0d9553e5d | 564.57 |   36 | cyan    | 2013-12-03 13:13:31 | 2012-10-06 | t

Но пока это просто данные, для завершения картины оформим их в таблицу и снабдим до кучи индексами, а вместо 5 строк сгенерируем 1 000 000 записей:
# create table products as select 
  generate_series(1,1000000) as id,
  md5(random()::text)::char(10) as name,
  (random()*1000)::numeric(10,2) as price,
  (random() * 21 + 22)::int as size,
  (array['cyan','magenta'])[ceil(random()*2)] as color,
  (now() - interval '1 day' * round(random()*100))::timestamp(0) as updated_at,
  (now() - interval '2 year' + interval '1 year' * random())::date as built,
  random()::int::bool as avail;
SELECT 1000000
# alter table products add primary key (id);
ALTER TABLE
# create index products_name_idx on products using btree (product_name);
CREATE INDEX

Вот, на этом все на сегодня. До скорых встреч!

P.S. В качестве небольшого бонуса, как взять 10%, 25% случайных данных от уже существующей таблицы? С помощью random() конечно же:
# create table random_data as select * from products where random() < 0.25;
SELECT 250221

Как видим взято 250221 строк, что как раз составляет ~25% от 1 миллиона строк в products.

В коментарии задали вопрос, цитирую "А как сделать, чтобы 'генерилась' незначительная доля boolean в false, а остальная - в true."
Хорошую задачку вы мне задали, полтора часа с ней просидел))) У меня получилось только через CTE (Common Table Expressions) и CASE, поэтому может показаться запутанно. В общем виде, мы сначала создаем 1000 строк по random() функции и получаем дробные числа от 0 до 1 (data as). Затем мы сравниваем полученные числа с граничным значением 0.03 (те самые 3%) и получаем два поля, первое это собственно само значение и второе (n) это присвоенное булевое значение по результату проверки с граничным значением (data2 as). На этом CTE завершается и мы берем второе поле n из data2.
# with
 data as (select random() i from generate_series(1,1000)),
 data2 as (select i, case when i > 0.03 then true when i < 0.03 then false end n from data)
select n from data2;
SELECT 1000

И для проверки добавляем count() и group by.
# with 
 data as (select random() i from generate_series(1,1000)),
 data2 as (select i, case when i > 0.03 then true when i < 0.03 then false end n from data)
select count(n) from data2 group by n;
 count 
-------
    28
   972

(2 rows)

Увы, проще вариант не смог придумать.

Updated: вот более простой и понятный вариант, без CTE:
# select case when random()<=0.03 then false else true end as n from generate_series(1,1000);

На главную "Virtualizing Linux"