T-SQL で累積を取得する 3 - SUM() OVER & ROWS UNBOUNDED PRECEDING

あるカラムの値で区切って累積を取得するには？

前回の 「 SQL で累積を取得する 2 」 では、SQL でカラムの値で区切って累積を計算する方法をご紹介しました。

今回は、SQL Server 2012 以降で使えるウインドウフレームを使って累積を計算してみましょう。


データは前回と全く同じものを使います。

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

CREATE TABLE Sales2 (
   SalesID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
   SalesDate DATE NULL,
   SalesPerson VARCHAR(50) NULL,
   SalesAmount MONEY NULL
);

INSERT INTO Sales2 (
   SalesDate,
   SalesPerson,
   SalesAmount
)
VALUES
   ('1/1/2011', 'Yamada', 100),
   ('1/2/2011', 'Yamada', 150),
   ('1/2/2011', 'Suzuki', 120),
   ('1/3/2011', 'Suzuki', 200),
   ('1/5/2011', 'Yamada', 80),
   ('1/4/2011', 'Suzuki', 90),
   ('1/4/2011', 'Tanaka', 110),
   ('1/5/2011', 'Yamada', 50),
   ('1/6/2011', 'Suzuki', 90),
   ('1/6/2011', 'Tanaka', 40);

T-SQL で累積を取得する 3-1

SUM() OVER で ROWS UNBOUNDED PRECEDING を使って累積を計算する

SQL Server 2012 以降では、SUM() OVER で、 PARTITION BY、ORDER BY、 ROWS UNBOUNDED PRECEDING を指定することによって、簡単に累積を取得することができます。

SalesPerson でグループして、SalesDate 順に累積を計算するクエリーは次の通りです。 

SELECT	SalesPerson,
	SalesDate,
	SalesAmount,
	SUM(SalesAmount) OVER (PARTITION BY SalesPerson
			       ORDER BY	    SalesDate,
					    SalesID
			       ROWS UNBOUNDED PRECEDING) AS CumulativeAmount
FROM	Sales2
ORDER BY SalesPerson, SalesDate, SalesID;

T-SQL で累積を取得する 3-2

SUM(SalesAmount) OVER に続けて、SalesPerson ごとにグルーピングしたいので、PARTITION BY SalesPerson を指定し、SalesDate, SalesID 順に計算したいので ORDER BY SalesDate, SalesID を指定しています。

ここまでで、ROWS UNBOUNDED PRECEDING はなくても同様の結果が得られるのですが、指定しなかった場合、RANGE UNBOUNDED PRECEDING AND CURRENT ROW がデフォルトとして使われ、RANGE のほうがパフォーマンスが良くないので、RANGE である必要がない限り ROWS を指定したほうが良いです。

ROWS UNBOUNDED PRECEDING は ROWS UNBOUNDED PRECEDING AND CURRENT ROW の略で、UNBOUNDED PRECEDING は 「 パティションの最初から 」 という意味で、ウィンドウの開始位置がパティションの開始位置から、現在の行までという意味になります。


いとも簡単に、前回の 「 SQL で累積を取得する 2 」 と全く同様の結果が得られていますね。

パフォーマンス的にもウインドウファンクションを使って累積を取得するほうが良いので、使える時はこちらを使ってくださいね！

© 2010-2024 SQL Server 入門