SQL Server 入門 SQL Server 入門

ホーム > 便利なT-SQL&クエリー集 > SQL で累積を取得する 2

SQL で累積を取得する 2

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

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

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

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

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

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

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;

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

クエリーの実行結果1

累積を計算する

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

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

前回は、Sales1 テーブル同士を、「 1つ目のテーブルの SalesDate が2つ目の SalesDate より同じか大きい 」 という条件で JOIN しましたが、今回は Sales2 テーブル同士を前回の条件に 「 SalesPerson カラムの値が同じ 」 という条件を追加します。

SELECT   S1.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;

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

クエリーの実行結果2

これを、SalesPerson, SalesDate, SalesAmount1 で GroupBy して、SalesAmount2 の SUM() をとることによって、累積を取得することができます。

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

SELECT   S1.SalesPerson,
         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 区切りで累積を取得するクエリーの実行結果

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

SQL Server 関連の人気書籍
ホーム > 便利なT-SQL&クエリー集 > SQL で累積を取得する 2