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

本スライドは、MIXIの2025年度新卒向け技術研修で使用された資料です。
　
MIXI 2025新卒技術研修
『データベース研修 分析向けSQL入門』
　
▼リポジトリ
https://github.com/mixigroup/2024BeginnerTrainingDataBaseAnalysisPublic
▼動画
https://youtu.be/mjxX2o7gcNo
　
───────────────────────────────
※皆様へのお願い※ 資料・動画・リポジトリのご利用について
───────────────────────────────
公開している資料や動画は、是非、勉強会や社内の研修などにご自由にお使いいただければと思いますが、以下のような場でのご利用はご遠慮ください。
- 受講者から参加費や授業料など金銭を集めるような場での利用
（会場費や飲食費など勉強会の運営に必要な実費を集める場合は問題ありません）
- 出典を削除または改変しての利用

Transcript

1. ©MIXI 25新卒技術研修 データベース研修 分析向けSQL⼊⾨ 2025.04.21
2. 2 ©MIXI 本研修の⽬的 1. データ分析基盤の基礎知識を⾝につけて、データウェアハウスをはじめとする データ分析基盤を活⽤できるように 2. リレーショナルモデルの基礎と分析向けSQLの基礎/応⽤⼒を⾝につけて SQLを⽤いて⾃⼒でデータ分析できるように
3. 3 ©MIXI ⽬次 1. 座学 2. SQL⼊⾨演習 3. SQLチャレンジ演習
4. ©MIXI 座学
5. 5 ©MIXI なぜデータ分析基盤が必要か 1. 業務システムのDBはリアルタイムトランザクション処理（OLTP）に特化しているこ とが多く、集計処理は⼤きな負荷を与える（ユーザー体験の低下） ◦ OLTPシステムは少量のデータを⾼速に処理することに特化 2. 業務システムのDBは業務遂⾏に必要な最低限のデータしか保持しないことが多く、障 害調査やCS対応に必要なデータが得られないことがある ◦ データは常に更新されていく。 ◦ 全てのデータの履歴を保持しておくわけにはいかない。 3. なるべく早くビジネスの健全性をデータから把握したいが、業務システムのDBが OLTPの場合、集計処理に時間がかかる可能性がある ◦ 特に複数のシステムにまたがってデータを結合処理するのは⼤変
6. 6 ©MIXI データ分析基盤の役割 データ⽣成 （ソース） 分析 機械学習 リバース ETL 保存 提供 データ分析基盤 OLTPデータベース ログ収集エージェント (MySQL, fluentdなど) データ取り込み (Load) BIツール ETLツール 変換 (Transform) データ抽出 (Extract)
7. 7 ©MIXI （最近の）データ分析基盤の役割 データ⽣成 （ソース） 分析 機械学習 リバース ETL 保存 取り込み (Load) 変換 (Transform) 提供 データ分析基盤 ELT データ抽出 (Extract)
8. 8 ©MIXI データウェアハウス（DWH）の特徴 1. 分析のためのデータを⼀元的に管理‧統合 a. 組織全体のデータを横断的に分析可能に 2. 履歴データの保持と分析 a. 過去のデータを⻑期間にわたって保持し、時系列分析や傾向分析を可能に 3. ビジネスインテリジェンス（BI）の⽀援 a. レポート作成、ダッシュボード表⽰のためのデータ提供 4. 業務システムへの影響軽減 a. 分析処理を業務システムから分離することで、業務システムのパフォーマンスへ の影響を最⼩限に 5. マネージドサービスを利⽤するケースが多い a. Amazon Redshift、Google BigQueryなどのクラウドベンダーが提供するもの b. SnowflakeやDatabricksなどクラウドネイティブなSaaS型プラットフォームも
9. 9 ©MIXI Google BigQuery テラバイト級データを数秒で処理できる サーバーレスでスケーラブルなデータウェアハウス（DWH） BigQueryの特徴 1. SQLでクエリ可能 2. カラムナ（列指向）ストレージ 3. ComputeとStorageの分離 a. 今までのDWHでは計算するマシンとデータを保存するマシンが同じマシン。ストレージ追加のため にマシンのスケールアップが必要だった b. データの物理的な場所に関係なく迅速にクエリできる 4. Google Cloudの他サービスとの連携が容易 5. BigQueryを⽀える技術: Dremel, Colossus File System, Jupiter networking etc
10. 10 ©MIXI （おさらい）列指向 1. 集計処理の際、必要な列のデータのみ取り出せる 2. 圧縮しやすい a. 連続するデータは連⻑（ランレングス）圧縮が有効 b. カーディナリティ(濃度)が低いならビットマップエンコー ディングが使える 3. 書き込みは苦⼿ 「列に含まれるすべての値をまとめて保存」 name age Mike 25 Alice 23 John 21 列方向にデータを保存
11. 11 ©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)も多い
12. 12 ©MIXI リレーショナルデータモデル 1. 数学的で徹底的にフォーマルなデータモデル a. 集合論に基づいてデータを表現 b. ⾼度に抽象的（実装に無関⼼） c. どこにデータがあるか、どうやって取得するか、パフォーマンスなどは実装の問題 2. データモデルは原理 a. 原理：本質的な性質 b. 製品や技術は変化するが原理は持続⼒がある c. SQL⾔語はあくまでもリレーショナルデータモデルを設計基盤とした実装の⼀つ 3. 歴史 a. E. F. Coddにより1969 年に掲載された論⽂がオリジナル 4. 構造、操作、整合性の 3 つの基本要素で構成
13. 13 ©MIXI リレーショナルデータモデルの構造 Domain(Type) Relation : HeadingとBody Heading : Attributeの集合 Attribute : (Name, Type) Body : Attributeに準拠するTupleの集合 Relation Heading Body Attribute: Tupleの構造を定義 Tuple: 実際の値 Attributeの構造に対応
14. 14 ©MIXI リレーショナルデータモデルの構造（SQL版） Relation（リレーション） => Table（表） Attribute（属性） => Column（列） Tuple（タプル） => Row（⾏） Table（表） Column（列） ↓ Row（⾏）→ ⾏1 Column（列） ↓ Row（⾏）→ Row（⾏）→ Row（⾏）→
15. 15 ©MIXI リレーショナルデータモデルの構造 Domain(Type) Relation : HeadingとBody Heading : Attributeの集合 Attribute : (Name, Type) Body : Attributeに準拠するTupleの集合 Relation Heading Body Attribute: Tupleの構造を定義 Tuple: 実際の値 Attributeの構造に対応 Attributeの数 => degree(次数), arity(項数) Bodyに含まれるTupleの数 => Cardinality（濃度）
16. 16 ©MIXI リレーショナルデータモデルの操作 1. 「⼀つ以上の”リレーション”を⼊⼒し、⼀つだけの”リレーション”を出⼒する」演算⼦の集合 a. 例えばリレーションAとリレーションBの和集合を計算した新たなリレーションABを⽣成 b. ⼊出⼒どちらも”リレーション”型。つまり演算の結果を他の演算の⼊⼒にできる。 閉包性(closure property) 和 UNION 積 INTERSECT 差 DIFFERENCE (EXCEPT)
17. 17 ©MIXI リレーショナルデータモデルの操作 Rに含まれるAttribute(列)を射影 した新たなR’を⽣成 射影（選択） project (select) R R' 指定した条件を満たすTuple(⾏) が全て含まれた関係を返す 制限 restrict R R' (⾃然) 結合 (natural) join a1 b1 a2 b1 b1 c1 b2 c2 a1 b1 c1 a2 b1 c1 共通属性に対して共通の値 を持つタプルだけを組み合 わせる
18. 18 ©MIXI タプルを⼀意に特定できる属性 リレーショナルデータモデルの制約 1. 制約（整合性制約）: 必ず満たす必要がある決まり事 a. 必ずTRUEに評価されなければならないブール式 2. リレーショナルモデルの基本的で重要な制約は2つ a. 実体整合性: 主キー属性にnullは許可されない b. 参照整合性: 外部キーに⼀致しない値があってはならない 候補キー 候補キーから選んだ 1つ以上の属性の集合 主キー 主キーを参照する属性 外部キー
19. 19 ©MIXI リレーショナルデータモデルの制約 社員ID 名前 部⾨ID 100001 J0001 100002 S0002 100003 K0001 主キー（Primary Key） 部⾨ID 部署名 J0001 ⼈事部採⽤課 S0002 総務部設備課 K0001 開発部第1課 社員テーブル 部⾨テーブル 外部キー （Foreign Key） ⼀意、かつ、 NULL は許容しない 部⾨テーブルに 無い部⾨IDは 社員テーブルに 登録できない
20. 20 ©MIXI サマリ 1. データ分析基盤の存在意義を学びました ◦ 本番ワークロードに影響を与えることなくBIを提供します 2. データ分析基盤（データウェアハウス）はSQLをインターフェースとします 3. SQLが設計基盤としているリレーショナルデータモデルについて学びました ◦ リレーショナルモデルの構造、操作、整合性 次は実際のデータウェアハウスであるBigQueryを⽤いて、SQLでどのようにデータを分析 していくのかを体験してみましょう
21. ©MIXI 座学END ~ 休憩 ~
22. ©MIXI 分析向けSQL⼊⾨演習
23. 23 ©MIXI 概要 • 本演習では座学と演習を通して、分析向けのSQLについて学んでいきます • 演習の時間は解説後に設けますが解説中に演習を解いても構いません。やりやすい⽅ 法で取り組んで下さい • 本スライドの解説で登場するテーブルと演習で扱うテーブルは別物です！ • すでにSQLをマスターしている⽅はBigQueryの様々な機能をつかって遊んでみましょ う。 ◦ Looker Studioを⽤いたデータビジュアライゼーション
24. 24 ©MIXI 概要 - 実⾏環境について • 演習ではGoogle Cloud BigQueryを⽤います • 演習問題は次のURLにあります ◦ https://colab.research.google.com/drive/1ma8rBjRtz0-yashh_jDVOOBOIhJWDTkH? usp=drive_link • BigQueryを使った演習はGoogle Colaboratoryを⽤いてやります
25. 25 ©MIXI 分析向けSQL⼊⾨演習 1. データの取得 2. 集計処理 3. 条件式 4. 集合操作（サブクエリ‧UNION） 5. 集合操作（JOIN） 6. 分析関数
26. ©MIXI 1. データの取得
27. 27 ©MIXI 1. データの取得 SELECT⽂を⽤いたデータの取得について、BigQueryを⽤いて演習を⾏います。 - SELECT - LIMIT - ORDER BY - WHERE
28. 28 ©MIXI 1.1 データの取得 (SELECT⽂) - DBからデータを取得する際にはSELECT⽂を使⽤します。 - usersテーブルから、ユーザーID (id) とユーザー名 (name) を取得する場合は下の例 のように記述します。 - この場合、対象となるデータを全件取得します。 SELECT <カラム> FROM <テーブル名> SELECT id, name FROM users id name 1 一串 2 二串 ... ... 99 九九串
29. 29 ©MIXI 1.2 データの取得 (LIMIT句) - SELECT⽂の末尾にLIMIT句をつけると、取得するレコード数を制限できます。 - usersテーブルから10件取得したい場合は以下のように記述します。 SELECT id, name FROM users LIMIT 10 id name 1 一串 2 二串 ... ... 10 十串
30. 30 ©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 九〇串
31. 31 ©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
32. 32 ©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
33. 33 ©MIXI 1.6 おまけ: ⽂と句 1. ⽂（Statement）は句（clause）で構成される 2. SELECT⽂の場合、必須となる句はSELECT句のみ。 a. SELECT 1は有効なSELECT⽂ 3. JOINやUNIONなどは句ではない。 a. JOINはFROM句で利⽤できるキーワード b. UNIONは演算⼦
34. 34 ©MIXI 1.7 データの取得 (演習) ★ Notebookの「設定」をやってみましょう。 ★ Notebookの「データの取得」をやってみましょう。
35. ©MIXI 2. 集計処理
36. 36 ©MIXI 2. 集計処理 - COUNT - GROUP BY - HAVING
37. 37 ©MIXI 2.1 集計 (COUNT関数) - 集計は複数の⾏のデータをまとめて、ひとつの要約的な値を求めること。 - レコード数を数える際はCOUNT関数を使います。 - COUNT関数は評価する式がNULLではないレコードの数を返します。 - COUNT(1), COUNT(*)の場合NULLレコードも含めて全件数えます。 - usersテーブルのレコード数を数える場合は以下のように記述します。 - 集計関数にはCOUNT関数以外に、合計を出すSUM関数や平均を出すAVG関数なども あります。 SELECT COUNT(1) AS cnt FROM users cnt 99 関数名 説明 count 件数を数える sum 合計を求める avg 平均を求める max 最大値を求める min 最小値を求める
38. 38 ©MIXI 2.2 集計 (GROUP BY句) - カラムでグループ分けした結果に対して集計を⾏う場合、GROUP BY句を使います。 - 20歳以上のユーザーを対象に、その⼈数を集計する場合、右の例のように記述しま す。 SELECT <カラム> FROM <テーブル> WHERE <条件> GROUP BY <集約するカラム名 or カラムの番号> SELECT age, COUNT(1) AS cnt FROM users WHERE 20 <= age GROUP BY age age cnt 20 2 21 1 22 5 23 0 ... ...
39. 39 ©MIXI 2.3 集計 (HAVING句) - カラムでグループ分けした結果に対して集計をおこなった後、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 ... ...
40. 40 ©MIXI 2.4 集計 (演習) ★ Notebookの「集計」をやってみましょう。
41. ©MIXI 3. 条件分岐
42. 42 ©MIXI 3. 条件分岐 - CASE式 - 真偽値を返す演算⼦ - IN - LIKE - BETWEEN
43. 43 ©MIXI 3.1 条件式 (CASE式) 単純CASE式 条件分岐に該当するもので、2通りの書き⽅があります。 なお、ELSEを省略するとNULLを返します CASE <カラム> WHEN <値> THEN <カラム = 値のときの返り値> … ELSE <上記を満たさないときの返り値> END 検索CASE式 CASE WHEN <条件> THEN <条件を満たすときの返り値> … ELSE <上記を満たさないときの返り値> END
44. 44 ©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⽂字にマッチ 便利な演算⼦がいくつかあります。
45. 45 ©MIXI 3.3 条件式 (演習) ★ Notebookの「条件式」をやってみましょう。
46. ©MIXI 4. 集合操作（サブクエリ‧UNION）
47. 47 ©MIXI 4. 集合操作（サブクエリ‧UNION） - サブクエリ - WITH - UNION
48. 48 ©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の結果を別のクエリ内で使⽤することをサブクエリと⾔います。 - SELECT句、FROM句、WHERE句などで使うことができます - 例えば、ユーザーテーブルにおいて、2⼈以上該当者がいる年齢とその⼈数を数える場 合、以下のように記述します。
49. 49 ©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 ... ...
50. 50 ©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種類あります。
51. 51 ©MIXI 4.4 集合操作 (演習) ★ Notebookの「集合操作（サブクエリ‧UNION）」をやってみましょう。
52. ©MIXI 5. 集合操作（JOIN）
53. 53 ©MIXI 5 集合操作 (JOIN) • 内部結合 (INNER JOIN) ◦ 条件に一致するレコードのみを結合する • 外部結合 (OUTER JOIN) ◦ 条件に一致するレコードがない場合はNULLとして結合する • クロス結合 (CROSS JOIN) ◦ 直積 ◦ 配列データをバラすときなどに使用 - 複数のテーブルやサブクエリを特定の条件で横⽅向に結合することができます。 - JOINは3種類あります。
54. 54 ©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で結合できるので、以下のようなクエリになります。
55. 55 ©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もあります。
56. 56 ©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 ... ... ... ... ... ... - メリットは、可読性が⾼くなることです - デメリットは、共通のカラム名でないと使えないことです
57. 57 ©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 一串 二串 三串
58. 58 ©MIXI 5.5 集合操作 (演習) ★ Notebookの「集合操作（JOIN)」をやってみましょう。
59. ©MIXI 6. 分析関数
60. 60 ©MIXI 6. 分析関数（ウィンドウ関数） ⾏をウィンドウ(window)にまとめる能⼒ Analytic function（解析関数、分析関数）とも呼ぶ。 分析関数特有の関数 - LAG/LEAD関数: 前の⾏/後続の⾏の値を取得する - FIRST_VALUE関数: ウィンドウ内の最初の値 - ROW_NUMBER/RANK/DENSE_RANK: 順序付け. タイ(同率)の扱いが異なる. 集計関数もウィンドウ関数に対応 - COUNT関数: ウィンドウに含まれる⾏数をカウント - SUM関数: ウィンドウに含まれる値の合計 - MAX関数: ウィンドウに含まれる値の中の最⼤値 など
61. 61 ©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
62. 62 ©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
63. 63 ©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
64. 64 ©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 ウィンドウ（窓）が動いていくイメージ
65. 65 ©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
66. 66 ©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 ウィンドウ（窓）が動いていくイメージ
67. 67 ©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！
68. 68 ©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！
69. 69 ©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！
70. 70 ©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
71. 71 ©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
72. 72 ©MIXI 6. 分析関数 (演習) ★ Notebookの「分析関数（ウィンドウ関数）」をやってみましょう。
73. ©MIXI お疲れ様でした！
74. 74 ©MIXI 最後に 1. ★ Notebookの「⽚付け」でGoogle Cloudプロジェクトを削除してください。 2. 復習を⾏いたい場合はもう⼀度「設定」を⾏うと新たなGoogle Cloudプロジェクトを 作成できます。