Upgrade to PRO for Only $50/Year—Limited-Time Offer! 🔥
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
PHPカンファレンス北海道2024 リーダブルSQL
Search
y-tsuzaki
January 13, 2024
5
580
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
900
ある日PHPerがベンチャー企業のデータ基盤を作ることになったら
ytsuzaki
1
350
【PhpStorm】モブプログラミングの実践と学び【結局はバランス?】
ytsuzaki
0
350
PHPerKaigi 2022 【Laravel】 サクッとN + 1問題を見つけて倒しチャオ!
ytsuzaki
1
2.7k
【超特急】「SQLアンチパターン」 総おさらいLT 【4分で25個】
ytsuzaki
2
750
Nuxt Composition API 使ってみた
ytsuzaki
0
160
Nuxt.js x Composition API x TypeScript
ytsuzaki
0
670
PHPerKaigi 2021 LT PHPで簡単コード生成! 同じようなコードをたくさん書くなら コード生成しチャイナ!
ytsuzaki
0
110
アクセス制御ライブラリ Casbinを使ってみた
ytsuzaki
2
1.3k
Featured
See All Featured
Fight the Zombie Pattern Library - RWD Summit 2016
marcelosomers
232
17k
Side Projects
sachag
452
42k
jQuery: Nuts, Bolts and Bling
dougneiner
61
7.5k
How To Stay Up To Date on Web Technology
chriscoyier
789
250k
The Invisible Side of Design
smashingmag
298
50k
The Power of CSS Pseudo Elements
geoffreycrofte
73
5.3k
Put a Button on it: Removing Barriers to Going Fast.
kastner
59
3.5k
Design and Strategy: How to Deal with People Who Don’t "Get" Design
morganepeng
126
18k
Rails Girls Zürich Keynote
gr2m
94
13k
Keith and Marios Guide to Fast Websites
keithpitt
410
22k
A Tale of Four Properties
chriscoyier
156
23k
Happy Clients
brianwarren
98
6.7k
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