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

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

WITH句(共通テーブル式)


VBA マクロ SQL WITH

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


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

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

全テーブル定義とテーブル自動作成VBA
・テーブル:m_customer ・テーブル:m_item ・テーブル:t_sales ・テーブル自動作成

WITH句の構文

WITH 名前 AS (サブクエリ)

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

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

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

WITH句の最も簡単な使用例

サブクエリ(副問合せ)
・サブクエリ(副問合せ)とは ・FROM句でサブクエリを使う ・WHERE句でサブクエリを使う ・SELECTのカラムにサブクエリを使う ・UPDATAEでサブクエリを使う ・DELETEでサブクエリを使う ・サブクエリ(副問合せ)の最後に
ここでのサンプルとして、以下の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における再帰処理は以下を参照してください。
再帰呼出しについて(再帰プロシージャー)
・指定数値の階乗を求める再帰VBA ・再帰プロシージャで考慮すべき事項 ・再帰呼び出しの実践例
練習問題24(再帰呼出し)
・マクロVBA練習問題 ・シンキングタイム ・マクロVBA練習問題回答へ

再帰クエリは、データベースにより書き方は変わりますが、
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関数と演算子
・集計/数値関数 ・文字列関数 ・日付時刻関数 ・その他の関数 ・演算子 ・CASE演算子 ・SQL関数と演算子の最後に

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

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

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



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

データの更新(UPDATE)

・UPDATATEの構文 ・UPDATEの使用例 ・SELECTで取得→シートで編集→UPDATEで更新 ・データの更新(UPDATE)の最後に
データの削除(DELETE)
・DELETEの構文 ・DELETEの使用例 ・SELECTで取得→シートで削除行を指定→DELETEで削除 ・データの削除(DELETE)の最後に
他のテーブルのデータで追加/更新/削除
・新規テーブルを追加 ・他のテーブルを基にデータを追加 ・他のテーブルを基にデータを更新 ・他のテーブルを基にデータを削除 ・他のテーブルを基にデータを追加/更新/削除の最後に
インデックスを作成して高速化(CREATE INDEX)
・インデックスとは ・インデックスを作成 ・UNIQUEインデックスを作成 ・インデックスを削除 ・インデックスを作製の最後に
トランザクション処理
・トランザクション処理とは ・ADOでのトランザクション処理 ・VBAクラスの全コード ・トランザクション処理の使用例 ・トランザクション処理の最後に
VBAクラスの全コード:トランザクション処理
SQL入門の「トランザクション処理」時点のVBAクラスの全コードです。ADOを使ったDB接続のVBAクラスの全コード クラスモジュール:clsSQLite SQLの初心者向け入門解説、VBAからデータベースを扱うためのSQLを解説。
サブクエリ(副問合せ)
・サブクエリ(副問合せ)とは ・FROM句でサブクエリを使う ・WHERE句でサブクエリを使う ・SELECTのカラムにサブクエリを使う ・UPDATAEでサブクエリを使う ・DELETEでサブクエリを使う ・サブクエリ(副問合せ)の最後に
サブクエリのネストとSQLコメント&整形
・サブクエリのネスト ・サブクエリのネストの組み方 ・SQLのコメントについて ・SQLの整形 ・サブクエリのネストとSQLコメント&整形の最後に
WITH句(共通テーブル式)
取得行数を限定するLIMIT句
・LIMIT句の構文 ・LIMIT句の使用例 ・LIMIT句、OFFSET句の最後に
分析関数(OVER句,WINDOW句)
・分析関数とは ・OVER句 ・WINDOW句 ・FILTER句 ・分析関数の使用例 ・分析関数の使用例:グループの最大/最小/平均を明細に出力 ・分析関数の使用例:FILTER句で条件指定 ・分析関数の使用例:明細にレコード番号(連番)を振る ・分析関数の使用例:前回販売日を取得 ・RANK関数,DENSE_RANK関数 ・LAG関数,LEAD関数 ・分析関数の最後に


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

ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




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


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



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