DBスペシャリスト午後2問1で覚える RDBMSの機能 (1) インデックス・木構造と再帰CTE・ウィンドウ関数 インフラ勉強会　2020/03/06　まつひさ（hmatsu47）

自己紹介 松久裕保（@hmatsu47） https://qiita.com/hmatsu47 名古屋でWebインフラのお守り係（非DBA） ○ 会社ではAmazon Aurora（MySQL 5.6互換版）を 使ってます MySQL 8.0：趣味・遊び ○ MySQL 8.0の薄い本を作って配っています https://qiita.com/hmatsu47/items/ceb75caf46e3c761095d ○ 現在8.0.19対応版です ○ GitHubリポジトリのほか、印刷版を勉強会などでタダで配っています 2

わたしの登壇歴 単独セッション ● オンプレインフラエンジニアのための AWS移行の始め方（2019/2/21） LTその他で参加 ● 名古屋IT事情について話し合う part2（2019/3/17） ● Local IT Meetup -Episode1- IWATA オンライン同時配信（2019/9/7） ● インフラ勉強会2周年記念非公式イベント・年忘れライブLT大会＠大阪 (の西隣)（2019/12/21） ● 技術書同人誌について話そう！夜の部（2020/3/1） その他、オフラインなどいろいろ https://speakerdeck.com/hmatsu47 3

わたしの情報処理技術者試験受験歴 ● 平成 4年秋 第二種情報処理技術者試験 ● 平成 6年春 第一種情報処理技術者試験 （間が空いて…） ● 平成26年春 情報セキュリティスペシャリスト試験（SC） ○ その後、情報処理安全確保支援士に登録（001158） ● 平成26年秋 ネットワークスペシャリスト試験（NW） ● 平成27年春 データベーススペシャリスト試験（DB） ○ 午後2は問1を選択して93点でした ● 平成28年春 情報セキュリティマネジメント試験（SG） 4

今日のテーマ IPAのデータベーススペシャリスト試験問題を、 ● MySQLやPostgreSQL での再現（実行）例を参照しながら、 ● 実務と共通する部分 ● 実務とは違う部分 を読み解いていきます ※一部文字が小さいページがあります 　スライドを手元にダウンロードしてお使いください https://twitter.com/hmatsu47/status/1234490770134007809 5

伝えたいこと ● IPA試験の問題も、使い方次第で実務に役立ちます ● 実務とは違うところもあるので、その違いを意識して役立てま しょう ○ RDBMSによって実装されている機能は異なります ○ 同じ機能が実装されていても、仕様や挙動が違うこともあります ● とはいえ、このセッションでは厳密なことは説明しないので、 雰囲気がわかればOKです 6

おしながき ● データベーススペシャリスト試験とは？ ● 平成31年春期試験・午後２問１の概要 ● ①オプティマイザの仕様とインデックス ○ 「インデックスが効かないケース」 ○ MySQLとPostgreSQLで確認してみる ● ②木構造と再帰共通テーブル式（CTE） ○ ナイーブツリーで再帰CTEを使う ○ 経路列挙モデル・入れ子集合モデルへの変更 ● ③ウィンドウ関数の紹介 ○ 試験問題とは別の例で（RANK関数） 7

データベーススペシャリスト試験とは？ ● 高度試験 ○ 記述式（スペシャリスト試験）区分の1つ ● 高度試験の中では合格率が高めだった（16～18%） ○ ただし、ここ3年は他の高度試験並みの約14% ● 午後問題では概念データモデルおよび関係スキーマの完成問題 の出題が多い ○ E-R図（エンティティ、リレーションシップ、スーパータイプとサブタ イプ、…） ○ 関数従属性、主キーと候補キー、正規化 8

データベーススペシャリスト試験とは？ ● 以前よりDBA・インフラエンジニア寄りになった？ ○ SQLや物理（テーブル）実装、RDBMSの機能に関する問題が増えた 　　　（IPA 情報処理技術者試験・情報処理安全確保支援士試験のページより引用） ● 午後1および午後2問1にその傾向が強くなってきた？ 9

