ログ日記

作業ログと日記とメモ

トップ > SQL > SQLが分からない

SQLが分からない

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    3
2008-02-07

これのPostgreSQLでのやり方。
PostgreSQL 8からは集合を返す関数が使えるようになっているらしい。


答えその１。

select * from onerow, generate_series(1,3) as seq;

答えその２。三行ぐらいならべた書きでもいい。

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 ブックス
この辺？本あったかなぁ…。