SQL入門
WITH句(共通テーブル式)

SQLの初心者向け入門解説、VBAからデータベースを扱うためのSQLを解説
最終更新日:2019-12-18

WITH句(共通テーブル式)


VBA マクロ SQL WITH

エクセルVBAでデータベースを扱うためのSQL入門です。
前回までに、サブクエリの基本的な使い方やサブクエリをネストした場合のSQLの書き方について説明しました。


今回は、このサブクエリをより分かり易く簡潔に書くことができるWITH句について解説します。
WITH句は共通テーブル式と呼ばれ、同じサブクエリを一つにまとめることができます。

使用するテーブル定義は以下になります。

全テーブル定義とテーブル自動作成VBA
データベースの正規化とマスタの作成で作成した全テーブル定義と、テーブル名変更と列追加(ALTERTABLE)とテーブル自動作成で作成したテーブル自動作成VBAを掲載しておきます。エクセルVBAでデータベースを扱うためのSQL入門です。前回までにテーブルを作成したり、削除したりできるようになりました。

WITH句の構文

WITH 名前 AS (サブクエリ)

(サブクエリ)名前を付けておくことで、その後はこの名前だけでサブクエリを使用することができます。
(サブクエリ)の結果で一時的なテーブルを作成し名前をつけてあるものとイメージして下さい。

複数のサブクエリに対して使う場合は、カンマで区切って指定します。
WITH 名前 AS (サブクエリ) [ , 名前 AS (サブクエリ) [ , 名前 AS (サブクエリ) ] ・・・]

WITH句に続けて、メインのSQLを書きます。
そこでは、WITH句で作成した名前を一つのテーブルとして扱うだけになりますので、
SQLが読みやすく簡潔になります。

WITH句の最も簡単な使用例

サブクエリ(副問合せ)
エクセルVBAでデータベースを扱うためのSQL入門です。前回までで、基本的な部分については一通り説明しました。今回は、より便利にSQLを使えるように副問合せ(サブクエリ)を解説します。サブクエリが使えるようになると、SQLのすごさがより実感として分かってくるはずです。
ここでのサンプルとして、以下のSQLを紹介しています。

サブクエリを使ったSQL
SELECT T.code,T.sales_date,T.item_count,TT.sum_count
  FROM t_sales T
  LEFT JOIN (SELECT code,SUM(item_count) AS sum_count
               FROM t_sales
               GROUP BY code) TT
  ON T.code = TT.code
このサブクエリをWITH句を使って書き直してみます。

WITH句を使ったSQL
WITH TT AS
  (SELECT code,SUM(item_count) AS sum_count
     FROM t_sales
     GROUP BY code) 
SELECT T.code,T.sales_date,T.item_count,TT.sum_count
  FROM t_sales T
  LEFT JOIN TT
  ON T.code = TT.code
変更点は、元のSQLのサブクエリ部分を取り出しWITH句に移動しただけになります。

(サブクエリ) TT

WITH TT AS (サブクエリ)

WITH句に書き直すだけであれば、単純にこのような書き直しが可能です。

WITH句に複数のサブクエリを使用する例

縦に日付、横にコードが並ぶようなマトリックスに集計した表を作成します。

VBA マクロ SQL WITH

※このようにマトリックスにするには
サブクエリを使わずにもっと簡単に書くことができます。
これについては後の章で紹介しています。
あくまで、WITHの説明としてサブクエリを使ったサンプルSQLになります。

以下では、横列として'001','002','003'の3つのコードだけに絞り込んでいます。

サブクエリを使ったSQL
SELECT TDATE.sales_date
,T001.sum_count AS 'code_001'
,T002.sum_count AS 'code_002'
,T003.sum_count AS 'code_003'
  FROM
  -- 日付
  (SELECT sales_date
   FROM t_sales
   GROUP BY sales_date) TDATE
  -- code=001
  LEFT JOIN 
    (SELECT sales_date
     ,SUM(item_count) AS sum_count
     FROM t_sales
     WHERE code = '001'
     GROUP BY sales_date) T001
    ON TDATE.sales_date = T001.sales_date
 -- code=002
  LEFT JOIN 
    (SELECT sales_date
     ,SUM(item_count) AS sum_count
     FROM t_sales
     WHERE code = '002'
     GROUP BY sales_date) T002
    ON TDATE.sales_date = T002.sales_date
 -- code=003
  LEFT JOIN 
    (SELECT sales_date
     ,SUM(item_count) AS sum_count
     FROM t_sales
     WHERE code = '003'
     GROUP BY sales_date) T003
    ON TDATE.sales_date = T003.sales_date
各サブクエリごとにコメントを入れておきました。
まず、日付の一覧を取得するサブクエリを主として、
このサブクエリに、codeが001,002,003ごとのサブクエリをJOINしています。
それぞれのサブクエリをSELECTの選択カラムとすることで横展開しています。

ほぼ同様のサブクエリが3つ使われているのが分かると思います。
このSQLをWITH句を使って書き直します。

WITH句を使ったSQL
WITH  TDATE AS    (SELECT sales_date
     FROM t_sales
     GROUP BY sales_date)
 ,TSUM AS    (SELECT code,sales_date
     ,SUM(item_count) AS sum_count
     FROM t_sales
     WHERE code IN ('001','002','003')
     GROUP BY code,sales_date)
