人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

PostgreSQLでのソートについての質問です。
SELECTの結果をソートする際、あらかじめ与えられた配列をみて、その並び順どおりに並べたいとき、どのような方法がありますか?

例えば、
idの配列:3,5,2,1,4...
SELECT * FROM items WHERE id IN ([idの配列]) AND [条件いろいろ] LIMIT 10 OFFSET 0
で結果が得られる場合、さらに「idの配列」を使ってソートする、というものです。(無論LIMIT/OFFSETを使っているのでソートによって抽出されるレコードは変化します)

このような設計の良し悪しは別として、これが実現可能で(ある程度のパフォーマンスを維持できる)方法を考えています。
よろしければ皆様の知恵をお貸しください。

●質問者: taslam
●カテゴリ:コンピュータ
✍キーワード:postgreSQL SELECT T/O ソート パフォーマンス
○ 状態 :終了
└ 回答数 : 2/2件

▽最新の回答へ

1 ● chuken_kenkou
●24ポイント

操作性や性能を気にするなら、配列でなく列にソートに使う値を入れた方がいいと思います。

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
◎質問者からの返答

サブクエリを使って結果セットに対してソートを行うんですね。参考になります。

>列にソートに使う値を入れた方がいい

やはりそうですよね。

ただ今回の場合、ソートに使いたい値が外から与えられたもので、さらに「値が配列どの位置にあるか」でソートすることになるので、単にソートキーとして列名を与えるだけでは対応できないように思います。

値から配列の添え字を取得する方法などがあれば、それをキーにしてソートできるのですが・・・

わかりにくい質問文で申し訳なかったです。

ただ、今回教えていただいた手法は今後使うことがありそうです。

ありがとうございました。


2 ● b-wind
●46ポイント ベストアンサー

配列が固定なら 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 すればいいかと。

今回、配列は固定ではないのでこれはできませんね。

※追記 テンポラリテーブルで使えました。

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