平成31年春期試験・午後2問1の概要 ● データベースの実装、設計（＝物理設計）の問題 ● 銀行の窓口端末のログを分析して事務手続やアプリケーション ソフトウェアの改善に役立てるために、新たな分析システムを 構築することになった ● 木構造をもつログデータを効率よく扱いたい ● 性能面を考えて、テーブルのパーティショニングやクラスタ化 を検討したい 10

平成31年春期試験・午後2問1の概要 ● データベースの実装、設計（＝物理設計）の問題 ● 銀行の窓口端末のログを分析して事務手続やアプリケーション ソフトウェアの改善に役立てるために、新たな分析システムを 構築することになった ● 木構造をもつログデータを効率よく扱いたい ● 性能面を考えて、テーブルのパーティショニングやクラスタ化 を検討したい 11

①オプティマイザの仕様とインデックス オプティマイザとは？ ● SQLの処理内容を最適化し、実行計画を立てる機構 ○ どのインデックスを使う？テーブルスキャンにする？ ○ どれを駆動表（外部表）にして結合（JOIN）する？ ● プランナともいう 12 構文解析 パーサ・アナライザ リライタなど 実行計画 オプティマイザ （プランナ） 実行 エグゼキュータ など

①オプティマイザの仕様とインデックス 試験問題（平成31年春期試験・午後2問1）P.10〔RDBMSの主な仕様〕より抜粋 試験問題：前方一致以外の文字列検索や、関数を含む検索では… ● テーブルスキャン（表探索）になる ● インデックス（索引）は使われない 13

①オプティマイザの仕様とインデックス MySQL 8.0で確認してみる（文字列検索） 14 mysql> CREATE DATABASE index_test; Query OK, 1 row affected (0.01 sec) mysql> USE index_test; Database changed mysql> CREATE TABLE like_test ( -> id INT PRIMARY KEY NOT NULL, -> val INT NOT NULL, -> str VARCHAR(255) NOT NULL, -> INDEX idx_val(val), -> INDEX idx_str(str) -> ); Query OK, 0 rows affected (0.04 sec) （データINSERTは省略）

①オプティマイザの仕様とインデックス MySQL 8.0で確認してみる（文字列検索） 15 mysql> SELECT * FROM like_test; +----+-----+---------+ | id | val | str | +----+-----+---------+ | 1 | 100 | abcdefg | | 2 | 100 | bcdefgh | | 3 | 100 | cdefghi | | 4 | 100 | defghij | | 5 | 101 | abcdefg | | 6 | 101 | bcdefgh | | 7 | 101 | cdefghi | | 8 | 102 | abcdefg | | 9 | 102 | bcdefgh | | 10 | 103 | abcdefg | +----+-----+---------+ 10 rows in set (0.00 sec)

①オプティマイザの仕様とインデックス MySQL（InnoDB）のテーブル・インデックス構造 クラスタインデックスになっている（データ行が主キー順に並ぶ） 16

①オプティマイザの仕様とインデックス MySQL（InnoDB）のテーブル・インデックス構造 クラスタインデックスになっている（データ行が主キー順に並ぶ） 17 【注】インデックス自体は木構造（B+木）になって 　いますが、②木構造と再帰共通テーブル式(CTE) 　の説明のときに混乱するので今回はB+木について 　は説明しません。

①オプティマイザの仕様とインデックス WHERE句に単独で文字列の部分一致（b）を指定した場合… 試験問題と同様、テーブルスキャンに ● MySQL（InnoDB）の構造上、テーブルスキャン＝主キーの全行スキャン 18 mysql> EXPLAIN SELECT * FROM like_test WHERE str LIKE '%b%'; +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | like_test | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 11.11 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)

①オプティマイザの仕様とインデックス 全10行中、マッチした7行が抽出された 19 mysql> SELECT * FROM like_test WHERE str LIKE '%b%'; +----+-----+---------+ | id | val | str | +----+-----+---------+ | 1 | 100 | abcdefg | | 2 | 100 | bcdefgh | | 5 | 101 | abcdefg | | 6 | 101 | bcdefgh | | 8 | 102 | abcdefg | | 9 | 102 | bcdefgh | | 10 | 103 | abcdefg | +----+-----+---------+ 7 rows in set (0.00 sec)

