SELECTの結果をソートする際、あらかじめ与えられた配列をみて、その並び順どおりに並べたいとき、どのような方法がありますか？
例えば、
idの配列：3,5,2,1,4...
SELECT * FROM items WHERE id IN ([idの配列]) AND [条件いろいろ] LIMIT 10 OFFSET 0
で結果が得られる場合、さらに「idの配列」を使ってソートする、というものです。（無論LIMIT/OFFSETを使っているのでソートによって抽出されるレコードは変化します）
このような設計の良し悪しは別として、これが実現可能で（ある程度のパフォーマンスを維持できる）方法を考えています。
よろしければ皆様の知恵をお貸しください。
No.2
3344
440
46pt
配列が固定なら ORDER BY 句に CASE 文を使ってソート用の数値に置き換えることで対処可能かと思う。
SELECT * FROM items WHERE ... ORDER BY CASE WHEN id = 3 THEN 1 WHEN id = 5 THEN 2 ... END LIMIT 10 OFFSET 0
試してないから CASE 文は SELECT のフィールドで無いとだめかもしれないけど。
もうちょっと複雑になりそうなら、id とその並び順を定義したテーブルを作成し JOIN すればいいかと。
CREATE TABLE items_order ( item_id integer primary key, sort_order integer not null );
とか。
No.1
72254
24pt
操作性や性能を気にするなら、配列でなく列にソートに使う値を入れた方がいいと思います。
PostgreSQLは、
(1)サブクエリ内でORDER BYの指定が可能
(2)サブクエリ内でLIMIT、OFFSETの指定が可能
です。
SQL例を示せば、以下のような感じです。
SELECT * FROM (SELECT * FROM item WHERE 検索条件 ORDER BY ソートキー1 LIMIT m OFFSET m) as x ORDER BY ソートキー2
サブクエリを使って結果セットに対してソートを行うんですね。参考になります。
>列にソートに使う値を入れた方がいい
やはりそうですよね。
ただ今回の場合、ソートに使いたい値が外から与えられたもので、さらに「値が配列どの位置にあるか」でソートすることになるので、単にソートキーとして列名を与えるだけでは対応できないように思います。
値から配列の添え字を取得する方法などがあれば、それをキーにしてソートできるのですが・・・
わかりにくい質問文で申し訳なかったです。
ただ、今回教えていただいた手法は今後使うことがありそうです。
ありがとうございました。
No.2
3344440
ここでベストアンサー
46pt
配列が固定なら ORDER BY 句に CASE 文を使ってソート用の数値に置き換えることで対処可能かと思う。
SELECT * FROM items WHERE ... ORDER BY CASE WHEN id = 3 THEN 1 WHEN id = 5 THEN 2 ... END LIMIT 10 OFFSET 0
試してないから CASE 文は SELECT のフィールドで無いとだめかもしれないけど。
もうちょっと複雑になりそうなら、id とその並び順を定義したテーブルを作成し JOIN すればいいかと。
CREATE TABLE items_order ( item_id integer primary key, sort_order integer not null );
とか。
>ORDER BY 句に CASE 文を使ってソート用の数値に置き換える
よさそうですね。
配列は実行のたびに違うものの可能性があり、固定にすることはできないので、プログラムで動的にCASEを生成することになりますね。CASEは式なので、ORDER BYでも使えます。本当にCASE式は便利です。
>id とその並び順を定義したテーブルを作成し JOIN すればいいかと。
今回、配列は固定ではないのでこれはできませんね。
※追記 テンポラリテーブルで使えました。
taslam
2008/05/09 12:22:23
1
1
b-wind
2
>ORDER BY 句に CASE 文を使ってソート用の数値に置き換える
よさそうですね。
配列は実行のたびに違うものの可能性があり、固定にすることはできないので、プログラムで動的にCASEを生成することになりますね。CASEは式なので、ORDER BYでも使えます。本当にCASE式は便利です。
>id とその並び順を定義したテーブルを作成し JOIN すればいいかと。
今回、配列は固定ではないのでこれはできませんね。
※追記 テンポラリテーブルで使えました。