- PR -

SQLにおいてグループの代表リストを作成する。

1
投稿者投稿内容
有末　清華
ベテラン
会議室デビュー日: 2006/10/09
投稿数: 52
お住まい・勤務地: 北海道
投稿日時: 2008-12-20 21:55
こんにちは、現在物件の検索システムを作っているものです。

やりたいことは以下の手順です。
1. roomテーブルをnameやprice,addressで並び替え
2. roomテーブルをproperty_idによってグループ化
3. 並び替えのときに各グループの先頭に来たもの（最安や「あ〜」から始まる名前等）をそのグループの代表として抽出

テーブルの構造は簡素化すると以下のようなものです

コード:
---room---------------------------------------------------
id      property_id     name        price       address
 0      0               ABCDE       10000       AAAAAAA
 1      0               BCDEF       9000        BBBBBBB
 2      1               CDEFG       20000       AAAABBB
 3      2               AABBC       100         ZZZZZZZ
------------------------------------------------------------------


これを例えばproperty_id,priceで並べ替えて
コード:
---room---------------------------------------------------
id      property_id     name        price       address
 3      2               AABBC       100         ZZZZZZZ
 1      0               BCDEF       9000        BBBBBBB
 0      0               ABCDE       10000       AAAAAAA
 2      1               CDEFG       20000       AAAABBB
------------------------------------------------------------------

みたいなテーブルをproperty_idが同一のものは並び替えが一番上のだけ取得して…
コード:
---room---------------------------------------------------
id      property_id     name        price       address
 3      2               AABBC       100         ZZZZZZZ
 1      0               BCDEF       9000        BBBBBBB
 2      1               CDEFG       20000       AAAABBB
------------------------------------------------------------------

みたいなリストを作りたいわけです。

priceだけならばmin()を使えばできそうですが（できるのかな？）addressやnameがあるのでお手上げです…orz


どうにかこれを実行できないものでしょうか？言語はMySQL か SQLiteです（正確にはpython + SQLAlchemy + MySQL/SQLite）

よろしくお願いします。

_________________
有末　清華
crazy(){for;;{you();}} - プログラマの覚書
べる
ぬし
会議室デビュー日: 2003/09/20
投稿数: 1093
投稿日時: 2008-12-21 00:21
並び替えの基準がよくわかりませんが、サブクエリが使えるバージョンなら
コード:
SELECT            room1.*
FROM              room AS room1 INNER JOIN
 (SELECT            property_id, MIN(price) AS minprice
  FROM              room
  GROUP BY       property_id) AS minroom 
  ON minroom.property_id = room1.property_id 
   AND minroom.minprice = room1.price
ただし、同じproperty_idで最小のpriceをもつレコードが複数ある場合は複数表示されます。
忠犬
大ベテラン
会議室デビュー日: 2006/05/01
投稿数: 109
投稿日時: 2008-12-21 00:36
引用:

どうにかこれを実行できないものでしょうか？言語はMySQL か SQLiteです（正確にはpython + SQLAlchemy + MySQL/SQLite）


MySQLは、
4.1・・・unicode、サブクエリなどの実装
5.0・・・ビュー、ストアドプロシジャ、トリガなどの実装
と、バージョンにより大きな機能差があったり、一部の仕様変更があります。

SQLiteも頻繁には開発は行われていないようですが、バージョンによる機能差は少なくないようです。
具体的なアドバイスを得たいなら、具体的な質問にする必要があります。
King
ぬし
会議室デビュー日: 2008/06/20
投稿数: 284
投稿日時: 2008-12-21 15:32
まずグルーピングしたい対象を考えて、
その中で取得したいレコードのプライマリキーを取得する事を考えて、
それが出来たらそのプライマリキーのレコードを取得する事を考えてみては？

ただ色々と情報が少ないです。
プライマリキーはあるのか。
各グループで比較材料の列が同一の場合はないのか。
「これを例えばproperty_id,priceで並べ替えて」（昇順？）
となっている所の例は「property_id」と「price」で実際は並んでいないのはなぜか。
有末　清華
ベテラン
会議室デビュー日: 2006/10/09
投稿数: 52
お住まい・勤務地: 北海道
投稿日時: 2008-12-21 17:31
すみません、生でSQL自体をいじることがないので調べながらご指摘いただいたことを試したりしてると返信が遅くなりました。

>べるさん
SQLiteで実行してみて動作確認ができました。以下が実行結果です。（同じような疑問持つ人いないと思うけど、一応…）
-------------------------------------------------------------------------------------------------------------
SQLite version 2.8.17
Enter ".help" for instructions
sqlite> CREATE TABLE room (id INT NOT NULL, property_id INT, name VARCHAR(32), price INT, address VARCHAR(255), PRIMARY KEY(id));
sqlite> INSERT INTO room (id, property_id, name, price, address) VALUES (0, 0, "ABCDE", 10000, "AAAAAAA");
sqlite> INSERT INTO room (id, property_id, name, price, address) VALUES (1, 0, "BCDEF", 9000, "BBBBBBB");
sqlite> INSERT INTO room (id, property_id, name, price, address) VALUES (2, 1, "CDEFG", 20000, "AAAABBBB");
sqlite> INSERT INTO room (id, property_id, name, price, address) VALUES (3, 2, "AABBC", 100, "ZZZZZZZ");
sqlite> CREATE VIEW minroom AS SELECT property_id, MIN(price) AS minprice FROM room GROUP BY property_id;
sqlite> SELECT room.* FROM room INNER JOIN minroom ON minroom.property_id = room.property_id AND minroom.minprice = room.price;
1|0|BCDEF|9000|BBBBBBB
2|1|CDEFG|20000|AAAABBBB
3|2|AABBC|100|ZZZZZZZ
sqlite> CREATE VIEW minroom2 AS SELECT property_id, MIN(address) AS minaddress FROM room GROUP BY property_id;
sqlite> SELECT room.* FROM room INNER JOIN minroom2 ON minroom2.property_id = room.property_id AND minroom2.minaddress = room.address;
0|0|ABCDE|10000|AAAAAAA
2|1|CDEFG|20000|AAAABBBB
3|2|AABBC|100|ZZZZZZZ
-----------------------------------------------------------------------------------------------------------
VIEW作った方が個人的にわかりやすかったので作りました。

＞ただし、同じproperty_idで最小のpriceをもつレコードが複数ある場合は複数表示されます。
ってのが困りますが、'minroom'や'minroom2'でdistinctすればOKですな（またはGROUP BYかな？）

助かりました、ってか min() については数値のサンプルしかなかったんで数値しかできないものだと思っていたorz


>忠犬さん
えっと、どのバージョンを使用するかは得に決まってないんですよね（汗。 ORM使って書いているんで、MySQL特有やSQLite特有のコードじゃダメだったんで…MySQLに関しては最終的に使用する予定というだけで環境構築もしてない状態です（汗。



＞Kingさん
＞「property_id」と「price」で実際は並んでいないのはなぜか。
priceが第一、property_idが第２で並び替えという意味でした、言葉たらずですいません（汗。



べるさんが教えてくれたコードをSQLAlchemyを使って書いてみようと思います。書け次第後世のため（必要か微妙だけど）SQLAlchemyのコードも書きます。

お答えくださったみなさん。SQLをちゃんと理解していない＆言葉たらずの僕に心やさしい返信本当にありがとうございます^_^どうも上手に説明できなくて……
本当に助かりました、ありがとうございました。
1

スキルアップ／キャリアアップ（JOB@IT）