[SQL Server]再帰SQLで集計する

WITH(共通テーブル式)を使って集計する

WITH句とは

共通テーブル式 (CTE) は、単一の SELECT、INSERT、UPDATE、DELETE、CREATE VIEW の各ステートメントの実行スコープ内で定義される一時結果セットと考えることができます。CTE は、オブジェクトとして格納されず、クエリが実行されている間しか保持されない点で、派生テーブルに似ています。派生テーブルと異なるのは、CTE では自己参照が可能であり、同じクエリ内で複数回参照が可能なことです。

イメージとしては、サブクエリに名前を付け一時テーブルとして扱い、クエリ内から参照可能としたもの。

SQL　Server　2016の教科書 基礎から実践まで学べる [ 松本美穂 ]

created by Rinker

• Amazon
• 楽天市場
• Yahooショッピング

使い方

下記のような売上データがあるとします。

日別の商品売上金額を集計するには下記のクエリで集計できます。

SELECT 日付, SUM(売上金額) AS 計
FROM 売上データ
GROUP BY 日付

上記の場合は、売上レコードが存在する日のみ集計されますが、 売上が存在しない日も含めて集計したい場合には、WITH句を使って集計します。

最初に一時テーブルとして対象範囲のカレンダーを作成し、 一時テーブルに対して集計データを結合する下記のクエリで集計できます。

DECLARE @DateFrom DATE = '2014-10-01';
DECLARE @DateTo DATE = '2014-10-05';

WITH DateTable (MyDate)
AS
(
     SELECT @DateFrom
     UNION ALL
     SELECT DATEADD(dd, 1, MyDate)
     FROM DateTable
     WHERE MyDate < @DateTo
)

SELECT DT.MyDate AS 日付, ISNULL(T.計, 0) AS 計
FROM DateTable DT
LEFT JOIN
(
    SELECT 日付, SUM(売上金額) AS 計
    FROM 売上データ
    GROUP BY 日付
) T
ON DT.MyDate = T.日付
OPTION (MAXRECURSION 0);