irhnhhtn回答ポイント 130ptウォッチ 3

SQL文（MySQL）の質問です。

以下のようなテーブルがあります。

テーブル名：gamePlayData
項目１：playerName (String)
項目２：score (int)

scoreでorder byしたときに、ある特定の条件（playerName='HOGE' and score = 1234）が上から何レコード目にあるかをＳＱＬ文だけで取得することができたらそのＳＱＬ文を教えてください。
（アクセスが多く負荷をかけたくないので、PHPなどのプログラムで１件目からループさせて条件を判別していく方法は避けたいのでＳＱＬ一発で取得したいのです）

※過去の回答履歴を見て、適当な回答を繰り返していると判断した場合、その方の回答は開きません。
支払い率が１００％でないのは、最近そういうモラルが感じられない回答がある為です、ご了承ください。

• はてなアカウントでログイン
• 無料ユーザー登録

ベストアンサー

• ku__ra__ge2009/01/16 21:56:12

  満足40pt

  以下のSQLで指定したレコードが上から何レコード目にあるか求まります。

  サブクエリで各レコードに連番を付与して、指定したレコードのみ抽出しています。

  (指定したレコードが複数合った場合は最小値)

  SELECT MIN(rank)
    FROM (
      SELECT playerName
           , score
           , @a:=@a+1 AS rank
        FROM gamePlayData
            ,(select @a:=0) AS rank_dummy
       ORDER BY score DESC
        ) AS rank_data
  WHERE playerName = 'HOGE'
    AND score      = 1234;
  

  
  

  ただ、勝手に想像して申し訳ないのですが id:irhnhhtnさん が求めているSQLは、

  指定したスコアが何位なのかを求めるSQLのような気がします。

  ちなみにそうだとすれば、以下のようなSQLになります。

  SELECT MIN(rank)
    FROM (
      SELECT playerName
           , score
           , @a:=@a+1 AS rank
        FROM gamePlayData
            ,(select @a:=0) AS rank_dummy
       ORDER BY score DESC
        ) AS rank_data
  WHERE score = 1234;
  

  スター

  • irhnhhtn 2009/01/17 10:12:48

    なるほど～サブクエリと@変数の使い方は知りませんでした。

    ズバリ模範回答でしょう。

    ちなみに負荷的にはzero-uhuraさんの方法とどちらがいいんでしょう？

    分かれば追加で回答お願いします。

その他の回答

• goodvn2009/01/16 17:42:16

  0pt

  元のデータを変更しても良い，という前提であれば，インクリメントするカラムを追加して，その値を取れば ok です

  つまり，カラムに，"line"(int型) を追加し，SELECT してやります

  AUTO_INCREMENT を有効にしておきます．INSERT する際には，このカラムは指定しなくても，自動でインクリメントされた数値が入ります

  他にも方法はあると思いますが，PHP で処理するのに比べてコストが安いとは思えません

  スター

  • irhnhhtn 2009/01/16 18:09:34

    AUTO_INCREMENTになっている項目lineがあったとして・・・・

    質問のような「特定の条件のレコードは上から何番目か？」

    というＳＱＬ文は具体的にはどのように書くのでしょうか？

zero-uhura2009/01/16 17:51:06

満足36pt

質問意図から少しずれますので、ポイントは結構です。

ある特定の条件（playerName='HOGE' and score = 1234）よりスコアが多い人を数えれば何レコード目にあるか分かると思います。




スコアが多い方が上位の場合です。

また、スコアで比べるので「playerName」は無視します。




SELECT COUNT(*) +1 FROM table WHERE score > 1234;




例）以下の場合

1234ポイントより高いポイントの方は4名で、五番目以降に1234ポイントの方が出てきます、すなわち「HOGE」さんを含む1234ポイントの方は5位タイになります。（同順位なので、5～7の方はすべて5位）




1. 5000 AAA
2. 4000 BBB
3. 3000 CCC
4. 2000 DDD

5. 1234 EEE
6. 1234 HOGE
7. 1234 FFF

8. 1000 GGG

スター

• irhnhhtn 2009/01/16 18:15:11

  いや、これはなかなかいいアイデアですね。

  要は、負荷がかからなければＰＨＰで多少プログラム書いてもいいんです。

  （まさしく質問から外れてしまいますが・・・）

  という訳でポイントは差し上げます。

