SQL で累積を取得する 2

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

前回の 「 SQL で累積を取得する 1 」 ではシンプルなケースの累積の計算方法をご紹介しました。

このページでは、カラムの値で区切って累積を計算するクエリーをご紹介します。

前回のサンプルでは SalesDate と SalesAmount だけでしたが、もう 1 カラム SalesPerson を追加して、SalesPerson ごとに累積を計算してみましょう。

カラム区切りで累積を取得する 5


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

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);

前回は SalesDate カラムと SalesAmount カラムだけでしたが、今回は SalesPerson カラムが増えています。 

SELECT	SalesPerson, 
	SalesDate,
	SalesAmount
FROM	Sales2
ORDER BY SalesPerson, 
	 SalesDate, 
	 SalesID;

以下の結果が得られるはずです。

カラム区切りで累積を取得する 2

累積を計算する

前回も書きましたが、オラクルでは、SUM() に PARTITION BY、ORDER BY を指定すると 簡単に累積を取得することができますが、SQL SERVER 2012 以前では SUM() の PARTITION BY の後に ORDER BY を指定することができません。

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

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

まず、以下のクエリーを実行して、Sales2 に累計を計算する際のグループとなる　GroupNo と、グループ内で累計を計算する順番となる GroupSortNo カラムを追加した、テンポラリーテーブルの #TempSales2 を作っておきます。 

SELECT	DENSE_RANK() OVER (ORDER BY SalesPerson) AS GroupNo,
	ROW_NUMBER() OVER (PARTITION BY SalesPerson ORDER BY SalesDate, SalesID) AS GroupSortNo,
	SalesPerson, 
	SalesDate,
	SalesAmount
INTO	#TempSales2
FROM	Sales2;

カラム区切りで累積を取得する 3

DENSE_RANK を使って GroupNo を取得しています。 GroupNo は今回はグルーピングに使用したいカラムひとつなので、つけなくても良いのですが、複数のカラムでグルーピングしたい時は必要なのでつけておきます。

前回と同様に累計を計算する順番を決めておかないと、同じ SalesDate が出てきた時にどちらを先に計算してよいか判断がつかなくなってしまうので、グループ内のソート番号を振っておきます。


前回は、#TempSales1 テーブル同士を、「 １つ目のテーブルの SortNo が２つ目の SortNo より同じか大きい 」 という条件で JOIN しましたが、今回は #TempSales2 テーブル同士を 「 GroupNo の値が同じで、１つ目のテーブルの GroupSortNo が２つ目の GroupSortNo より同じか大きい 」 という条件で　JOIN します。 

SELECT   S1.GroupNo,
	 S1.SalesPerson,
	 S1.GroupSortNo,
         S1.SalesDate,
         S1.SalesAmount AS SalesAmount1,
         S2.SalesAmount AS SalesAmount2
FROM     #TempSales2 AS S1
             INNER JOIN #TempSales2 AS S2
                ON S1.GroupNo = S2.GroupNo
                   AND S1.GroupSortNo >= S2.GroupSortNo
ORDER BY S1.GroupNo, S1.GroupSortNo;

上のクエリーを実行すると以下のような結果が得られます。

カラム区切りで累積を取得する 4

これを、GroupNo, SalesPerson, GroupSortNo, SalesDate, SalesAmount1 で GROUP BY して、SalesAmount2 の SUM() をとることによって、グループごとの累積を取得することができます。

※ SalesPerson と SalesDate は SELECT で表示したいので GROUP BY に追加しています。

実際のクエリーは以下のようになります。 

SELECT   S1.GroupNo,
	 S1.SalesPerson,
         S1.GroupSortNo,
	 S1.SalesDate,
         S1.SalesAmount,
         SUM(S2.SalesAmount) AS CumulativeAmount
FROM     #TempSales2 AS S1
             INNER JOIN #TempSales2 AS S2
                ON S1.GroupNo = S2.GroupNo
                   AND S1.GroupSortNo >= S2.GroupSortNo
GROUP BY S1.GroupNo, S1.SalesPerson, S1.GroupSortNo, S1.SalesDate, S1.SalesAmount
ORDER BY S1.GroupNo, S1.GroupSortNo;

カラム区切りで累積を取得する 5

SalesPerson ごとの累積が取得できましたね！


次は SQL Server 2012 以降で使える SUM() OVER の ROWS UNBOUNDED PRECEDING を使った、簡単な累積の取得方法をご紹介したいと思います。

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

© 2010-2024 SQL Server 入門