- PR -

10万件のデータでmin()関数を使ったときに実行されない

«前のページへ 1|2|3 次のページへ»
投稿者投稿内容
忠犬
大ベテラン
会議室デビュー日: 2006/05/01
投稿数: 109
投稿日時: 2009-03-04 18:25
横から少し失礼。

SQLiteの独自仕様は、MySQLの独自仕様と似た部分があります。

MySQLでは、

「group byでグループ化することで一意になるなら、group byに指定していない列も指定可能。ただし、エラーにしないけど、一意にならないなら結果は保証しない」

という、誤りを生む仕様となっています。（MySQL 5.0以降は、この仕様を無効化するオプションあり）

http://dev.mysql.com/doc/refman/4.1/ja/group-by-hidden-fields.html?ff=nopfpls


私自身は、SQLiteでこの部分を明確にしたドキュメントは見つけていませんが、同じようになっているのかも。
忠犬
大ベテラン
会議室デビュー日: 2006/05/01
投稿数: 109
投稿日時: 2009-03-04 18:28
引用:

group byでグループ化することで一意になるなら、group byに指定していない列も指定可能


↓　「selectで」が抜けていました。

引用:

group byでグループ化することで一意になるなら、group byに指定していない列もselectで指定可能

デューン
大ベテラン
会議室デビュー日: 2004/04/21
投稿数: 174
お住まい・勤務地: Tokyo
投稿日時: 2009-03-04 19:06
とりあえず「100件ある」ことは「最小値のroom.idがとれてる」ことにはなりません。

外部キーは設定してませんが
コード:

select * 

    from room, room_cost 

    where room.id = room_cost.room_id

    

room.id room.property_id    room_cost.room_id   room_cost.house_rent

---------------------------------------------------------------------

1       1                   1                   10000

2       1                   2                   20000

3       2                   3                   20000

4       2                   4                   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



id  property_id minprice

---------------------------------------------------------------------

1   1           10000

3   2           15000

と、なりました。

# あくまでうちのsqliteでは、ですけど。

忠犬さんが示してくれていますが、
MySQLと同じように「group byで一意になるなら」自動で選択してくれる機能であるとすれば、
今回はそのケースにはあてはまらないですよね。
(group by property_idとgroup by room.id, property_idとでは結果が違う)


# なんでとまっちゃうかというのにも関係してる・・・とは断言できないのですが＾＾；

[ メッセージ編集済み 編集者: デューン 編集日時 2009-03-04 19:12 ]
有末　清華
ベテラン
会議室デビュー日: 2006/10/09
投稿数: 52
お住まい・勤務地: 北海道
投稿日時: 2009-03-04 20:16
>忠犬様
やはり「誤った」仕様だったんですね（汗。これは修正するしかなさそうですね…

>デューン様
げげ、大変な結果になってますね（汗。

このSQLを作るまでに結構かかったんですが、動かない（or誤った）SQLなら修正しなければなりませんね（汗。
僕の持てる知識総動員で出した答えが「誤り」だったのは悲しいですが、他の方法を模索します。よろしければヒントをいただけないでしょうか？

（新しいSQLを組み立てた後「動かない」の現象が治っていることを祈って取り合えず新しいSQLの組み立てにシフトします。治らなかったら・・・汗）
_________________
有末　清華
crazy(){for;;{you();}} - プログラマの覚書
すなめり
常連さん
会議室デビュー日: 2003/01/29
投稿数: 37
お住まい・勤務地: 横浜
投稿日時: 2009-03-04 22:44
内側のselectの、今話題になっているroom.idって、どこにも使っていないんじゃないですか？
room.idは外してしまってもいいような気がします。

で。結果が戻ってこない理由ですが、予測では、
room_cost.house_rent = anon_1.minprice
のところが、どちらもインデックス無しになってるからではないかと。
anon_1の件数が1万件、room_costが10万件なので、最悪の状況だと10万×1万=10億の
比較が発生します。
その前のproperty_idの比較で絞り込めているといいんですが。
とりあえず、room_cost.house_rentにインデックスを張ってみては？

あと、こういう使い方をするのであれば、roomテーブルとroom_costテーブルは
一つに纏めてしまった方が良さそうな気がします。
一部屋に複数の値段がつく可能性があるなら分けるべきですけど。

戻ってくる結果の数は、必ずしもpropertyテーブルの件数とイコールにはなりませんよね？
複数の部屋が同じ値段である場合は、複数の結果が戻ってくる筈です。

他に気になる点としては、内側のselectにorder byしているところ。
JOINの時に高速化に効くならいいのですけど、そうでなければソートするだけ無駄なので、外してしまってもいいのではないかと。
デューン
大ベテラン
会議室デビュー日: 2004/04/21
投稿数: 174
お住まい・勤務地: Tokyo
投稿日時: 2009-03-05 00:22
引用:

