この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
こんにちは。アプリの運用を担当していますkobayashiです。毎日寒いですね。嫌な予感通り風邪をひいています。
カスタマーサポートを愛してやまない人達が集まり、「CS」を徹底的にハックするCS HACKとGYOMU HACKのコラボイベント「QueryNight〜クエリナイト・エンジニアに頼らない分析と生産性向上の手法〜」に参加して来ました。当日の様子、後日イベント時の手順をやり直してみた結果、補足などをまとめたいと思います。
イベントの概要
今回は、QueryNight〜クエリナイト・エンジニアに頼らない分析と生産性向上の手法〜 と題して、 CSや業務効率化に必須スキルになってきているデータ抽出と分析について学びます！ SQLを使ってデータの抽出、分析を行い、エンジニアにお願いしなくても自分の力でデータドリブンな改善提案をするための基礎を身に付けましょう。
イベントページはこちら 会場は「freee株式会社」様のイベントスペースでした。夜食系の施設が充実していて羨ましい...
タイムテーブルは以下の通りです。
| 時間 | 内容 |
| 19:00~19:30 | 開場 |
| 19:30~ | 趣旨説明 |
| 19:35~ | 作業 |
| 20:35~ | 懇親会 |
| 21:30~ | 集合写真＆終了 |
イベント趣旨
- サービス改善のスピードと品質をどうあげるか
- SQLを覚えると自分の力だけで数値が把握できる
- 開発に頼らないスキルを身につける今日がスタート
イベント詳細
講師 株式会社ビズリーチ 事業戦略本部 BPR部 市川 亘様
- 講座のゴール
- データベースとは何かを理解する
- SQLの書き方を理解する
- 基本的なSQLを自分で作れるようになる
- データベースとは
- 検索や蓄積ができるように整理された情報の集まり
- リレーショナルデータベース
- 複数のデータベースを関連つけるために関係をもたせたもの
- excelでレコードとカラムをイメージする
- SQLとは
- リレーショナルデータベースを操作するための言語
- データベースの各種操作（選択・作成・更新・削除）を実行できる
SQLハンズオン
イベントの後にハンズオンに書かれていたSQLを使って、自分でSQLを叩いてみました。PCはMacBook Air、OSはSiera10.12.6で実行しました。
※は当日すべてのSQLを叩けなかった人の助けになればと思って書いた補足です。
秘密鍵をダウンロードしてターミナルを起動、コマンドを実行してサーバーに接続する
chmod 600 “secret_keys.pemのフルパス” ssh -i “secret_keys.pemのフルパス” [サーバーのホスト名]
※改行の時にEnterキーでコマンドを実行してください
Last login: Fri Oct 27 09:47:57 on console
MacBook-Air:~ kobayashi$ chmod 600 /Users/kobayashi.misako/Downloads/secret_keys.pem
MacBook-Air:~ kobayashi$ ssh -i /Users/kobayashi.misako/Downloads/secret_keys.pem guestuser@[サーバーのホスト名]
Welcome to Ubuntu 16.04.2 LTS (GNU/Linux 4.4.0-1035-aws x86_64)</code>
* Documentation: https://help.ubuntu.com
* Management: https://landscape.canonical.com
* Support: https://ubuntu.com/advantage
Get cloud support with Ubuntu Advantage Cloud Guest:
http://www.ubuntu.com/business/services/cloud
92 packages can be updated.
0 updates are security updates.
*** System restart required ***
Last login: Thu Oct 26 08:51:49 2017 from 118.238.219.241
guestuser@ip-172-31-7-240:~$※スペースが入ってるとエラーになるので、一字一句間違えずに入力orコピペ ※「ファイルのパスを入力して」と言われたら、ファイルをそのままターミナルにドラッグ&ドロップでOK ※「↑」を押していくとこれまで実行したコマンドが出てくるので、活用する
MySQLへ接続
mysql -h [MySQLのホスト名]3306 -u queryguest -D ecsite -p
mysql -h [MySQLのホスト名] -P 3306 -u queryguest -D ecsite -p
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13907
Server version: 5.6.35-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>※passwordはターミナル上に表示されないが入力すれば打てているので、間違えないように入力 ※「mysql >」が表示されたら、mysqlに接続完了
SQLを実行してみる
商品一覧の表示
SELECT product_cd, product_name, category, writer, unit_price FROM PRODUCT;
SELECT
-> product_cd, product_name, category, writer, unit_price
-> FROM
-> PRODUCT;
+------------+--------------------------------+-----------------+---------------------------+------------+
| product_cd | product_name | category | writer | unit_price |
+------------+--------------------------------+-----------------+---------------------------+------------+
| B0001 | LIFE SHIFT | ビジネス書 | リンダ グラットン | 2000 |
| B0002 | 多動力 | ビジネス書 | 堀江 貴文 | 1500 |
| B0003 | 革命のファンファーレ | ビジネス書 | 西野 亮廣 | 1600 |
| N0001 | 騎士団長殺し | 小説 | 村上 春樹 | 4000 |
| N0002 | 海辺のカフカ | 小説 | 村上 春樹 | 1700 |
| N0003 | ノルウェイの森 | 小説 | 村上 春樹 | 1300 |
| N0004 | わたしを離さないで | 小説 | カズオ・イシグロ | 900 |
| N0005 | 忘れられた巨人 | 小説 | カズオ・イシグロ | 2100 |
| N0006 | 日の名残り | 小説 | カズオ・イシグロ | 2100 |
| R0001 | スッキリわかるSQL入門 | 参考書 | 中山 清喬 | 1100 |
| R0002 | データベースのきほん | 参考書 | ミック | 2400 |
| R0003 | Excel 最強の教科書 | 参考書 | 藤井 直弥 | 1800 |
+------------+--------------------------------+-----------------+---------------------------+------------+
12 rows in set (0.00 sec)
※「product_cd, product_name, category, writer, unit_priceのカラム（列）をPRODUCTというテーブルから抽出して」という命令
※最後の「 ; 」が無いと実行されないので注意
商品一覧の表示（全カラムの抽出）
SELECT * FROM PRODUCT;
mysql> SELECT *
-> FROM
-> PRODUCT;
+------------+--------------------------------+-----------------+---------------------------+------------+
| product_cd | product_name | category | writer | unit_price |
+------------+--------------------------------+-----------------+---------------------------+------------+
| B0001 | LIFE SHIFT | ビジネス書 | リンダ グラットン | 2000 |
| B0002 | 多動力 | ビジネス書 | 堀江 貴文 | 1500 |
| B0003 | 革命のファンファーレ | ビジネス書 | 西野 亮廣 | 1600 |
| N0001 | 騎士団長殺し | 小説 | 村上 春樹 | 4000 |
| N0002 | 海辺のカフカ | 小説 | 村上 春樹 | 1700 |
| N0003 | ノルウェイの森 | 小説 | 村上 春樹 | 1300 |
| N0004 | わたしを離さないで | 小説 | カズオ・イシグロ | 900 |
| N0005 | 忘れられた巨人 | 小説 | カズオ・イシグロ | 2100 |
| N0006 | 日の名残り | 小説 | カズオ・イシグロ | 2100 |
| R0001 | スッキリわかるSQL入門 | 参考書 | 中山 清喬 | 1100 |
| R0002 | データベースのきほん | 参考書 | ミック | 2400 |
| R0003 | Excel 最強の教科書 | 参考書 | 藤井 直弥 | 1800 |
+------------+--------------------------------+-----------------+---------------------------+------------+
12 rows in set (0.00 sec)※「全部のカラム（列）をPRODUCTというテーブルから抽出して」という命令 ※「 ＊ 」はすべてという意味 ※実際のデータベースでやるとデータが多い場合負荷がすごくかかり、エンジニアに怒られるので注意
特定の条件のレコードを抽出する
カテゴリが「参考書」のものだけ抽出
SELECT product_cd, product_name, category, writer, unit_price FROM PRODUCT WHERE category = '参考書';
mysql> SELECT
-> product_cd, product_name, category, writer, unit_price
-> FROM
-> PRODUCT
-> WHERE
-> category = '参考書';
+------------+--------------------------------+-----------+---------------+------------+
| product_cd | product_name | category | writer | unit_price |
+------------+--------------------------------+-----------+---------------+------------+
| R0001 | スッキリわかるSQL入門 | 参考書 | 中山 清喬 | 1100 |
| R0002 | データベースのきほん | 参考書 | ミック | 2400 |
| R0003 | Excel 最強の教科書 | 参考書 | 藤井 直弥 | 1800 |
+------------+--------------------------------+-----------+---------------+------------+
3 rows in set (0.00 sec)※「カテゴリーが参考書になっている、product_cd, product_name, category, writer, unit_priceのカラム（列）をPRODUCTというテーブルから抽出して」という命令 ※「参考書」などの日本語文字は「 ' ' 」で囲む ※WHEREは抽出する時の条件を加える時に使う ※ちなみに複数条件の指定もできる category = '参考書' AND unit_price >= 2000 カテゴリが「参考書」かつ単価が2000円以上のもの ※「 >= 」は以上という意味 ※ANDは複数の条件をくっつける時に使う
複数のテーブルを結合する
注文情報に商品名を付けて抽出する
SELECT ORDER_INFO.order_cd, ORDER_INFO.order_ymd, PRODUCT.product_name, PRODUCT.unit_price, ORDER_INFO.quantity, ORDER_INFO.total_price FROM ORDER_INFO INNER JOIN PRODUCT ON ORDER_INFO.product_cd = PRODUCT.product_cd;
mysql> SELECT
-> ORDER_INFO.order_cd, ORDER_INFO.order_ymd, PRODUCT.product_name,
-> PRODUCT.unit_price, ORDER_INFO.quantity, ORDER_INFO.total_price
-> FROM
-> ORDER_INFO
-> INNER JOIN
-> PRODUCT
-> ON
-> ORDER_INFO.product_cd = PRODUCT.product_cd;
+------------+------------+--------------------------------+------------+----------+-------------+
| order_cd | order_ymd | product_name | unit_price | quantity | total_price |
+------------+------------+--------------------------------+------------+----------+-------------+
| ODR-000001 | 2017-10-01 | 日の名残り | 2100 | 1 | 2100 |
| ODR-000002 | 2017-10-01 | わたしを離さないで | 900 | 2 | 1800 |
| ODR-000003 | 2017-10-02 | LIFE SHIFT | 2000 | 1 | 2000 |
| ODR-000004 | 2017-10-02 | 海辺のカフカ | 1700 | 1 | 1700 |
| ODR-000005 | 2017-10-03 | LIFE SHIFT | 2000 | 1 | 2000 |
| ODR-000006 | 2017-10-03 | Excel 最強の教科書 | 1800 | 1 | 1800 |
| ODR-000007 | 2017-10-04 | スッキリわかるSQL入門 | 1100 | 1 | 1100 |
| ODR-000008 | 2017-10-04 | 日の名残り | 2100 | 2 | 4200 |
| ODR-000009 | 2017-10-05 | データベースのきほん | 2400 | 1 | 2400 |
| ODR-000010 | 2017-10-05 | 日の名残り | 2100 | 1 | 2100 |
| ODR-000011 | 2017-10-06 | 忘れられた巨人 | 2100 | 1 | 2100 |
| ODR-000012 | 2017-10-06 | ノルウェイの森 | 1300 | 1 | 1300 |
| ODR-000013 | 2017-10-07 | わたしを離さないで | 900 | 1 | 900 |
| ODR-000014 | 2017-10-07 | ノルウェイの森 | 1300 | 1 | 1300 |
| ODR-000015 | 2017-10-08 | スッキリわかるSQL入門 | 1100 | 2 | 2200 |
| ODR-000016 | 2017-10-08 | 騎士団長殺し | 4000 | 1 | 4000 |
| ODR-000017 | 2017-10-09 | わたしを離さないで | 900 | 1 | 900 |
| ODR-000018 | 2017-10-09 | ノルウェイの森 | 1300 | 1 | 1300 |
| ODR-000019 | 2017-10-10 | LIFE SHIFT | 2000 | 1 | 2000 |
| ODR-000020 | 2017-10-10 | わたしを離さないで | 900 | 1 | 900 |
| ODR-000021 | 2017-10-11 | 海辺のカフカ | 1700 | 1 | 1700 |
| ODR-000022 | 2017-10-11 | 日の名残り | 2100 | 1 | 2100 |
| ODR-000023 | 2017-10-12 | Excel 最強の教科書 | 1800 | 2 | 3600 |
| ODR-000024 | 2017-10-12 | 海辺のカフカ | 1700 | 1 | 1700 |
| ODR-000025 | 2017-10-13 | データベースのきほん | 2400 | 1 | 2400 |
| ODR-000026 | 2017-10-13 | スッキリわかるSQL入門 | 1100 | 1 | 1100 |
| ODR-000027 | 2017-10-14 | 海辺のカフカ | 1700 | 1 | 1700 |
| ODR-000028 | 2017-10-14 | 忘れられた巨人 | 2100 | 1 | 2100 |
| ODR-000029 | 2017-10-15 | 海辺のカフカ | 1700 | 1 | 1700 |
| ODR-000030 | 2017-10-15 | 日の名残り | 2100 | 1 | 2100 |
| ODR-000031 | 2017-10-16 | スッキリわかるSQL入門 | 1100 | 1 | 1100 |
| ODR-000032 | 2017-10-16 | 革命のファンファーレ | 1600 | 1 | 1600 |
| ODR-000033 | 2017-10-17 | 騎士団長殺し | 4000 | 2 | 8000 |
| ODR-000034 | 2017-10-17 | Excel 最強の教科書 | 1800 | 1 | 1800 |
| ODR-000035 | 2017-10-18 | スッキリわかるSQL入門 | 1100 | 1 | 1100 |
| ODR-000036 | 2017-10-18 | LIFE SHIFT | 2000 | 1 | 2000 |
| ODR-000037 | 2017-10-19 | 日の名残り | 2100 | 1 | 2100 |
| ODR-000038 | 2017-10-19 | ノルウェイの森 | 1300 | 1 | 1300 |
| ODR-000039 | 2017-10-20 | 日の名残り | 2100 | 1 | 2100 |
| ODR-000040 | 2017-10-20 | 忘れられた巨人 | 2100 | 1 | 2100 |
| ODR-000041 | 2017-10-21 | 忘れられた巨人 | 2100 | 1 | 2100 |
| ODR-000042 | 2017-10-21 | スッキリわかるSQL入門 | 1100 | 1 | 1100 |
| ODR-000043 | 2017-10-22 | 革命のファンファーレ | 1600 | 1 | 1600 |
| ODR-000044 | 2017-10-22 | 海辺のカフカ | 1700 | 1 | 1700 |
| ODR-000045 | 2017-10-23 | 騎士団長殺し | 4000 | 1 | 4000 |
| ODR-000046 | 2017-10-23 | ノルウェイの森 | 1300 | 1 | 1300 |
| ODR-000047 | 2017-10-24 | わたしを離さないで | 900 | 1 | 900 |
| ODR-000048 | 2017-10-24 | スッキリわかるSQL入門 | 1100 | 1 | 1100 |
| ODR-000049 | 2017-10-25 | 忘れられた巨人 | 2100 | 1 | 2100 |
| ODR-000050 | 2017-10-25 | ノルウェイの森 | 1300 | 1 | 1300 |
| ODR-000051 | 2017-10-26 | 忘れられた巨人 | 2100 | 1 | 2100 |
| ODR-000052 | 2017-10-26 | LIFE SHIFT | 2000 | 1 | 2000 |
| ODR-000053 | 2017-10-27 | 革命のファンファーレ | 1600 | 1 | 1600 |
| ODR-000054 | 2017-10-27 | 日の名残り | 2100 | 2 | 4200 |
| ODR-000055 | 2017-10-28 | わたしを離さないで | 900 | 1 | 900 |
| ODR-000056 | 2017-10-28 | スッキリわかるSQL入門 | 1100 | 1 | 1100 |
| ODR-000057 | 2017-10-29 | データベースのきほん | 2400 | 1 | 2400 |
| ODR-000058 | 2017-10-29 | 革命のファンファーレ | 1600 | 1 | 1600 |
| ODR-000059 | 2017-10-30 | 忘れられた巨人 | 2100 | 1 | 2100 |
| ODR-000060 | 2017-10-30 | 多動力 | 1500 | 1 | 1500 |
| ODR-000061 | 2017-10-31 | Excel 最強の教科書 | 1800 | 1 | 1800 |
| ODR-000062 | 2017-10-31 | Excel 最強の教科書 | 1800 | 3 | 5400 |
+------------+------------+--------------------------------+------------+----------+-------------+
62 rows in set (0.02 sec)※赤文字のテーブルと青文字のテーブルを結合するイメージ ※「ORDER_INFOテーブルとPRODUCTテーブルに共通するproduct_cdをキーとして、ORDER_INFOテーブルにPRODUCTテーブルをくっつけ、のカラム（列）を抽出して」という命令
抽出結果を並び替える・集計する
売り上げ金額の降順に並び替える（売上金額が同じ場合は注文コードの昇順)
※このSQL以降、ORDER、PRODUCTというテーブル名はそれぞれodr、pdtと別名を使用されていますが同じ意味です。
SELECT odr.order_cd, odr.order_ymd, pdt.product_name, pdt.unit_price, odr.quantity, odr.total_price FROM ORDER_INFO odr INNER JOIN PRODUCT pdt ON odr.product_cd = pdt.product_cd ORDER BY odr.total_price DESC, odr.order_cd ASC;
mysql> SELECT
-> odr.order_cd, odr.order_ymd, pdt.product_name,
-> pdt.unit_price, odr.quantity, odr.total_price
-> FROM
-> ORDER_INFO odr
-> INNER JOIN
-> PRODUCT pdt
-> ON
-> odr.product_cd = pdt.product_cd
-> ORDER BY
-> odr.total_price DESC,
-> odr.order_cd ASC;
+------------+------------+--------------------------------+------------+----------+-------------+
| order_cd | order_ymd | product_name | unit_price | quantity | total_price |
+------------+------------+--------------------------------+------------+----------+-------------+
| ODR-000033 | 2017-10-17 | 騎士団長殺し | 4000 | 2 | 8000 |
| ODR-000062 | 2017-10-31 | Excel 最強の教科書 | 1800 | 3 | 5400 |
| ODR-000008 | 2017-10-04 | 日の名残り | 2100 | 2 | 4200 |
| ODR-000054 | 2017-10-27 | 日の名残り | 2100 | 2 | 4200 |
| ODR-000016 | 2017-10-08 | 騎士団長殺し | 4000 | 1 | 4000 |
| ODR-000045 | 2017-10-23 | 騎士団長殺し | 4000 | 1 | 4000 |
| ODR-000023 | 2017-10-12 | Excel 最強の教科書 | 1800 | 2 | 3600 |
| ODR-000009 | 2017-10-05 | データベースのきほん | 2400 | 1 | 2400 |
| ODR-000025 | 2017-10-13 | データベースのきほん | 2400 | 1 | 2400 |
| ODR-000057 | 2017-10-29 | データベースのきほん | 2400 | 1 | 2400 |
| ODR-000015 | 2017-10-08 | スッキリわかるSQL入門 | 1100 | 2 | 2200 |
| ODR-000001 | 2017-10-01 | 日の名残り | 2100 | 1 | 2100 |
| ODR-000010 | 2017-10-05 | 日の名残り | 2100 | 1 | 2100 |
| ODR-000011 | 2017-10-06 | 忘れられた巨人 | 2100 | 1 | 2100 |
| ODR-000022 | 2017-10-11 | 日の名残り | 2100 | 1 | 2100 |
| ODR-000028 | 2017-10-14 | 忘れられた巨人 | 2100 | 1 | 2100 |
| ODR-000030 | 2017-10-15 | 日の名残り | 2100 | 1 | 2100 |
| ODR-000037 | 2017-10-19 | 日の名残り | 2100 | 1 | 2100 |
| ODR-000039 | 2017-10-20 | 日の名残り | 2100 | 1 | 2100 |
| ODR-000040 | 2017-10-20 | 忘れられた巨人 | 2100 | 1 | 2100 |
| ODR-000041 | 2017-10-21 | 忘れられた巨人 | 2100 | 1 | 2100 |
| ODR-000049 | 2017-10-25 | 忘れられた巨人 | 2100 | 1 | 2100 |
| ODR-000051 | 2017-10-26 | 忘れられた巨人 | 2100 | 1 | 2100 |
| ODR-000059 | 2017-10-30 | 忘れられた巨人 | 2100 | 1 | 2100 |
| ODR-000003 | 2017-10-02 | LIFE SHIFT | 2000 | 1 | 2000 |
| ODR-000005 | 2017-10-03 | LIFE SHIFT | 2000 | 1 | 2000 |
| ODR-000019 | 2017-10-10 | LIFE SHIFT | 2000 | 1 | 2000 |
| ODR-000036 | 2017-10-18 | LIFE SHIFT | 2000 | 1 | 2000 |
| ODR-000052 | 2017-10-26 | LIFE SHIFT | 2000 | 1 | 2000 |
| ODR-000002 | 2017-10-01 | わたしを離さないで | 900 | 2 | 1800 |
| ODR-000006 | 2017-10-03 | Excel 最強の教科書 | 1800 | 1 | 1800 |
| ODR-000034 | 2017-10-17 | Excel 最強の教科書 | 1800 | 1 | 1800 |
| ODR-000061 | 2017-10-31 | Excel 最強の教科書 | 1800 | 1 | 1800 |
| ODR-000004 | 2017-10-02 | 海辺のカフカ | 1700 | 1 | 1700 |
| ODR-000021 | 2017-10-11 | 海辺のカフカ | 1700 | 1 | 1700 |
| ODR-000024 | 2017-10-12 | 海辺のカフカ | 1700 | 1 | 1700 |
| ODR-000027 | 2017-10-14 | 海辺のカフカ | 1700 | 1 | 1700 |
| ODR-000029 | 2017-10-15 | 海辺のカフカ | 1700 | 1 | 1700 |
| ODR-000044 | 2017-10-22 | 海辺のカフカ | 1700 | 1 | 1700 |
| ODR-000032 | 2017-10-16 | 革命のファンファーレ | 1600 | 1 | 1600 |
| ODR-000043 | 2017-10-22 | 革命のファンファーレ | 1600 | 1 | 1600 |
| ODR-000053 | 2017-10-27 | 革命のファンファーレ | 1600 | 1 | 1600 |
| ODR-000058 | 2017-10-29 | 革命のファンファーレ | 1600 | 1 | 1600 |
| ODR-000060 | 2017-10-30 | 多動力 | 1500 | 1 | 1500 |
| ODR-000012 | 2017-10-06 | ノルウェイの森 | 1300 | 1 | 1300 |
| ODR-000014 | 2017-10-07 | ノルウェイの森 | 1300 | 1 | 1300 |
| ODR-000018 | 2017-10-09 | ノルウェイの森 | 1300 | 1 | 1300 |
| ODR-000038 | 2017-10-19 | ノルウェイの森 | 1300 | 1 | 1300 |
| ODR-000046 | 2017-10-23 | ノルウェイの森 | 1300 | 1 | 1300 |
| ODR-000050 | 2017-10-25 | ノルウェイの森 | 1300 | 1 | 1300 |
| ODR-000007 | 2017-10-04 | スッキリわかるSQL入門 | 1100 | 1 | 1100 |
| ODR-000026 | 2017-10-13 | スッキリわかるSQL入門 | 1100 | 1 | 1100 |
| ODR-000031 | 2017-10-16 | スッキリわかるSQL入門 | 1100 | 1 | 1100 |
| ODR-000035 | 2017-10-18 | スッキリわかるSQL入門 | 1100 | 1 | 1100 |
| ODR-000042 | 2017-10-21 | スッキリわかるSQL入門 | 1100 | 1 | 1100 |
| ODR-000048 | 2017-10-24 | スッキリわかるSQL入門 | 1100 | 1 | 1100 |
| ODR-000056 | 2017-10-28 | スッキリわかるSQL入門 | 1100 | 1 | 1100 |
| ODR-000013 | 2017-10-07 | わたしを離さないで | 900 | 1 | 900 |
| ODR-000017 | 2017-10-09 | わたしを離さないで | 900 | 1 | 900 |
| ODR-000020 | 2017-10-10 | わたしを離さないで | 900 | 1 | 900 |
| ODR-000047 | 2017-10-24 | わたしを離さないで | 900 | 1 | 900 |
| ODR-000055 | 2017-10-28 | わたしを離さないで | 900 | 1 | 900 |
+------------+------------+--------------------------------+------------+----------+-------------+
62 rows in set (0.01 sec)※「ORDER_INFOテーブルとPRODUCTテーブルに共通するproduct_cdをキーとして、ORDER_INFOテーブルにPRODUCTテーブルをくっつけ、order_cd,order_ymd,product_name,unit_price,quantity,total_priceのカラム（列）を抽出して、total_price(合計金額)を降順にし、その後order_cd（オーダー番号)を昇順にして」という命令 ※ASC→昇順（12345) ※DESC→降順(54321)
商品毎の売り上げ金額と注文回数を抽出する
SELECT pdt.product_name, SUM(odr.total_price), COUNT(*) FROM ORDER_INFO odr INNER JOIN PRODUCT pdt ON odr.product_cd = pdt.product_cd GROUP BY pdt.product_name
mysql> SELECT
-> pdt.product_name, SUM(odr.total_price), COUNT(*)
-> FROM
-> ORDER_INFO odr
-> INNER JOIN
-> PRODUCT pdt
-> ON
-> odr.product_cd = pdt.product_cd
-> GROUP BY
-> pdt.product_name;
+--------------------------------+----------------------+----------+
| product_name | SUM(odr.total_price) | COUNT(*) |
+--------------------------------+----------------------+----------+
| Excel 最強の教科書 | 14400 | 5 |
| LIFE SHIFT | 10000 | 5 |
| わたしを離さないで | 6300 | 6 |
| スッキリわかるSQL入門 | 9900 | 8 |
| データベースのきほん | 7200 | 3 |
| ノルウェイの森 | 7800 | 6 |
| 多動力 | 1500 | 1 |
| 忘れられた巨人 | 14700 | 7 |
| 日の名残り | 21000 | 8 |
| 海辺のカフカ | 10200 | 6 |
| 革命のファンファーレ | 6400 | 4 |
| 騎士団長殺し | 16000 | 3 |
+--------------------------------+----------------------+----------+
12 rows in set (0.02 sec)※「ORDER_INFOテーブルとPRODUCTテーブルに共通するproduct_cdをキーとして、ORDER_INFOテーブルにPRODUCTテーブルをくっつけ、product_nameのカラム（列）とtotal_price(合計金額)を足したカラムとレコード数（注文回数）を数えたのち、product_nameでまとめて」という命令
列名を別名で表示した場合
SELECT pdt.product_name, SUM(odr.total_price) AS 売上金額合計 , COUNT(*) AS 注文回数 FROM ORDER_INFO odr INNER JOIN PRODUCT pdt ON odr.product_cd = pdt.product_cd GROUP BY pdt.product_name;
mysql> SELECT
-> pdt.product_name, SUM(odr.total_price) AS 売上金額合計 , COUNT(*) AS 注文回数
-> FROM
-> ORDER_INFO odr
-> INNER JOIN
-> PRODUCT pdt
-> ON
-> odr.product_cd = pdt.product_cd
-> GROUP BY
-> pdt.product_name;
+--------------------------------+--------------------+--------------+
| product_name | 売上金額合計 | 注文回数 |
+--------------------------------+--------------------+--------------+
| Excel 最強の教科書 | 14400 | 5 |
| LIFE SHIFT | 10000 | 5 |
| わたしを離さないで | 6300 | 6 |
| スッキリわかるSQL入門 | 9900 | 8 |
| データベースのきほん | 7200 | 3 |
| ノルウェイの森 | 7800 | 6 |
| 多動力 | 1500 | 1 |
| 忘れられた巨人 | 14700 | 7 |
| 日の名残り | 21000 | 8 |
| 海辺のカフカ | 10200 | 6 |
| 革命のファンファーレ | 6400 | 4 |
| 騎士団長殺し | 16000 | 3 |
+--------------------------------+--------------------+--------------+
12 rows in set (0.00 sec)※「ORDER_INFOテーブルとPRODUCTテーブルに共通するproduct_cdをキーとして、ORDER_INFOテーブルにPRODUCTテーブルをくっつけ、product_nameのカラムと、total_priceの合計金額を売上金額合計と名付けたカラムと、レコードの数を数えて注文回数と名付けたカラム（列）を抽出して、product_name(本の名前)でまとめて」という命令 ※ASは別名を付けることができる
商品毎の売上金額と注文回数を抽出し、売上金額合計の降順で並び替える
SELECT pdt.product_name, SUM(odr.total_price) AS 売上金額合計 , COUNT(*) AS 注文回数 FROM ORDER_INFO odr INNER JOIN PRODUCT pdt ON odr.product_cd = pdt.product_cd GROUP BY pdt.product_name ORDER BY SUM(odr.total_price) DESC;
mysql> SELECT
-> pdt.product_name, SUM(odr.total_price) AS 売上金額合計 , COUNT(*) AS 注文回数
-> FROM
-> ORDER_INFO odr
-> INNER JOIN
-> PRODUCT pdt
-> ON
-> odr.product_cd = pdt.product_cd
-> GROUP BY
-> pdt.product_name
-> ORDER BY
-> SUM(odr.total_price) DESC;
+--------------------------------+--------------------+--------------+
| product_name | 売上金額合計 | 注文回数 |
+--------------------------------+--------------------+--------------+
| 日の名残り | 21000 | 8 |
| 騎士団長殺し | 16000 | 3 |
| 忘れられた巨人 | 14700 | 7 |
| Excel 最強の教科書 | 14400 | 5 |
| 海辺のカフカ | 10200 | 6 |
| LIFE SHIFT | 10000 | 5 |
| スッキリわかるSQL入門 | 9900 | 8 |
| ノルウェイの森 | 7800 | 6 |
| データベースのきほん | 7200 | 3 |
| 革命のファンファーレ | 6400 | 4 |
| わたしを離さないで | 6300 | 6 |
| 多動力 | 1500 | 1 |
+--------------------------------+--------------------+--------------+
12 rows in set (0.00 sec)※「ORDER_INFOテーブルとPRODUCTテーブルに共通するproduct_cdをキーとして、ORDER_INFOテーブルにPRODUCTテーブルをくっつけ、product_nameのカラムと、total_priceの合計金額を売上金額合計と名付けたカラムと、レコードの数を数えて注文回数と名付けたカラム（列）を抽出して、product_name(本の名前)でまとめて、合計金額の降順に並べて」という命令
感想
非エンジニアがSQLを理解しておくと、一度エンジニアに出してもらった数値の期間や条件を変えて知りたい時に役立つかもと思いました。単純に日にちだけ変えれば良いと思うかもしれませんが、意外と細かいルールでハマってお手上げになることがあります。
今回のように、お手本のSQLを自分で書いて叩いて「こんな結果が返ってくるんだ」ということが分かってから、構文を勉強して自分で書いてみるとより理解が深まるのではないでしょうか。
また、これはSQLに限りませんが、エンジニアに「やってはいけないこと」を聞いておくか「やってはいけないことができない」環境を用意してもらった方が安全です。本番データベースには接続できないようにしてもらう、社内のネットワーク以外は接続できないようにしてもらうなど、生半可な知識でやると怖い環境設定周りはエンジニアに頼ろうと思います。
ちなみに次回は『自走するコミュニティ』のつくりかたというテーマで開催されるようです。ご興味ある方はぜひ。
2
