- PR -

最多取引先番号取得SQLの作り方を教えて下さい

«前のページへ 1|2
投稿者投稿内容
よっしー
大ベテラン
会議室デビュー日: 2007/05/17
投稿数: 143
投稿日時: 2007-09-10 20:20
引用:

Ｒ・田中一郎さんの書き込み (2007-09-10 19:28) より:
(HAVING 句の符号は、逆ですよね？)

逆でした。すみません。

しかも、某有名掲示板（？）で模範解答が出てるし...orz

select 品番, 仕入先番号
from 注文テーブル a
group by 品番, 仕入先番号
having count(*) >= all(select count(*)
from 注文テーブル b
where b.品番=a.品番
group by 仕入先番号);

無許可でコピペ。（だってこっちの方がいいし。。）
こあら
大ベテラン
会議室デビュー日: 2007/06/26
投稿数: 157
投稿日時: 2007-09-11 10:16
非インデックス項目による GROUP BY はフルスキャンですので、
パフォーマンスチューニングには少し気をつけた方が良いです。
カーニー
ぬし
会議室デビュー日: 2003/09/04
投稿数: 358
お住まい・勤務地: 東京
投稿日時: 2007-09-11 15:54
引用:

こあらさんの書き込み (2007-09-11 10:16) より:
非インデックス項目による GROUP BY はフルスキャンですので、
パフォーマンスチューニングには少し気をつけた方が良いです。


今回のSELECT文にはWHERE句がないから、どっちにしろフルスキャンでいいんじゃないですか? WHERE句があればあったで、そっちの条件でインデックスを使わせたほうが効率が良い場合が多いでしょうし。

それはそれとして純粋な質問なんですが、MySQLのオプティマイザは、インデックス項目でGROUP BYすると、どのようにインデックスを上手に使ってくれるのでしょうか?
こあら
大ベテラン
会議室デビュー日: 2007/06/26
投稿数: 157
投稿日時: 2007-09-11 16:29
引用:

非インデックス項目による GROUP BY はフルスキャンですので、
パフォーマンスチューニングには少し気をつけた方が良いです。


すみません間違いです。GROUP BYではなくWHEREです。

フルスキャンとなるサブクエリの呼び出し回数には注意すべき、
ということを言いたかったのです。



よっしーさんの挙げられたSELECT文は構文的に非常にエレガントです。
コード:
select 品番, 仕入先番号 
from 注文テーブル a 
group by 品番, 仕入先番号 
having count(*) >= all(select count(*) 
from 注文テーブル b 
where b.品番=a.品番 
group by 仕入先番号);


しかし、サブクエリがボトルネックになる可能性があると思い、
上記のような書き込みをしました。
Ｒ・田中一郎
ぬし
会議室デビュー日: 2005/11/03
投稿数: 979
投稿日時: 2007-09-11 18:03
引用:

よっしーさんの書き込み (2007-09-10 20:20) より:

しかも、某有名掲示板（？）で模範解答が出てるし...orz

なるほど。
これはとてもわかりやすく、且つ「どうすれば、こんなことが考え付くのだろう？」と感心してしまいます。
ところで、ここでALL句を明示しているのは何故なのでしょうか？

カーニーさん、こあらさんは、いろいろ勉強させていただきました。
ご心配の点については、確認したところ、インデックス項目となっていますのでパフォーマンスの心配はなさそうです。
_________________
Ｒ・田中一郎 - 　R.Tanaka.Ichiro’s Blog
こあら
大ベテラン
会議室デビュー日: 2007/06/26
投稿数: 157
投稿日時: 2007-09-11 18:24
引用:
ところで、ここでALL句を明示しているのは何故なのでしょうか？


ある品番, 仕入先番号の注文数が、
その品番の仕入先番号ごとの、どの注文数よりも大きい（つまり最多注文数）
を検索するためです。
# 「=(イコール)」 は最多注文数レコードが自分自身を除外しないための条件です。

日本語にすると非常に分かりにくいですね。。。
こあら
大ベテラン
会議室デビュー日: 2007/06/26
投稿数: 157
投稿日時: 2007-09-12 17:32
私が最初に掲示したクエリも、OVER句を使えると簡潔に書けるみたいです。

コード:

SELECT

   注文数V.品番, 注文数V.仕入先番号



FROM

   (SELECT

        品番, 仕入先番号, COUNT(*) as 注文数, MAX(COUNT(*)) OVER() as 最多注文数

    FROM

        注文テーブル

    GROUP BY

        品番, 仕入先番号

   ) 注文数V



WHERE

   注文数V.注文数 = 注文数V.最多注文数




[編集]
「OVER句を使うと」を「OVER句を使えると」に編集
OVER句を使えるDBMSって結構少ないみたいなので。
[/編集]


[ メッセージ編集済み 編集者: こあら 編集日時 2007-09-12 17:38 ]
«前のページへ 1|2

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