goodvn2009/01/16 19:21:59

0pt

INSERT INTO playGameData (`playerName`, `score`) VALUES ('HOGE', '1234');

でインサートし，

SELECT `line`, `playerName`, `score` FROM playGameData WHERE playerName='HOGE' AND score = 1234

とすると，line が行数になります

もちろん，最初から入れて置かないと意味がないので，後から追加する場合は，適宜 UPDATE しておく必要があります

スター

• irhnhhtn 2009/01/16 19:59:05

  scoreは小さい順に並んでInsertされていけばlineは上から何番目のレコード、を意味することになりますが、scoreの値は大小バラバラでInsertされてくるので、Select時には order by score　します。そうすると、lineの値は何番目のレコードという意味をなしません。

chuken_kenkou2009/01/16 20:20:54

満足15pt

MySQLの質問をする場合は、バージョンを明記してください。

SQL周辺だけでも、使える機能が大きく違ってきます。

• MySQL 4.0以前・・・一部のjoin、union、一時表などは実装済
• MySQL 4.1・・・joinの実装範囲の拡張、union、unicodeなどの実装
• MySQL 5.0・・・ビュー、ストアドプロシジャ、トリガなどの実装

負荷がかからなければＰＨＰで多少プログラム書いてもいい




例えば、行数をカウントするにしても、ストアドプロシジャなどを使えば、MySQLサーバ側で処理が行え、オーバヘッドを大幅に減らせます。

zero-uhuraさんのcount関数を使う方法は、php側の処理は簡単になります。しかし、母体データ件数が多いなら、MySQLサーバ側の負荷を軽減するためにはscore列にインデクスを定義しておくといった措置が必要です。

スター

• irhnhhtn 2009/01/17 09:52:00

  MySQLのバージョンは5.0です。phpmyAdminを使ってます。

  score列のインデックス定義も了解です。

  行数をカウントするストアドプロシジャについて、もしよければ具体的に回答ください。

  質問とは外れますが、負荷に対して効果的な内容であればポイントは差し上げます。

otasuketyan2009/01/16 22:34:05

0pt

もしテーブルのフィールドが

key,playerName,score

で

(1,Jon,2000)

(2,May,5600)

(3,Him,0022)

...

とあるのであれば、max(key)でフィールド最大数を取得し、count(key)で条件に一致したフィールドの数をカウントして、引き算するという方法があります。

スター

• irhnhhtn 2009/01/17 10:28:06

  keyはAUTO_INCREMENTになると思いますが、テストデータなど追加・削除する場合があるので

  max(key)=最大件数にはなりません。都度採番し直すのも現実的じゃありません。

  引き算する方法については既にzero-uhuraさんから回答頂いております。

komamitsu2009/01/17 14:24:00

満足34pt

http://www.amazon.co.jp/%E9%81%94%E4%BA%BA%E3%81%AB%E5%AD%A6%E3%...

に載っていた自己非等値結合という手法で。

mysql> SELECT * FROM gamePlayData;
+------------+-------+
| playerName | score |
+------------+-------+
| abc        |    50 |
| def        |    80 |
| ghi        |    20 |
| jkl        |   100 |
| mno        |    10 |
+------------+-------+
5 rows in set (0.00 sec)

という状態で

mysql> SELECT p1.playerName, p1.score,
    ->         (SELECT COUNT(p2.score)
    ->          FROM gamePlayData p2
    ->          WHERE p2.score > p1.score) + 1 AS rank
    -> FROM gamePlayData p1
    -> ORDER BY rank
    -> ;
+------------+-------+------+
| playerName | score | rank |
+------------+-------+------+
| jkl        |   100 |    1 | 
| def        |    80 |    2 | 
| abc        |    50 |    3 | 
| ghi        |    20 |    4 | 
| mno        |    10 |    5 |
+------------+-------+------+
5 rows in set (0.00 sec)

とすると個々のレコードの位置（＝自分よりもscoreが大きいレコード数）をrankとして取得できます。

なので、

