スポンサーリンク

SQLで，テーブルに存在しない複数行のデータを取得する方法　（PostgreSQLの generate_series関数をマスターしよう）

DB PostgreSQL プログラミング 数値計算 グラフ描画

PostgreSQLで，テーブルを使わずに，複数行のデータを取得する方法。

  • SQLの中で，大量の定数データを使う必要がある。（例えば，１ヶ月間の日付全部とか，1000までの素数全部とか）
  • だが，それらのデータを，前もってどこかのテーブルに格納しておくことはできない。（データが多すぎるから・・・）


そんなとき，「複数行の使い捨てデータを動的に生成する」というテクニックが役立つ。

テーブルやシーケンスは使わない。



以下はその方法。

特に，Webアプリの開発でどう役立つのか？という点に重きを置いている。


できることの一覧：

  • 数字，日付，その他の定数データを大量に生成する方法。
  • 生成された複数行のデータを使って「補間」を行なうことができる。
  • 無理すれば，一般の言語での「配列」みたいなこともSQLでできる！！


目次：

（１）複数列のデータをSQLで定義して返す方法


「複数行」ではなく「複数列」であれば，話は簡単だ。

SELECT
	now(),
	'hoge',
	1
;

実行結果：

2010-03-08 12:00:00.000000000    hoge    1

'hoge'や 1 は定数，now() は関数呼び出しであり，いずれも「評価式」の一種。

評価式とは，簡単に言えば，テーブルに存在していないデータ。スカラとも。

4.1. 語彙の構成：定数について
http://www.postgresql.jp/document/pg8...


評価式について
http://www.postgresql.jp/document/pg8...


複数「列」の定数とか，複数列の関数呼び出しをコーディングすることは簡単だ。

しかしそれだと数が多くなった場合，各列に対して，手書きでデータの内容を書かなければいけないので非効率的。


複数のデータ（テーブルに無い，擬似的なレコード）を効率的に生成して，SELECT文で取得することは可能か？

（２）generate_series() を使ってみよう

ポスグレの generate_series という関数を使えば，複数行のデータを返すことが可能。

この関数は，連続した数値（連番）を複数行で返す。

9.18. 集合を返す関数
http://www.postgresql.jp/document/pg8...
※postgres8.0以降が条件

さっそく使ってみよう。


1から10までの連番を表示：

SELECT
	generate_series( 1, 10 )
;

または

SELECT
	*
FROM
	generate_series( 1, 10 )
;


実行結果：

1
2
3
4
5
6
7
8
9
10

シーケンスと違い，一時的な連番を作れる。保存されない。

「使い捨てシーケンス」といった感じ。

※本来のシーケンスは，テーブル内の特定のカラムに連番を保存してゆくための仕組み。使い捨てではない。



第三引数で，刻み幅を指定できる。

SELECT
	*
FROM
	generate_series( 2, 10, 3 )
;

実行結果：

2
5
8


テーブルのように，別名（エイリアス）を利用できる。

SELECT
	*
FROM
	generate_series( 1, 10 ) AS s(i)
WHERE
	s.i > 5
;

実行結果：

6
7
8
9
10

s が擬似テーブルのエイリアスのようなもの。

i が擬似カラム名のようなもの。


s.iのかわりに，temp_table.temp_column のような名前をつけてみれば，
普通のテーブルと同じように使えるんだという事が伝わりやすいかもしれない。

あるいは，s(i) とするかわりに，思い切って arr(i) としてみれば，
連続したレコードが並んでいるんだという意思表示をする事ができるかもしれない。



数値以外のことにも役立つ。

'hoge'を３つ取得したい場合

SELECT
	'hoge'
FROM
	generate_series( 1, 3 )
;

実行結果：

hoge
hoge
hoge


たった３つなら手書きで３回「hoge」と直打ちしたほうが早いが，

数が大きくなってくるとgenerate_seriesが役立つ。

（Excelで，列方向のオートフィルを行なうようなイメージ。）



例えば，もし 'hoge' の部分を random() に変えれば，独立した乱数をいっぺんに複数個得ることができる。

※random()関数の使用法については下記を参照。

ランダムにデータを取り出す【PostgreSQL】
http://www.programming-magic.com/2008...


数列を自動生成できるので，数学的な調査を行なうアルゴリズムには重宝する。


１００の約数(＝１００を割り切る数)を全部求めるSQL：

SELECT
	*
FROM
	generate_series( 1, 100 ) as s(i)
WHERE
	MOD( 100, s.i ) = 0
;

実行結果：

1
2
4
5
10
20
25
50
100

1000以下の素数を求めるSQL：

SELECT
	s1.i AS prime
FROM
	generate_series( 1, 1000 ) as s1(i)
WHERE
	s1.i > 1
		AND
		
	NOT EXISTS(
		SELECT
			*
		FROM
			generate_series( 1, 1000 ) as s2(i)
		WHERE
			s2.i > 1
				AND
			s2.i <= s1.i / 2
				AND
			MOD( s1.i, s2.i ) = 0
	)
	
ORDER BY
	s1.i
;

実行結果：

2
3
5
7

...
...

983
991
997

まさに，目からウロコという感じのSQLではないか？

NOT EXISTS(〜〜) の部分は，「s1.i には約数が存在しないか？」という判定処理をしている。


下記のURLでは，同じ事をするためにテーブル作成が必要。

しかし，generate_series() を使えば，テーブルは不要だ。

なので，気軽にSQLを実行できる。

SQLで数学パズルを解く（数論編-解答）
http://www.geocities.jp/mickindex/dat...


※上記SQLについて補足（自己相関サブクエリについて）：

NOT EXISTS　のサブクエリの部分だけをまとめて，

  • s1.i という１件の数値を渡すと
  • その数値に約数が存在するかどうかを判定して返してくれる

という役目を持った，判定用の「関数」のような物だと思えばよい。



以下は応用例。

（３）取得した連番を加工してみよう


今日から７日間の日付を表示

SELECT
	current_date + arr.i
FROM
	generate_series( 0, 6 ) AS arr( i )
;

実行結果：

2010-03-09
2010-03-10
2010-03-11
2010-03-12
2010-03-13
2010-03-14
2010-03-15


ちょっと工夫して，今日の日付のところにアイコンを出してみよう。

SELECT
	CASE
		WHEN arr.i = 0 THEN '★今日★'
		WHEN arr.i < 0 THEN '　 ↓ 　'
		ELSE                ''
	END,
	to_char( current_date + arr.i, 'YYYY/MM/DD' ),
	to_char( current_date + arr.i, 'Day' )
FROM
	generate_series( -7, 7 ) AS arr( i )
;

実行結果：

　 ↓ 　   2010/03/02  Tuesday
　 ↓ 　   2010/03/03  Wednesday
　 ↓ 　   2010/03/04  Thursday
　 ↓ 　   2010/03/05  Friday
　 ↓ 　   2010/03/06  Saturday
　 ↓ 　   2010/03/07  Sunday
　 ↓ 　   2010/03/08  Monday
★今日★   2010/03/09  Tuesday
           2010/03/10  Wednesday
           2010/03/11  Thursday
           2010/03/12  Friday
           2010/03/13  Saturday
           2010/03/14  Sunday
           2010/03/15  Monday
           2010/03/16  Tuesday

ちょっとしたカレンダー。

テーブル不要というのが良いところ。


※念のため注意書き：レイアウトや見せ方に関する処理は，本当はDBが行なうべきではない。


PostgreSQL で連番を生成する generate_series
http://www.deftrash.com/blog/archives...
これまでプログラムでごりごり頑張っていた部分のいくつかは、generate_series を使うことで、SQLだけで書けるようになる


連番データ生成機のgenerate_series(start, stop, step)
http://d.hatena.ne.jp/poch-7003/20091...


to_charで利用可能なフォーマットの一覧
http://www.postgresql.jp/document/pg8...

（４）補間をしてみよう

足りない行を自動的に埋め合わせてくれる，という意味での「データ補間」をやってみよう。


投稿された記事を管理するための articles というテーブルがあり，

各レコードには新規作成日時として created_at というTIMESTAMP型のカラムがあるとする。

（Ruby on Railsであれば，created_atはしょっちゅう目にするはず）



投稿件数を日別に集計したい場合，下記のようなSQLになるだろう。

--（１）

SELECT
	to_char( created_at, 'YYYY/MM/DD' ) as c,
	count(*)
FROM
	articles
GROUP BY
	c
ORDER BY
	c
;

実行結果の例：

2010/01/02    2
2010/01/03    1
2010/01/05    3
2010/01/08    3
2010/01/09    1

group by created_atだと，日単位で集計されず，マイクロ秒単位で集計されてしまう（TIMESTAMP型だから）。

そこでto_char関数でフォーマットを年月日にすれば，同じ日付のレコードは同じ部分集合に属するようになるから，日単位で集計することができる。

