連続値生成関数generate_series

time 2018/01/12

folder PostgreSQL

本記事では、PostgreSQLの連続値生成関数であるgenerate_seriesの基本的な使い方について、まとめました。

応用すれば、大量データを作成が容易になります。まずは、その基本をしっかりとおさえましょう。

generate_seriesとは

generate_seriesとは、集合を返す関数の一つで連続値を生成する関数です。

大きな利点は、同時に接続される複数のセッション間でも、一意の値を容易に生成できる点です。これは、コミットやロールバックに影響せずに、順序から整数を取得した段階で、順序がカウントアップ（またはカウントダウン）されるためです。

使用方法

文法
①generate_series(start[数値型], stop[数値型], step[数値型])
※step[数値型]は省略可能。省略した場合は1と指定した場合と同義
②generate_series(start[timestamp型], stop[timestamp型], step[interval型])

戻り値
引数のstartやstopと同じ型

説明
startからstopまで、step刻みで連続する値を生成する
stepにはマイナス値を指定することも可能、ただしその場合はstart > stopの値を指定する必要がある

備考
使用する際は、以下のいずれか
①SELECT * FROM generate_series(1,10);
②SELECT generate_series(1,10);

具体例

-- ①1から10までのデータを1刻みで作成
SELECT * FROM generate_series(1,10);

 generate_series
-----------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
              10
(10 rows)

-- ①1から10までのデータを1刻みで作成

SELECT * FROM generate_series(1,10);

generate_series

-----------------

(10 rows)

-- ②1から15までの奇数のデータを作成
SELECT * FROM generate_series(1,15,2);

 generate_series
-----------------
               1
               3
               5
               7
               9
              11
              13
              15
(8 rows)

-- ②1から15までの奇数のデータを作成

SELECT * FROM generate_series(1,15,2);

generate_series

-----------------

(8 rows)

-- ③10から0までのデータを3刻みで作成
SELECT * FROM generate_series(10,0,-3);

 generate_series
-----------------
              10
               7
               4
               1
(4 rows)

-- ③10から0までのデータを3刻みで作成

SELECT * FROM generate_series(10,0,-3);

generate_series

-----------------

(4 rows)

-- ④1から2までのデータを0.3刻みで作成
SELECT * FROM generate_series(1,2,0.3);

 generate_series
-----------------
               1
             1.3
             1.6
             1.9
(4 rows)

-- ④1から2までのデータを0.3刻みで作成

SELECT * FROM generate_series(1,2,0.3);

generate_series

-----------------

1.3

1.6

1.9

(4 rows)

-- ⑤2018年1月1日0時から1月3日12時まで10時間刻みで作成
SELECT * FROM generate_series('2018-01-01 00:00'::timestamp
                             ,'2018-01-03 12:00'::timestamp
                             ,'10 hours');

   generate_series
---------------------
 2018-01-01 00:00:00
 2018-01-01 10:00:00
 2018-01-01 20:00:00
 2018-01-02 06:00:00
 2018-01-02 16:00:00
 2018-01-03 02:00:00
 2018-01-03 12:00:00
(7 rows)

-- ⑤2018年1月1日0時から1月3日12時まで10時間刻みで作成

SELECT * FROM generate_series('2018-01-01 00:00'::timestamp

,'2018-01-03 12:00'::timestamp

,'10 hours');

generate_series

---------------------

2018-01-01 00:00:00

2018-01-01 10:00:00

2018-01-01 20:00:00

2018-01-02 06:00:00

2018-01-02 16:00:00

2018-01-03 02:00:00

2018-01-03 12:00:00

(7 rows)

注意点

数値型を引数に指定する場合で、stepに0を指定するとエラーになります。

SELECT * FROM generate_series(1,10,0);

ERROR:  step size cannot equal zero

SELECT * FROM generate_series(1,10,0);

ERROR: step size cannot equal zero

stepに正の値を指定し、start>stopとなるような値を指定した場合、結果が0件となります。
またその逆で、stepに負の値を指定し、start

SELECT * FROM generate_series(10,1,1);

 generate_series
-----------------
(0 rows)

SELECT * FROM generate_series(10,1,1);

generate_series

-----------------

(0 rows)

ちょっとした応用

generate_seriesで生成した値を利用して計算して出力したり、生成した値を使わずに定数等を出力（行を生成するという機能だけを利用）したりすることも可能です。

SELECT num                num  -- 生成値
      ,'1'                cons -- 定数
      ,current_date + num date -- 生成値を利用
FROM   generate_series(1,10) num
;

 num | cons |    date
-----+------+------------
   1 | 1    | 2018-01-13
   2 | 1    | 2018-01-14
   3 | 1    | 2018-01-15
   4 | 1    | 2018-01-16
   5 | 1    | 2018-01-17
   6 | 1    | 2018-01-18
   7 | 1    | 2018-01-19
   8 | 1    | 2018-01-20
   9 | 1    | 2018-01-21
  10 | 1    | 2018-01-22
(10 rows)