SQL Server 入門SQL で累積を取得する 2
あるカラムの値で区切って累積を取得するには？
前回の 「 SQL で累積を取得する 1 」 ではシンプルなケースの累積の計算方法をご紹介しました。
このページでは、カラムの値で区切って累積を計算するクエリーをご紹介します。
前回のサンプルでは SalesDate と SalesAmount だけでしたが、もう 1 カラム SalesPerson を追加して、SalesPerson ごとに累積を計算してみましょう。
まず以下のクエリーを実行し、テスト用のテーブルを作って、データをインサートします。
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 カラムが増えています。
SalesDate,
SalesAmount
FROM Sales2
ORDER BY SalesPerson,
SalesDate;
以下の結果が得られるはずです。
累積を計算する
前回も書きましたが、オラクルでは、SUM() に PARTITION BY、ORDER BY を指定すると 簡単に累積を取得することができますが、SQL SERVER は SUM() の PARTITION BY の後に ORDER BY を指定することができません。
ですので、クエリーを駆使して累積を取得してみましょう。
前回は、Sales1 テーブル同士を、「 １つ目のテーブルの SalesDate が２つ目の SalesDate より同じか大きい 」 という条件で JOIN しましたが、今回は Sales2 テーブル同士を前回の条件に 「 SalesPerson カラムの値が同じ 」 という条件を追加します。
S1.SalesDate,
S1.SalesAmount AS SalesAmount1,
S2.SalesAmount AS SalesAmount2
FROM Sales2 AS S1
INNER JOIN Sales2 AS S2
ON S1.SalesPerson = S2.SalesPerson
AND S1.SalesDate >= S2.SalesDate
ORDER BY S1.SalesPerson, S1.SalesDate;
上のクエリーを実行すると以下のような結果が得られます。
これを、SalesPerson, SalesDate, SalesAmount1 で GroupBy して、SalesAmount2 の SUM() をとることによって、累積を取得することができます。
実際のクエリーは以下のようになります。
S1.SalesDate,
S1.SalesAmount,
SUM(S2.SalesAmount) AS CumulativeAmount
FROM Sales2 AS S1
INNER JOIN Sales2 AS S2
ON S1.SalesPerson = S2.SalesPerson
AND S1.SalesDate >= S2.SalesDate
GROUP BY S1.SalesPerson, S1.SalesDate, S1.SalesAmount
ORDER BY S1.SalesPerson, S1.SalesDate;
SalesPerson ごとの累積が取得できました。
