SQLの組み立て方

開発

ソフトウェア開発をやっていると、SQLが苦手なプログラマに多く出会う。SQLはすごく柔軟で高度な処理を簡潔にかけるにも関わらず、わざわざ単純なSQLを使って大量のデータをした後、Javaなどの手続き型言語で加工するわけだ。単にアホなだけという可能性も否定しがたいけれども、SQLの組み方を学ぶ機会が少ないこともあるのかもしれない。

というわけで、割と自己流なんだけど、SQLの組み立て方を紹介してみることにする。

１．幹となるデータを取得する

  • 残高試算表なら仕訳明細、売上推移表なら売上明細などまず幹となるデータを単に取得するSQLを作る。
  • 幹となるデータは、予想される取得結果がサブセットになるものを選ぶ。
  • 幹となるデータがヘッダ、明細で分かれている場合は、INNER JOIN でヘッダ⇒明細の順で繋ぐ。
    • やや趣味の問題だが、ON句の結合条件は、結合先 = 結合元 の順で書く。このようにしておくと、フラグや区分を指定する場合のように、片側だけ表れる条件でも常に同じ側に同じテーブルが来てわかりやすい。
SELECT
    *
FROM
    仕訳ヘッダ SH
    INNER JOIN 仕訳明細 SM
        ON SM.伝票ID = SH.伝票ID

２．幹となるデータに対し絞込み条件を設定する

  • 絞り込み条件は WHERE/ON 句による絞り込みとテーブルによる絞り込みの2種類がある。
  • テーブルによる絞込みにも INNER JOIN する場合と EXISTS 句を使う場合がある。
    • どちらでも書けるが、結合先の主キー項目を使って絞り込む場合はINNER JOIN、その他の複雑な条件を使う場合は EXISTS句を使うことが多いような気がする（INNER JOINでも、GROUP BYした結果をサブクエリ化して結合することで同じことができるが、結果を利用しないのであれば直感的ではないので避けたほうが良いかもしれない）
    • テーブルによる絞込みをする場合、IN 句は利用してはならない。なぜなら、結果に NULL が含まれる際の動作が直感的でなくバグの温床になるから（もちろん IN ('1', '2') のように固定値に対して使用する分には一向に構わない）
SELECT
    *
FROM
    仕訳ヘッダ SH
    INNER JOIN 仕訳明細 SM
        ON SM.伝票ID = SH.伝票ID
    INNER JOIN 勘定制御マスタ KSM
        ON KSM.勘定科目コード = SM.勘定科目コード
WHERE
    SH.伝票日付 LIKE '201001%'

３．幹となるデータを集計する

  • 明細出力するような処理の場合は、もちろんこの項はとばしていい。
  • 集計のキー項目はコード類に対して行い、名称に対して行わないのがミソ。
SELECT
    SM.勘定科目コード,
    NVL(SUM(CASE WHEN SM.貸借区分 = 'D' THEN SM.金額 END), 0) AS 借方金額,
    NVL(SUM(CASE WHEN SM.貸借区分 = 'C' THEN SM.金額 END), 0) AS 貸方金額
FROM
    仕訳ヘッダ SH
    INNER JOIN 仕訳明細 SM
        ON SM.伝票ID = SH.伝票ID
    INNER JOIN 勘定制御マスタ KSM
        ON KSM.勘定科目コード = SM.勘定科目コード
WHERE
    SH.伝票日付 LIKE '201001%'
GROUP BY
    SM.勘定科目コード

４．幹となるデータに対し名称など付加情報を付与する

  • マスタから名称を取得するなど付加情報を取得するため、幹となるデータに対し LEFT JOIN する。
    • この時、決して INNER JOIN してはならない。たかがマスタ不整合が、トランザクションデータの欠けを発生させ業務に大きな支障を与えてしまう。
  • 結合した結果、取得行数が変わらないよう注意する。
    • 結合するテーブルは、幹となるデータに対し N対1 の関係でなければならない。この関係が破られるとデータが重複してしまう。
    • しばしば、N対Mになるようなテーブルを結合し DISTINCT で処理する人がいるが死んでしまえ、異なる名称のレコードが混入した瞬間データが重複してしまう。そのような場合は、キー項目でGROUP BY を行うなど N対1 の関係が維持できるように加工してから結合する。
  • SELECT句に抽出項目を書く時は、コードは結合前のテーブルから、名称は結合後のテーブルから取得する。このように書くと、マスタ不整合が発生した場合でもコードだけは表示でき不備の原因が特定しやすくなる。
SELECT
    S.勘定科目コード,
    KM.勘定科目名称,
    S.借方金額,
    S.貸方金額
FROM
    (
        SELECT
            SM.勘定科目コード,
            NVL(SUM(CASE WHEN SM.貸借区分 = 'D' THEN SM.金額 END), 0) AS 借方金額,
            NVL(SUM(CASE WHEN SM.貸借区分 = 'C' THEN SM.金額 END), 0) AS 貸方金額
        FROM
            仕訳ヘッダ SH
            INNER JOIN 仕訳明細 SM
                ON SM.伝票ID = SH.伝票ID
            INNER JOIN 勘定制御マスタ KSM
                ON KSM.勘定科目コード = SM.勘定科目コード
        WHERE
            SH.伝票日付 LIKE '201001%'
        GROUP BY
            SM.勘定科目コード
    ) S
    LEFT JOIN 勘定科目マスタ KM
        ON KM.勘定科目コード = S.勘定科目コード

５．ソート順を書く

  • 最後に表示順を明示する。しばしば ORDER BY 句を書かない人を見かけるが、システムにおいて実行する度に表示順が変わるというのでは安定感に欠ける。1件しか戻されない場合は別だが、複数件取得する場合には必ず ORDER BY 句をつけ忘れないようにしたい。