PostgreSQLについての質問です、 Select *** order by でソートを行った時の行番号をSelectしたカラムにUpdateしたいのですがどのようにすれば良いのでしょうか?ご教授おねがいします。

回答の条件
  • URL必須
  • 1人2回まで
  • 登録:2006/04/19 13:50:50
  • 終了:2006/04/26 13:55:02

回答(2件)

id:taknt No.1

きゃづみぃ回答回数13539ベストアンサー獲得回数11982006/04/19 14:56:49

ポイント35pt

http://www.thinkit.co.jp/free/marugoto/2/1/22/1.html

offsetの値をセットするようにしたらいかがでしょうか?

id:disca

回答ありがとうございます。具体的に行いたいことは、、、

1. select num_row, * from table;

num_row 列には、 ソート順の値0から始まる数字が入る。

2. update table num_row ???

次にテーブルのフィールドである num_row を更新する。

プログラム言語はPHPで考えています。できるだけPHPを使わない形で実装したいのですが、ご教授おねがいします。

2006/04/19 17:49:43
id:bonlife No.2

回答回数421ベストアンサー獲得回数752006/04/19 17:47:44

ポイント35pt

OFFSETはSELECTの対象には出来なかったはずなので、そう簡単にはUPDATEの値として利用することは出来ないと思います。

上記にORDER BYした後の行番号を表示させる方法が載っています。

一時的にSEQUENCEを作成し、その内容を表示させています。

CREATE TEMP SEQUENCE seq_rank; 
SELECT NEXTVAL('seq_rank'), member_name, point 
  FROM (SELECT member_name, point 
        FROM member_score 
        ORDER BY point DESC LIMIT 10) member_rank; 
DROP SEQUENCE seq_rank; 

MySQLであればUPDATEにてORDER BYの指定が可能なので、上記の方法を応用してUPDATEが可能だと思うのですが、PostgreSQLではUPDATEにORDER BYの指定はできません。

そこでORDER BY後の行番号を付与したものを別テーブルとして作成し、元々のテーブルを削除した後、ALTER TABLEでRENAMEする方法を考えてみました。

テーブルのサイズや用途にもよると思いますが、一時的にテーブルが消えることになりますので、バッチ処理でサービス停止時間外に行った方が良いでしょう。

CREATE TEMP SEQUENCE tmp_seq_rank;
CREATE TEMP TABLE sample01_ordered
AS ( SELECT col1, NEXTVAL('tmp_seq_rank') AS rank
     FROM ( SELECT col1
            FROM sample01 ORDER BY col1) AS tmp);
DROP SEQUENCE tmp_seq_rank;
ALTER TABLE sample01 RENAME TO sample01_bkup;
ALTER TABLE sample01_ordered RENAME TO sample01;
DROP TABLE sample01_bkup;

上記の例ではcol1、rankという2つの項目しか存在しないsample01というテーブルを想定しています。

col1の値でソート(ORDER BY)した行番号をrankに設定するサンプルです。

テーブルの構造に合わせてSQLを変更してください。

また、INDEXなどについては考慮できておりませんので、上記の内容のままではパフォーマンスの問題が発生する可能性があります。

必要に応じて、適宜修正してください。

参考になると幸いです。

以下、補足です。

特定の列によってソートした並び、というものはINSERT、UPDATEの度に変更される可能性があります。

場合によっては全レコードをUPDATEすることになり、DBの負荷も高くなることが懸念されます。

どういった目的で利用されるテーブルなのか分からないのでなんとも言えませんが、変更される頻度が高く、影響範囲が大きいもので、別の項目から算出可能な項目はDBに持たせないでプログラムで算出した方が良いと思います。

プログラム側でソートした結果を配列などに格納し、表示の際に行番号を出力させる、といったような対応の方が簡単で負荷も小さいと思います。

コメントはまだありません

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

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

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

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