SQLが分からない
2日ほど悩んでる。
おかげで変な技術は身に付いた。
連番生成とか。
例えば
次のような一行だけデータを持つテーブルがあります。データの中身は重要ではありません。
CREATE TABLE OneRow (col1 VARCHAR(8) NOT NULL, col2 VARCHAR(8) NOT NULL, col3 VARCHAR(8) NOT NULL, PRIMARY KEY (col1));INSERT INTO OneRow VALUES('test', 'data', 'col')
問題は、このテーブルから取得したレコードを、次のように3行で表示したい、ということ。col1 col2 col3 seq ==== ==== ==== === test data col 1 test data col 2 test data col 32008-02-07
これのPostgreSQLでのやり方。
PostgreSQL 8からは集合を返す関数が使えるようになっているらしい。
答えその1。
select * from onerow, generate_series(1,3) as seq;
答えその2。三行ぐらいならべた書きでもいい。
select * from onerow, (values(1), (2), (3)) as foo(seq);
valuesってinsert以外でも使えるんだね。
select * from (values(1, 'test'), (2, 'a'), (3, 'b')) as foo(seq, col1); seq | col1 -----+------ 1 | test 2 | a 3 | b (3 行)
で、今悩んでいるSQLはまだ解決していない。
CREATE TABLE sample ( col1 VARCHAR(8) NOT NULL, col2 TIMESTAMP NOT NULL, col3 INT NOT NULL, unique(col1, col2) ); INSERT INTO sample VALUES('A', '2011-05-25', 3); INSERT INTO sample VALUES('A', '2011-05-15', 2); INSERT INTO sample VALUES('A', '2011-05-18', 5); INSERT INTO sample VALUES('B', '2011-05-15', 0); INSERT INTO sample VALUES('B', '2011-05-16', 3); INSERT INTO sample VALUES('B', '2011-05-20', 2);
この表で、col1でグループ化した中でcol2がmaxになる行のcol3の値を全てのcol1分合計し、それを一定間隔のシーケンスで出したい。
select B.day, sum(A.col3) as col3 from sample as A cross join ( select '2011-05-01'::Timestamp + ((days.d || ' days')::interval) as day from generate_series(0, 100) as days(d) ) as B where B.day < '2011-06-01' and B.day >= '2011-05-15' and A.col2 = ( select col2 from sample where col2 <= B.day and col1 = A.col1 -- (※) order by col2 desc limit 1 ) group by B.day order by B.day ; day | col3 ---------------------+------ 2011-05-15 00:00:00 | 2 2011-05-16 00:00:00 | 5 2011-05-17 00:00:00 | 5 2011-05-18 00:00:00 | 8 2011-05-19 00:00:00 | 8 2011-05-20 00:00:00 | 7 2011-05-21 00:00:00 | 7 2011-05-22 00:00:00 | 7 2011-05-23 00:00:00 | 7 2011-05-24 00:00:00 | 7 2011-05-25 00:00:00 | 5 2011-05-26 00:00:00 | 5 2011-05-27 00:00:00 | 5 2011-05-28 00:00:00 | 5 2011-05-29 00:00:00 | 5 2011-05-30 00:00:00 | 5 2011-05-31 00:00:00 | 5 (17 行)
これで一応目的の結果が得られているのだが、(※)の箇所の相関サブクエリが良くない。
この方法だと結合時に {sampleの行数 * whereサブクエリの行数} のコストがかかる。
この表は次々にデータが溜まっていくが、col1の種類は滅多に増えない。
サブクエリの中はcol1でグループ化したいだけなので、{col1の種類数 * whereサブクエリの行数}のコストに抑えたい。
しかし方法が思い付かず…。
cross join で 'A', 'B' のデータだけ持つテーブルを結合しても、クロス結合されている別のテーブルのカラムはwhere句から参照できないらしい。
Head First SQL: atama to karada de oboeru esukyūeru no kihon - Google ブックス
この辺?本あったかなぁ…。