PostgreSQLでgenerate_seriesを用いたデータ生成

概要

PostgreSQLでデータ生成を行う時のチートシート的なものです。

• 検証バージョン
  • PostgreSQL 11

数列

連続する数の列、ランダム列

• generate_series(1, 3)で生成される列に i と別名をつける
• 1列目は i の内容を取得
• 2列目は i を使わずにrandom()からランダムな値を取得

SELECT
  i,
  random()*100 AS cpu
FROM generate_series(0, 3) AS i;

 i |       cpu
---+------------------
 0 | 24.7656353283674
 1 | 60.2324245031923
 2 | 16.6800626087934
 3 | 55.9774012770504
(4 rows)

Boolean

SELECT round(random())::int::boolean AS male
  FROM generate_series(1, 3) AS i;

 male
------
 f
 f
 t
(3 rows)

文字列

0パディング

SELECT to_char(i, 'FM000') FROM generate_series(1, 3) AS i;

 to_char
---------
 001
 002
 003
(3 rows)

16進数の文字列

手っ取り早いのは md5 関数を使うこと。最大32文字までの16進数のランダム文字列が得られる。

SELECT left(md5(i::text), 8) FROM generate_series(1, 3) AS i;

=> SELECT left(md5(i::text), 8) FROM generate_series(1, 3) AS i;
   left
----------
 c4ca4238
 c81e728d
 eccbc87e
(3 rows)

マルチバイト文字

• chr(12353)は「ぁ」のコードポイントとなる
• 1列目はひらがなの繰り返し
• 2列目はカタカナの繰り返し
• 3列目は漢字の繰り返し
• 4列目は文字を指定回数繰り返した文字列を作成

SELECT
  i,
  chr(12353 + mod(i, 85)) AS hira,
  chr(12449 + mod(i, 85)) AS kata,
  chr(20124 + mod(i, 8957)) AS kanji,
  repeat('あぁ', 3)
FROM generate_series(0, 3) AS i;

 i | hira | kata | kanji |    repeat
---+------+------+-------+--------------
 0 | ぁ   | ァ   | 亜    | あぁあぁあぁ
 1 | あ   | ア   | 亝    | あぁあぁあぁ
 2 | ぃ   | ィ   | 亞    | あぁあぁあぁ
 3 | い   | イ   | 亟    | あぁあぁあぁ
(4 rows)

ひらがなのランダム文字列

string_aggを使用することで、ランダム文字を結合した文字列を作ることができる。

• 副問い合わせでは 列s, 列j のテーブルを生成する
  • 実体は (1, ぎ), (1, や), (2, ざ)の数字と文字の組となる
  • 60行のレコードが生成される
  • 列j は mod関数で 0, 1, 2 が得られて、それに1 を足すので 1, 2, 3 のいずれかになる
• string_agg にて s の列がj列の値に従ってグループ分けされ、結合される

SELECT j, string_agg(s, '')
  FROM
    (
      SELECT chr(12353 + (random() * 85)::INTEGER) AS s, 1 + mod(i, 3) AS j
      FROM generate_series(1,3 * 20) AS i
    ) AS hira
  GROUP BY j ORDER BY j;

 j |                string_agg
---+------------------------------------------
 1 | ぎやひぺゃそぜれりゖのざぺゆてぶぶうぐゃ
 2 | ざぬゔおぜからちゃめどぶつゕへづめぇをゆ
 3 | のぽてきぶぐぁてかぴらだぞてめるまぉりぶ
(3 rows)

日付・時刻

タイムスタンプの列(1時間)

SELECT
  timestamp '2014-01-10 22:00:00' + '1 hour'::INTERVAL * i
  FROM generate_series(1, 3) AS i;

      ?column?
---------------------
 2014-01-10 23:00:00
 2014-01-11 00:00:00
 2014-01-11 01:00:00
(3 rows)

日付列(1日): date型

SELECT
  CAST(date'2022-02-27' + '1 day'::INTERVAL * i AS date)
  FROM generate_series(0, 2) AS i;

    date
------------
 2022-02-27
 2022-02-28
 2022-03-01
(3 rows)

ある日時間のランダム日時

• 2列目は現在時刻から 90日後までのタイムスタンプを生成
• 3列目は 2014-01-10 ～ 2014-01-20 の範囲のタイムスタンプを生成

SELECT
  i,
  now() + (random() * (now() + '90 days' - now())) + '30 days',
  timestamp '2014-01-10 20:00:00' +
    random() * (timestamp '2014-01-20 20:00:00' -
                timestamp '2014-01-10 10:00:00')
FROM generate_series(1, 3) AS i;

 i |           ?column?            |          ?column?
---+-------------------------------+----------------------------
 1 | 2022-10-10 04:45:04.45053+09  | 2014-01-12 15:38:11.182564
 2 | 2022-10-08 14:52:35.675307+09 | 2014-01-11 01:53:01.954444
 3 | 2022-08-21 10:04:44.695721+09 | 2014-01-12 19:08:04.359665
(3 rows)

条件判定

• FROM句には Temporary table を用いた
  • FROM (VALUES ～) AS テーブル名(列名1, 列名2) のように定義する

SELECT
  c1,
  c2,
  CASE
    WHEN c1 < c2 THEN '<'
    WHEN c1 > c2 THEN '>'
    ELSE '='
  END
FROM
  (VALUES (1, 2), (2, 1), (2, 2)) AS tuple_c1_c2 (c1, c2);

 c1 | c2 | case
----+----+------
  1 |  2 | <
  2 |  1 | >
  2 |  2 | =
(3 rows)

Temporary table の部分は、副問い合わせに置き換えても良い。

SELECT
  c1,
  c2,
  CASE
    WHEN c1 < c2 THEN '<'
    WHEN c1 > c2 THEN '>'
    ELSE '='
  END
FROM
  (
   SELECT
     round(random() * 6) AS c1,
     round(random() * 6) AS c2
   FROM generate_series(1, 3) AS i
  ) AS tuple_c1_c2;

応用例: データ挿入

INSERT では SELECT で生成したテーブルを挿入できる

データ追加の例: char列

CREATE TABLE todos(article_id CHAR(3) NOT NULL UNIQUE);
INSERT INTO todos(article_id)
  SELECT to_char(i, 'FM000') FROM generate_series(1, 3) AS i;
SELECT * FROM todos;
DROP TABLE todos;

=> SELECT * FROM todos;
 article_id
------------
 001
 002
 003
(3 rows)

列挿入の例: integer, numeric, timestamp

CREATE TABLE devices(
  device_id INTEGER NOT NULL UNIQUE,
  cpu numeric(3,1) NOT NULL,
  log_time timestamp NOT NULL
);

INSERT INTO devices(device_id, cpu, log_time)
  SELECT
    i,
    round(CAST(random()*100 AS numeric), 1),
    timestamp '2022-01-10 22:00:00' + '10 min'::INTERVAL * i
  FROM generate_series(1, 3) AS i;

SELECT * FROM devices;

DROP TABLE devices;

 device_id | cpu  |      log_time
-----------+------+---------------------
         1 | 59.9 | 2022-01-10 22:10:00
         2 | 58.1 | 2022-01-10 22:20:00
         3 | 28.6 | 2022-01-10 22:30:00
(3 rows)