すなめりさんの書き込み (2009-03-04 22:44) より:
内側のselectの、今話題になっているroom.idって、どこにも使っていないんじゃないですか？


自分も最初はそう思ってた(property_idと価格の最少が拾えればいいのかと思っていた)んですが、
組んでいるSQLを見ていると取得したいのは
「property毎の最小の価格を持つroom.id」が欲しいような気がしています。

列は省略されているのでどのテーブルにどの情報が入っているのかはわからないままですが。

room.idから価格は再取得できますが、価格からもproperty_idからもroom.idは再取得できないからっていうのがそう感じた一番の理由なんですけどね。


ヒントですか。
もっといい手があるかもしれませんが、自分が今思いつくのでは、自己結合でしょうか。
(コスト的にどうかは考えてません)

select room_id,house_rent,(select count(house_rent) from room_cost B where B.house_rent < A.house_rent ) from room_cost A
とすると、room_costテーブルのランク付けができます。

select room_id,house_rent,(select count(house_rent) from room_cost B where B.house_rent < A.house_rent and B.room_id = A.room_id ) from room_cost A
とすると、room_idごとのランク付けができます。
（もちろん、room_idは重複しませんから意味ないですけど、ヒントですので。）
有末　清華
ベテラン
会議室デビュー日: 2006/10/09
投稿数: 52
お住まい・勤務地: 北海道
投稿日時: 2009-03-05 01:50
>すなめり様
room_cost.house_rentにインデックスを振ってみても結果は変わりませんでした（Core 2 Duo 3GHzだからCPUのせいにはできないですね涙）

引用:

あと、こういう使い方をするのであれば、roomテーブルとroom_costテーブルは
一つに纏めてしまった方が良さそうな気がします。
一部屋に複数の値段がつく可能性があるなら分けるべきですけど。

かなり初期のころ Cost の内容をオブジェクト思考で考えてまとめていたんです。その構造が今にも引き継がれていて…

引用:

他に気になる点としては、内側のselectにorder byしているところ。
JOINの時に高速化に効くならいいのですけど、そうでなければソートするだけ無駄なので、外してしまってもいいのではないかと。

実際の処理ではやっていません。実験的に見やすくするように書いた奴を組み合わせたりしているうちにここにもそれをそのまま書いてしまっただけなんです

＞デューン様
引用:

自分も最初はそう思ってた(property_idと価格の最少が拾えればいいのかと思っていた)んですが、
組んでいるSQLを見ていると取得したいのは
「property毎の最小の価格を持つroom.id」が欲しいような気がしています。

まさにその通りです。欲しいのは「各物件内の最安」の部屋情報なので

ランク付けという作業は始めて聞きました。行っている処理はSQL文からはよくわからないのですが、実行結果から推測するに「house_rentで並び替えて同じ値段の物の順位は一緒にして順位をつけた」ということで間違いないでしょうか？加えて、これをどう利用したら行いたい処理が実行できるのか分かりません……下記のようなSQLをくんで見ましたが遅いしよく分からないってのが正直なところです（汗。

select id, property_id, house_rent, (select count(house_rent) from (
select room.id as id, property_id, room_cost.house_rent as house_rent from room
join property on room.property_id = property.id
join room_cost on room_id = room_cost.room_id
) as B
where B.house_rent < A.house_rent and B.property_id = A.property_id)
from (
select room.id as id, property_id, room_cost.house_rent as house_rent from room
join property on room.property_id = property.id
join room_cost on room.id = room_cost.room_id
) as A

たぶん根本的に理解していない…

[ メッセージ編集済み 編集者: 有末　清華 編集日時 2009-03-05 01:51 ]

[ メッセージ編集済み 編集者: 有末　清華 編集日時 2009-03-05 01:51 ]

[ メッセージ編集済み 編集者: 有末　清華 編集日時 2009-03-05 01:52 ]
デューン
大ベテラン
会議室デビュー日: 2004/04/21
投稿数: 174
お住まい・勤務地: Tokyo
投稿日時: 2009-03-05 11:41

select room_id,house_rent,(select count(house_rent)
from room_cost B where B.house_rent < A.house_rent ) from room_cost A
という書き方は、SQLiteでは許してくれないようでした


すいません、ヒントじゃなくなってしまうんですが、
コード:
select A.property_id, A.room_id, A.house_rent from 
     ( select property_id, room_id, house_rent
           from room_cost ,room
           where room.id =room_id ) A
     inner join 
     ( select property_id, min(house_rent) minprice
           from room_cost ,room
           where room.id=room_id  group by property_id) B
     on A.property_id = B.property_id and A.house_rent = B.minprice

というのはどうでしょうか。


提示してもらったSQLですが、
propertyは必ずしもjoinする必要はないのかなと思います。
(roomテーブルにあって、propertyテーブルにないproperty.idを削れますが、このあとのタイミングでもよいのではないかと思います。)
«前のページへ 1|2|3 次のページへ»

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