SQL で累積を取得する 1

SQL で累積を取得するには？

このページでは累積を取得するクエリーをご紹介します。

まずはシンプルな以下のようなケースです。

まず以下のクエリーを実行し、テスト用のテーブルを作って、データをインサートしておきましょう。

CREATE TABLE Sales1 (
   SalesID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
   SalesDate DATE NULL,
   SalesAmount MONEY NULL
);

INSERT INTO Sales1 (
   SalesDate,
   SalesAmount
)
VALUES
   ('1/1/2011', 100),
   ('1/2/2011', 150),
   ('1/4/2011', 120),
   ('1/5/2011', 80),
   ('1/3/2011', 90),
   ('1/3/2011', 130);

以下のクエリーを実行すると、このページの最初の画像と同様の結果が得られるはずです。

SELECT   SalesDate,
         SalesAmount
FROM     Sales1
ORDER BY SalesDate,
	 SalesID;

累積を計算する

オラクルでは、SUM() に PARTITION BY、ORDER BY を指定すると簡単に累積を取得することができますが、SQL Server 2012 より前のバージョンでは SQL SERVER は SUM() に ORDER BY を指定することができません。

SQL Server 2012 以降でサポートされました。詳しくはこちらをご覧ください。 T-SQL で累積を取得する 3 - SUM() OVER & ROWS UNBOUNDED PRECEDING

ここでは、クエリーを駆使して累積を取得してみましょう。

まず、以下のクエリーを実行して、Sales1 に累計を計算する順番となる SortNo カラムを追加した、テンポラリーテーブルの #TempSales1 を作っておきます。

SELECT	ROW_NUMBER() OVER (ORDER BY SalesDate, SalesID) AS SortNo,
	SalesDate,
	SalesAmount
INTO	#TempSales1
FROM	Sales1;

累計を計算する順番を決めておかないと、同じ SalesDate が出てきた時にどちらを先に計算してよいか判断がつかなくなってしまうためです。

次に、#TempSales1 テーブル同士で JOIN します。その際、「１つ目のテーブルの SortNo が２つ目の SortNo より同じか大きい」という条件で結合します。

SELECT   S1.SortNo,
	 S1.SalesDate,
         S1.SalesAmount AS SalesAmount1,
         S2.SalesAmount AS SalesAmount2
FROM     #TempSales1 AS S1
            INNER JOIN #TempSales1 AS S2
               ON S1.SortNo >= S2.SortNo
ORDER BY S1.SortNo;

上記のクエリーの実行結果は以下のようになります。

こちらを、 SortNo、SalesDate、SalesAmount1 で GROUP BY し、 SalesAmount2 の SUM() を計算すると累積になります。

SELECT   S1.SortNo,
	 S1.SalesDate,
         S1.SalesAmount,
         SUM(S2.SalesAmount) AS CumulativeAmount
FROM     #TempSales1 AS S1
            INNER JOIN #TempSales1 AS S2
               ON S1.SortNo >= S2.SortNo
GROUP BY S1.SortNo, S1.SalesDate, S1.SalesAmount
ORDER BY S1.SortNo;

累積を取得することができましたね。

次は SalesDate のほかにも SalesPerson の情報があり、 SalesPerson ごとの累積をとるケースを説明したいと思います。

>> SQL で累積を取得する 2