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

Slide 1

Slide 1 text

Slide 2

Slide 2 text

Slide 3

Slide 3 text

Slide 4

Slide 4 text

Slide 5

Slide 5 text

Slide 6

Slide 6 text

Copyright© M&Aクラウドリーダブルコードの基本定理 6 読みやすさの基本定理 “コードは他の⼈が最短時間で理解できるように書かなければいけない。” リーダブルコード P.3 より引⽤ Dustin Boswell (著), Trevor Foucher (著), 須藤功平 (解説), 角征典 (翻訳). リーダブルコード ―より良いコードを書くためのシンプルで実践的なテクニック (Theory in practice). オーム社. 2012.

Slide 7

Slide 7 text

Slide 8

Slide 8 text

Slide 9

Slide 9 text

Slide 10

Slide 10 text

Slide 11

Slide 11 text

Slide 12

Slide 12 text

Slide 13

Slide 13 text

Slide 14

Slide 14 text

Copyright© M&Aクラウドテクニック２：意図を明確にする 15 CTEを適切に分割しよう “コードは⼀つずつタスクを⾏うようにしなければならない” リーダブルコードより引⽤ “ パフォーマンスが許せば、 CTE は単⼀の論理的な作業単位を実⾏する必要があります。” dbt Lab / How we style our SQL より引⽤例 - テーブルからデータを取得する (前述のImport CTE) - カラムをリネームする - 合計値を求める - 上位N件を取得する

Slide 15

Slide 15 text

Slide 16

Slide 16 text

Slide 17

Slide 17 text

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 スタイルを整える - SQLFluﬀ で⾃動整形 - 保存時‧コミット時に⾃動でフォーマットされるように設定するとなおよい

Slide 18

Slide 18 text

Slide 19

Slide 19 text

Copyright© M&Aクラウドテクニック４：⽤意された構⽂を活⽤する 20 Window関数（分析関数, Window Function) ⾏セット（テーブルの⼀部または全部）に対して複雑な計算を⾏うために使⽤便利な構⽂を使ってシンプルなSQLを書こうデータベース Window関数のサポート MySQL サポート（8.0以降） 2018年 SQL Server サポート（2008以降） SQLite サポート（3.25.0以降） BigQuery サポート Oracle サポート Snowflake サポート

Slide 20

Slide 20 text

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() ウィンドウ内の値の標準偏差を計算する。

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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昇順で行番号を付与表注文テーブル（連番付き）

Slide 23

Slide 23 text

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 表注文テーブル（前回の注文日付き）

Slide 24

Slide 24 text

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() ウィンドウ内の値の標準偏差を計算する。

Slide 25

Slide 25 text

Slide 26

Slide 26 text

Slide 27

Slide 27 text

Slide 28

Slide 28 text

Copyright© M&Aクラウドまとめ 33 ● リーダブルコードの基本定理 ○ “コードは他の⼈が最短時間で理解できるように書かなければいけない。” ● サブクエリよりCTEを使う ● インポートCTEと機能的なCTEを分離する ● 単⼀の論理的な作業単位で分割する ● スタイルを統⼀する ● 便利な構⽂を使ってシンプルなSQLを書く ○ Window関数みんな使える ● データ分析基盤を構築してSQLを分割する

Slide 29

Slide 29 text