こんにちは。開発ブログ運営担当のktです。

前回に引き続きSQLネタです。

SQLのWITH句って利用していますか？私の周りでは意外と使ったことがないという方がいらっしゃいます。

今回はWITH句を利用して複雑なサブクエリをシンプルにして利用する方法を紹介します。

前提

従業員の交通費申請を保持する[申請テーブル]があって、その申請が今どういう状態かを表す「ステータス」という項目があります。

ステータスのコードの意味は、1:申請中、2:承認済です。

[申請テーブル]

従業員No	年	月	ステータス
1111	2017	9	1
2222	2017	9	2
3333	2017	9	2

もう一つ従業員の情報を保持する[従業員テーブル]があって、その従業員がどの支社に所属しているかを表す「支社コード」という項目があります。(他にも氏名等の項目を持ちますが、今回は説明を単純にするため省略します)

[従業員テーブル]

従業員No	支社コード
1111	B001
2222	B001
3333	B002

やりたかったこと

上記2テーブルから、支社別で月ごとの従業員数と申請数、承認数を取得したい。

解決した方法

まずWITH句を利用して[申請テーブル]と[従業員テーブル]を結合したものを[base]とし、

その[base]からそれぞれ従業員数、申請数、承認数をカウントし最後にひとまとめにして取得しています。

WITH base AS (
  SELECT
      従業員テーブル.支社コード
    , 申請テーブル.年
    , 申請テーブル.月
    , 申請テーブル.ステータス
  FROM
    申請テーブル
    INNER JOIN 従業員テーブル
      ON 申請テーブル.従業員No = 従業員テーブル.従業員No
),
jugyoin AS (
  SELECT
      base.支社コード, base.年, base.月, COUNT(*) cnt
  FROM
    base
  GROUP BY base.支社コード, base.年, base.月
),
shinsei AS (
  SELECT
      base.支社コード, base.年, base.月, COUNT(*) cnt
  FROM
    base
  WHERE
    base.ステータス = 1
  GROUP BY base.支社コード, base.年, base.月
),
shonin AS (
  SELECT
      base.支社コード, base.年, base.月, COUNT(*) cnt
  FROM
    base
  WHERE
    base.ステータス = 2
  GROUP BY base.支社コード, base.年, base.月
)
SELECT
    jugyoin.支社コード
  , jugyoin.年
  , jugyoin.月
  , jugyoin.cnt 従業員数
  , NVL(shinsei.cnt, 0) 申請数
  , NVL(shonin.cnt, 0) 承認数
FROM
  jugyoin
  LEFT JOIN shinsei
    ON  jugyoin.支社コード = shinsei.支社コード
    AND jugyoin.年 = shinsei.年
    AND jugyoin.月 = shinsei.月
  LEFT JOIN shonin
    ON jugyoin.支社コード = shonin.支社コード
    AND jugyoin.年 = shonin.年
    AND jugyoin.月 = shonin.月

取得した結果は以下のようになります。

支社コード	年	月	従業員数	申請数	承認数
B001	2017	9	2	1	1
B002	2017	9	1	0	1

解説

まずWITH句の最初に、以下の結果を返す[base]テーブルを作っているようなものです。

支社コード	年	月	ステータス
B001	2017	9	1
B001	2017	9	2
B002	2017	9	2

次に[base]テーブルから従業員数をカウントした[jugyoin]テーブルを用意し、

同じく[base]テーブルからステータスが「1:申請中」のものをカウントして[shinsei]テーブルとし、

続いて[base]テーブルからステータスが「2:承認済」のものをカウントして[shonin]テーブルとし、

最後にそれらを結合してSELECTで参照しています。

[base]を再利用することで、同じサブクエリが何回も出てくるのを防ぎ読みやすくなっていると思います。

まとめ

今回はサンプル用なので、そんなに複雑なWITH句になっていませんが、読みやすくなる効果は分かってもらえると思います。

他にも再帰的にWITH句を利用するテクニックもあります。

普通にサブクエリだけをつなげるのと、WITH句を利用したときのパフォーマンスを比較したサイトもありますね。

IT業界に入ってSQLの学習で初めてサブクエリというもの知ったとき便利なものだなと感じました。

サブクエリが使えるようになり、大概の業務要件には答えれるようになると思います。

ただし、そこで満足してSQLの学習が止まっていると、WITH句に出会うことがないのかもしれませんね。