SELECT TDATE.sales_date
  ,T001.sum_count AS code_001
  ,T002.sum_count AS code_002
  ,T003.sum_count AS code_003
  FROM
  -- 日付
  TDATE
  -- code=001
  LEFT JOIN 
    TSUM T001
    ON TDATE.sales_date = T001.sales_date
    AND T001.code = '001'
  -- code=002
  LEFT JOIN 
    TSUM T002
    ON TDATE.sales_date = T002.sales_date
    AND T002.code = '002'
  -- code=003
  LEFT JOIN 
    TSUM T003
    ON TDATE.sales_date = T003.sales_date
    AND T003.code = '003'
WITH句への書き直しは、ほぼ単純な変換だけになっています。
WITH句の中の、
WHERE code IN ('001','002','003')
この条件が無いと、いったん全データを集計することになり処理時間がかかってしまいます。

WITH句を使う事で、複数のサブクエリを一つにまとめて記述することができ読みやすいSQLになります。
ただし、
サブクエリが扱いやすくなったからと言って、そもそもサブクエリを使用する必要があるかは良く考えてみましょう。
サブクエリは付け足し付け足しができるので、出来上がったSQLが継ぎはぎだらけの複雑なものになってしまう事が多々あります。
各テーブル各データの関連性を良く考えてSQLを整理するようにしましょう。

WITH句を使った再帰SQL

WITH句の中でWITH句の結果を使う事で再帰クエリを実現できます。
再帰とは、その記述の中に自信への参照を含めることでループ処理させるものです。
VBAにおける再帰処理は以下を参照してください。
再帰呼出しについて(再帰プロシージャー)
再帰プロシージャとは、自分自身を呼び出すプロシージャです。プロシージャが、そのプロシージャ内で自分自身を呼び出すような処理を再帰呼び出しと呼びます。自分自身を呼び出し、終了条件を満たすまで、意図的に無限ループさせます。
練習問題24(再帰呼出し)
マクロVBA練習問題 ・以下の表の全組み合わせを作成して下さい。サンプルデータでは、組み合わせの数は、8*3*6*5=720通りです。・各項目はカンマ(,)で区切って下さい。・出力先は、新規シートを追加し、1行目に見出しとして"組み合わせ文字"と入れ、2行目より出力して下さい。

再帰クエリは、データベースにより書き方は変わりますが、
WITH [RECURSIVE]
SQLiteも含め多くはRECURSIVE(DBにより省略可能)を指定した書き方をします。

考え方も難しくなりますので、SQL入門では今のところは扱う予定はしていませんが、
今後良いサンプルがあれば紹介することもあるかもしれません。

CASE演算子を使ったマトリックス作成のSQL

上で紹介したマトリックスを作成するSQLは、そもそももっと簡単なSQLで実現することができます。

SELECT sales_date
 ,SUM(CASE code WHEN '001' THEN item_count ELSE 0 END) AS code_001
 ,SUM(CASE code WHEN '002' THEN item_count ELSE 0 END) AS code_002
 ,SUM(CASE code WHEN '003' THEN item_count ELSE 0 END) AS code_003
  FROM t_sales
  WHERE code IN ('001','002','003')
  GROUP BY sales_date
とても簡単なSQLですし、処理時間もサブクエリのSQLより速く処理されます。
CASE演算子については以下を参照してください。
SQL関数と演算子
エクセルVBAでデータベースを扱うためのSQL入門です。前2回でSELECTをやりましたが、その中で登場したCOUNTやSUMはSQL関数です。今回は、SQL関数と演算子について、代表的なものについて使い方を解説します。

先のサブクエリのSQLはWITH句の説明用なので、これは置いとくとしても、
似たような無駄なサブクエリが使われてしまう事は多々あります。
各テーブル各データの関連性を良く考えて、適切なSQLを書くようにしましょう。

WIYH句(共通テーブル式)の最後に

VBAでデータベースを扱うことを目的として、今回まで21回に渡ってSQLを解説してきました。
・SQLite環境作成
・テーブル作成
・INSERT
・SELECT
・WHERE、GROUP、HAVING、ORDER
・JOIN
・UNION
・UPDATE
・DELETE
・インデックス作成
・トランザクション処理
・サブクエリ
・WITH句
データベースを扱う上で通常必要とされる基本的事項については一通り説明できたと思います。
今後は、さらに知っておくと便利な機能やSQLについて、参考になりそうなものがあれば随時追加していきます。



同じテーマ「SQL入門」の記事

データの更新(UPDATE)
データの削除(DELETE)
他のテーブルのデータで追加/更新/削除
インデックスを作成して高速化(CREATE INDEX)
トランザクション処理
VBAクラスの全コード:トランザクション処理
サブクエリ(副問合せ)
サブクエリのネストとSQLコメント&整形
WITH句(共通テーブル式)
取得行数を限定するLIMIT句
分析関数(OVER句,WINDOW句)


新着記事NEW ・・・新着記事一覧を見る

Excel将棋:駒を動かす|VBAサンプル集(8月9日)
Excel将棋:将棋進行クラスの作成|VBAサンプル集(8月8日)
Excel将棋:将棋盤クラスの作成&単体テスト|VBAサンプル集(8月8日)
Excel将棋:位置クラスをデフォルトインスタンスに変更|VBAサンプル集(8月7日)
VBAで数値を漢数字に変換する方法|エクセル雑感(8月6日)
Excel将棋:駒台クラスの作成&単体テスト|VBAサンプル集(8月6日)
Excel将棋:駒クラスの単体テスト|VBAサンプル集(8月5日)
Excel将棋:駒クラスの作成|VBAサンプル集(8月5日)
Excel将棋:クラスの設計|VBAサンプル集(8月4日)
Excel将棋:マクロVBAの学習用|VBAサンプル集(8月3日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.繰り返し処理(For Next)|VBA入門
7.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