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

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

* 検証バージョン
  * PostgreSQL 11

## 数列

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

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

```sql
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

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

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

## 文字列

### 0パディング

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

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

### 16進数の文字列

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

```sql
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列目は文字を指定回数繰り返した文字列を作成

```sql
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列の値に従ってグループ分けされ、結合される

```sql
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時間)

```sql
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型

```sql
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 の範囲のタイムスタンプを生成

```sql
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) のように定義する

```sql
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 の部分は、副問い合わせに置き換えても良い。

```sql
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列

```sql
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

```sql
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)
```