PostgreSQLでのソートについての質問です。

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

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

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

回答の条件
  • 1人3回まで
  • 登録:2008/05/09 11:15:31
  • 終了:2008/05/10 22:05:24

ベストアンサー

id:b-wind No.2

b-wind回答回数3344ベストアンサー獲得回数4402008/05/09 12:16:44

ポイント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
);

とか。

id:taslam

>ORDER BY 句に CASE 文を使ってソート用の数値に置き換える

よさそうですね。

配列は実行のたびに違うものの可能性があり、固定にすることはできないので、プログラムで動的にCASEを生成することになりますね。CASEは式なので、ORDER BYでも使えます。本当にCASE式は便利です。

>id とその並び順を定義したテーブルを作成し JOIN すればいいかと。

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

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

2008/05/09 15:09:30

その他の回答(1件)

id:chuken_kenkou No.1

chuken_kenkou回答回数722ベストアンサー獲得回数542008/05/09 11:33:04

ポイント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
id:taslam

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

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

やはりそうですよね。

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

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

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

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

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

2008/05/09 12:14:34
id:b-wind No.2

b-wind回答回数3344ベストアンサー獲得回数4402008/05/09 12:16:44ここでベストアンサー

ポイント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
);

とか。

id:taslam

>ORDER BY 句に CASE 文を使ってソート用の数値に置き換える

よさそうですね。

配列は実行のたびに違うものの可能性があり、固定にすることはできないので、プログラムで動的にCASEを生成することになりますね。CASEは式なので、ORDER BYでも使えます。本当にCASE式は便利です。

>id とその並び順を定義したテーブルを作成し JOIN すればいいかと。

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

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

2008/05/09 15:09:30
  • id:taslam
    質問文がわかりにくかったですね。申し訳ないです。

    「idの配列」を使ってソートする、というのは、「idが配列中のどの位置にあるか」でソートという意味です。
    idの配列:3,5,2,1,4...
    なら、結果は
    ----------------
    id|name
    5 |hoge
    1 |fuga
    4 |hogefuga
    ----------------
    というかたちになる、というものです。
  • id:b-wind
    >>id とその並び順を定義したテーブルを作成し JOIN すればいいかと。
    >今回、配列は固定ではないのでこれはできませんね。
    むしろ固定じゃないならこっちのほうがよいかと。
    毎回違うなら、テンポラリテーブルにするだけだし。
  • id:taslam
    >b-wind さん
    テンポラリテーブルで実装してみました。
    INNER JOINを使えば「WHERE id IN ([idの配列])」なんてのは必要なくなりますし一石二鳥ですね。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません