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

Slide 1

Slide 1 text

©MIXI 24新卒技術研修データベース研修分析向けSQL⼊⾨ 2024.04.19

Slide 2

Slide 2 text

2 ©MIXI 本講座の⽬的 SQLをマスターする（分析向けSQLの基礎/応⽤⼒を⾝につけて⾃⼒でデータ分析できるようになる）

Slide 3

Slide 3 text

3 ©MIXI ⽬次 1. Introduction 2. SQL⼊⾨演習 3. SQLチャレンジ演習

Slide 4

Slide 4 text

©MIXI Introduction

Slide 5

Slide 5 text

5 ©MIXI Introduction 1. SQL 2. リレーショナルデータモデル 3. Google BigQuery

Slide 6

Slide 6 text

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)も多い

Slide 7

Slide 7 text

7 ©MIXI リレーショナルデータモデル 1. 数学的で徹底的にフォーマルなデータモデル a. 集合論に基づいてデータを表現 b. ⾼度に抽象的（実装に無関⼼） c. どこにデータがあるか、どうやって取得するか、パフォーマンスなどは実装の問題 2. データモデルは原理 a. 原理：本質的な性質 b. 製品や技術は変化するが原理は持続⼒がある 3. 歴史 a. E. F. Coddにより1969 年に掲載された論⽂がオリジナル b. 構造、整合性、操作の 3 つの基本要素で構成

Slide 8

Slide 8 text

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)

Slide 9

Slide 9 text

