SQL WITH句でVIEWを作成し重複するSQL文をまとめる
WITH句を使えば１つの副問い合わせ(SQL)を複数の箇所で使いまわすことができます。
例としてSQL 副問い合わせの基本を理解するで扱ったFROM句の副問い合わせのSQLをおさらいします。
SELECT MAX(T1.社員名) AS 社員名
,SUM(T2.売上金額) AS 確定売上金額
,SUM(T3.売上金額) AS 未確定売上金額
FROM 社員マスタ AS T1
LEFT JOIN
(
SELECT *
FROM 売上明細 S1
WHERE 状態区分 = 0
) T2
ON T1.社員コード = T2.社員コード
LEFT JOIN
(
SELECT *
FROM 売上明細 S1
WHERE 状態区分 = 1
) T3
ON T1.社員コード = T3.社員コード
GROUP BY T1.社員コード※今回は学習のために上記のようなわかりやすいSQLを例にしましたが、SQL CASE文で効率よく集計するテクニックでCASE文を使った効率のよいSQLを紹介しています。
もしこのSQLで「2015年5月の売上のみ集計したい」という場合、副問い合わせを使えば、下記のSQLのようになります。
SELECT MAX(T1.社員名) AS 社員名
,SUM(T2.売上金額) AS 確定売上金額
,SUM(T3.売上金額) AS 未確定売上金額
FROM 社員マスタ AS T1
LEFT JOIN
(
SELECT *
FROM 売上明細 S1
WHERE 状態区分 = 0
AND S1.売上年月 BETWEEN '20150501' AND '20150531'
) T2
ON T1.社員コード = T2.社員コード
LEFT JOIN
(
SELECT *
FROM 売上明細 S1
WHERE 状態区分 = 1
AND S1.売上年月 BETWEEN '20150501' AND '20150531'
) T3
ON T1.社員コード = T3.社員コード
GROUP BY T1.社員コードこの方法では、T2,T3を取得する副問い合わせのSQLでそれぞれ売上明細テーブルを同じように集計しています。
そのため、売上明細テーブルに大量のデータがあった場合、レスポンスがどんどん遅くなってしまいます。
WITH句で同じような集計テーブルは最初に指定してしまいます。
WITH句を指定したSQLは下記のようになります。
WITH 売上明細_WITH AS(
SELECT *
FROM 売上明細 S1
WHERE S1.売上年月 BETWEEN '20150501' AND '20150531'
)
SELECT MAX(T1.社員名) AS 社員名
,SUM(T2.売上金額) AS 確定売上金額
,SUM(T3.売上金額) AS 未確定売上金額
FROM 社員マスタ AS T1
LEFT JOIN
(
SELECT *
FROM 売上明細_WITH S1 --WITH句で定義したテーブルを参照
WHERE 状態区分 = 0
) T2
ON T1.社員コード = T2.社員コード
LEFT JOIN
(
SELECT *
FROM 売上明細_WITH S1 --WITH句で定義したテーブルを参照
WHERE 状態区分 = 1
) T3
ON T1.社員コード = T3.社員コード
GROUP BY T1.社員コードこの方法なら売上年月='201505'で絞り込んだ売上明細テーブルをT2,T3を取得する副問い合わせのSQLで使用しているため、データの件数や内容にもよりますが、レスポンスはだいぶ改善されるはずです。
上記例では売上年月のみの条件でしたが、複雑な条件になってくると、同じ条件の副問い合わせを何箇所も実装するのは大変なので、副問い合わせで同じような集計テーブルがあった場合はWITH句で最初に指定してしまうと、実装がだいぶ楽になります。