SQL Server パフォーマンスチューニング、コンサルティング、アドバイス、相談、定期診断、トレーニング

ホーム＞技術情報＞ SQL Server 2012 自習書 DWH 関連の新機能

SQL Server 2012 自習書シリーズ（HTML 版）
新機能編 No.3「DWH（データウェアハウス）関連の新機能」

松本美穂と松本崇博が執筆した SQL Server 2012 自習書シリーズの「新機能編 No.3 DWH 関連の新機能」の HTML 版です。日本マイクロソフトさんの Web サイトで Word または PDF 形式でダウンロードできますが、今回、HTML 版として公開する許可をいただきましたので、ここに掲載いたします。[2014年12月26日]

目次へ | 前のページへ | 次のページへ

3.2　前年同月や累積金額の取得

◆ 前年同月や累積金額の取得

次に、分析関数の実用的な利用方法として、前年同月や累積金額を取得する方法を説明します。ここでは、サンプルスクリプトに含まれている NorthwindJ データベースを利用します（このデータベースの作成手順については、付録へ記載しています）。

◆ Let's Try

それでは、これを試してみましょう。

1．まずは、結果を取得しやすくするために、受注年月ごとの受注金額を取得するビューを次のように作成しておきます。

USE NorthwindJ
go
CREATE VIEW 年月毎売上
AS
SELECT    YEAR(受注日) AS 年, MONTH(受注日) AS 月
    , SUM( od.数量 * od.単価 ) AS 受注金額
FROM 受注明細 od
    INNER JOIN 受注 o ON o.受注コード = od.受注コード
GROUP BY YEAR(受注日), MONTH(受注日)
go

2．作成後、このビュー（年月毎売上）に対して SELECT ステートメントを実行して、結果を確認します。

SELECT * FROM 年月毎売上
ORDER BY 年, 月

00067

◆ 前年同月値の取得（LAG 関数）

次に、前年同月の値を取得してみましょう。

1．前年同月の値は、LAG 関数を利用して、次のように簡単に取得することができます。

SELECT *, LAG(受注金額, 12) OVER(ORDER BY 年, 月) AS 前年同月
FROM 年月毎売上
ORDER BY 年, 月

00068

LAG 関数では ORDER BY 句で「年, 月」で並べ替えて、第2引数に「12」を指定しているので、12ヶ月前（1年前）の売上金額（前年同月値）を取得することができています。

◆ 累積金額の取得（OVER .. ROWS ..）

次に、累積金額を取得してみましょう。

1．累積金額は、ウィンドウ操作を行うことで取得することができます。次のように OVER 句で ROWS BETWEEN .. AND .. を指定して実行してみます。

SELECT *, SUM(受注金額) OVER(
             PARTITION BY 年 ORDER BY 年, 月
             ROWS BETWEEN UNBOUNDED PRECEDING
                  AND CURRENT ROW ) AS 累積金額
  FROM 年月毎売上
   ORDER BY 年, 月

00069

OVER 句での ROWS または RANGE で、BETWEEN .. AND を利用したウィンドウ操作は、SUM や AVG、MAX などの集計関数でも利用することができます。UNBOUNDED PRECEDING を指定することで、そこからウィンドウを開始することができ、CURRENT ROW で現在の行まで（処理を行っている行まで）と指定できるので、SUM 関数によって、累積値を取得することができています。