timestamp型のカラムを日付でgroup byしたい
http://blog.goo.ne.jp/rumine/e/3bc25d...


さて，上記の実行結果には「0件」という行が出てこない。

投稿が無かった日は，結果に現れないのだ。


しかし，集計表を作ったり，折れ線グラフを描画する時などには，

「0件」の日も含めて集計したいという場合がある。

（歯抜けのレコードを補間したい，ということ。）


そのためにgenerate_seriesが役立つ。




まず，特定の日から31日間のあいだの全日付は，下記のSQLで取得できる：

--（２）

SELECT
	'2010-01-01'::Date + arr.i
FROM
	generate_series( 0, 30 ) as arr(i)
;

実行結果：

2010-01-01
2010-01-02
2010-01-03
...
...
2010-01-29
2010-01-30
2010-01-31

「::Date」は型変換の演算子。

Type casting
http://www.microolap.com/products/con...


この全日付（２）と，日別の集計結果（１）とを，JOINすればよい。

--（３）：（１）と（２）をJOINしたもの
SELECT
	master_calendar.date1,
	COALESCE( counts_by_date.cnt, 0 ) as cnt
FROM

	(
		SELECT
			to_char( '2010-01-01'::Date + arr.i, 'YYYY/MM/DD' ) as date1
		FROM
			generate_series( 0, 30 ) as arr(i)
	) as master_calendar

	LEFT JOIN

	(
		SELECT
			to_char( created_at, 'YYYY/MM/DD' ) as date2,
			count(*) as cnt
		FROM
			articles
		GROUP BY
			date2
	) as counts_by_date
	
	ON 
	
	master_calendar.date1 = counts_by_date.date2

ORDER BY
	master_calendar.date1
;

実行結果：

2010/01/01    0
2010/01/02    2
2010/01/03    1
2010/01/04    0
2010/01/05    3
...
...
2010/01/30    0
2010/01/31    0


0件の日のレコードが補間されている。

counts_by_dateに存在しない日付の集計結果は，JOIN時にNULLとして計上されるから，

COALESCEを使って，NULLを0に変換している。



DB側でこういった補間を済ませておけば，

集計結果を受け取ったアプリ側は，データの整合性云々に煩わされない。

アクションの振り分けや，画面上での見せ方だけに専念できる。



例えば，下記のグラフ描画ライブラリ（jQuery.flot）を使って，ブラウザ上で折れ線グラフを描画する場合。

JavaScript で，クリックした座標に点を追加できるグラフチャートを描画する方法　（jQuery のプラグイン jquery.sparklines / jquery.flotの使い方)
http://language-and-engineering.hatenablog.jp/entry/20081120/1227114053


日付と，各日付におけるデータ値をこのスクリプトに読み込ませるためには，下記のようなコードを書く。

$.plot(
	$( 描画領域要素 ),
	[
		{
			label : "系列名",
			data  : [
				// 連番, y軸データ値
				[ 0, 0 ],
				[ 1, 2 ],
				[ 2, 0 ],
				[ 3, 1 ],
				
				...
				
				[ 27, 3 ],
				[ 28, 0 ],
				[ 29, 0 ],
				[ 30, 0 ]
			]
		}
	],
	{
		lines : { show : true },
		points : { show : true },
		xaxis : {
			ticks : [
				// 連番, x軸目盛りのラベル
				[ 0, "2010/01/01" ],
				[ 1, "2010/01/02" ],
				[ 2, "2010/01/03" ],
				[ 3, "2010/01/04" ],
				
				...
				
				[ 27, "2010/01/28" ],
				[ 28, "2010/01/29" ],
				[ 29, "2010/01/30" ],
				[ 30, "2010/01/31" ]
			]
		},
		yaxis : { tickDecimals : 0 }
	}
);

ticksとdataのところに，それぞれx軸・y軸の値がくる。
（その部分のコードは，サーバ側でDBを読みながら動的に生成することになるだろう。）


もしDB側で先ほどのように値の補間を済ませておけば，このスクリプト内では，0件の場合の例外処理を気にする必要がない。

そして，日付マスタテーブルみたいなものを用意することなくそれを実現するためには，generate_seriesのお世話になるのだ。

（５）「配列」のようなものを使う

ここまでで，複数行のデータをテーブル無しに動的に生成する方法として，連続数値や連続日付を扱った。

さらに一歩進めて，連続性の無い，任意の並び順のデータを複数行分生成してみよう。



以下のような出力を得たいとする。

