Upgrade to Pro — share decks privately, control downloads, hide ads and more …

データベース研修(SQL基礎編)【ミクシィ22新卒技術研修】

 データベース研修(SQL基礎編)【ミクシィ22新卒技術研修】

22新卒技術研修で実施したデータベース研修(SQL演習編)の講義資料です。
動画:https://youtu.be/dseGQ2MZF1U

演習用Colab Notebook
https://colab.research.google.com/github/mixigroup/2022BeginnerTrainingDataBasePublic/blob/master/22db_sql.ipynb

データベース基礎編
https://speakerdeck.com/mixi_engineers/2022-database-training

MIXI ENGINEERS

April 21, 2022
Tweet

More Decks by MIXI ENGINEERS

Other Decks in Programming

Transcript

  1. 目次 1. 概要 2. データの取得 3. データの操作 4. 集計 5.

    条件式 このあたりで昼休憩 6. 集合操作 7. インデックス 8. 最後に 2
  2. 概要 • 本演習では座学と演習を通してSQLについて学んでいきます。 • SQLの用例にも触れますが、データ分析に寄った内容になることが多いので、ご了承くださ い。 • 演習ではBigQueryとSQLiteを用います。 • SQLiteでの演習では以下のColab

    Notebookを使います。 https://colab.research.google.com/github/mixigroup/2022BeginnerTrainingDataBase Public/blob/master/22db_sql.ipynb ◦ ★ Notebookをマイドライブにコピーし「設定」を行いましょう。 • BigQueryでの演習では、以下のいずれかを使います。 ◦ Colab Notebook ◦ BigQueryコンソール (演習問題はColabにあります。)(補完が効いて便利) • 演習の時間は区切り毎に取りますが、解説中に演習を解いても構いません。やりやすい方 法で取り組んで下さい。 ◦ 解説で登場するテーブルと、演習で扱うテーブルは、別物です! 3
  3. 1. SQLiteを用いたデータの操作 基本的なテーブル・レコード操作について、SQLiteを用いて演習します。 - CREATE TABLE - INSERT - UPDATE

    - DELETE - DROP TABLE ※BigQueryの無料のサンドボックスモードではINSERT,UPDATE,DELETEの操作ができないため、 SQLiteを使用します。 4
  4. データの操作 (レコードの削除) • 検索条件を指定しない場合は全レコードが削除されます。 • usersの全レコードを削除する場合は以下のように記述します。 • なお、MySQL等のRDBMSの場合、テーブルの全レコードを削除するSQLは2種類 存在します。 DELETE

    FROM users 9 • DELETE文 ◦ トランザクションを使っている場合はロールバックできる。 • TRUNCATE文 ◦ テーブルを作り直す。 ◦ DELETE文より高速に動作し、AUTO INCREMENTを初期化できる。 ◦ RDBMSによってはロールバックできない。
  5. データの取得 (ORDER BY句) • 取得するレコードの順番を並び替える際はORDER BY句を使います。 • ソートに使うカラムの後ろに昇順で並べる場合は「ASC」を、降順で並べる場合は 「DESC」を指定します。何も指定しない場合は昇順になります。 •

    idでソートする場合は以下のように記述します。 15 SELECT id, name FROM users ORDER BY id ASC LIMIT 10 SELECT id, name FROM users ORDER BY id DESC LIMIT 10 id name 1 一串 2 二串 ... ... 10 十串 id name 99 九九串 98 九八串 ... ... 90 九〇串
  6. 集計 (GROUP BY句) • カラムでグループ分けした結果に対して集計を行う場合、GROUP BY句を使いま す。HAVING句で集計後の値を使った条件指定を行えます。 • 成人しているユーザーを対象に、2人以上該当者がいる年齢とその人数を集計す る場合、右の例のように記述します。

    SELECT <カラム> FROM <テーブル> WHERE <条件> GROUP BY <集約するカラム名 or カラムの番号> HAVING <集約後の結果に対する条件> 21 SELECT age, COUNT(1) AS cnt FROM users WHERE 20 <= age GROUP BY age HAVING 2 <= cnt age cnt 20 2 22 5 ... ...
  7. • 条件分岐に該当するもので、2通りの書き方があります。 • 単純CASE式 • 検索CASE式 • ELSEを省略するとNULLを返します 条件式 (CASE式)

    CASE <カラム> WHEN <値> THEN <カラム = 値のときの返り値> … ELSE <上記を満たさないときの返り値> END CASE WHEN <条件> THEN <条件を満たすときの返り値> … ELSE <上記を満たさないときの返り値> END 24
  8. 真偽値を返す構文 便利な構文がいくつかあります。 • IN句 ◦ item IN (1,2,3) ◦ item

    = 1 OR item = 2 OR item = 3 と同義 • BETWEEN句 ◦ id BETWEEN 10 AND 20 ◦ 10 <= id AND id <= 20 と同義。両端を含みます。 • LIKE句 ◦ name LIKE "Wata%be" ◦ % は0文字以上の任意の文字列にマッチ。上の例は「 Watabe」「Watanabe」両方にマッチします。 ◦ _ は任意の1文字にマッチ • ★ Notebookの「条件式」をやってみましょう。 25
  9. 集合操作 (UNION句) • クエリ結果を縦方向に結合させる場合はUNION句を使います。 • UNION句は2種類あります。 30 • UNION ◦

    重複するレコードの排除が行われるが、その分速度が落ちる。 • UNION ALL ◦ 重複するレコードの排除を行わないため高速。 SELECT ‘table1’ AS label, col1 FROM table1 UNION SELECT ‘table2’ AS label, col1 FROM table2 SELECT ‘table1’ AS label, col1 FROM table1 UNION ALL SELECT ‘table2’ AS label, col1 FROM table2 label col1 table1 A ... ... table2 B ... ...
  10. 集合操作 (JOIN) • 複数のテーブルやサブクエリを特定の条件で横方向に結合することができます。 • JOINは3種類あります。 32 • 内部結合 (INNER

    JOIN) ◦ 条件に一致するレコードのみを結合する • 外部結合 (OUTER JOIN) ◦ 条件に一致するレコードがない場合はNULLとして結合する • クロス結合 (CROSS JOIN) ◦ 直積 ◦ 配列データをバラすときなどに使用
  11. 集合操作 (INNER JOIN) • ユーザーテーブル users とログイン履歴 logins を内部結合 (INNER

    JOIN) する 場合を考えてみます。 • users.idとlogins.user_idで結合できるので、以下のようなクエリになります。 SELECT * FROM users AS u JOIN logins AS l ON u.id = l.user_id 33 users由来 logins由来 id name age id user_id time kind 2 二串 61 1 2 2021-04-01 12:00:01 1 5 五串 45 3 5 2021-04-01 12:03:35 0 ... ... ... ... ... ... ...
  12. 集合操作 (OUTER JOIN) • ログインしていないユーザー (JOINの左のusersにはレコードがあるが、右のlogins にはない) のレコードも出す場合はLEFT OUTER JOINにします。

    • ログインしていないユーザーのlogins由来のカラムにはNULLが入ります。 • なお、実際に使うことは皆無ですが、RIGHT OUTER JOINもあります。 SELECT * FROM users AS u LEFT OUTER JOIN logins AS l ON u.id = l.user_id 34 users由来 logins由来 id name age id user_id time kind 1 一串 32 NULL NULL NULL NULL 2 二串 61 1 2 2021-04-01 12:00:01 1 ... ... ... ... ... ... ...