第14回　実践的検索操作の総まとめ - MySQL講座

はじめに

みなさんこんにちは。サカイです。前回まで４回にわたって「実践的検索操作」を学んできました。今回はその総まとめとして、前回ついに足を踏み入れた「テーブルの結合」を振り返った後、その他の検索テクニックを紹介したいと思います。

もう少し「結合」を体験しよう

• 今日、なにやりたい？
• せ、、先輩。 「なにやりたい？」って・・・・。　あっ、僕の「やる気」を試しましたね？受け身になっているんじゃないかって。
• あはは。そんなわけじゃないんだけど。 前回教えた「結合」、理解してくれたかなとちょっと気になってね。
• そういう事なんですね。実は前回先輩に「他の結合も色々やってごらん」と言われて、いくつかの結合検索に挑戦してみたんです。

だいたい解った気分ではあるのですが、先輩に見てもらえたらと思っていて・・・・見ていただけますか？

• おっ、いいね。そういう前向きな姿勢の返事を期待していたんだよ。
• やっぱり姿勢を試していたんじゃないですか(笑)
• じゃぁ成果を聞かせてもらおう。

都道府県別顧客数

• 第11回で都道府県別の顧客数を集計するのをやりましたよね。

顧客テーブル(customers) だけから抽出していたので都道府県コードと合計数しか解らず、その都道府県コードが何県なのかを調べるのに再度 都道府県テーブル(prefecture)から検索を行っていました。こんな感じ。

mysql> SELECT prefecture_code, COUNT(*) count FROM customers
    ->  GROUP BY prefecture_code ORDER BY count DESC LIMIT 5;
+-----------------+-------+
| prefecture_code | count |
+-----------------+-------+
| 13              | 10279 |
| 12              |  7336 |
| 14              |  5011 |
| 11              |  4304 |
| 44              |  4257 |
+-----------------+-------+
5 rows in set (0.09 sec)

mysql> SELECT * FROM prefecture WHERE code=13;
+------+--------+
| code | name   |
+------+--------+
| 13   | 東京都 |
+------+--------+
1 row in set (0.02 sec)

• これを、結合を使えば、「都道府県コード／都道府県名／顧客人数」を一発で出せますよね。このようにやってみました。

ポイントは、

です。こんな感じ。

mysql> SELECT c.prefecture_code, p.name, COUNT(*) count
    ->   FROM customers c, prefecture p
    ->  WHERE c.prefecture_code = p.code
    ->  GROUP BY c.prefecture_code
    ->  ORDER BY count DESC
    ->  LIMIT 5;
+-----------------+----------+-------+
| prefecture_code | name     | count |
+-----------------+----------+-------+
| 13              | 東京都   | 10279 |
| 12              | 千葉県   |  7336 |
| 14              | 神奈川県 |  5011 |
| 11              | 埼玉県   |  4304 |
| 44              | 大分県   |  4257 |
+-----------------+----------+-------+
5 rows in set (0.36 sec)

• 完璧だね。さすがだ。もう調査集計の仕事は任せられるね。
• へへ。前回の「先輩のアタマん中 大解説」のおかげです。あれを聞いてからいままで何から考えたら良いのかごちゃごちゃしていたのが、スッキリと箱の中に収まるようになったような気分なんです。
• それはよかった。今回は上位５件を見たかったから LIMIT 5 しているけど、全部見ても都道府県の数は４７個しかないから、LIMIT を外して全部見てみるのも面白いかもしれないね。下位のものは重点営業対象都道府県とも言えるかもしれないし。
• そうですね。・・・・あれ？ でも今見たのは顧客数の絶対数ですから、人口の少ないところはどうしても少なくなったり、人口の多いところはほんの一部の人が利用してくれているだけでも結構多くなったりしがちですよね。
• それは鋭い視点だな。一本取られたよ。ではどのようにしたら、太郎君の欲しい情報を得られると思う？
• そうですねぇ・・・。都道府県ごとの人口情報があれば、その情報と今出した顧客数の情報を割り算することで、人口比が出せると思います。
• そうだね。どこかにその情報を持つ必要がある。新しいテーブルを作って人口情報を持たせてもいいし、あるいは都道府県と完全に一対一に対応する、、、つまり履歴を持ったりしない、、、というのならば都道府県テーブルにカラムを追加して持たせるという手もあるね。　

今日は太郎君の他の成果も聞かせて欲しいからやらないけど、せっかくだから今度やってみようか。人口データ、どこかから探してきておいてね。

• ありがとうございます。
• それにしても太郎君もこうやって、今持っているデータで何をできるか、あるいは何のデータを加えれば拡がりが出るのか、よく考えているよね。「データ」は使ってナンボなので、その習慣は今後も続けて欲しいな。

人気商品ランキング(注文回数)

• もうひとつ行きます！　人気商品ランキングで注文回数と注文個数それぞれのランキングを見るというものでした(第12回参照)。両方やったのでこれも見てください。

もとのSQL：

mysql> SELECT item_id, count(*) cnt FROM kounyuu_m GROUP BY item_id
    ->  ORDER BY cnt DESC LIMIT 5;
+---------+------+
| item_id | cnt  |
+---------+------+
|     477 | 2700 |
|     335 | 2698 |
|     378 | 2694 |
|      93 | 2682 |
|     346 | 2677 |
+---------+------+
5 rows in set (0.72 sec)

• これも同様に、kounyuu_m テーブルだけからの抽出だったものを、商品名が欲しいので items テーブルを結合するようにしました。

考え方はさっきと同じで、

です。

mysql> SELECT k.item_id, i.name,count(*) cnt
    ->   FROM kounyuu_m k, items i
    ->  WHERE k.item_id=i.id
    ->  GROUP BY k.item_id
    ->  ORDER BY cnt DESC
    ->  LIMIT 5;
+---------+--------------+------+
| item_id | name         | cnt  |
+---------+--------------+------+
|     477 | シリアス28   | 2700 |
|     335 | スコープ12   | 2698 |
|     378 | 松王丸49     | 2694 |
|      93 | スペシャル94 | 2682 |
|     346 | スコープ86   | 2677 |
+---------+--------------+------+
5 rows in set (2.08 sec)

人気商品ランキング(注文個数)

• 続けて注文個数編、行きます！ 先ほどの回数編と基本的に一緒です。ベースにするSQLが以前やった注文個数抽出のやつになるだけですね。

そのベースになるSQLは：

mysql> SELECT item_id, sum(amount) amounts FROM kounyuu_m GROUP BY item_id
    ->  ORDER BY sum(amount) DESC LIMIT 5;
+---------+---------+
| item_id | amounts |
+---------+---------+
|     335 |    4580 |
|     378 |    4579 |
|     540 |    4548 |
|     535 |    4547 |
|     295 |    4522 |
+---------+---------+
5 rows in set (10 min 51.92 sec)

• こんな感じでした。なので、items テーブルを結合して、名前を一緒に表示するには：

mysql> SELECT k.item_id, i.name, sum(k.amount) amounts
    ->   FROM kounyuu_m k, items i
    ->  WHERE k.item_id=i.id
    ->  GROUP BY k.item_id
    ->  ORDER BY sum(k.amount) DESC
    ->  LIMIT 5;
+---------+------------+---------+
| item_id | name       | amounts |
+---------+------------+---------+
|     335 | スコープ12 |    4580 |
|     378 | 松王丸49   |    4579 |
|     540 | メタポン77 |    4548 |
|     535 | メタポン38 |    4547 |
|     295 | 助六23     |    4522 |
+---------+------------+---------+
5 rows in set (13.56 sec)

• これでどうでしょう。
• 完璧だね。言うことなしだよ。
• それにしても変な商品名ですね。
• そうか、太郎君はまだ全商品を見たことがなかったんだっけ？ ざっと眺めてみたらどうかな。
• そうですね。

SELECT * FROM items;
+-----+------------+------------+
| id  | name       | base_price |
+-----+------------+------------+
: (略)             :            :
+-----+------------+------------+
636 rows in set (0.00 sec)

• あはは。笑っちゃう商品名ばかりですね。ウチはこんな製品を売っていたんですか(^^;)。