mysql> SELECT p1.playerName, p1.score,
    ->         (SELECT COUNT(p2.score)
    ->          FROM gamePlayData p2
    ->          WHERE p2.score > p1.score) + 1 AS rank
    -> FROM gamePlayData p1
    -> WHERE p1.playerName = 'def' and p1.score = 80
    -> ;
+------------+-------+------+
| playerName | score | rank |
+------------+-------+------+
| def        |    80 |    2 | 
+------------+-------+------+
1 row in set (0.00 sec)

とすると指定されたレコードの位置が取得できます。

なお、今回はscoreの降順でやってますが

p2.score > p1.score をいじれば昇順にもなります。

スター

• irhnhhtn 2009/01/17 15:53:41

  詳しく丁寧な回答ありがとうございます。

  今までの回答によるとSELECT文は大きく分けて

  ・サブクエリで入れ子にして１回で問い合わせする（回答頂いたようなＳＱＬ文で）

  ・全体の件数／自分より大きいscoreの件数を複数回問い合わせしPHP側で結果を元に割り出す

  の２通りありますが、やはり前者の方がＤＢへの負荷は少ないんでしょうか

komamitsu2009/01/17 16:55:00

満足5pt

改めて問題と回答一覧を見てみましたが、ランキング一覧を作る必要は無く、当該レコードの順位さえわかれば良いのですよね。

なので私のやり方はやりすぎ（不必要なクエリーが発行されている）でした。失礼。




もしscoreだけがキーになるのでしたらzero-uhuraさんの回答が一番簡単で負荷も軽いと思いますよ。コメントで「PHP側で…」とありますが、SQL文一発だと思われ。

> ・サブクエリで入れ子にして１回で問い合わせする（回答頂いたようなＳＱＬ文で）

> ・全体の件数／自分より大きいscoreの件数を複数回問い合わせしPHP側で結果を元に割り出す

> の２通りありますが、やはり前者の方がＤＢへの負荷は少ないんでしょうか




一応、こちらにも。

一般的に、SQL負荷の合計が同程度であれば、SQLのパースやらネットワーク負荷やらがあるので前者のほうが良いでしょう。

スター

• irhnhhtn 2009/01/17 18:25:25

  ＞前者のほうが良いでしょう。

  やはりそうですよね。

  ありがとうございました。

• その他の回答を読む

この質問へのコメント

• goodvn2009/01/17 20:34:54
  >>
  scoreは小さい順に並んでInsertされていけばlineは上から何番目のレコード、を意味することになりますが、scoreの値は大小バラバラでInsertされてくるので、Select時には order by score　します。そうすると、lineの値は何番目のレコードという意味をなしません。
  <<
  
  そんなこと質問文のどこにも書いてないですよ
  
  自分の質問文が曖昧なのに，それに対する回答に対し，ちょっと態度が失礼かと思います
  
  この行為ははてなに通報しておきます
• irhnhhtn2009/01/17 22:53:58
  >>goodvnさん
  不適切な回答とさせて頂いたのは、「scoreでorder byしたときに」という質問の意図を無視した内容となっていたためです。
  バラバラに並んでいなければ、order by scoreと記述する必要はない訳です。

質問の情報

登録日時

2009-01-16 17:16:48

終了日時

2009-01-17 20:26:44

回答条件

1人2回まで

この質問のカテゴリ

• インターネット
• ウェブ制作

この質問に含まれるキーワード

人気の質問

• 私は、【チルスポ】　という名前で、 チルスポット情報を共有するアプリを開発しています。 皆さんの知って…匿名質問者117時間前
• 同じような体験をしたことがある人はいますか？。 ある日、お葬式の職業に就きたいなあなんて話題にしてい…匿名質問者13日前
• b型就労支援施設に通っている方に質問です。どういうことが通っていて辛いと感じますか？他の利用者の方と…匿名質問者9時間前
• 令和は読み方で言うなら霊和とも読めますよね？令和は縁起が良いでしょうか？。 霊和となって幽霊は大喜び…匿名質問者1日前
• 私の彼氏は毒親育ちで、うつ病を患い、家を追い出された後ホームレスになって、現在は何故か逮捕されて留置…匿名質問者23日前

メニュー

• 質問一覧
• カテゴリ一覧
• 無料ユーザー登録
• 人力検索はてなトップ

PC版

• お問い合わせ
• ヘルプ
• お知らせ
• はてなトップ