①オプティマイザの仕様とインデックス 主キー（のリーフノード行）を直接スキャンした 赤字のid行をスキャンして黄色の行がマッチしたので返却 20

①オプティマイザの仕様とインデックス 他の条件とあわせて指定した場合… 他の条件で使えるインデックスがあればそれを使って絞り込む ● このケースでは「val = 100」で絞り込んだ行に対してスキャン ● 「val = 100」は4行 多くのRDBMSはコストベースで判断して実行計画を立てている ● 最低コストの実行計画を選択 21 mysql> EXPLAIN SELECT * FROM like_test WHERE val = 100 AND str LIKE '%b%'; +----+-------------+-----------+------------+------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | like_test | NULL | ref | idx_val | idx_val | 4 | const | 4 | 11.11 | Using where | +----+-------------+-----------+------------+------+---------------+---------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)

①オプティマイザの仕様とインデックス スキャンした4行中、マッチした2行が抽出された 22 mysql> SELECT * FROM like_test WHERE val = 100 AND str LIKE '%b%'; +----+-----+---------+ | id | val | str | +----+-----+---------+ | 1 | 100 | abcdefg | | 2 | 100 | bcdefgh | +----+-----+---------+ 2 rows in set (0.00 sec)

①オプティマイザの仕様とインデックス idx_valで絞り込んで主キーのリーフノード行をスキャンした 赤字→青字の行をスキャンして黄色の行がマッチしたので返却 23

①オプティマイザの仕様とインデックス PostgreSQL 12の場合 ● テーブルとインデックスは別々のDDLで作成 24 index_test=# CREATE TABLE like_test ( index_test(# id INT PRIMARY KEY NOT NULL, index_test(# val INT NOT NULL, index_test(# str VARCHAR(255) NOT NULL index_test(#); CREATE TABLE index_test=# CREATE INDEX ON like_test (val); CREATE INDEX index_test=# CREATE INDEX ON like_test (str); CREATE INDEX

①オプティマイザの仕様とインデックス PostgreSQL 12の場合 25 index_test=# EXPLAIN VERBOSE SELECT * FROM like_test WHERE str LIKE '%b%'; QUERY PLAN --------------------------------------------------------------------- Seq Scan on public.like_test (cost=0.00..11.75 rows=140 width=524) Output: id, val, str Filter: ((like_test.str)::text ~~ '%b%'::text) (3 rows) index_test=# EXPLAIN VERBOSE SELECT * FROM like_test WHERE val = 100 AND str LIKE '%b%'; QUERY PLAN -------------------------------------------------------------------------------------------- Index Scan using like_test_val_idx on public.like_test (cost=0.14..8.16 rows=1 width=524) Output: id, val, str Index Cond: (like_test.val = 100) Filter: ((like_test.str)::text ~~ '%b%'::text) (4 rows) valのインデックスが効いている

①オプティマイザの仕様とインデックス 試験問題と実際の動作はなぜ違う？ ● 試験問題：特定の製品の動作を意図した出題ではない ● 仕様を単純化しないと記述式問題の解答が難しくなる ● 解答が難しい→正解パターンを集約しづらい ● 結果として、採点が難しくなる ○ ある程度単純化は必要 ○ 完全に個人の推測ですが 26

②木構造と再帰共通テーブル式（CTE） 試験問題（平成31年春期試験・午後2問1）P.10〔RDBMSの主な仕様〕より抜粋 共通テーブル式（CTE）＝WITH句 ● 複雑なSQLを分かりやすくする ○ サブクエリ部分の分離・抽出など ● RECURSIVEを付けると、再帰問い合わせができる ○ UNION（ALL）と組み合わせる 27

②木構造と再帰共通テーブル式（CTE） 試験問題（平成31年春期試験・午後2問1）P.14〔”ログ関連”テーブルの検討〕より抜粋 28

②木構造と再帰共通テーブル式（CTE） 試験問題（平成31年春期試験・午後2問1）P.14〔”ログ関連”テーブルの検討〕より抜粋 29

②木構造と再帰共通テーブル式（CTE） 試験問題（平成31年春期試験・午後2問1）P.14〔”ログ関連”テーブルの検討〕より抜粋 ※案3以外でも木構造の親子関係は同じ 30

