- 有末 清華
- ベテラン
- 会議室デビュー日: 2006/10/09
- 投稿数: 52
- お住まい・勤務地: 北海道
|
投稿日時: 2009-03-04 13:37
【環境】
OS: Ubuntu 8.10 Destop
DB: sqlite3
こんにちは、物件の検索サイトを作っているものです。先日まで処理能力が劣るPCを使っていたため約1000件のデータで行っていたんですが実際の運用のテストということで（これも少なめなんですが）10万件のランダムデータを作成して処理を行ったところあまりにもレスポンスが遅すぎ、不思議に思ってsqlite3のCUIでいろいろと処理で行っているsqlを打ってみました。すると不思議なことに処理可能なSQLを組み合わせただけのSQLで処理が先に進まなくなりました（正確にはsqlite3で処理を実行させても何の反応もしなくなる）
下記に書いてあるのが問題の構造・文なのですが原因が特定できません（汗。よろしければお力添えをお願い致します。
※ ちなみに property[100], room[1000] のデータでは実行できてました（汗。
【データーベース構造 - 一部省略】
@property
+ id - Integer, Primary Key
@room
+ id - Integer, Primary Key
+ property_id - Integer, Foreign Key
@room_cost
+ room_id - Integer, Primary Key, Foreign Key
+ house_rent - Integer
＜視覚的には＞
[property]
* [room]
+ [room_cost]
* - 複数所持, + 一つだけ所持
【実行できるSQL文 - 1秒もかからず処理結果が表示され始める】
SELECT count() FROM room;
- 100000
SELECT count() FROM property;
- 10000
SELECT * FROM room JOIN room_cost ON room.id = room_cost.room_id;
- 略
SELECT * FROM room JOIN property ON room.property_id = property.id;
- 略
SELECT room.id as id, room.property_id as property_id, min(room_cost.house_rent) AS minprice
FROM room JOIN room_cost ON room.id = room_cost.room_id GROUP BY room.property_id ORDER BY minprice, property_id;
- 略
【問題のSQL文】
SELECT *
FROM room JOIN room_cost ON room.id = room_cost.room_id JOIN (SELECT room.id as id, room.property_id as property_id, min(room_cost.house_rent) AS minprice
FROM room JOIN room_cost ON room.id = room_cost.room_id GROUP BY room.property_id ORDER BY minprice, property_id) AS anon_1 ON room.property_id = anon_1.property_id AND room_cost.house_rent = anon_1.minprice GROUP BY room.property_id;
＜上のSQLでやりたいこと＞
各 property に属する room の中で最安のものだけを取得
最初の部分のsqlはホントは単純に全部取得なわけじゃなく、条件によって絞り込んであるため、このような形をとっている
_________________
[ メッセージ編集済み 編集者: 有末 清華 編集日時 2009-03-04 13:42 ]
|
|---|
- デューン
- 大ベテラン
- 会議室デビュー日: 2004/04/21
- 投稿数: 174
- お住まい・勤務地: Tokyo
|
投稿日時: 2009-03-04 14:22
| 引用: |
|
SELECT room.id as id, room.property_id as property_id, min(room_cost.house_rent) AS minprice
FROM room JOIN room_cost ON room.id = room_cost.room_id GROUP BY room.property_id ORDER BY minprice, property_id;
|
これのroom.id as idって何が取れるんですか？
Group Byにいない様ですが。
|
|---|
- 有末 清華
- ベテラン
- 会議室デビュー日: 2006/10/09
- 投稿数: 52
- お住まい・勤務地: 北海道
|
投稿日時: 2009-03-04 14:58
＞デューン様
room と room_cost の接続のためにコラムとして使ってま…す…？
申し訳ありません、質問の意図をしっかりと理解していないので的外れな解答になっているかもしれません（汗
|
|---|
- King
- ぬし
- 会議室デビュー日: 2008/06/20
- 投稿数: 284
|
投稿日時: 2009-03-04 15:11
| コード: |
|
SELECT
room.id as id,
room.property_id as property_id,
min(room_cost.house_rent) AS minprice
FROM
room JOIN room_cost ON room.id = room_cost.room_id
GROUP BY
room.property_id
ORDER BY
minprice,
property_id
|
質問の意図は上記の JOIN している SQL の中で
SELECT で３つの項目を取得していますが
集計関数をかましてる room_cost.house_rent は良いとして
room.id と room.property_id は両方が GROUP BY 句の中に無いと
エラーになるけどどうなの？
って事ではないでしょうか。
|
|---|
- デューン
- 大ベテラン
- 会議室デビュー日: 2004/04/21
- 投稿数: 174
- お住まい・勤務地: Tokyo
|
投稿日時: 2009-03-04 15:44
Kingさん、補足ありがとうございます。
| 引用: |
|
Kingさんの書き込み (2009-03-04 15:11) より:
room.id と room.property_id は両方が GROUP BY 句の中に無いと
エラーになるけどどうなの？
って事ではないでしょうか。
|
Kingさんに補足していただいた通りです。
SQLite3で試してみたところ、エラーが起きなかったんですが、
ならば何がとれているのか(そして何がとりたいか)が気になるところです。
|
|---|
- 有末 清華
- ベテラン
- 会議室デビュー日: 2006/10/09
- 投稿数: 52
- お住まい・勤務地: 北海道
|
投稿日時: 2009-03-04 16:02
＞King様・デューン様
デューン様がおっしゃっているように sqlite3 ではエラーにならなかったので全然気がつきませんでした（汗。
propertyにはいくつかのroomが存在しています（一つの物件には部屋がいくつもある）そのroomの中でもっとも安い物をそのpropertyの代表として抽出したいのです。たとえば以下のような表なら…
id, property_id, house_rent
-----------------------------
1, 1, 10000
2, 1, 20000
3, 2, 20000
4, 2, 15000
-----------------------------
ここから安い物を代表として抽出
id, property_id, house_rent
-----------------------------
1, 1, 10000
4, 2, 15000
-----------------------------
何で property_id でグループ化して…という作業を行っています。
|
|---|
- King
- ぬし
- 会議室デビュー日: 2008/06/20
- 投稿数: 284
|
投稿日時: 2009-03-04 16:42
実際に
| 引用: |
| d, property_id, house_rent
-----------------------------
1, 1, 10000
2, 1, 20000
3, 2, 20000
4, 2, 15000
-----------------------------
|
に対して
| コード: |
|
SELECT
room.id as id,
room.property_id as property_id,
min(room_cost.house_rent) AS minprice
FROM
room JOIN room_cost ON room.id = room_cost.room_id
GROUP BY
room.property_id
ORDER BY
minprice,
property_id
|
という SQL を実行すると
| 引用: |
|
id, property_id, house_rent
-----------------------------
1, 1, 10000
4, 2, 15000
-----------------------------
|
というレコードがエラーも出ずに抽出できているのですか？
|
|---|
- 有末 清華
- ベテラン
- 会議室デビュー日: 2006/10/09
- 投稿数: 52
- お住まい・勤務地: 北海道
|
投稿日時: 2009-03-04 18:08
>King様
以下、実行結果全文です。問題なくできているように見えますが・・・
（property 100, room 1000件のデータでの実行結果）
| コード: |
|
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> SELECT room.id as id, room.property_id as property_id, min(room_cost.house_rent) as minprice FROM room JOIN room_cost on room.id = room_cost.room_id GROUP BY room.property_id ORDER BY minprice, property_id;
940|2|20000
928|3|20000
891|4|20000
953|5|20000
960|6|20000
961|7|20000
968|8|20000
982|10|20000
970|12|20000
967|15|20000
875|16|20000
878|17|20000
830|18|20000
803|23|20000
826|24|20000
945|28|20000
952|30|20000
870|32|20000
807|34|20000
911|37|20000
951|39|20000
688|41|20000
914|42|20000
964|43|20000
963|44|20000
897|45|20000
866|46|20000
852|47|20000
980|48|20000
993|49|20000
979|51|20000
999|52|20000
716|53|20000
991|54|20000
934|56|20000
912|57|20000
997|61|20000
910|65|20000
919|67|20000
937|69|20000
938|70|20000
994|71|20000
877|72|20000
689|73|20000
924|74|20000
828|79|20000
966|80|20000
962|81|20000
799|83|20000
884|84|20000
931|85|20000
973|88|20000
981|89|20000
735|90|20000
864|95|20000
901|97|20000
722|99|20000
974|100|20000
976|1|25000
965|11|25000
736|14|25000
1000|20|25000
838|22|25000
935|27|25000
936|29|25000
948|35|25000
969|36|25000
983|38|25000
956|50|25000
791|55|25000
797|59|25000
984|60|25000
903|63|25000
946|64|25000
995|66|25000
988|68|25000
959|75|25000
996|77|25000
743|78|25000
851|82|25000
734|86|25000
925|91|25000
644|92|25000
837|13|30000
615|19|30000
894|25|30000
986|26|30000
879|40|30000
920|62|30000
819|76|30000
777|87|30000
977|93|30000
990|98|30000
859|9|35000
987|31|35000
998|33|35000
950|94|35000
881|21|40000
992|96|40000
779|58|45000
sqlite>
|
見にくいので、property_idだけで並べ替えたデータは
| コード: |
|
976|1|25000
940|2|20000
928|3|20000
891|4|20000
953|5|20000
960|6|20000
961|7|20000
968|8|20000
859|9|35000
982|10|20000
965|11|25000
970|12|20000
837|13|30000
736|14|25000
967|15|20000
875|16|20000
878|17|20000
830|18|20000
615|19|30000
1000|20|25000
881|21|40000
838|22|25000
803|23|20000
826|24|20000
894|25|30000
986|26|30000
935|27|25000
945|28|20000
936|29|25000
952|30|20000
987|31|35000
870|32|20000
998|33|35000
807|34|20000
948|35|25000
969|36|25000
911|37|20000
983|38|25000
951|39|20000
879|40|30000
688|41|20000
914|42|20000
964|43|20000
963|44|20000
897|45|20000
866|46|20000
852|47|20000
980|48|20000
993|49|20000
956|50|25000
979|51|20000
999|52|20000
716|53|20000
991|54|20000
791|55|25000
934|56|20000
912|57|20000
779|58|45000
797|59|25000
984|60|25000
997|61|20000
920|62|30000
903|63|25000
946|64|25000
910|65|20000
995|66|25000
919|67|20000
988|68|25000
937|69|20000
938|70|20000
994|71|20000
877|72|20000
689|73|20000
924|74|20000
959|75|25000
819|76|30000
996|77|25000
743|78|25000
828|79|20000
966|80|20000
962|81|20000
851|82|25000
799|83|20000
884|84|20000
931|85|20000
734|86|25000
777|87|30000
973|88|20000
981|89|20000
735|90|20000
925|91|25000
644|92|25000
977|93|30000
950|94|35000
864|95|20000
992|96|40000
901|97|20000
990|98|30000
722|99|20000
974|100|20000
|
です。ちゃんと100件。
|
|---|