ただし，このような値が記録されたテーブルはどこにもない。

a
b
c


ここで，一般的なプログラミング言語での「配列」をイメージしてみよう。


C言語やVBの静的配列の場合，配列を使うときは・・・

  • まず，配列を宣言する。変数名と，配列のサイズを決める。
  • 次に，配列の中に，順番に要素を格納してゆく。

という手順だ。



これと同じ事を，SQLで実現してみよう。


SELECT
	CASE i
		WHEN 1 THEN 'a'
		WHEN 2 THEN 'b'
		WHEN 3 THEN 'c'
	END as value
FROM
	generate_series( 1, 3 ) as arr(i)
;

実行結果：

a
b
c


FROM句の中では，いわば配列の「宣言」を行なっている。

名前はarrで，サイズは3だ。


SELECT句の中では，いわば要素の「格納」を行なっている。

配列の添え字が1, 2, 3のそれぞれの場合について，そこにあるべき要素の内容を指定している。




愚直なコードではあるが，複数行の定数をSQLだけで生成することができた。

※SELECT句の中身を増やせば，複数行かつ複数列のデータもOK。




なお，複数「列」のほうが圧倒的に簡単だ。

SELECT
	'a', 'b', 'c'
;

だけで済むのだから。


それでも，ここまでで考えたように行方向にデータを展開することができれば，

それらの各データはSQLの性質上「別レコード」とみなされるので，使い道が広がる。



※注：
PostgreSQLには「配列型」というれっきとしたデータ型が存在する。
なので，ここで試した複数行のレコードを，真の意味で「配列」と呼ぶことはできない。

PostgreSQLで配列型のカラムを使ってみる
http://d.hatena.ne.jp/pasela/20070301...


※ちなみに，下記のような邪道だがシンプルな方法もある。

SELECT 'a'

    UNION 

SELECT 'b'

    UNION 

SELECT 'c'
;

実行結果：

a
b
c

たしかに複数行だが…。


さらに，汎用性は失われるが，以下のように文字列分解をするという方法も。

SELECT
	regexp_split_to_table( 'a,b,c', ',' )
;

もしくは

SELECT
	regexp_split_to_table( 'abc', '' )
;

実行結果：

a
b
c

カンマ区切りの文字列を複数の行に分割する方法：regexp_split_to_table
http://postgresql.g.hatena.ne.jp/iaki...


（６）generate_seriesでできないこと

「SQLを使って，検索結果に連番をふりたい」というニーズがよくあるが，それはgenerate_seriesではできない。


相関サブクエリを使って，重い処理として実行することはできる：

--usersテーブルから，名前が「山田」であるようなレコードを検索し，行番号付きで表示する
SELECT
	(
		SELECT
			count(*)
		FROM
			users AS u2
		WHERE
			u2.name = '山田'
				AND
			u2.id <= u1.id
	) AS bangou,
	u1.id,
	u1.name
FROM
	users AS u1
WHERE
	u1.name = '山田'
ORDER BY
	bangou
;

実行結果の例：

bangou    id    name
1         3     山田
2         10    山田
3         11    山田
4         25    山田

行番号としてbangouカラムが表示されている。


※bangouでソートすることによって，自動的にidが若い順にソートされる。
なぜなら，bangouの大小は「自idよりも若いidの件数」＝「idの若さ」で決まるから。


ただし，このSQLは重いので実行してはならない。


参考：

累積を計算するSQL（MySQL）
http://blog.xole.net/article.php?id=162


検索結果に対して簡単に行番号をふれない理由は，PostgreSQLに「rowid」が無いからだ。

【PostgreSQL】 OIDとSERIAL
http://www.cisvul.jp/blog/2007/08/ent...

  • OracleのROWIDに相当するのは，postgresではOID
  • デフォルトではOIDは無効になっているため，利用できない。


CTID とは何ですか？
http://www.linux.or.jp/JF/JFdocs/Post...

  • OIDと別に，特定の行を識別するために使われるCTIDというカラムもある。
  • しかし，大小比較とか連番とかの用途で使うカラムではない。

これはアプリ側で対処するしかないだろう。

補足

id:umitanuki様より，本エントリーについての有益な指摘を頂いた。

SETOF関数のあれこれ
http://postgresql.g.hatena.ne.jp/umit...

  • generate_seriesを始めとするSETOF関数言及ページへのリンク
  • 配列型について
  • 8.4〜の情報

などなど


language_and_engineering 読者になる

広告を非表示にする
スポンサーリンク
スポンサーリンク