②木構造と再帰共通テーブル式（CTE） ● 案1：SQLアンチパターン「ナイーブツリー」 ○ 親へのリンク（親ID）しか持たない ○ 再帰問い合わせを使うのなら問題なし（この構造のままでも） ■ MySQLは8.0から対応なので、5.7以前を使う場合はこの構造では辛い ● 案2 : 経路列挙モデルへの変更 ○ アンチパターン「ジェイウォーク」でもある（非正規形） ○ バグに繋がる仕様も…（末尾は「/」にしましょう） ● 案3 : 入れ子集合モデルへの変更 ○ 更新負荷が高い ○ 構造がちょっと複雑になる（バグの原因に…） 31

②木構造と再帰共通テーブル式（CTE） MySQL 8.0で確認してみる（案1） ※カラム名は英数字と記号、ID列は数値に変更（以降すべて同じ） 　外部キー制約は定義せず（出題ケースでは更新処理がないので、外部キー制約を定義しない可能性も） 32 mysql> CREATE DATABASE tree_test; Query OK, 1 row affected (0.01 sec) mysql> USE tree_test; Database changed mysql> CREATE TABLE log_relation1 ( -> log_id INT PRIMARY KEY NOT NULL, -> parent_log_id INT, -> INDEX (parent_log_id) -> ); Query OK, 0 rows affected (0.02 sec) （データINSERTは省略）

②木構造と再帰共通テーブル式（CTE） MySQL 8.0で確認してみる（案1） 33 mysql> SELECT * FROM log_relation1 ORDER BY log_id; +--------+---------------+ | log_id | parent_log_id | +--------+---------------+ | 101 | NULL | | 102 | 101 | | 103 | 101 | | 104 | 103 | | 105 | 103 | | 106 | 103 | | 107 | NULL | | 108 | 107 | | 109 | 107 | +--------+---------------+ 9 rows in set (0.00 sec) MySQL 8.0では必ずしも 主キー順にならなくなった

②木構造と再帰共通テーブル式（CTE） MySQL 8.0で確認してみる（案1問合せ[A]） 34 mysql> WITH RECURSIVE temp(log_id) AS -> (SELECT log_id FROM log_relation1 WHERE log_id = 101 -> UNION ALL -> SELECT a.log_id FROM log_relation1 a, temp b -> WHERE a.parent_log_id = b.log_id) -> SELECT log_id FROM temp; +--------+ | log_id | +--------+ | 101 | | 102 | | 103 | | 104 | | 105 | | 106 | +--------+ 6 rows in set (0.00 sec)

②木構造と再帰共通テーブル式（CTE） MySQL 8.0で確認してみる（案1問合せ[A]） 35

②木構造と再帰共通テーブル式（CTE） EXPLAINではRecursiveが表示される ※再帰で繰り返す処理全行分の情報は表示されない点に注意 36 mysql> EXPLAIN -> WITH RECURSIVE temp(log_id) AS -> (SELECT log_id FROM log_relation1 WHERE log_id = 101 -> UNION ALL -> SELECT a.log_id FROM log_relation1 a, temp b -> WHERE a.parent_log_id = b.log_id) -> SELECT log_id FROM temp; +----+-------------+---------------+------------+-------+---------------+---------------+---------+----------+------+----------+------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+------------+-------+---------------+---------------+---------+----------+------+----------+------------------------+ | 1 | PRIMARY | | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL | | 2 | DERIVED | log_relation1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | | 3 | UNION | b | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Recursive; Using where | | 3 | UNION | a | NULL | ref | parent_log_id | parent_log_id | 5 | b.log_id | 2 | 100.00 | Using index | +----+-------------+---------------+------------+-------+---------------+---------------+---------+----------+------+----------+------------------------+ 4 rows in set, 1 warning (0.00 sec)

②木構造と再帰共通テーブル式（CTE） PostgreSQL 12でも同じように行ける（案1問合せ[A]） 37 tree_test=# WITH RECURSIVE temp(log_id) AS tree_test-# (SELECT log_id FROM log_relation1 WHERE log_id = 101 tree_test(# UNION ALL tree_test(# SELECT a.log_id FROM log_relation1 a, temp b tree_test(# WHERE a.parent_log_id = b.log_id) tree_test-# SELECT log_id FROM temp; log_id -------- 101 102 103 104 105 106 (6 rows)

