Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
PHPカンファレンス北海道2024 リーダブルSQL
Search
y-tsuzaki
January 13, 2024
5
590
PHPカンファレンス北海道2024 リーダブルSQL
PHPカンファレンス北海道2024で登壇する資料です
※スライド中では2023とありますが誤りです
y-tsuzaki
January 13, 2024
Tweet
Share
More Decks by y-tsuzaki
See All by y-tsuzaki
PHPカンファレンス福岡2024 【超特急】SQLアンチパターン総おさらいLT
ytsuzaki
0
970
ある日PHPerがベンチャー企業のデータ基盤を作ることになったら
ytsuzaki
1
350
【PhpStorm】モブプログラミングの実践と学び【結局はバランス?】
ytsuzaki
0
350
PHPerKaigi 2022 【Laravel】 サクッとN + 1問題を見つけて倒しチャオ!
ytsuzaki
1
2.8k
【超特急】「SQLアンチパターン」 総おさらいLT 【4分で25個】
ytsuzaki
2
760
Nuxt Composition API 使ってみた
ytsuzaki
0
170
Nuxt.js x Composition API x TypeScript
ytsuzaki
0
680
PHPerKaigi 2021 LT PHPで簡単コード生成! 同じようなコードをたくさん書くなら コード生成しチャイナ!
ytsuzaki
0
110
アクセス制御ライブラリ Casbinを使ってみた
ytsuzaki
2
1.4k
Featured
See All Featured
Navigating Team Friction
lara
183
15k
We Have a Design System, Now What?
morganepeng
51
7.3k
A Philosophy of Restraint
colly
203
16k
Designing for humans not robots
tammielis
250
25k
How to Ace a Technical Interview
jacobian
276
23k
Statistics for Hackers
jakevdp
796
220k
The Cult of Friendly URLs
andyhume
78
6.1k
Exploring the Power of Turbo Streams & Action Cable | RailsConf2023
kevinliebholz
28
4.4k
StorybookのUI Testing Handbookを読んだ
zakiyama
27
5.4k
Java REST API Framework Comparison - PWX 2021
mraible
28
8.3k
Reflections from 52 weeks, 52 projects
jeffersonlam
347
20k
Agile that works and the tools we love
rasmusluckow
328
21k
Transcript
Copyright© M&Aクラウド リーダブルSQL [より良いSQLを書くためのシンプルで実践的なテクニック] PHPカンファレンス北海道2023 M&Aクラウド プロダクト統括部 データエンジニアリング部 つざき
Copyright© M&Aクラウド 2 ⾃⼰紹介 つざき 経歴:Webエンジニア5年くらい → データエンジニア⼊⾨中 最近使ってる: BigQuery,
MySQL, dbt, trocco 所属 :株式会社M&Aクラウド 住み :北海道⼩樽市⽣まれ → 東京 地元北海道でPHPerコミュニティに参加できて嬉しいです! 運営の皆さんありがとうございます🙌
Copyright© M&Aクラウド ⽬次 3 • トークの⽬的 • リーダブルコードの基本定理 • 読みやすいSQLを書くテクニック
◦ テクニック1:処理の流れを上から読めるようにする ◦ テクニック2:意図を明確にする ◦ テクニック3:⾒ただけで構造がわかるようにする ◦ テクニック4:⽤意された構⽂を活⽤する ◦ テクニック5:SQLの実⾏基盤を整える
Copyright© M&Aクラウド トークの⽬的 4 普通のSQL(CRUD) → シンプル 複雑なロジックはアプリケーション上に データ分析⽤SQL → 肥⼤で複雑になりがち
SQLだけでデータ加⼯や集計、多段的な結合など多くの処理が必要
Copyright© M&Aクラウド トークの⽬的 5 普通のSQL(CRUD) → シンプル 複雑なロジックはアプリケーション上に データ分析⽤SQL → 肥⼤で複雑になりがち
SQLだけでデータ加⼯や集計、多段的な結合など多くの処理が必要 どうにかしたい!
Copyright© M&Aクラウド リーダブルコードの基本定理 6 読みやすさの基本定理 “コードは他の⼈が最短時間で 理解できるように書かなければいけない。” リーダブルコード P.3 より引⽤
Dustin Boswell (著), Trevor Foucher (著), 須藤 功平 (解説), 角 征典 (翻訳). リーダブルコード ―より良い コードを書くためのシンプルで実践的なテクニック (Theory in practice). オーム社. 2012.
Copyright© M&Aクラウド テクニック1:処理の流れを上から読めるようにする 8
Copyright© M&Aクラウド テクニック1:処理の流れを上から読めるようにする 9 サブクエリではなくCTEを使おう • CTE(Common Table Expression; 共通テーブル式)
• クエリ内で⼀時的な結果セットを定義するための機能
Copyright© M&Aクラウド テクニック1:処理の流れを上から読めるようにする 10 サブクエリではなくCTEを使おう
Copyright© M&Aクラウド テクニック1:処理の流れを上から読めるようにする 11 各ユーザーの最後の3回の購⼊の中で、 最も⾼い合計⾦額の注⽂を⾒つける サブクエリだと、ネストの内側から読んでいく必要 があり 読みづらい
Copyright© M&Aクラウド テクニック1:処理の流れを上から読めるようにする 12 CTEを使うと上から順に読めるようになる コード書いてる時と同じ感じ! ヒューマンフレンドリー!
Copyright© M&Aクラウド テクニック2:意図を明確にする 13
Copyright© M&Aクラウド テクニック2:意図を明確にする 14 「インポートのためのCTE」と「機能的なCTE」を分けよう どのテーブルの、どのカラム、どの行に 依存してるかわかりやすい
Copyright© M&Aクラウド テクニック2:意図を明確にする 15 CTEを適切に分割しよう “コードは⼀つずつタスクを⾏うようにしなければならない” リーダブルコードより引⽤ “ パフォーマンスが許せば、 CTE
は単⼀の論理的な作業単位を実⾏する必要があります。” dbt Lab / How we style our SQL より引⽤ 例 - テーブルからデータを取得する (前述のImport CTE) - カラムをリネームする - 合計値を求める - 上位N件を取得する
Copyright© M&Aクラウド テクニック2:意図を明確にする 16 コメントを残そう アプリケーションのコードと⼀緒 他⼈が理解できる時間を最短にできるようなコメントを ステータスの1, 3, 5ってそれぞれどういう意
味だっけ? なるほどね!
Copyright© M&Aクラウド テクニック3:⾒ただけで構造がわかるようにする 17
Copyright© M&Aクラウド テクニック3:⾒ただけで構造がわかるようにする 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 で⾃動整形 - 保存時‧コミット時に⾃動でフォーマットされるように設定するとなおよい
Copyright© M&Aクラウド テクニック4:⽤意された構⽂を活⽤する 19
Copyright© M&Aクラウド テクニック4:⽤意された構⽂を活⽤する 20 Window関数(分析関数, Window Function) ⾏セット(テーブルの⼀部または全部)に対して複雑な計算を⾏うために使⽤ 便利な構⽂を使ってシンプルなSQLを書こう データベース
Window関数のサポート MySQL サポート(8.0以降) 2018年 SQL Server サポート(2008以降) SQLite サポート(3.25.0以降) BigQuery サポート Oracle サポート Snowflake サポート
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() ウィンドウ内の値の標準偏差を計算する。
Copyright© M&Aクラウド テクニック4:⽤意された構⽂を活⽤する 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
Copyright© M&Aクラウド テクニック4:⽤意された構⽂を活⽤する 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昇順で 行番号を付与 表 注文テーブル(連番付き)
Copyright© M&Aクラウド テクニック4:⽤意された構⽂を活⽤する 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 表 注文テーブル(前回の注文日付き)
Copyright© M&Aクラウド テクニック4:⽤意された構⽂を活⽤する 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() ウィンドウ内の値の標準偏差を計算する。
Copyright© M&Aクラウド テクニック4:⽤意された構⽂を活⽤する 30 QUALIFY構⽂ ※BigQuery, SnowFlake, Teradataなど⼀部のDBのみサポート ウィンドウ関数の結果を⽤いて絞り込みを⾏たい時 QUALIFY句を使えば、WHEREと同じようにシンプルに絞り込みができる
図 CTEを使ってフィルタするクエリ 図 QUALIFY句を使ってフィルタするクエリ
Copyright© M&Aクラウド テクニック5:SQLの実⾏基盤を整える 31
Copyright© M&Aクラウド SQLの実⾏基盤を整える 32 データ分析基盤を構築してSQLを分割する SQLを分割するにはデータ分析基盤が必要になる 詳細はトークの範疇から出るので割愛 クソデカSQL シンプルな SQL
シンプルな SQL シンプルな SQL 分割 シンプルな SQL どういうふうに 分割する? だれがいつ実行する? 元テーブルや 結果テーブルをどこに保存 する?
Copyright© M&Aクラウド まとめ 33 • リーダブルコードの基本定理 ◦ “コードは他の⼈が最短時間で理解できるように書かなければいけない。” • サブクエリよりCTEを使う
• インポートCTEと機能的なCTEを分離する • 単⼀の論理的な作業単位で分割する • スタイルを統⼀する • 便利な構⽂を使ってシンプルなSQLを書く ◦ Window関数みんな使える • データ分析基盤を構築してSQLを分割する
Copyright© M&Aクラウド THANK YOU FOR WATCHING! 34