[pgsql-jp: 37605] Re: 1:n から 1:1 を抽出するSQL文

[Toshihiro Kano]

かのうです。

> select X, Y from A, B
> where I=K and J in (select max(J) from B group by K ) ;
> として、解決しましたが、かのうさんのかかれたもので、最後の where
> bq,k=tb.k ですが、これのほうが効率的でしょうか？

> select ta.x,tb.y
> from tb inner join ta
> on tb.k=ta.i
> where tb.j=
> (select max(bq.j)
> from tb as bq
> where bq.k=tb.k);

以下のことは、PostgreSQLのプランナの動作を把握した上での
記載ではないことをご了承願います。

副問合せは、上手く使わないと性能が著しく劣化します。
副問合せとして単純にSELECT文を書くと、まずそのSELECT文を実
行してから、親の問合せを実行する手順になりそうな気がします。
ですので、テーブルB を一回アクセスしきった後に、再度JOIN演
算でアクセスすることになりそうで、二度手間の可能性がありま
す。

副問合せを、外のSELECT文と結んでやる（where bq.k=tb.k）と、
テーブルB のアクセスは、親の問合せから引継いだ範囲に限られ
ますから、バッファヒットの可能性が高くなります。また、商用
のDBMS等を用いた大規模なデータベースで、パーティション化さ
れている場合で、アクセスをパーティションの範囲に限定させる
等、分散を考慮する場合は、上記のような仕掛けが必要になりま
す。

もちろん、適切にインデックスが設定されている上での話です。

逆にテーブルB が全てバッファリング出来るサイズであれば、物
理IOに差異はないでしょうから、気にする必要はないと思います。

いずれにしても、それ相応の行数があり、統計情報を最新化した
上で、ANALYZE した結果でどちらのアクセスパスがコストが小さ
いか見なければ、全て想像の範囲の話です。


> ところで、sum()はマニュアルの例にありましたが、max()などはマニュアルのど
> こかに書かれているのでしょうか？

MAX関数は、集約関数の1つです。標準SQLですので、他のRDBMSで
も使えます。PostgreSQLも下記に記載がありますよ。
http://www.postgresql.jp/document/pg815doc/html/tutorial-agg.html