データベース研修 分析向けSQL入門【MIXI 24新卒技術研修】

本スライドは、MIXIの2024年度新卒向け技術研修で使用された資料です。

＜科目名＞
データベース研修
分析向けSQL入門

＜関連リンク＞
動画▶ https://youtu.be/gVMi0FVawMM
リポジトリ▶ https://github.com/mixigroup/2024BeginnerTrainingDataBaseAnalysisPublic

※お願い※ 〜 資料・動画・リポジトリのご利用について〜
公開している資料や動画は、是非、勉強会や社内の研修などにご自由にお使いいただければと思いますが、以下のような場でのご利用はご遠慮ください。
- 受講者から参加費や授業料など金銭を集めるような場での利用
（会場費や飲食費など勉強会の運営に必要な実費を集める場合は問題ありません）
- 出典を削除または改変しての利用

Transcript

1. ©MIXI 24新卒技術研修 データベース研修 分析向けSQL⼊⾨ 2024.04.19
2. 2 ©MIXI 本講座の⽬的 SQLをマスターする （分析向けSQLの基礎/応⽤⼒を⾝につけて⾃⼒でデータ分析できるようになる）
3. 3 ©MIXI ⽬次 1. Introduction 2. SQL⼊⾨演習 3. SQLチャレンジ演習
4. ©MIXI Introduction
5. 5 ©MIXI Introduction 1. SQL 2. リレーショナルデータモデル 3. Google BigQuery
6. 6 ©MIXI SQL 1. リレーショナルデータモデルを設計基盤として開発された宣⾔型クエリ⾔語 2. 集合を基本のデータ構造とする 3. 1986年 ANSI(American National Standards Institute)により標準化 a. 1989, 1992, 1999, 2003, 2008, 2011,2016年に新しい規格がリリース b. SQL:1999では再帰問い合わせの導⼊によりチューリング完全に c. SQL:2016ではJSONを扱う関数が追加 4. 多くの製品は標準SQLへ準拠しているが⽅⾔(dialect)も多い
7. 7 ©MIXI リレーショナルデータモデル 1. 数学的で徹底的にフォーマルなデータモデル a. 集合論に基づいてデータを表現 b. ⾼度に抽象的（実装に無関⼼） c. どこにデータがあるか、どうやって取得するか、パフォーマンスなどは実装の問題 2. データモデルは原理 a. 原理：本質的な性質 b. 製品や技術は変化するが原理は持続⼒がある 3. 歴史 a. E. F. Coddにより1969 年に掲載された論⽂がオリジナル b. 構造、整合性、操作の 3 つの基本要素で構成
8. 8 ©MIXI リレーショナルデータモデルの構造 Domain(Type) Player Item Relation : HeadingとBody Heading: Attributeの集合 Attribute : (Name, Type) Body : Attributeに準拠するTupleの集合 Relation: プレイヤー所持アイテム Heading Body Tuple: AttributeのDomainに対応 する実際の値の集合 Attribute: Tupleの構造を定義 ⾒出しに含まれ る属性の数 => degree(次数), arity(項数) 本体に含まれるタプルの数 => 濃度(cardinality)
9. 9 ©MIXI リレーショナルデータモデルの操作 ⼊出⼒どちらも”関係”型である => ある演算の結果を他の演算の⼊⼒にすることが可能 => ネストしたリレーショナル式を書くことができる 図はC. J. Date 著『Database in Depth: Relational Theory for Practitioners』 (O'Reilly Media, Inc.)より引⽤ 閉包性(Closure property)
10. 10 ©MIXI Google BigQuery テラバイト級データを数秒で処理できる サーバーレスでスケーラブルなデータウェアハウス BigQueryの特徴 1. SQLでクエリ可能 2. カラムナ（列指向）ストレージ 3. ComputeとStorageの分離 a. 今までのDWHでは計算するマシンとデータを保存するマシンが同じマシン。ストレージ追加のため にマシンのスケールアップが必要だった b. データの物理的な場所に関係なく迅速にクエリできる 4. Google Cloudの他サービスとの連携が容易。 5. BigQueryを⽀える技術: Dremel, Colossus File System, Jupiter networking etc
11. 11 ©MIXI 列指向 1. 集計処理の際、必要な列のデータのみ取り出せる 2. 圧縮しやすい a. 連続するデータは連⻑（ランレングス）圧縮が有効 b. カーディナリティ(濃度)が低いならビットマップエンコー ディングが使える 3. 書き込みは苦⼿ 「列に含まれるすべての値をまとめて保存」 name age Mike 25 Alice 23 John 21 列方向にデータを保存
12. 12 ©MIXI Google BigQueryの料⾦体系 基本使った分だけ課⾦（従量課⾦） コンピューティング料⾦の課⾦体系はOn-demandとCapacityから選べる 料⾦2024年4⽉17⽇時点におけるTokyo(asia-northeast1)リージョンのものを掲載 最新の情報は公式ドキュメントをご参考ください https://cloud.google.com/bigquery/pricing 1. On-demand: スキャンしたデータサイズに応じて課⾦. (default) a. $7.5 per TiB 2. Capacity: 使⽤したコンピュートリソース(CPU)で課⾦. a. スロットという単位で管理（1slot≒0.5vCPU） b. スキャンに必要な分だけ消費され、消費した分だけ課⾦される。 c. $0.0765 / slot hour (Enterprise Edition) 3. ストレージ料⾦ a. $0.052 per GiB per month
13. ©MIXI 分析向けSQL⼊⾨演習
14. 14 ©MIXI 概要 • 本演習では座学と演習を通して、分析向けのSQLについて学んでいきます • 演習の時間は解説後に設けますが解説中に演習を解いても構いません。やりやすい⽅ 法で取り組んで下さい • 本スライドの解説で登場するテーブルと演習で扱うテーブルは別物です！
15. 15 ©MIXI 概要 - 実⾏環境について • 演習ではGoogle Cloud BigQueryを⽤います • 演習問題は次のURLにあります ◦ https://github.com/mixigroup/2024BeginnerTrainingDataBaseAnalysisPublic • BigQueryを使った演習はGoogle Colaboratoryを⽤いてやります
16. 16 ©MIXI 分析向けSQL⼊⾨演習 1. データの取得 2. 集計処理 3. 条件式 4. 集合操作（サブクエリ‧UNION） 5. 集合操作（JOIN） 6. 分析関数
17. ©MIXI 1. データの取得
18. 18 ©MIXI 1. データの取得 SELECT⽂を⽤いたデータの取得について、BigQueryを⽤いて演習を⾏います。 - SELECT - LIMIT - ORDER BY - WHERE
19. 19 ©MIXI 1.1 データの取得 (SELECT⽂) - DBからデータを取得する際にはSELECT⽂を使⽤します。 - usersテーブルから、ユーザーID (id) とユーザー名 (name) を取得する場合は下の例 のように記述します。 SELECT <カラム> FROM <テーブル名> SELECT id, name FROM users id name 1 一串 2 二串 ... ... 99 九九串
20. 20 ©MIXI 1.2 データの取得 (LIMIT句) - SELECT⽂の末尾にLIMIT句をつけると、取得するレコード数を制限できます。 - usersテーブルから10件取得したい場合は以下のように記述します。 SELECT id, name FROM users LIMIT 10 id name 1 一串 2 二串 ... ... 10 十串
21. 21 ©MIXI 1.3 データの取得 (ORDER BY句) - 取得するレコードの順番を並び替える際はORDER BY句を使います。 - ソートに使うカラムの後ろに昇順で並べる場合は「ASC」を、 降順で並べる場合は「DESC」を指定します。何も指定しない場合は昇順になります。 - idでソートする場合は以下のように記述します。 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 九〇串
22. 22 ©MIXI 1.4 データの取得 (WHERE句) - 検索条件を指定するときはWHERE句を使います。 - ユーザーの年齢が⼊っている「age」から、 18歳以上のユーザーの情報を取得する場合は以下のように記述します。 SELECT id, name, age FROM users WHERE 18 <= age LIMIT 10 id name age 1 一串 32 2 二串 61 ... ... ... 10 十串 20
23. 23 ©MIXI 1.5 SELECT⽂の構成 [ WITH [ RECURSIVE ] { non_recursive_cte | recursive_cte }[, ...] ] { select | ( query_expr ) | set_operation } [ ORDER BY expression [{ ASC | DESC }] [, ...] ] [ LIMIT count [ OFFSET skip_rows ] ] select: SELECT [ WITH differential_privacy_clause ] [ { ALL | DISTINCT } ] [ AS { STRUCT | VALUE } ] select_list [ FROM from_clause[, ...] ] [ WHERE bool_expression ] [ GROUP BY group_by_specification ] [ HAVING bool_expression ] [ QUALIFY bool_expression ] [ WINDOW window_clause ] SELECT⽂は句の順番が決まっています。 順番がわからなくなった時は公式リファレンスを確認してください。 https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax
24. 24 ©MIXI 1.6 おまけ: ⽂と句 1. ⽂（Statement）は句（clause）で構成される 2. SELECT⽂の場合、必須となる句はSELECT句のみ。 a. SELECT 1は有効なSELECT⽂ 3. JOINやUNIONなどは句ではない。 a. JOINはFROM句で利⽤できるキーワード b. UNIONは演算⼦
25. 25 ©MIXI 1.7 データの取得 (演習) ★ Notebookの「設定」をやってみましょう。 ★ Notebookの「データの取得」をやってみましょう。
26. ©MIXI 2. 集計処理
27. 27 ©MIXI 2. 集計処理 - COUNT - GROUP BY - HAVING
28. 28 ©MIXI 2.1 集計 (COUNT関数) - レコード数を数える際はCOUNT関数を使います。 - COUNT関数は評価する式がNULLではないレコードの数を返します。 - COUNT(1), COUNT(*)の場合NULLレコードも含めて全件数えます。 - usersテーブルのレコード数を数える場合は以下のように記述します。 - 集計関数にはCOUNT関数以外に、合計を出すSUM関数や平均を出すAVG関数なども あります。 SELECT COUNT(1) AS cnt FROM users cnt 99
29. 29 ©MIXI 2.2 集計 (GROUP BY句‧HAVING句) - カラムでグループ分けした結果に対して集計を⾏う場合、GROUP BY句を使います。 また、HAVING句で集計後の値を使った条件指定を⾏えます。 - 20歳以上のユーザーを対象に、2⼈以上該当者がいる年齢とその⼈数を集計する場 合、右の例のように記述します。 SELECT <カラム> FROM <テーブル> WHERE <条件> GROUP BY <集約するカラム名 or カラムの番号> HAVING <集約後の結果に対する条件> SELECT age, COUNT(1) AS cnt FROM users WHERE 20 <= age GROUP BY age HAVING 2 <= cnt age cnt 20 2 22 5 ... ...
30. 30 ©MIXI 2.3 集計 (演習) ★ Notebookの「集計」をやってみましょう。
31. ©MIXI 3. 条件分岐
32. 32 ©MIXI 3. 条件分岐 - CASE式 - 真偽値を返す演算⼦ - IN - LIKE - BETWEEN
33. 33 ©MIXI 3.1 条件式 (CASE式) 単純CASE式 条件分岐に該当するもので、2通りの書き⽅があります。 ELSEを省略するとNULLを返します CASE <カラム> WHEN <値> THEN <カラム = 値のときの返り値> … ELSE <上記を満たさないときの返り値> END 検索CASE式 CASE WHEN <条件> THEN <条件を満たすときの返り値> … ELSE <上記を満たさないときの返り値> END
34. 34 ©MIXI 3.2 真偽値を返す演算⼦ - 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⽂字にマッチ 便利な演算⼦がいくつかあります。
35. 35 ©MIXI 3.3 条件式 (演習) ★ Notebookの「条件式」をやってみましょう。
36. ©MIXI 4. 集合操作（サブクエリ‧UNION）
37. 37 ©MIXI 4. 集合操作（サブクエリ‧UNION） - サブクエリ - WITH - UNION
38. 38 ©MIXI 4.1 集合操作 (サブクエリ) SELECT age, cnt FROM ( SELECT age, COUNT(1) AS cnt FROM users GROUP BY age ) WHERE 2 <= cnt age cnt 20 2 22 5 ... ... - SELECTの結果を別のクエリ内で使⽤することをサブクエリと⾔います。 - ユーザーテーブルにおいて、2⼈以上該当者がいる年齢とその⼈数を数える場合、以下 のように記述します。
39. 39 ©MIXI 4.2 集合操作 (WITH句) - サブクエリに名前を付けてクエリの外側へ持って⾏く場合はWITH句を使います。 - ネストを下げたり、サブクエリを複数箇所で使⽤したりすることができます。 WITH ages AS ( SELECT age, COUNT(1) AS cnt FROM users GROUP BY age ) SELECT age, cnt FROM ages WHERE 2 <= cnt age cnt 20 2 22 5 ... ...
40. 40 ©MIXI 4.3 集合操作 (UNION演算⼦) • UNION DISTINCT ◦ 重複するレコードの排除が行われるが、その分速度が落ちる。 • UNION ALL ◦ 重複するレコードの排除を行わないため高速。 SELECT ‘table1’ AS label, col1 FROM table1 UNION DISTINCT 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 ... ... - クエリ結果を縦⽅向に結合させる場合はUNION演算⼦を使います。 - UNION演算⼦は2種類あります。
41. 41 ©MIXI 4.4 集合操作 (演習) ★ Notebookの「集合操作（サブクエリ‧UNION）」をやってみましょう。
42. ©MIXI 5. 集合操作（JOIN）
43. 43 ©MIXI 5 集合操作 (JOIN) • 内部結合 (INNER JOIN) ◦ 条件に一致するレコードのみを結合する • 外部結合 (OUTER JOIN) ◦ 条件に一致するレコードがない場合はNULLとして結合する • クロス結合 (CROSS JOIN) ◦ 直積 ◦ 配列データをバラすときなどに使用 - 複数のテーブルやサブクエリを特定の条件で横⽅向に結合することができます。 - JOINは3種類あります。
44. 44 ©MIXI 5.1 集合操作 (INNER JOIN) SELECT * FROM users AS u JOIN logins AS l ON u.id = l.user_id 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 ... ... ... ... ... ... ... - ユーザーテーブル users とログイン履歴 logins を内部結合 (INNER JOIN) する場合を 考えてみます。 - ONの後に結合条件を書きます - users.idとlogins.user_idで結合できるので、以下のようなクエリになります。
45. 45 ©MIXI 5.2 集合操作 (OUTER JOIN) SELECT * FROM users AS u LEFT OUTER JOIN logins AS l ON u.id = l.user_id 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 ... ... ... ... ... ... ... - ログインしていないユーザー (JOINの左のusersにはレコードがあるが、右のlogins にはない) のレコードも出す場合はLEFT OUTER JOINにします。 - ログインしていないユーザーのlogins由来のカラムにはNULLが⼊ります。 - なお、実際に使うことはほぼありませんが、RIGHT OUTER JOINもあります。
46. 46 ©MIXI 5.3 集合操作 (USING) - 結合条件を書く際にはON以外にもUSINGを⽤いることが出来ます - 結合するテーブル間で共通のカラム名を指定し、そのカラムを基準に結合がおこなわ れます SELECT * FROM users AS u JOIN logins AS l USING(user_id) users由来 logins由来 user_id name age id user_id kind 2 二串 61 1 2 1 5 五串 45 3 5 0 ... ... ... ... ... ... - メリットは、可読性が⾼くなることです - デメリットは、共通のカラム名でないと使えないことです
47. 47 ©MIXI 5.4 集合操作 (CROSS JOIN) id name 1 一串 1 二串 1 三串 SELECT id, name FROM user_list AS t CROSS JOIN UNNEST(names) AS name - テーブルの直積をとる場合はCROSS JOINを使います。 - 主に、配列データをバラすときなどに使⽤します。 id names 1 一串 二串 三串
48. 48 ©MIXI 5.5 集合操作 (演習) ★ Notebookの「集合操作（JOIN)」をやってみましょう。
49. ©MIXI 6. 分析関数
50. 50 ©MIXI 6. 分析関数（ウィンドウ関数） ⾏をウィンドウ(window)にまとめる能⼒ Analytic function（解析関数、分析関数）とも呼ぶ。 分析関数特有の関数 - LAG/LEAD関数: 前の⾏/後続の⾏の値を取得する - FIRST_VALUE関数: ウィンドウ内の最初の値 - ROW_NUMBER/RANK/DENSE_RANK: 順序付け. タイ(同率)の扱いが異なる. 集計関数もウィンドウ関数に対応 - COUNT関数: ウィンドウに含まれる⾏数をカウント - SUM関数: ウィンドウに含まれる値の合計 - MAX関数: ウィンドウに含まれる値の中の最⼤値 など
51. 51 ©MIXI 6. 分析関数（ウィンドウ関数） SELECT num, SUM(num) OVER(ORDER BY num ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) FROM UNNEST(GENERATE_ARRAY(1,12)) num num sum 1 null 2 1 3 3 4 6 num sum 5 9 6 12 7 15 8 18 num sum 9 21 10 24 11 27 12 30
52. 52 ©MIXI 6. 分析関数（ウィンドウ関数） SELECT num, SUM(num) OVER(ORDER BY num ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) FROM UNNEST(GENERATE_ARRAY(1,12)) num num sum 1 null 2 1 3 3 4 6 num sum 5 9 6 12 7 15 8 18 num sum 9 21 10 24 11 27 12 30
53. 53 ©MIXI 6. 分析関数（ウィンドウ関数） SELECT num, SUM(num) OVER(ORDER BY num ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) FROM UNNEST(GENERATE_ARRAY(1,12)) num num sum 1 null 2 1 3 3 4 6 num sum 5 9 6 12 7 15 8 18 num sum 9 21 10 24 11 27 12 30
54. 54 ©MIXI 6. 分析関数（ウィンドウ関数） 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15 SELECT num, SUM(num) OVER(ORDER BY num ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) FROM UNNEST(GENERATE_ARRAY(1,15)) num 6 ウィンドウ（窓）が動いていくイメージ
55. 55 ©MIXI 6. 分析関数（ウィンドウ関数） 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15 SELECT num, SUM(num) OVER(ORDER BY num ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) FROM UNNEST(GENERATE_ARRAY(1,15)) num ウィンドウ（窓）が動いていくイメージ 9
56. 56 ©MIXI 6. 分析関数（ウィンドウ関数） 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15 SELECT num, SUM(num) OVER(ORDER BY num ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) FROM UNNEST(GENERATE_ARRAY(1,15)) num 12 ウィンドウ（窓）が動いていくイメージ
57. 57 ©MIXI 6. 分析関数（ウィンドウ関数） 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15 SELECT num, SUM(num) OVER(ORDER BY num ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), SUM(num) OVER(ORDER BY num ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) FROM UNNEST(GENERATE_ARRAY(1,15)) num ⾃分⾃⾝や後続の⾏を含めたり New！
58. 58 ©MIXI 6. 分析関数（ウィンドウ関数） SELECT num, SUM(num) OVER( PARTITION BY MOD(num, 2) ORDER BY num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM UNNEST(GENERATE_ARRAY(1,12)) num ORDER BY num num MOD(num,2) sum 1 1 1 2 0 2 3 1 4 4 0 6 5 1 9 6 0 12 7 1 16 PARTITION BYで指定したグループごと に集計したりなど強⼒な分析⼒を提供 New！
59. 59 ©MIXI 6. 分析関数（ウィンドウ関数） SELECT num, SUM(num) OVER( PARTITION BY MOD(num, 2) ORDER BY num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM UNNEST(GENERATE_ARRAY(1,12)) num ORDER BY num num MOD(num,2) sum 1 1 1 2 0 2 3 1 4 4 0 6 5 1 9 6 0 12 7 1 16 PARTITION BYで指定したグループごと に集計したりなど強⼒な分析⼒を提供 New！
60. 60 ©MIXI 6. 分析関数（ウィンドウ関数）の構⽂ function_name ( [ argument_list ] ) OVER over_clause over_clause: { named_window | ( [ window_specification ] ) } window_specification: [ named_window ] [ PARTITION BY partition_expression [, ...] ] [ ORDER BY expression [ { ASC | DESC } ] [, ...] ] [ window_frame_clause ] window_frame_clause: { rows_range } { frame_start | frame_between } rows_range: { ROWS | RANGE } https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
61. 61 ©MIXI 6. 分析関数（ウィンドウ関数）の構⽂ window_frame_clause: { rows_range } { frame_start | frame_between } rows_range: { ROWS | RANGE } frame_between: { BETWEEN unbounded_preceding AND frame_end_a | BETWEEN numeric_preceding AND frame_end_a | BETWEEN current_row AND frame_end_b | BETWEEN numeric_following AND frame_end_c } frame_start: { unbounded_preceding | numeric_preceding | [ current_row ] } frame_end_a: { numeric_preceding | current_row | numeric_following | unbounded_following } https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
62. 62 ©MIXI 6. 分析関数 (演習) ★ Notebookの「分析関数（ウィンドウ関数）」をやってみましょう。
63. ©MIXI お疲れ様でした！
64. 64 ©MIXI 最後に 1. ★ Notebookの「⽚付け」でGoogle Cloudプロジェクトを削除してください。 2. 復習を⾏いたい場合はもう⼀度「設定」を⾏うと新たなGoogle Cloudプロジェクトを 作成できます。