非エンジニア向けSQLの基礎の続きです
ゴール(再掲)
- 簡単な集計SQLを書けるようになること
- 会社ごとの〇〇率をグラフ化して、と言われてSQLを書きはじめられるようになること
- SQLの結果をRedashで棒グラフ、積み上げ棒グラフで表現できること
想定する受講者
- データベース関係の基本的な用語について、聞いたことはある人
- テーブル、カラム、レコード、フィールド、SQLなど。
- 参考) https://academy.gmocloud.com/know/20160425/2259
- Redashなどのダッシュボードツールを見たことはある人
関数を使えるようになろう
ExcelにあるIF()とかそういうアレ
MySQL 5.6 リファレンスマニュアル
以上
ではツラいのでいくつか抜粋
でも、自分でリファレンス読めるようになってくださいね
CASE WHEN ~ THEN ~ END
SELECT
CASE WHEN a.sign_in_count = 0 THEN 'ログインしたことない人'
WHEN a.sign_in_count < 5 THEN '何回かログインしたことある人'
ELSE 'めっちゃログインしたことある人'
END AS HOW_TO_CASE,
a.*
FROM accounts a
;
-
ExcelでいうIF関数
CASE WHEN {条件} THEN {値} ELSE {値} END
同じ事ができるIF関数もあるが、DBによって使えたりダメだったりするので、CASE WHEN ~ を覚えた方がよい。まじで。
COUNT()
SELECT COUNT(*) AS COMPANY_COUNT FROM companies AS c;
- 読んで字のごとくカウントします。
- COUNT(c.id)のようにカラム指定もできるが、この場合はNULLだとカウントされません。
SELECT COUNT(c.deleted_at) FROM companies c WHERE c.deleted_at IS NULL;
SELECT COUNT(c.id) FROM companies c WHERE c.deleted_at IS NULL;
* なぜ結果が異なるのか考えてみましょう。
COUNTの活用法
WHEREとの組み合わせ
- 〇〇な人何人いる?→どんなデータ入っている?
- そんなパターンでよく使う
SELECT COUNT(*) FROM owners o WHERE o.note IS NOT NULL;
* `COUNT(*)` → `o.*` に変更することで、データの中身がすぐに確認できる。
CASEとの組み合わせ
- Redashでよく使う。SQL一発の情報量が高まる。
- ログイン済みアカウントと、そうでないアカウントの集計
SELECT
COUNT(CASE WHEN a.sign_in_count = 0 THEN 1 ELSE NULL END) AS ACCOUNTS_NOT_SIGNIN,
COUNT(CASE WHEN a.sign_in_count > 0 THEN 1 ELSE NULL END) AS ACCOUNTS_SIGNIN
FROM accounts a
;
演習1
- 問1
- ownersテーブルから、owners.emailが入っていない人の件数をSQLで調査してください。
- 問2
- emailが入っている人、入っていない人の件数をSQL一発で出してください。
- 期待する実行結果↓
WITHOUT_EMAIL | WITH_EMAIL |
---|---|
9999 | 3333 |
関連テーブルの情報をくっつけてみよう(JOIN句)
SELECT o.name AS OWNER_NAME,
c.name AS COMPANY_NAME
FROM owners o
LEFT OUTER JOIN companies c ON c.id = o.company_id LIMIT 10 ;
演習2
- 問1
- 日本の住所を持っていないオーナーの物件一覧を抽出してください。
- 日本の住所を持っていない、は
owners.address_code IS NULL
でOK
- 問2
- 問1で出した物件が、東京・神奈川・埼玉に何件ずつあるのか出してください。
TOKYO_COUNT | KANAGAWA_COUNT | SAITAMA_COUNT |
---|---|---|
9999 | 3333 | 1111 |