和田省二さんの SQL 講座のメモ書き。文責は sunaot

gistfile1.textile

SQL 講座

SQL

• SELECT 句
• FROM 句
• WHERE 句
• GROUP BY 句
• HAVING 句
• WINDOW 句

SQL の BNF を見る (1992, 1999, 2003)

式、句、関数があるので区別すること。

SQL の書き順

書き順は

1. FROM 句
2. WHERE 句
3. GROUP BY 句
4. HAVING 句
5. WINDOW 句
6. SELECT 句

RDBMS の内部処理手順に従って書くのがいい。

結合

結合はどこでやるべきか。FROM 句でやるべき。WHERE 句でもできるけど、やらないほうがいい。

FROM 句は結合条件。WHERE 句はフィルター条件。

集合演算

• 射影 (SELECT)
• 選択 (WHERE)
• 和集合 (UNION (UNION ALL))
• 積集合 (INTERSECT)
• 差集合 (EXCEPT, MINUS (oracle))
• 直積 (CROSS JOIN)
• 結合 (INNER JOIN/OUTER JOIN)

射影と選択

+----+----+----+----+----+----+
|    |    |    |    |    |    |
+----+----+----+----+----+----+
|    |    |    |    |    |    | ← 選択は行を選んでくる
+----+----+----+----+----+----+
|    |    |    |    |    |    | ← 
+----+----+----+----+----+----+
|    |    |    |    |    |    | ← 
+----+----+----+----+----+----+
|    |    |    |    |    |    |
+----+----+----+----+----+----+
       ↑        ↑
     列を選んでくるのが射影

和集合

集合同士を合わせたものを得る

積集合

集合同士の重複部分を得る

差集合

未入荷 = (発注済 – 入荷済)

二項演算

T1, T2, T3 でクロスジョインするときも三つの集合を一気に掛け合わせるのでなく、順に二つずつの処理が行われることを覚えておく。この辺りはオプティマイザーが解決してくれるけれど、オプティマイザーが賢くないときには SQL を書くときに意識せざるを得ないことがある

  +-----+
  |     |
+---+   |
|   |   |
T1  T2  T3

  +-----+
  |     |
+---+   |
|   |   |
T1  T3  T2

  +-------+
  |       |
+---+   +---+
|   |   |   |
T1  T2  T3  T4

     +-------+
     |       |
  +---. .    |
  |     .    |
+---+   .    |
|   |   .    |
T1  T2  T3   T4

実践集合演算

結合

同一の product_code で複数レコードを持つ products テーブルがある。それぞれに有効開始日が設定されているので、2010/10/01 時点で有効になっている products の集まりを得たい (途中で空白期間があるようなケースは今回は考えない) 。

条件を満たすキー項目の集合を得るためのクエリと
欲しいデータを持ってる集合をそのキーで等価結合して
欲しいデータの集まりを得るという発想をしてます

select
  p.*
from
  products p
    inner join
  (
    select
      product_code,
      max(enabled_from) as active
    from
      products
    where
      to_date('2010/10/01', 'yyyy/mm/dd') >= enabled_from
    group by
      product_code
  ) a
    on     p.product_code = a.product_code
       and p.enabled_from = a.active
;

OUTER JOIN は遅い。実装を見るとわかるが、INNER JOIN に NULL となってるものを後から UNION ALL で追加しているので、その分のオーバーヘッドが単純に増える。

行と行の比較

行と行の比較がしたい。でも SQL には（というよりも集合には）その発想がない。
あくまで便宜的に別の集合とのやりとりにする必要がある。

考え方だけだと

1. 同じ集合の中の行同士で計算ができない
2. 同じ集合同士で計算できないなら集合二つにすればいいじゃない
3. 自分の行同士の比較なんだから、扱いたい集合は二つとも自分
4. 比較するもの同士の関係性を作らないといけないから、仮の行番号を振ってしまえば「一つ下の行」という概念が扱えるね

product_code, enabled_from
            ,                1
            ,                2
            ,                3
            ,                4


product_code, t1.enabled_from       t2.enabled_from
            ,                  1                     2
            ,                  2                     3
            ,                  3                     4
            ,                  4                     5

ここまでいけば、(t2.enabled_from – t1.enabled_from) はかんたんにできる。

with
  product_with_date_rank
    as
      (
        select
          id,
          product_code,
          enabled_from,
          row_number() over (pratition by product_code order by enabled_from) as start_date_seq
        from 
          products p
      ),
  products_with_enabled_to
    as 
      (
        select
          t1.*,
          case 
            when t2.enabled_from is null then to_date('2999/01/01', 'yyyy/mm/dd')
            else t2.enabled_from 
          end as enabled_to
        from
          product_with_date_rank t1
            left outer join
          product_with_date_rank t2
            on     t1.product_code = t2.product_code
               and t1.start_date_seq + 1 = t2.start_date_seq
      )

集合的に扱う SQL の書き方まとめ

• キー項目同士の集合演算というのが基本
• キーというのは集合に対する視点で PK とは違う
• 正規化されたテーブルから、自分がほしい集合を表現するキーをつくって
• そうして得たキー同士で集合演算をやって最後にほしい結果の集合を導く
  • JOIN, UNION ALL, MINUS など
  • この視点だと INNER JOIN も共通する要素を出すための集合演算
• SQL としては絞り込みをなるべく大きく早くかけるように書く