9 ©MIXI リレーショナルデータモデルの操作⼊出⼒どちらも”関係”型である => ある演算の結果を他の演算の⼊⼒にすることが可能 => ネストしたリレーショナル式を書くことができる図はC. J. Date 著『Database in Depth: Relational Theory for Practitioners』 (O'Reilly Media, Inc.)より引⽤閉包性(Closure property)

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

11 ©MIXI 列指向 1. 集計処理の際、必要な列のデータのみ取り出せる 2. 圧縮しやすい a. 連続するデータは連⻑（ランレングス）圧縮が有効 b. カーディナリティ(濃度)が低いならビットマップエンコーディングが使える 3. 書き込みは苦⼿「列に含まれるすべての値をまとめて保存」 name age Mike 25 Alice 23 John 21 列方向にデータを保存

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

©MIXI 分析向けSQL⼊⾨演習

Slide 14

Slide 14 text

14 ©MIXI 概要 ● 本演習では座学と演習を通して、分析向けのSQLについて学んでいきます ● 演習の時間は解説後に設けますが解説中に演習を解いても構いません。やりやすい⽅法で取り組んで下さい ● 本スライドの解説で登場するテーブルと演習で扱うテーブルは別物です！

Slide 15

Slide 15 text

15 ©MIXI 概要 - 実⾏環境について ● 演習ではGoogle Cloud BigQueryを⽤います ● 演習問題は次のURLにあります ○ https://github.com/mixigroup/2024BeginnerTrainingDataBaseAnalysisPublic ● BigQueryを使った演習はGoogle Colaboratoryを⽤いてやります

Slide 16

Slide 16 text

16 ©MIXI 分析向けSQL⼊⾨演習 1. データの取得 2. 集計処理 3. 条件式 4. 集合操作（サブクエリ‧UNION） 5. 集合操作（JOIN） 6. 分析関数

Slide 17

Slide 17 text

©MIXI 1. データの取得

Slide 18

Slide 18 text

18 ©MIXI 1. データの取得 SELECT⽂を⽤いたデータの取得について、BigQueryを⽤いて演習を⾏います。 - SELECT - LIMIT - ORDER BY - WHERE

Slide 19

Slide 19 text

19 ©MIXI 1.1 データの取得 (SELECT⽂) - DBからデータを取得する際にはSELECT⽂を使⽤します。 - usersテーブルから、ユーザーID (id) とユーザー名 (name) を取得する場合は下の例のように記述します。 SELECT <カラム> FROM <テーブル名> SELECT id, name FROM users id name 1 一串 2 二串 ... ... 99 九九串

Slide 20

Slide 20 text

20 ©MIXI 1.2 データの取得 (LIMIT句) - SELECT⽂の末尾にLIMIT句をつけると、取得するレコード数を制限できます。 - usersテーブルから10件取得したい場合は以下のように記述します。 SELECT id, name FROM users LIMIT 10 id name 1 一串 2 二串 ... ... 10 十串

Slide 21

Slide 21 text

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 九〇串

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

24 ©MIXI 1.6 おまけ: ⽂と句 1. ⽂（Statement）は句（clause）で構成される 2. SELECT⽂の場合、必須となる句はSELECT句のみ。 a. SELECT 1は有効なSELECT⽂ 3. JOINやUNIONなどは句ではない。 a. JOINはFROM句で利⽤できるキーワード b. UNIONは演算⼦

Slide 25

Slide 25 text

25 ©MIXI 1.7 データの取得 (演習) ★ Notebookの「設定」をやってみましょう。 ★ Notebookの「データの取得」をやってみましょう。

Slide 26

Slide 26 text

©MIXI 2. 集計処理

Slide 27

Slide 27 text

27 ©MIXI 2. 集計処理 - COUNT - GROUP BY - HAVING

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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 ... ...

Slide 30

Slide 30 text

30 ©MIXI 2.3 集計 (演習) ★ Notebookの「集計」をやってみましょう。

Slide 31

Slide 31 text

©MIXI 3. 条件分岐

Slide 32

Slide 32 text

32 ©MIXI 3. 条件分岐 - CASE式 - 真偽値を返す演算⼦ - IN - LIKE - BETWEEN

Slide 33

Slide 33 text

33 ©MIXI 3.1 条件式 (CASE式) 単純CASE式条件分岐に該当するもので、2通りの書き⽅があります。 ELSEを省略するとNULLを返します CASE <カラム> WHEN <値> THEN <カラム = 値のときの返り値> … ELSE <上記を満たさないときの返り値> END 検索CASE式 CASE WHEN <条件> THEN <条件を満たすときの返り値> … ELSE <上記を満たさないときの返り値> END

Slide 34

Slide 34 text

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⽂字にマッチ便利な演算⼦がいくつかあります。

Slide 35

Slide 35 text

35 ©MIXI 3.3 条件式 (演習) ★ Notebookの「条件式」をやってみましょう。

Slide 36

Slide 36 text

©MIXI 4. 集合操作（サブクエリ‧UNION）

Slide 37

Slide 37 text

37 ©MIXI 4. 集合操作（サブクエリ‧UNION） - サブクエリ - WITH - UNION

Slide 38

Slide 38 text

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⼈以上該当者がいる年齢とその⼈数を数える場合、以下のように記述します。

Slide 39

Slide 39 text

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 ... ...

Slide 40

Slide 40 text

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種類あります。

Slide 41

Slide 41 text

41 ©MIXI 4.4 集合操作 (演習) ★ Notebookの「集合操作（サブクエリ‧UNION）」をやってみましょう。

Slide 42

Slide 42 text

©MIXI 5. 集合操作（JOIN）

Slide 43

Slide 43 text

43 ©MIXI 5 集合操作 (JOIN) ● 内部結合 (INNER JOIN) ○ 条件に一致するレコードのみを結合する ● 外部結合 (OUTER JOIN) ○ 条件に一致するレコードがない場合はNULLとして結合する ● クロス結合 (CROSS JOIN) ○ 直積 ○ 配列データをバラすときなどに使用 - 複数のテーブルやサブクエリを特定の条件で横⽅向に結合することができます。 - JOINは3種類あります。

Slide 44

Slide 44 text

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で結合できるので、以下のようなクエリになります。

Slide 45

Slide 45 text

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もあります。

Slide 46

Slide 46 text

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 ... ... ... ... ... ... - メリットは、可読性が⾼くなることです - デメリットは、共通のカラム名でないと使えないことです

Slide 47

Slide 47 text

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 一串二串三串

Slide 48

Slide 48 text

Slide 49

Slide 49 text

©MIXI 6. 分析関数

Slide 50

Slide 50 text

50 ©MIXI 6. 分析関数（ウィンドウ関数）⾏をウィンドウ(window)にまとめる能⼒ Analytic function（解析関数、分析関数）とも呼ぶ。分析関数特有の関数 - LAG/LEAD関数: 前の⾏/後続の⾏の値を取得する - FIRST_VALUE関数: ウィンドウ内の最初の値 - ROW_NUMBER/RANK/DENSE_RANK: 順序付け. タイ(同率)の扱いが異なる. 集計関数もウィンドウ関数に対応 - COUNT関数: ウィンドウに含まれる⾏数をカウント - SUM関数: ウィンドウに含まれる値の合計 - MAX関数: ウィンドウに含まれる値の中の最⼤値など

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

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

Slide 53

Slide 53 text

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

Slide 54

Slide 54 text

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 ウィンドウ（窓）が動いていくイメージ

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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 ウィンドウ（窓）が動いていくイメージ

Slide 57

Slide 57 text

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！

Slide 58

Slide 58 text

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！

Slide 59

Slide 59 text

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！

Slide 60

Slide 60 text

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

Slide 61

Slide 61 text

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

Slide 62

Slide 62 text

Slide 63

Slide 63 text

©MIXI お疲れ様でした！