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