WITH問い合わせ（共通テーブル式・ＣＴＥ）　簡単なテストデータに活用

自己都合によりマニュアルへのリンクは9.2としています。ご了承下さい。


━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ PostgreSQL スキルアップノート
■■■■
■◆■■ WITH問い合わせ（共通テーブル式・ＣＴＥ）　簡単なテストデータに活用
■■■■
■■■■
■■■■ 使用環境：PostgreSQL9.1.7 (CentOS6.2)
         2013/02/03
         2013/03/16
                                                                   (C) 2013 ohdb
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

WITH問い合わせにより生成されるデータを、テストなどに活用する方法


【マニュアル】

WITH問い合わせ（共通テーブル式）→●[マニュアル]






■１■ テーブルを作成しないでSQL文をテストする用途
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━


実テーブルを作成しなくてもSQL文のテストができる。



【□】 

WITH table1(c1,c2) AS (
         values(1,10),
               (2,20)
     ) ,
     table2(c1,c2,c3) AS (
         values(1,10,100),
               (2,20,300)
     ) 
SELECT * from table1,table2 ;



 c1 | c2 | c1 | c2 | c3
----+----+----+----+-----
  1 | 10 |  1 | 10 | 100
  1 | 10 |  2 | 20 | 300
  2 | 20 |  1 | 10 | 100
  2 | 20 |  2 | 20 | 300
(4 行)


・・・table1はカラム２個のテーブル
      table2はカラム３個のテーブル





■２■ 大きなテストテーブルの生成
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

WITH RECURSIVEにより大量データを生成してINSERTすることで簡単に規模の大きなテーブ
ルを作成することができる。
前項とは違い、あらかじめテーブル実体を作成しておき、大量データを生成する部分に、
WITH問い合わせを活用する。
上限の条件を付けて無限のinsertにならないように注意のこと。

（参考）データによってはWITH句ではなくgenerate_seriesも便利
       連番とランダムな数値データのテーブルを１千万件作成
       create table t11 as select generate_series(1,1000000) as c1,floor(random() * 10) as c2;


以下はWITH句によるテストデータの作成として、

・連番
・１時間間隔の時刻データ
・ランダムな数字
を生成してテーブルに格納する。





【□】 準備

DROP TABLE t1;
CREATE temp TABLE t1 (c1 int,c2 timestamp,c3 int);

【□】 テーブルデータを生成

WITH RECURSIVE tmp(c1,c2,c3) AS (
    SELECT 1, current_timestamp ,floor(random() * 10000000) 
    UNION ALL 
    SELECT c1+1, c2 + '1 hours' ,floor(random() * 10000000) 
    FROM tmp WHERE c1 < 10000000
)
INSERT INTO t1 SELECT * FROM tmp;


時間: 23656.744 ms


database1=# SELECT * FROM t1;
  c1   |             c2             |   c3
-------+----------------------------+---------
     1 | 2013-02-03 12:45:54.294289 | 3418954
     2 | 2013-02-03 13:45:54.294289 | 6172883
     3 | 2013-02-03 14:45:54.294289 | 8616501
     4 | 2013-02-03 15:45:54.294289 | 8341051
     5 | 2013-02-03 16:45:54.294289 | 9104456
     6 | 2013-02-03 17:45:54.294289 | 5883997
     7 | 2013-02-03 18:45:54.294289 | 2989104
     8 | 2013-02-03 19:45:54.294289 | 7785673
     9 | 2013-02-03 20:45:54.294289 | 4781043
    10 | 2013-02-03 21:45:54.294289 | 2293272
    11 | 2013-02-03 22:45:54.294289 | 5438948
    12 | 2013-02-03 23:45:54.294289 | 6206132
    13 | 2013-02-04 00:45:54.294289 | 1368020
    14 | 2013-02-04 01:45:54.294289 | 6914549
    15 | 2013-02-04 02:45:54.294289 | 4274369
    16 | 2013-02-04 03:45:54.294289 | 9424468
    17 | 2013-02-04 04:45:54.294289 |  238237
    18 | 2013-02-04 05:45:54.294289 | 2134884
    19 | 2013-02-04 06:45:54.294289 | 9687331
    20 | 2013-02-04 07:45:54.294289 | 2992933
    21 | 2013-02-04 08:45:54.294289 | 1176006
    22 | 2013-02-04 09:45:54.294289 | 6268257
    23 | 2013-02-04 10:45:54.294289 | 9041512
    24 | 2013-02-04 11:45:54.294289 | 3023125
    25 | 2013-02-04 12:45:54.294289 | 2246261
    26 | 2013-02-04 13:45:54.294289 | 9536188
             :
             :
             :
             :









以上