②木構造と再帰共通テーブル式（CTE） EXPLAINするとCTE Scanになる（案1問合せ[A]） 38 tree_test=# EXPLAIN WITH RECURSIVE temp(log_id) AS (SELECT log_id FROM log_relation1 WHERE log_id = 101 UNION ALL SELECT a.log_id FROM log_relation1 a, temp b WHERE a.parent_log_id = b.log_id) SELECT log_id FROM temp; QUERY PLAN ----------------------------------------------------------------------------------------------------------- CTE Scan on temp (cost=456.09..478.71 rows=1131 width=4) CTE temp -> Recursive Union (cost=0.15..456.09 rows=1131 width=4) -> Index Only Scan using log_relation1_pkey on log_relation1 (cost=0.15..8.17 rows=1 width=4) Index Cond: (log_id = 101) -> Hash Join (cost=0.33..42.53 rows=113 width=4) Hash Cond: (a.parent_log_id = b.log_id) -> Seq Scan on log_relation1 a (cost=0.00..32.60 rows=2260 width=8) -> Hash (cost=0.20..0.20 rows=10 width=4) -> WorkTable Scan on temp b (cost=0.00..0.20 rows=10 width=4) (10 rows) ハッシュ結合

②木構造と再帰共通テーブル式（CTE） MySQL 8.0で確認してみる（案1問合せ[B]） 39 mysql> WITH RECURSIVE temp(log_id, parent_log_id) AS -> (SELECT log_id, parent_log_id FROM log_relation1 WHERE log_id = 105 -> UNION ALL -> SELECT a.log_id, a.parent_log_id FROM log_relation1 a, temp b -> WHERE a.log_id = b.parent_log_id) -> SELECT log_id FROM temp; +--------+ | log_id | +--------+ | 105 | | 103 | | 101 | +--------+ 3 rows in set (0.00 sec)

②木構造と再帰共通テーブル式（CTE） MySQL 8.0で確認してみる（案1問合せ[B]） 40

②木構造と再帰共通テーブル式（CTE） MySQL 8.0で確認してみる（案2） 41 mysql> CREATE TABLE log_relation2 ( -> log_id INT PRIMARY KEY NOT NULL, -> path VARCHAR(500) NOT NULL, -> INDEX (path) -> ); Query OK, 0 rows affected (0.02 sec) （データINSERTは省略） 仮で500文字に

②木構造と再帰共通テーブル式（CTE） MySQL 8.0で確認してみる（案2） 42 mysql> SELECT * FROM log_relation2 ORDER BY log_id; +--------+--------------+ | log_id | path | +--------+--------------+ | 101 | 101/ | | 102 | 101/102/ | | 103 | 101/103/ | | 104 | 101/103/104/ | | 105 | 101/103/105/ | | 106 | 101/103/106/ | | 107 | 107/ | | 108 | 107/108/ | | 109 | 107/109/ | +--------+--------------+ 9 rows in set (0.00 sec) バグ除けのために末尾を/に

②木構造と再帰共通テーブル式（CTE） MySQL 8.0で確認してみる（案2問合せ[A]） 43 mysql> SELECT log_id FROM log_relation2 WHERE path LIKE '101/%'; +--------+ | log_id | +--------+ | 101 | | 102 | | 103 | | 104 | | 105 | | 106 | +--------+ 6 rows in set (0.02 sec)

②木構造と再帰共通テーブル式（CTE） MySQL 8.0で確認してみる（案2問合せ[A]） 試験問題と同様にインデックス検索になる 44 mysql> EXPLAIN -> SELECT log_id FROM log_relation2 WHERE path LIKE '101/%'; +----+-------------+---------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | log_relation2 | NULL | range | path | path | 2002 | NULL | 6 | 100.00 | Using where; Using index | +----+-------------+---------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)

