DAKPPOの日記

技術ブログの体を装った日記

「わかりみSQL」を読んで得た知見と感想

わかりみSQL - カウプラン機関極東支部 https://kauplan.org/books/wakarimisql/ booth.pm

動機

SQLの理解が曖昧だった。 一度包括的に学び、テーブル設計や実行効率などを考える上での足がかりとしたい。

新しく知ったこと

  • サブクエリという概念。withでサブクエリの結果に名前をつけられるが、最適化が外れるので使用の際は注意。
  • テーブル結合 = すべての組み合わせの生成 + 条件による絞り込み。 演算子joinはテーブル結合をwhereから分離する。
    • using: 列名が同じかつ = のとき
    • natural joinは可読性に難があるので避ける
    • 自己結合: 同テーブル内の別の行を結合したりするなど。
  • left outer joinとright outer join: 集合A,Bの共通部分をとる内部結合(inner join)に対し、A\Bをくっつけるイメージ。leftとrightはテーブルの位置関係。cross joinはすべての組み合わせの生成だけする。
  • 複合主キー/外部キー: 複数の主/外部キーを指定
  • 関連
    • 1:多は、join時の各行がそれぞれrootからleafに至る１つのパスに対応する。
    • 多:多は交差テーブルで対処。実質inner joinみたいなもの。
  • index
    • 必要な列だけbtreeで管理し、高速検索(数百倍違う！)を可能にする。
    • カーディナリティが高い列に作成しよう。uniqueな列には勝手に作成されてる。
    • 変更が加わる際はちょっと遅くなるので注意。create index concurrently
    • 式インデックス、部分インデックス、Index Only Scan
  • 計測系
    • timing on: 時間測定モード
    • explain: 実行内容を表示
  • 日付と日時の型(ソフトウェアに依存するので話半分)
    • date, timestamp, interval
    • current_date, current_timestamp, now()
    • date_part, extract, age()
    • date_truncで週初めや月始めを取得。generate_seriesなどと組み合わせて今月の日の全列挙なども
  • case
    • case when P then A else B end
    • case COL when A then A' else B' end
  • 相関サブクエリ
    • サブクエリ内でサブクエリ外で取得した行の値を使用すると、その行の数だけサブクエリが実行される。
    • MySQLはwhere句に書いたサブクエリについては相関サブクエリと同じ動きをする。
  • 数字の連番 generate_series(begin, end)とそれを使った日付の連番 select ('2020-02-02'::date + make_interval(days => n.num))::date from generate_series(0, 7-1) as n(num)
  • window関数
    • over (partition by ... order by ...) または over hoge_window ... window hoge_window as (partition by ... order by ...)
    • partition by: 集約しないでgroup byみたいなもの。パーティションに分ける。指定しない場合全体が１つのパーティション。
    • order by: パーティションごとにorder by。ウィンドウフレームの現在行の基準を定める。指定しない場合現在行はパーティション最終行に固定。
    • ウィンドウフレーム: パーティション先頭から現在の行までの範囲
    • row_number()とrank(), lag()とlead(), sum/max/minはウィンドウフレームに注意
  • 集約関数いろいろ。文字列をjoinするやつ、配列やjsonつくるやつ、and/or
  • insert関連
    • returning: insertなどの処理に返り値を設定できる
    • values: 行をベタ書きできる
    • update or insert: returningを使うか on conflict
  • union/union all
  • 再帰クエリ
    • with recursive X as ( + 最初の実行結果Xを取得 + union all + 直前のXを用いて次の実行結果を取得 + ) select * from X
  • トランザクション in postgres

感想

ボリュームはあるが、しっかりとした実践的な理解が得られた。SQL素人の一冊目に最適だったと思う。

Docker上でやる場合は、コンテナ内にサンプルファイルを落とすのがいいんじゃないでしょうか。

$ apt update && apt install curl unzip
$ curl https://kauplan.org/books/wakarimisql/sqlfiles_20190922.zip > tmp.zip
$ unzip tmp.zip

総合問題23.1のpriceの値が書籍とサンプルファイルで異なるので注意。