Perform aggregation inside recursive processing using common table expressions

Page updated :

Sample data

Use tables with the following hierarchical data and tables with aggregate data:

Amount hierarchy table

ID (int, PK) Name (nvarchar(50)) Money (bigint) ParentID (int)
1 A 10000 Null
2 B 5000 1
3 C 3000 2
11 K 20000 Null
12 L 1000 11

Point table

Point
Point ID (int, PK) Amount ID (int)(bigint)
1 1 100
2 1 50
3 2 40
4 3 10
5 3 60
6 3 80
7 11 20
8 12 50
9 12 40

How to use common table expressions

The main subject is not written in too detail because it is not here, but a simple summary of the common table expression is that you can define a select expression beforehand, and you can use the select expression that you have defined without creating more than one of the same descriptions in the table reference later. It might be an image that is close to the view.

Here's how to use it.

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

Results

Representation of hierarchical data

I think this pattern is probably the most effective use of common table expressions. The select expression of the data i just introduced can be substituted for other views and table variables, so we don't take advantage of it very much.

You can create a hierarchical representation by calling more common table expressions in a common table expression, as in the following SQL:

The points to be aware of when using it are to combine the select that connects the starting select with the hierarchical data with union all, and to unify the size when combining strings.

-- 共通テーブル式で階層データをつなげる
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]
;

Results

Aggregates in the hierarchical processing of common table expressions (error occurs)

I think that there is a case where it wants to calculate the aggregate value in the hierarchy processing of the common table expression. Here we are trying to calculate the sum of the points tied to the amount 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]
;

However, when you do this, you receive an error similar to the following:

GROUP BY、HAVING、または集計関数は、再帰共通テーブル式 'cte' の再帰部分では許可されません。

Recursive common table expressions do not seem to be able to perform grouping-related processing.

Aggregates in the hierarchical processing of common table expressions (normal behavior)

Recursion common table expressions cannot describe aggregation processing, but you can define the aggregate processing part further as a common table expression.

To write multiple common table expressions, separate common table expressions with commas.

-- 共通テーブル式の階層処理内で集計を使用する (正常)
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]
;

Results