②木構造と再帰共通テーブル式（CTE） MySQL 8.0で確認してみる（案2問合せ[B]） 45 mysql> SELECT b.log_id FROM log_relation2 a, log_relation2 b -> WHERE a.log_id = 105 AND INSTR(a.path, b.path) = 1; +--------+ | log_id | +--------+ | 101 | | 103 | | 105 | +--------+ 3 rows in set (0.00 sec)

②木構造と再帰共通テーブル式（CTE） MySQL 8.0で確認してみる（案2問合せ[B]） 試験問題とは違ってINDEX（主キー）が使われる ● 経路（path）側はINDEXフルスキャンに 46 mysql> EXPLAIN -> SELECT b.log_id FROM log_relation2 a, log_relation2 b -> WHERE a.log_id = 105 AND INSTR(a.path, b.path) = 1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ | 1 | SIMPLE | a | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | b | NULL | index | NULL | path | 2002 | NULL | 9 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ 2 rows in set, 1 warning (0.00 sec)

②木構造と再帰共通テーブル式（CTE） PostgreSQL 12でEXPLAINを確認（案2問合せ[B]） こちらも試験問題とは違ってINDEX（主キー）が使われる 47 tree_test=# EXPLAIN SELECT b.log_id FROM log_relation2 a, log_relation2 b WHERE a.log_id = 105 AND POSITION(b.path IN a.path) = 1; QUERY PLAN -------------------------------------------------------------------------------------------------- Nested Loop (cost=0.14..21.66 rows=1 width=4) Join Filter: ("position"((a.path)::text, (b.path)::text) = 1) -> Index Scan using log_relation2_pkey on log_relation2 a (cost=0.14..8.16 rows=1 width=516) Index Cond: (log_id = 105) -> Seq Scan on log_relation2 b (cost=0.00..11.40 rows=140 width=520) (5 rows)

②木構造と再帰共通テーブル式（CTE） 案3の実行例（MySQL・PostgreSQL）は省略 ● MySQLの場合「左端番号, 右端番号」「右端番号, 左端番号」 の複合インデックスを作るとカバリングインデックスとなる ○ インデックスを読むだけで結果を返すことができる ■ データ行を直接読まなくて良い分読み取り負荷が低い ■ ただし更新負荷は高くなる ○ 主キー（ログID）列が暗黙的にインデックスに含まれる ■ MySQL（InnoDB）がクラスタインデックスを採用していることと関係 ■ 参考：16ページの図 ○ やるかどうかはテーブル行数と木構造の深さ次第 48

②木構造と再帰共通テーブル式（CTE） 試験問題は案3を採用 ● 案3の弱点「更新」（後から挿入・削除）を行わないため ● 要件が違えば最適な選択肢は異なる ○ おそらく案3（入れ子集合モデル）が採用されるケースは限定的 再帰CTEの参考情報 ● MySQL 8.0 https://qiita.com/hmatsu47/items/01211556089b19913d05 ● PostgreSQL 12 https://www.sraoss.co.jp/tech-blog/pgsql/pg12-cte/ 49

③ウィンドウ関数の紹介 試験問題（平成31年春期試験・午後2問1）P.10〔RDBMSの主な仕様〕より抜粋 ※今回は出題とは別の例を紹介 50

③ウィンドウ関数の紹介 ウィンドウ関数とは？ ● Wikipedia - 窓関数（SQL）より SQL において、窓関数もしくはウィンドウ関数 (英: window function) は結果セットを部分的に切り出した領域に 集約関数を適用できる、拡張された SELECT ステートメントである。SQL:2003 以降の標準SQLで規定されてい る。分析関数やOLAP機能と呼ばれる場合もある。 ● PostgreSQL 11.5文書 - 3.5. ウインドウ関数より ウィンドウ関数は現在の行に何らかとも関係するテーブル行の集合に渡って計算を行います。 これは集約関数によ り行われる計算の形式と似たようなものです。 とは言っても、非ウィンドウ集約呼び出しのように、ウィンドウ関 数により行が単一出力行にグループ化されることはありません。 その代わり、行はそれぞれ個別の身元を維持しま す。 裏側では、ウィンドウ関数は問い合わせ結果による現在行だけでなく、それ以上の行にアクセスすることがで きます。 51

