SQL備忘録、ソートで先頭行の出力
一般的なソートのSQL
select key,num,value from table order by key,num;
で同一のkeyがある場合に先頭の行だけを出力したいときの式。
select key,num,value from table a
inner join
(select key,min(num) from table group by key) b
on a.key=b.key and a.num=b.num;
とやればまあできる。だけどこれの嫌いなところは副問い合わせに対して
内部結合をしているところ。
副問合だからINDEXはないし、件数が多くなればおそらく返ってこなくなる。
でいろいろ調べたところ最近のRDBではWindow関数なるものがあるらしい。
分析関数ともいうらしい。
そいつを使って書き直してみる。
select key,num,value from (
select
row_number() over(partition by key order by num) row_num,
key,num,value
from table
) where row_num=1;
これで同一キーの先頭だけを取り出すことができる。
副問合せはあるが、結合ではないのでパフォーマンスの低下はないはず。
これは覚えておくべし！！
自宅の環境で試したところ、まずMySQLはダメ。Window関数がない。
CentOSでPostgreSQLでやったが、これもダメ。バージョンが古すぎ。
そこでPostgreSQL9.2をCentOSに入れなおして実施。
やっとできた。
仕事場のOracleでは当然OK。
あとたぶんSQLServerやDB2でもいける（と思う）。
典型的なキーブレイクのロジックなのに、SQL単独では単純にはできないのが
不思議だった。やっと最近の実装でできるようになったとは意外な気がする。
もっともPLSQLやPGSQLなどの付属のスクリプト言語を使えば全く一般のプログラムと
同じようにキーブレイクのロジックが書けるから問題なかったのかもしれない。
select key,num,value from table order by key,num;
で同一のkeyがある場合に先頭の行だけを出力したいときの式。
select key,num,value from table a
inner join
(select key,min(num) from table group by key) b
on a.key=b.key and a.num=b.num;
とやればまあできる。だけどこれの嫌いなところは副問い合わせに対して
内部結合をしているところ。
副問合だからINDEXはないし、件数が多くなればおそらく返ってこなくなる。
でいろいろ調べたところ最近のRDBではWindow関数なるものがあるらしい。
分析関数ともいうらしい。
そいつを使って書き直してみる。
select key,num,value from (
select
row_number() over(partition by key order by num) row_num,
key,num,value
from table
) where row_num=1;
これで同一キーの先頭だけを取り出すことができる。
副問合せはあるが、結合ではないのでパフォーマンスの低下はないはず。
これは覚えておくべし！！
自宅の環境で試したところ、まずMySQLはダメ。Window関数がない。
CentOSでPostgreSQLでやったが、これもダメ。バージョンが古すぎ。
そこでPostgreSQL9.2をCentOSに入れなおして実施。
やっとできた。
仕事場のOracleでは当然OK。
あとたぶんSQLServerやDB2でもいける（と思う）。
典型的なキーブレイクのロジックなのに、SQL単独では単純にはできないのが
不思議だった。やっと最近の実装でできるようになったとは意外な気がする。
もっともPLSQLやPGSQLなどの付属のスクリプト言語を使えば全く一般のプログラムと
同じようにキーブレイクのロジックが書けるから問題なかったのかもしれない。
