Upgrade to PRO for Only $50/Year—Limited-Time Offer! 🔥

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

y-tsuzaki
January 13, 2024
580

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

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

※スライド中では2023とありますが誤りです

y-tsuzaki

January 13, 2024
Tweet

More Decks by y-tsuzaki

Transcript

  1. Copyright© M&Aクラウド 2 ⾃⼰紹介 つざき 経歴:Webエンジニア5年くらい → データエンジニア⼊⾨中 最近使ってる: BigQuery,

    MySQL, dbt, trocco 所属 :株式会社M&Aクラウド 住み :北海道⼩樽市⽣まれ → 東京 地元北海道でPHPerコミュニティに参加できて嬉しいです! 運営の皆さんありがとうございます🙌
  2. Copyright© M&Aクラウド ⽬次 3 • トークの⽬的 • リーダブルコードの基本定理 • 読みやすいSQLを書くテクニック

    ◦ テクニック1:処理の流れを上から読めるようにする ◦ テクニック2:意図を明確にする ◦ テクニック3:⾒ただけで構造がわかるようにする ◦ テクニック4:⽤意された構⽂を活⽤する ◦ テクニック5:SQLの実⾏基盤を整える
  3. Copyright© M&Aクラウド リーダブルコードの基本定理 6 読みやすさの基本定理 “コードは他の⼈が最短時間で 理解できるように書かなければいけない。” リーダブルコード P.3 より引⽤

    Dustin Boswell (著), Trevor Foucher (著), 須藤 功平 (解説), 角 征典 (翻訳). リーダブルコード ―より良い コードを書くためのシンプルで実践的なテクニック (Theory in practice). オーム社. 2012.
  4. Copyright© M&Aクラウド テクニック2:意図を明確にする 15 CTEを適切に分割しよう “コードは⼀つずつタスクを⾏うようにしなければならない” リーダブルコードより引⽤ “ パフォーマンスが許せば、 CTE

    は単⼀の論理的な作業単位を実⾏する必要があります。” dbt Lab / How we style our SQL より引⽤ 例 - テーブルからデータを取得する (前述のImport CTE) - カラムをリネームする - 合計値を求める - 上位N件を取得する
  5. 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 で⾃動整形 - 保存時‧コミット時に⾃動でフォーマットされるように設定するとなおよい
  6. Copyright© M&Aクラウド テクニック4:⽤意された構⽂を活⽤する 20 Window関数(分析関数, Window Function) ⾏セット(テーブルの⼀部または全部)に対して複雑な計算を⾏うために使⽤ 便利な構⽂を使ってシンプルなSQLを書こう データベース

    Window関数のサポート MySQL サポート(8.0以降) 2018年 SQL Server サポート(2008以降) SQLite サポート(3.25.0以降) BigQuery サポート Oracle サポート Snowflake サポート
  7. 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() ウィンドウ内の値の標準偏差を計算する。
  8. 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
  9. 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昇順で 行番号を付与 表 注文テーブル(連番付き)
  10. 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 表 注文テーブル(前回の注文日付き)
  11. 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() ウィンドウ内の値の標準偏差を計算する。
  12. Copyright© M&Aクラウド SQLの実⾏基盤を整える 32 データ分析基盤を構築してSQLを分割する SQLを分割するにはデータ分析基盤が必要になる 詳細はトークの範疇から出るので割愛 クソデカSQL シンプルな SQL

    シンプルな SQL シンプルな SQL 分割 シンプルな SQL どういうふうに 分割する? だれがいつ実行する? 元テーブルや 結果テーブルをどこに保存 する?
  13. Copyright© M&Aクラウド まとめ 33 • リーダブルコードの基本定理 ◦ “コードは他の⼈が最短時間で理解できるように書かなければいけない。” • サブクエリよりCTEを使う

    • インポートCTEと機能的なCTEを分離する • 単⼀の論理的な作業単位で分割する • スタイルを統⼀する • 便利な構⽂を使ってシンプルなSQLを書く ◦ Window関数みんな使える • データ分析基盤を構築してSQLを分割する