③ウィンドウ関数の紹介 ウィンドウ関数とは？ ● Wikipedia - 窓関数（SQL）より SQL において、窓関数もしくはウィンドウ関数 (英: window function) は結果セットを部分的に切り出した領域に 集約関数を適用できる、拡張された SELECT ステートメントである。SQL:2003 以降の標準SQLで規定されてい る。分析関数やOLAP機能と呼ばれる場合もある。 ● PostgreSQL 11.5文書 - 3.5. ウインドウ関数より ウィンドウ関数は現在の行に何らかとも関係するテーブル行の集合に渡って計算を行います。 これは集約関数によ り行われる計算の形式と似たようなものです。 とは言っても、非ウィンドウ集約呼び出しのように、ウィンドウ関 数により行が単一出力行にグループ化されることはありません。 その代わり、行はそれぞれ個別の身元を維持しま す。 裏側では、ウィンドウ関数は問い合わせ結果による現在行だけでなく、それ以上の行にアクセスすることがで きます。 …なるほどわからん 52

③ウィンドウ関数の紹介 大雑把にいうと、 ● 前後の行を参照しながら計算・集計結果を出す ○ 例1：1行前に記録された時間との差分計算 ■ 今回の試験問題はコレ ○ 例2 : 全体ランキング ● テーブルを複数の区画に分けて、グループ化せず各々の行に対 して区画ごとの計算・集計結果を出す ○ 例3：グループ別ランキング ○ 例4 : 累積値　など …というようなSELECTが可能 53

③ウィンドウ関数の紹介 MySQL 8.0でRANK()を試してみる（大分県の市町村人口データを使って） 54 mysql> SELECT * FROM population ORDER BY population DESC; +-----------------+------------+-------------------+ | name | population | municipality_type | +-----------------+------------+-------------------+ | 大分市 | 477522 | 1 | | 別府市 | 118054 | 1 | | 中津市 | 82857 | 1 | | 佐伯市 | 67722 | 1 | | 日田市 | 62797 | 1 | | 宇佐市 | 53715 | 1 | | 臼杵市 | 36443 | 1 | | 豊後大野市 | 33782 | 1 | | 由布市 | 33097 | 1 | | 杵築市 | 28373 | 1 | | 日出町 | 27965 | 2 | | 国東市 | 26447 | 1 | | 豊後高田市 | 22086 | 1 | | 竹田市 | 20315 | 1 | | 津久見市 | 16174 | 1 | | 玖珠町 | 14619 | 2 | | 九重町 | 8857 | 2 | | 姫島村 | 1778 | 3 | +-----------------+------------+-------------------+ 18 rows in set (0.00 sec)

③ウィンドウ関数の紹介 全行を対象にしたRANK() OVER句で区画（PARTITION）を指定 ● PARTITION BYを指定していないのでテーブル全行が対象 ● ORDER BYで人口の降順を指定 55 mysql> SELECT -> RANK() OVER (ORDER BY population DESC) AS pop_rank, -> name, -> population, -> municipality_type -> FROM population;

③ウィンドウ関数の紹介 結果：全体順位が表示される（pop_rank） 56 +----------+-----------------+------------+-------------------+ | pop_rank | name | population | municipality_type | +----------+-----------------+------------+-------------------+ | 1 | 大分市 | 477522 | 1 | | 2 | 別府市 | 118054 | 1 | | 3 | 中津市 | 82857 | 1 | | 4 | 佐伯市 | 67722 | 1 | | 5 | 日田市 | 62797 | 1 | | 6 | 宇佐市 | 53715 | 1 | | 7 | 臼杵市 | 36443 | 1 | | 8 | 豊後大野市 | 33782 | 1 | | 9 | 由布市 | 33097 | 1 | | 10 | 杵築市 | 28373 | 1 | | 11 | 日出町 | 27965 | 2 | | 12 | 国東市 | 26447 | 1 | | 13 | 豊後高田市 | 22086 | 1 | | 14 | 竹田市 | 20315 | 1 | | 15 | 津久見市 | 16174 | 1 | | 16 | 玖珠町 | 14619 | 2 | | 17 | 九重町 | 8857 | 2 | | 18 | 姫島村 | 1778 | 3 | +----------+-----------------+------------+-------------------+ 18 rows in set (0.02 sec) 日出町（ひじまち）はこの順位

