共通テーブル式を使用した再帰処理の内部で集計処理を行う

サンプルデータ

以下の階層データを持つテーブルと集計データを持つテーブルを使用します。

金額階層テーブル

ポイントテーブル

共通テーブル式の使い方

本題はここではないのであまり詳しく書きませんが、簡単にまとめると共通テーブル式とは事前に select 式を定義しておき、のちのテーブル参照で同じ記述を複数作成せずに定義しておいた select 式を使うことができるようなものになります。ビューに近いイメージかもしれません。

使い方は次のようになります。

-- 共通テーブル式で事前に select 内容を定義する
with [cte] as
(
  select *
  from [金額階層テーブル]
  where [Money] >= 5000
)
select * from [cte]
union all
select * from [cte]
;

結果

階層データの表現

共通テーブル式が有効に使われるのはおそらくこのパターンだと思います。先ほど紹介したデータの select 式はビューやテーブル変数など他でも代用できるのであまりメリットは活かせていません。

次の SQL のように共通テーブル式の中でさらに共通テーブル式を呼ぶことによって階層的な表現ができるようになります。

使う際に気を付ける点としては「起点となる select と階層データを結びつける select を union all で結合すること」「文字列を結合する場合はサイズを統一すること」です。

-- 共通テーブル式で階層データをつなげる
with [cte] as
(
  select
    [ID]
   ,[Name]
   ,[ParentID]
   ,[Money] as [金額合計]
   ,1 as [レベル]
   ,cast([Name] as nvarchar(4000)) as [階層]
  from [金額階層テーブル]
  where [ParentID] is null

  union all

  select
    [子].[ID]
   ,[子].[Name]
   ,[子].[ParentID]
   ,[親].[金額合計] + [子].[Money] as [金額合計]
   ,[親].[レベル] + 1 as [レベル]
   ,[親].[階層] + N'⇒' + [子].[Name] as [階層]
  from [金額階層テーブル] [子]
  inner join [cte] [親]
    on [子].[ParentID] = [親].[ID]
)
select *
from [cte]
order by [ID]
;

結果

共通テーブル式の階層処理内の集計 (エラー発生)

共通テーブル式の階層処理の中で集計値を算出したい場合があると思います。ここでは金額IDに紐づくポイントの合計を算出しようとしています。

-- 共通テーブル式の階層処理内で集計を使用する (エラー)
with [cte] as
(
  select
    [ID]
   ,[Name]
   ,[ParentID]
   ,[Money] as [金額合計]
   ,(
      select sum([ポイント])
      from [ポイントテーブル]
      where [金額ID] = [ID]
    ) as [ポイント合計]
   ,1 as [レベル]
   ,cast([Name] as nvarchar(4000)) as [階層]
  from [金額階層テーブル]
  where [ParentID] is null

  union all

  select
    [子].[ID]
   ,[子].[Name]
   ,[子].[ParentID]
   ,[親].[金額合計] + [子].[Money] as [金額合計]
   ,[親].[ポイント合計] + (
                            select sum([ポイント])
                            from [ポイントテーブル]
                            where [金額ID] = [子].[ID]
                          ) as [ポイント合計]
   ,[親].[レベル] + 1 as [レベル]
   ,[親].[階層] + N'⇒' + [子].[Name] as [階層]
  from [金額階層テーブル] [子]
  inner join [cte] [親]
    on [子].[ParentID] = [親].[ID]
)
select *
from [cte]
order by [ID]
;

しかし、実行すると次のようなエラーが表示されます。

再帰共通テーブル式ではグループ化関連の処理をいれることはできないようです。

共通テーブル式の階層処理内の集計 (正常動作)

再帰共通テーブル式の中では集計処理を記述できないのですが、集計処理の部分をさらに共通テーブル式として定義すると実行することができます。

複数の共通テーブル式を記述するには共通テーブル式をカンマで区切ります。

-- 共通テーブル式の階層処理内で集計を使用する (正常)
with [ポイント合計CTE] as
(
  select
    [金額ID]
   ,sum([ポイント]) as [ポイント合計]
  from [ポイントテーブル]
  group by [金額ID]
)
,[cte] as
(
  select
    [ID]
   ,[Name]
   ,[ParentID]
   ,[Money] as [金額合計]
   ,(
      select [ポイント合計]
      from [ポイント合計CTE]
      where [金額ID] = [ID]
    ) as [ポイント合計]
   ,1 as [レベル]
   ,cast([Name] as nvarchar(4000)) as [階層]
  from [金額階層テーブル]
  where [ParentID] is null

  union all

  select
    [子].[ID]
   ,[子].[Name]
   ,[子].[ParentID]
   ,[親].[金額合計] + [子].[Money] as [金額合計]
   ,[親].[ポイント合計] + (
                            select [ポイント合計]
                            from [ポイント合計CTE]
                            where [金額ID] = [子].[ID]
                          ) as [ポイント合計]
   ,[親].[レベル] + 1 as [レベル]
   ,[親].[階層] + N'⇒' + [子].[Name] as [階層]
  from [金額階層テーブル] [子]
  inner join [cte] [親]
    on [子].[ParentID] = [親].[ID]
)
select *
from [cte]
order by [ID]
;

結果