PHPカンファレンス北海道2023 リーダブルSQL

PHPカンファレンス北海道2023で登壇する資料です

Transcript

1. Copyright© M&Aクラウド リーダブルSQL [より良いSQLを書くためのシンプルで実践的なテクニック] PHPカンファレンス北海道2023 M&Aクラウド プロダクト統括部 データエンジニアリング部 つざき
2. Copyright© M&Aクラウド 2 ⾃⼰紹介 つざき 経歴：Webエンジニア5年くらい → データエンジニア⼊⾨中 最近使ってる： BigQuery, MySQL, dbt, trocco 所属 ：株式会社M&Aクラウド 住み ：北海道⼩樽市⽣まれ → 東京 地元北海道でPHPerコミュニティに参加できて嬉しいです! 運営の皆さんありがとうございます🙌
3. Copyright© M&Aクラウド ⽬次 3 • トークの⽬的 • リーダブルコードの基本定理 • 読みやすいSQLを書くテクニック ◦ テクニック１：処理の流れを上から読めるようにする ◦ テクニック２：意図を明確にする ◦ テクニック３：⾒ただけで構造がわかるようにする ◦ テクニック４：⽤意された構⽂を活⽤する ◦ テクニック５：SQLの実⾏基盤を整える
4. Copyright© M&Aクラウド トークの⽬的 4 普通のSQL(CRUD)  →  シンプル             複雑なロジックはアプリケーション上に データ分析⽤SQL  →  肥⼤で複雑になりがち    SQLだけでデータ加⼯や集計、多段的な結合など多くの処理が必要
5. Copyright© M&Aクラウド トークの⽬的 5 普通のSQL(CRUD)  →  シンプル             複雑なロジックはアプリケーション上に データ分析⽤SQL  →  肥⼤で複雑になりがち    SQLだけでデータ加⼯や集計、多段的な結合など多くの処理が必要 どうにかしたい！
6. Copyright© M&Aクラウド リーダブルコードの基本定理 6 読みやすさの基本定理 “コードは他の⼈が最短時間で 理解できるように書かなければいけない。” リーダブルコード P.3 より引⽤ Dustin Boswell (著), Trevor Foucher (著), 須藤 功平 (解説), 角 征典 (翻訳). リーダブルコード ―より良い コードを書くためのシンプルで実践的なテクニック (Theory in practice). オーム社. 2012.
7. Copyright© M&Aクラウド テクニック１：処理の流れを上から読めるようにする 8
8. Copyright© M&Aクラウド テクニック１：処理の流れを上から読めるようにする 9 サブクエリではなくCTEを使おう • CTE（Common Table Expression; 共通テーブル式） • クエリ内で⼀時的な結果セットを定義するための機能
9. Copyright© M&Aクラウド テクニック１：処理の流れを上から読めるようにする 10 サブクエリではなくCTEを使おう
10. Copyright© M&Aクラウド テクニック１：処理の流れを上から読めるようにする 11 各ユーザーの最後の3回の購⼊の中で、 最も⾼い合計⾦額の注⽂を⾒つける サブクエリだと、ネストの内側から読んでいく必要 があり 読みづらい
11. Copyright© M&Aクラウド テクニック１：処理の流れを上から読めるようにする 12 CTEを使うと上から順に読めるようになる コード書いてる時と同じ感じ！ ヒューマンフレンドリー！
12. Copyright© M&Aクラウド テクニック２：意図を明確にする 13
13. Copyright© M&Aクラウド テクニック２：意図を明確にする 14 「インポートのためのCTE」と「機能的なCTE」を分けよう どのテーブルの、どのカラム、どの行に 依存してるかわかりやすい
14. Copyright© M&Aクラウド テクニック２：意図を明確にする 15 CTEを適切に分割しよう “コードは⼀つずつタスクを⾏うようにしなければならない” リーダブルコードより引⽤ “ パフォーマンスが許せば、 CTE は単⼀の論理的な作業単位を実⾏する必要があります。” dbt Lab / How we style our SQL より引⽤ 例 - テーブルからデータを取得する (前述のImport CTE) - カラムをリネームする - 合計値を求める - 上位N件を取得する
15. Copyright© M&Aクラウド テクニック２：意図を明確にする 16 コメントを残そう アプリケーションのコードと⼀緒 他⼈が理解できる時間を最短にできるようなコメントを ステータスの1, 3, 5ってそれぞれどういう意 味だっけ？ なるほどね！
16. Copyright© M&Aクラウド テクニック３：⾒ただけで構造がわかるようにする 17
17. Copyright© M&Aクラウド テクニック３：⾒ただけで構造がわかるようにする 18 スタイルガイドを定めよう - SQLには公式のスタイルガイドが存在しない - これにしておけば安定！ みたいなのもない（？） - キーワードが⼤⽂字か⼩⽂字かも流派によって異なる 良さげなやつ - gitLab / SQL Style Guide : - https://handbook.gitlab.com/handbook/business-technology/data-team/platform/sql-style-guide/ - dbt / How we style our SQL: - https://docs.getdbt.com/best-practices/how-we-style/2-how-we-style-our-sql スタイルを整える - SQLFluff で⾃動整形 - 保存時‧コミット時に⾃動でフォーマットされるように設定するとなおよい
18. Copyright© M&Aクラウド テクニック４：⽤意された構⽂を活⽤する 19
19. Copyright© M&Aクラウド テクニック４：⽤意された構⽂を活⽤する 20 Window関数（分析関数, Window Function) ⾏セット（テーブルの⼀部または全部）に対して複雑な計算を⾏うために使⽤ 便利な構⽂を使ってシンプルなSQLを書こう データベース Window関数のサポート MySQL サポート（8.0以降） 2018年 SQL Server サポート（2008以降） SQLite サポート（3.25.0以降） BigQuery サポート Oracle サポート Snowflake サポート
20. Copyright© M&Aクラウド ⽤意された機能を活⽤する 21 Window関数の例 カテゴリー 関数 説明 ランキング ROW_NUMBER() 各⾏に⼀意の連番を割り当てる。 例）1,2,3,4 RANK() 各⾏に順位を割り当てる。（同じ値は同じランク） 例) 1,2,2,4 DENSE_RANK() 各⾏に順位を割り当てる。（同じ値は同じランク.ギャップなし） 例) 1,2,2,3。 NTILE(n) 結果セットをn個の等しい部分に分割し各⾏にグループ番号を割り当てる。 PERCENT_RANK() 各⾏の相対的なパーセンタイルランクを計算する。 例）成績上位0.1% みたいな CUME_DIST() 各⾏の累積分布値を計算する。 解析 LEAD() 1⾏先の値を返す。 LAG() 1⾏前の値を返す。 FIRST_VALUE() ウィンドウ内の最初の値を返す。 LAST_VALUE() ウィンドウ内の最後の値を返す。 集約 SUM() ウィンドウ内の値の合計を計算する。 AVG() ウィンドウ内の値の平均を計算する。 COUNT() ウィンドウ内の⾏数をカウントする。 MIN() ウィンドウ内の最⼩値を返す。 MAX() ウィンドウ内の最⼤値を返す。 STDDEV() ウィンドウ内の値の標準偏差を計算する。
21. Copyright© M&Aクラウド テクニック４：⽤意された構⽂を活⽤する 25 ROW_NUMBER() 各⾏に⼀意の連番を割り当てる order_id user_id order_date 1 1 2023-01-01 3 1 2023-01-02 2 1 2023-01-03 5 2 2023-01-04 4 2 2023-01-05 6 3 2023-01-06 8 3 2023-01-07 7 3 2023-01-08 9 3 2023-01-09 10 3 2023-01-10 表 注文テーブル 図 ROW_NUMBER()を使ったSQL
22. Copyright© M&Aクラウド テクニック４：⽤意された構⽂を活⽤する 26 ROW_NUMBER() 使い所：「最新/最古の1件を取得」「最⼤/最⼩の1件を取得」「上位N件を取得」 order_id user_id order_date row_num 1 1 2023-01-01 1 3 1 2023-01-02 2 2 1 2023-01-03 3 5 2 2023-01-04 1 4 2 2023-01-05 2 6 3 2023-01-06 1 8 3 2023-01-07 2 7 3 2023-01-08 3 9 3 2023-01-09 4 10 3 2023-01-10 5 user_idごとに order_date昇順で 行番号を付与 表 注文テーブル（連番付き）
23. Copyright© M&Aクラウド テクニック４：⽤意された構⽂を活⽤する 28 LAG() 1⾏前の値を返す 使い所：「前⽇の値」「前回からの差分」 order_id user_id order_date previous_order_date 1 1 2023-01-01 3 1 2023-01-02 2023-01-01 2 1 2023-01-03 2023-01-02 図 LAGを使ったSQL 表 注文テーブル（前回の注文日付き）
24. Copyright© M&Aクラウド テクニック４：⽤意された構⽂を活⽤する 29 他にもいろいろ カテゴリー 関数 説明 ランキング ROW_NUMBER() 各⾏に⼀意の連番を割り当てる。 例）1,2,3,4 RANK() 各⾏に順位を割り当てる。（同じ値は同じランク） 例) 1,2,2,4 DENSE_RANK() 各⾏に順位を割り当てる。（同じ値は同じランク.ギャップなし） 例) 1,2,2,3。 NTILE(n) 結果セットをn個の等しい部分に分割し各⾏にグループ番号を割り当てる。 PERCENT_RANK() 各⾏の相対的なパーセンタイルランクを計算する。 例）成績上位0.1% みたいな CUME_DIST() 各⾏の累積分布値を計算する。 解析 LEAD() 1⾏先の値を返す。 LAG() 1⾏前の値を返す。 FIRST_VALUE() ウィンドウ内の最初の値を返す。 LAST_VALUE() ウィンドウ内の最後の値を返す。 集約 SUM() ウィンドウ内の値の合計を計算する。 AVG() ウィンドウ内の値の平均を計算する。 COUNT() ウィンドウ内の⾏数をカウントする。 MIN() ウィンドウ内の最⼩値を返す。 MAX() ウィンドウ内の最⼤値を返す。 STDDEV() ウィンドウ内の値の標準偏差を計算する。
25. Copyright© M&Aクラウド テクニック４：⽤意された構⽂を活⽤する 30 QUALIFY構⽂ ※BigQuery, SnowFlake, Teradataなど⼀部のDBのみサポート ウィンドウ関数の結果を⽤いて絞り込みを⾏たい時 QUALIFY句を使えば、WHEREと同じようにシンプルに絞り込みができる 図 CTEを使ってフィルタするクエリ 図 QUALIFY句を使ってフィルタするクエリ
26. Copyright© M&Aクラウド テクニック５：SQLの実⾏基盤を整える 31
27. Copyright© M&Aクラウド SQLの実⾏基盤を整える 32 データ分析基盤を構築してSQLを分割する SQLを分割するにはデータ分析基盤が必要になる 詳細はトークの範疇から出るので割愛 クソデカSQL シンプルな SQL シンプルな SQL シンプルな SQL 分割 シンプルな SQL どういうふうに 分割する？ だれがいつ実行する？ 元テーブルや 結果テーブルをどこに保存 する？
28. Copyright© M&Aクラウド まとめ 33 • リーダブルコードの基本定理 ◦ “コードは他の⼈が最短時間で理解できるように書かなければいけない。” • サブクエリよりCTEを使う • インポートCTEと機能的なCTEを分離する • 単⼀の論理的な作業単位で分割する • スタイルを統⼀する • 便利な構⽂を使ってシンプルなSQLを書く ◦ Window関数みんな使える • データ分析基盤を構築してSQLを分割する
29. Copyright© M&Aクラウド THANK YOU FOR WATCHING! 34