$30 off During Our Annual Pro Sale. View Details »

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

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

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

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

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

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

MIXI ENGINEERS

July 22, 2024
Tweet

Video

More Decks by MIXI ENGINEERS

Other Decks in Technology

Transcript

  1. 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)も多い
  2. 7 ©MIXI リレーショナルデータモデル 1. 数学的で徹底的にフォーマルなデータモデル a. 集合論に基づいてデータを表現 b. ⾼度に抽象的(実装に無関⼼) c.

    どこにデータがあるか、どうやって取得するか、パフォーマンスなどは実装の問題 2. データモデルは原理 a. 原理:本質的な性質 b. 製品や技術は変化するが原理は持続⼒がある 3. 歴史 a. E. F. Coddにより1969 年に掲載された論⽂がオリジナル b. 構造、整合性、操作の 3 つの基本要素で構成
  3. 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)
  4. 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
  5. 11 ©MIXI 列指向 1. 集計処理の際、必要な列のデータのみ取り出せる 2. 圧縮しやすい a. 連続するデータは連⻑(ランレングス)圧縮が有効 b.

    カーディナリティ(濃度)が低いならビットマップエンコー ディングが使える 3. 書き込みは苦⼿ 「列に含まれるすべての値をまとめて保存」 name age Mike 25 Alice 23 John 21 列方向にデータを保存
  6. 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
  7. 15 ©MIXI 概要 - 実⾏環境について • 演習ではGoogle Cloud BigQueryを⽤います •

    演習問題は次のURLにあります ◦ https://github.com/mixigroup/2024BeginnerTrainingDataBaseAnalysisPublic • BigQueryを使った演習はGoogle Colaboratoryを⽤いてやります
  8. 16 ©MIXI 分析向けSQL⼊⾨演習 1. データの取得 2. 集計処理 3. 条件式 4.

    集合操作(サブクエリ‧UNION) 5. 集合操作(JOIN) 6. 分析関数
  9. 19 ©MIXI 1.1 データの取得 (SELECT⽂) - DBからデータを取得する際にはSELECT⽂を使⽤します。 - usersテーブルから、ユーザーID (id)

    とユーザー名 (name) を取得する場合は下の例 のように記述します。 SELECT <カラム> FROM <テーブル名> SELECT id, name FROM users id name 1 一串 2 二串 ... ... 99 九九串
  10. 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 九〇串
  11. 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
  12. 24 ©MIXI 1.6 おまけ: ⽂と句 1. ⽂(Statement)は句(clause)で構成される 2. SELECT⽂の場合、必須となる句はSELECT句のみ。 a.

    SELECT 1は有効なSELECT⽂ 3. JOINやUNIONなどは句ではない。 a. JOINはFROM句で利⽤できるキーワード b. UNIONは演算⼦
  13. 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
  14. 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 ... ...
  15. 33 ©MIXI 3.1 条件式 (CASE式) 単純CASE式 条件分岐に該当するもので、2通りの書き⽅があります。 ELSEを省略するとNULLを返します CASE <カラム>

    WHEN <値> THEN <カラム = 値のときの返り値> … ELSE <上記を満たさないときの返り値> END 検索CASE式 CASE WHEN <条件> THEN <条件を満たすときの返り値> … ELSE <上記を満たさないときの返り値> END
  16. 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⽂字にマッチ 便利な演算⼦がいくつかあります。
  17. 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⼈以上該当者がいる年齢とその⼈数を数える場合、以下 のように記述します。
  18. 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種類あります。
  19. 43 ©MIXI 5 集合操作 (JOIN) • 内部結合 (INNER JOIN) ◦

    条件に一致するレコードのみを結合する • 外部結合 (OUTER JOIN) ◦ 条件に一致するレコードがない場合はNULLとして結合する • クロス結合 (CROSS JOIN) ◦ 直積 ◦ 配列データをバラすときなどに使用 - 複数のテーブルやサブクエリを特定の条件で横⽅向に結合することができます。 - JOINは3種類あります。
  20. 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で結合できるので、以下のようなクエリになります。
  21. 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もあります。
  22. 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 ... ... ... ... ... ... - メリットは、可読性が⾼くなることです - デメリットは、共通のカラム名でないと使えないことです
  23. 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 一串 二串 三串
  24. 50 ©MIXI 6. 分析関数(ウィンドウ関数) ⾏をウィンドウ(window)にまとめる能⼒ Analytic function(解析関数、分析関数)とも呼ぶ。 分析関数特有の関数 - LAG/LEAD関数:

    前の⾏/後続の⾏の値を取得する - FIRST_VALUE関数: ウィンドウ内の最初の値 - ROW_NUMBER/RANK/DENSE_RANK: 順序付け. タイ(同率)の扱いが異なる. 集計関数もウィンドウ関数に対応 - COUNT関数: ウィンドウに含まれる⾏数をカウント - SUM関数: ウィンドウに含まれる値の合計 - MAX関数: ウィンドウに含まれる値の中の最⼤値 など
  25. 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
  26. 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
  27. 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
  28. 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 ウィンドウ(窓)が動いていくイメージ
  29. 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
  30. 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 ウィンドウ(窓)が動いていくイメージ
  31. 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!
  32. 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!
  33. 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!
  34. 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
  35. 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