MySQLでは、抽出条件を増やすことで、目的に応じたデータを絞り込んで抽出することができます。特にgroup by句やwhere句を使用することで、カテゴリごとの集計をしたり、カテゴリごとにデータを取り出したりすることができます。また、like句を使うと曖昧な検索をするとができます。この記事では、MySQLの基本的なデータ抽出の書き方について紹介しています。
データの用意
今回使用するデータは、２０１６年のゲームランキングのデータを使用しています。このCSVファイルは、順位、ゲームタイトル、メーカー、発売日、ゲーム機種、ジャンル、価格、売上本数、売上累計の８つのフィールドからなるデータとなっています。game.csvファイルとしてデスクトップに保存していることを想定しています。
1,バイオハザード7 レジデント イービル,カプコン,2017年01月26日,PS4,アクション,7990円［税抜］,4万2165本,累計：27万3353本 2,ポケットモンスター サン・ムーン,ポケモン,2016年11月18日,3DS,RPG,4980円［税抜］,2万0902本,累計：362万1107本 3,妖怪ウォッチ3 スキヤキ,レベルファイブ,2016年12月15日,3DS,RPG,4800円［税抜］,1万3223本,累計：64万8741本 4,ポチと！ ヨッシーウールワールド,任天堂,2017年01月19日,3DS,アクション,4700円［税抜］,1万1964本,累計：8万4863本 5,スーパーマリオメーカー for ニンテンドー3DS,任天堂,2016年12月01日,3DS,アクション,4700円［税抜］,1万1910本,累計：105万6770本 6,GRAVITY DAZE 2/重力的眩暈完結編：上層への帰還の果て、彼女の内宇宙に収斂した選択,ソニー・インタラクティブエンタテインメントジャパンアジア,2017年01月19日,PS4,アクション・アドベンチャー,6900円［税抜］ ,8952本,累計：9万4765本 7,NARUTO-ナルト- 疾風伝 ナルティメットストーム4 ROAD TO BORUTO,バンダイナムコエンターテインメント,2017年02月02日,PS4,アクション,6800円［税抜］,8547本,累計：8547本 8,ハコボーイ！ ハコづめBOX,任天堂,2017年02月02日,3DS,パズル・アクション,3200円［税抜］,6010本,累計：6010本 9,キングダム ハーツ HD 2.8 ファイナル チャプター プロローグ,スクウェア・エニックス,2017年01月12日,PS4,アクション・RPG,6800円［税抜］,5935本,累計：17万3623本 10,とびだせ どうぶつの森 amiibo＋,任天堂,2016年11月23日,3DS,その他,2700円［税抜］,5047本,累計：12万9213本 11,グランド・セフト・オートV（新価格版）,ロックスター・ゲームス,2015年10月08日,PS4,アクション,4990円［税抜］,4968本,累計：20万3668本 12,桃太郎電鉄2017 たちあがれ日本!!,任天堂,2016年12月22日,3DS,ボードゲーム,4980円［税抜］,4802本,累計：36万9141本 13,ファイナルファンタジーXV,スクウェア・エニックス,2016年11月29日,PS4,RPG,8800円［税抜］,4765本,累計：93万8468本 14,Miitopia（ミートピア）,任天堂,2016年12月08日,3DS,コミュニケーション,4700円［税抜］,4524本,累計：19万2934本 15,妖怪ウォッチ3 スシ/テンプラ,レベルファイブ,2016年07月16日,3DS,RPG,4800円［税抜］,4424本,累計：145万7398本 16,Minecraft： PlayStation Vita Edition,ソニー・インタラクティブエンタテインメントジャパンアジア,2015年03月19日,Vita,コンストラクション,2400円［税抜］,4008本,累計：93万9228本 17,蒼き革命のヴァルキュリア,セガゲームス,2017年01月19日,PS4,RPG,7990円［税抜］,3836本,累計：5万3396本 18,ニューダンガンロンパV3 みんなのコロシアイ新学期,スパイク・チュンソフト,2017年01月12日,Vita,アドベンチャー,6400円［税抜］,3719本,累計：10万5236本 19,マリオカート7,任天堂,2011年12月01日,3DS,アクション,4571円［税抜］,3441本,累計：266万6011本 20,Minecraft： Wii U Edition,日本マイクロソフト,2016年06月23日,Wii U,コンストラクション,3600円［税抜］,3308本,累計：28万5250本 21,バイオハザード6,カプコン,2016年12月01日,PS4,アクション・アドベンチャー,2800円［税抜］,2976本,累計：3万5437本 22,レインボーシックス シージ,ユービーアイソフト,2015年12月10日,PS4,FPS,8400円［税抜］,2860本,累計：17万0763本 23,Minecraft： PlayStation 4 Edition,ソニー・インタラクティブエンタテインメントジャパンアジア,2015年12月03日,PS4,コンストラクション,2400円［税抜］,2748本,累計：17万8029本 24,蒼き革命のヴァルキュリア,セガゲームス,2017年01月19日,Vita,RPG,6990円［税抜］,2601本,累計：2万8495本 25,バトルフィールド 1,エレクトロニック・アーツ,2016年10月21日,PS4,FPS,7800円［税抜］,2275本,累計：25万5967本 26,実況パワフルプロ野球 ヒーローズ,KONAMI,2016年12月15日,3DS,スポーツ,4500円［税抜］,2260本,累計：10万3553本 27,うたの☆プリンスさまっ♪ Repeat LOVE,ブロッコリー,2017年01月26日,Vita,アドベンチャー,5800円［税抜］,2234本,累計：1万9777本 28,パズドラクロス 神の章/龍の章,ガンホー・オンライン・エンターテイメント,2016年07月28日,3DS,パズル・RPG,4800円［税抜］,2180本,累計：30万0968本 29,星のカービィ ロボボプラネット,任天堂,2016年04月28日,3DS,アクション,4700円［税抜］,2064本,累計：50万6351本 30,Splatoon（スプラトゥーン）,任天堂,2015年05月28日,Wii U,TPS,5700円［税抜］,2026本,累計：148万4429本
テーブルの作成
先ほどのフィールド名をそれぞれ、title、maker、release01、model、genre、price、sales、cumulativeというフィールド名でテーブルを作成しています。また、順位については、フィールド名をidとして、プライマリーキーを設定しています。
create table game (id int primary key,title varchar(100), maker varchar(100),release01 varchar(100),model varchar(100), genre varchar(100),price varchar(100),sales varchar(100), cumulative varchar(100));
Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 10.1.19-MariaDB Source distribution Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> use data_analysis; Database changed MariaDB [data_analysis]> create table game (id int primary key,title varchar(100), -> maker varchar(100),release01 varchar(100),model varchar(100), -> genre varchar(100),price varchar(100),sales varchar(100), -> cumulative varchar(100)); Query OK, 0 rows affected (0.44 sec)
文字コードの変更
次に文字コードを変更する必要があります。文字コードを変更する箇所は２箇所あります。一つ目は、先ほど作ったテーブルの文字コードをutf8に変更します。次に、データベースの文字コードをutf8mb4に変更します。
alter table game convert to character set utf8; SET character_set_database=utf8mb4;
MariaDB [data_analysis]> alter table amazon convert to character set utf8; Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [data_analysis]> SET character_set_database=utf8mb4; Query OK, 0 rows affected (0.05 sec)
CVSファイルのインポート
テーブルを作成して、テーブルとデータベースの文字コードの変更を行ったら、CSVファイルをインポートすることができます。インポートのSQL文は前回の記事で紹介した書き方を同じです。load data local infileコマンドにファイルのパスを書いて、テーブル名とデータ区切りを指定します。ここではデスクトップに最初に用意したデータをgame.csvとして保存していますので以下のようなコマンドとなっています。
load data local infile '~/Desktop/game.csv’ into table game fields terminated by ',';
MariaDB [data_analysis]> load data local infile '~/Desktop/game.csv' into table game fields terminated by ','; Query OK, 0 rows affected, 31 warnings (0.07 sec) Records: 30 Deleted: 0 Skipped: 30 Warnings: 31
データの表示
無事にインポートができたら、まずはselect文でtitleとgenreのみを表示してみます。タイトルは、文字数が多いので、left(title, 10)として最初の１０文字のみ表示します。追加でもう一つフィールドを表示したい場合は、コンマ区切りでフィールド名を書けば表示したいフィールドを追加できます。あと、今回のデータが３０件を多めなので、表示するレーコードも指定しています。select文の最後にlimit 10と書くことで表示するレコードの件数を指定します。大量のデータをSQLで扱うときには必須の書き方です。
select left(title, 10), genre from game limit 10;
MariaDB [data_analysis]> select left(title, 10), genre from game limit 10; +--------------------------------+-----------------------------------------+ | left(title, 10) | genre | +--------------------------------+-----------------------------------------+ | バイオハザード7 レ | アクション | | ポケットモンスター | RPG | | 妖怪ウォッチ3 スキ | RPG | | ポチと！ ヨッシーウ | アクション | | スーパーマリオメーカ | アクション | | GRAVITY DA | アクション・アドベンチャー | | NARUTO-ナルト | アクション | | ハコボーイ！ ハコづ | パズル・アクション | | キングダム ハーツ | アクション・RPG | | とびだせ どうぶつの | その他 | +--------------------------------+-----------------------------------------+ 10 rows in set (0.07 sec)
group by句での集計
ここでは、group by句を使ってゲーム機種ごとの集計をしています。書き方としては、表示したいフィールを指定します。ここで表示したいのは、ゲーム機種のフィールドのmodelとその集計値です。なので、modelとcount(model)を表示するフィールドにします。次にfrom以降は、テーブル名はgameなので、gameと書きます。次に、group byを書き足して、そこにグループ化したいフィールド名を書きます。count(model)と書くことで、ゲーム機種ごとに集計がされます。これを書かないと、単にmodelの集計結果だけになります。最後に、descを書き足して、データの並び方を数値の大きい順にします。以上が、ゲーム機種ごとに集計結果を表示する書き方です。実行すると、以下のように3DS、PS4、Vita、Wii Uごとのソフトの数が集計されています。
select model,count(model) from game group by model order by count(model) desc;
MariaDB [data_analysis]> select model,count(model) from -> game group by model -> order by count(model) desc; +-------+--------------+ | model | count(model) | +-------+--------------+ | 3DS | 13 | | PS4 | 11 | | Vita | 4 | | Wii U | 2 | +-------+--------------+ 4 rows in set (0.00 sec)
where句での検索
ここではwhere句を使って条件を絞って表示しています。where句というのは、あるフィールドの何々といった感じで、抽出するデータを絞り込みたい時に使います。この例では、3DSのタイトルだけ表示させるということをやっています。この場合の書き方としては、まず表示したいフィールドはゲームタイトルと、機種としています。次にfrom以降に、テーブル名のgameを書いて、続いて、where句を書きます。where句は、where フィール名 = ‘指定したい項目’という感じで書きます。この場合は、where model = ‘3ds’と書くことで、modelというフィールド内にある3DSのデータのみを抽出しています。すると以下のように、3DSのゲームタイトルのみが表示されます。
select title, model from game where model = '3ds';
MariaDB [data_analysis]> select title, model from game where model = '3ds'; +-------------------------------------------------------------+-------+ | title | model | +-------------------------------------------------------------+-------+ | ポケットモンスター サン・ムーン | 3DS | | 妖怪ウォッチ3 スキヤキ | 3DS | | ポチと！ ヨッシーウールワールド | 3DS | | スーパーマリオメーカー for ニンテンドー3DS | 3DS | | ハコボーイ！ ハコづめBOX | 3DS | | とびだせ どうぶつの森 amiibo＋ | 3DS | | 桃太郎電鉄2017 たちあがれ日本!! | 3DS | | Miitopia（ミートピア） | 3DS | | 妖怪ウォッチ3 スシ/テンプラ | 3DS | | マリオカート7 | 3DS | | 実況パワフルプロ野球 ヒーローズ | 3DS | | パズドラクロス 神の章/龍の章 | 3DS | | 星のカービィ ロボボプラネット | 3DS | +-------------------------------------------------------------+-------+ 13 rows in set (0.00 sec)
like句でのあいまいな検索
like句というのは、あるキーワードが含まれていたら抽出するというようなものです。ここでは、マリオというキーワードが含まれているゲームタイトルのみを抽出しています。書き方は、表示したいフィールドはタイトルとゲーム機種としています。次にfrom以降はテーブル名にgameと書いて、where句としてはタイトルとしています。そのタイトルのレコードの中でマリオのキーワードが含まれているレコードのみを表示したいので、like ‘%マリオ%’という書き方でlike句を書きます。すると、以下のように、マリオというキーワードが含まれたゲームタイトルのみを表示することができます。
select title, model from game where title like '%マリオ%';
MariaDB [data_analysis]> select title, model from game where title like '%マリオ%'; +-------------------------------------------------------------+-------+ | title | model | +-------------------------------------------------------------+-------+ | スーパーマリオメーカー for ニンテンドー3DS | 3DS | | マリオカート7 | 3DS | +-------------------------------------------------------------+-------+ 2 rows in set (0.35 sec)