공통 테이블 표현식을 사용하여 재귀 처리 내에서 집계 처리 수행

샘플 데이터

다음과 같은 계층적 데이터가 있는 테이블과 요약된 데이터가 있는 테이블을 사용합니다.

금액 계층 테이블

포인트 테이블

공통 테이블 표현식을 사용하는 방법

주요 주제가 여기에 없기 때문에 너무 자세하게 작성하지는 않겠지 만 간단히 요약하면 공통 테이블 표현식은 미리 정의 한 select 표현식과 같으며 이후 테이블 참조에서 동일한 설명을 여러 개 만들지 않고 정의 한 select 표현식을 사용할 수 있습니다. 뷰에 가까운 이미지일 수 있습니다.

사용 방법은 다음과 같습니다.

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

결과

계층적 데이터의 표현

아마도 공통 테이블 표현식을 매우 유용하게 만드는 패턴이라고 생각합니다. 이전에 도입 된 데이터의 select 표현식은 뷰 및 테이블 변수와 같은 다른 것들로 대체 될 수 있으므로 많이 활용하지 않습니다.

계층적 표현은 다음 SQL에서와 같이 공통 테이블 표현식에서 더 일반적인 테이블 표현식을 호출하여 수행할 수 있습니다.

그것을 사용할 때주의해야 할 점은 "시작점 인 선택과 계층 적 데이터를 모두 결합시키는 선택을 결합하는 것"과 "문자열을 결합 할 때 크기를 통일하는 것"입니다.

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

결과