③ウィンドウ関数の紹介 次は市町村種別で区切ってみる OVER句で区画（PARTITION）を指定 ● PARTITION BYで市町村種別を指定→市・町・村別の順位に ● ORDER BYで人口の降順を指定 57 mysql> SELECT -> RANK() OVER (PARTITION BY municipality_type ORDER BY population DESC) AS pop_rank, -> name, -> population, -> municipality_type -> FROM population;

③ウィンドウ関数の紹介 結果：市・町・村それぞれの順位が表示される（pop_rank） 58 +----------+-----------------+------------+-------------------+ | pop_rank | name | population | municipality_type | +----------+-----------------+------------+-------------------+ | 1 | 大分市 | 477522 | 1 | | 2 | 別府市 | 118054 | 1 | | 3 | 中津市 | 82857 | 1 | | 4 | 佐伯市 | 67722 | 1 | | 5 | 日田市 | 62797 | 1 | | 6 | 宇佐市 | 53715 | 1 | | 7 | 臼杵市 | 36443 | 1 | | 8 | 豊後大野市 | 33782 | 1 | | 9 | 由布市 | 33097 | 1 | | 10 | 杵築市 | 28373 | 1 | | 11 | 国東市 | 26447 | 1 | | 12 | 豊後高田市 | 22086 | 1 | | 13 | 竹田市 | 20315 | 1 | | 14 | 津久見市 | 16174 | 1 | | 1 | 日出町 | 27965 | 2 | | 2 | 玖珠町 | 14619 | 2 | | 3 | 九重町 | 8857 | 2 | | 1 | 姫島村 | 1778 | 3 | +----------+-----------------+------------+-------------------+ 18 rows in set (0.00 sec) 日出町は「町」の1位に

③ウィンドウ関数の紹介 ほかにも、 ● CUME_DIST() 累積分布値 ● FIRST_VALUE() 最初の行の値 ● LEAD() 次行の値 ● PERCENT_RANK() パーセントランク値 ● ROW_NUMBER() 行番号 などの専用関数や、COUNT()・SUM()など通常の集約関数を使う ことが可能 59

③ウィンドウ関数の紹介 ウインドウ関数の参考情報 ● MySQL 8.0 https://qiita.com/hmatsu47/items/6cc0e69f3895f3e4a486 https://qiita.com/hmatsu47/items/7976e81100604f8984d2 ● PostgreSQL 11（12も同じ） https://www.postgresql.jp/document/11/html/functions-window.html https://www.postgresql.jp/document/11/html/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS ● 書籍「SQLデータ分析・活用入門」（ソシム）※第7章 https://www.socym.co.jp/book/1226 MySQL / PostgreSQL のどちらにも対応 60

まとめ ● SQLの実行計画を立てるのがオプティマイザ ○ 多くのRDBMSでは、オプティマイザはコストベース ○ コストが最も低い（と見積もった）実行計画を選択する ○ RDBMSによって（結合などの際に）選択可能な実行計画は異なる ● インデックスをうまく使うとコストを低減できる ● 木構造のテーブルはユースケースにあわせて設計する ○ 想定する階層の深さ、更新の有無など ● 木構造のテーブルを扱うときは再帰CTEが便利 ● 集計・分析にはウィンドウ関数が便利 61

まとめ ● 実務と違うところ ○ オプティマイザのようなRDBMSの内部の仕組み ■ 実際の製品のほうが複雑 ■ 製品ごとに仕組みが異なる場合がある ○ SQLの細かい文法（関数名・パラメータ等） ● 実務と同じところ ○ SQLの基本的な使い方 ○ 採用すべき設計の（基本的な）選択方法 ■ ユースケースに合わせた設計を行う 62

次回の内容（リクエストがあれば開催します） ● テーブルパーティショニング ○ 1つのテーブルを複数に分割して扱う機能 ● パラレルクエリ（パラレルスキャン） ○ 1つのSQLの処理の中で、複数のスキャンを並列で行う ● クラスタリング ○ 複数のサーバでクラスタを構成する 63