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.1　DWH／BIの T-SQL 分析関数（Analytic Functions）のサポート

◆ DWH／BI の T-SQL 分析関数（Analytic Functions）のサポート

SQL Server 2012 では、DWH（データウェアハウス）および BI（Business Intelligence）環境で役立つ Transact-SQL の分析関数（Analytic Functions）が多数サポートされるようになりました。その主なものは、次のとおりです。

00050

これらの分析関数では、ウィンドウ操作を行うことができるので、累積値や比率、前年同月値、移動累計、移動平均などといったデータ分析でよく利用する計算値を簡単に導き出せるようになります。これらの分析関数の基本構文は、次のとおりです。

関数名(引数1, 引数2, …)
   OVER(
    [PARTITION BY 列1, 列2, …]  ORDER BY 列1, 列2, …)
   [ROWS | RANGE BETWEEN .. AND ..]

ROW_NUMBER や RANK 関数などを利用する場合と同様、OVER 句で PARTITION BY や ORDER BY を利用して、パーティション化（グループ化）や並べ替え列の指定を行います。ROWS または RANGE では、BETWEEN .. AND .. で指定した範囲のウィンドウを作成することができます。

◆ Let's Try

それでは、分析関数を試してみましょう。

1．まずは、分析関数を試すためのデータベースとテーブルを次のように作成します。

-- データベース「afTestDB」の作成
CREATE DATABASE afTestDB
go
-- テーブル「afTest」の作成
CREATE TABLE afTest
( a int PRIMARY KEY
,b int
,c int )
-- データを 7件追加
INSERT INTO afTest VALUES(1, 111, 1)
INSERT INTO afTest VALUES(2, 222, 1)
INSERT INTO afTest VALUES(3, 333, 2)
INSERT INTO afTest VALUES(4, 444, 2)
INSERT INTO afTest VALUES(5, 555, 1)
INSERT INTO afTest VALUES(6, 111, 3)
INSERT INTO afTest VALUES(7, 222, 3)
-- データの確認
SELECT * FROM afTest

00051

データベースを「afTestDB」という名前で作成して、その中に「afTest」テーブルを作成し、データを 7件追加しています。

◆ LAG 関数（前の行を取得）

まずは、LAG 関数を試してみましょう。この関数は、前の行を取得することができます。

1．次のように LAG 関数の引数へ b 列を与えて、OVER 句の ORDER BY 句で a 列を指定して実行してみましょう。

SELECT a, b, LAG(b) OVER(ORDER BY a)
FROM afTest

00052

これにより、a 列で並べ替えた結果に対して、b 列の 1つ前の値を取得できていることを確認できます。

2．次に、LAG 関数の第2引数に「2」を指定して、実行してみましょう。

SELECT a, b, LAG(b, 2) OVER(ORDER BY a)
FROM afTest

00053

今度は、b 列の 2つ前の値を取得できていることを確認できます。このように、LAG 関数では、指定した値より前の値を取得することができます。

3．次に、c 列でパーティション化（グループ化）して、前の行を取得してみましょう。

SELECT a, c, b, LAG(b) OVER(PARTITION BY c ORDER BY a)
FROM afTest

00054

今後は、c 列でグループ化されて、その中で b 列の 1つ前の値を取得できていることを確認できます。このように、LAG 関数の OVER 句では、PATITION BY 句を利用して、パーティション化（グループ化）を行うことができます。

◆ LEAD 関数（後の行を取得）

次に、LEAD 関数を試してみましょう。この関数は、LAG とは逆に、後ろの行を取得することができます。

1．まずは、LEAD 関数の引数へ b 列を与えて、ORDER BY 句で a 列を指定して実行してみましょう。

SELECT a, b, LEAD(b) OVER(ORDER BY a)
FROM afTest

00055

a 列で並べ替えた結果に対して、b 列の 1つ後ろの値を取得できていることを確認できます。

2．次に、LEAD 関数の第2引数に「2」を指定して、実行してみましょう。

SELECT a, b, LEAD(b, 2) OVER(ORDER BY a)
FROM afTest

00056

今度は、b 列の 2つ後ろの値を取得できていることを確認できます。

3．次に、c 列でパーティション化して、後の行を取得してみましょう。

SELECT a, c, b, LEAD(b) OVER(PARTITION BY c ORDER BY a)
FROM afTest

00057

今後は、c 列でグループ化されて、その中で b 列の 1つ後ろの値を取得できていることを確認できます。

◆ FIRST_VALUE（最初の値を取得）

次に、FIRST_VALUE 関数を試してみましょう。この関数は、その名のとおり、最初の値を取得することができます。

1．まずは、FIRST_VALUE 関数の引数へ b 列を与えて実行してみましょう。

SELECT a, b, FIRST_VALUE(b) OVER(ORDER BY a)
FROM afTest

00058

a 列で並べ替えた結果に対して、b 列の最初の値を取得できていることを確認できます。

2．次に、c 列でパーティション化して、最初の行を取得してみましょう。

SELECT a, c, b, FIRST_VALUE(b) OVER(PARTITION BY c ORDER BY a)
FROM afTest

00059

今後は、c 列でグループ化されて、その中で b 列の最初の値を取得できていることを確認できます。

◆ LAST_VALUE（最後の値を取得。ウィンドウ操作）

次に、LAST_VALUE 関数を試してみましょう。この関数も、その名のとおり、最後の値を取得することができます。

1．まずは、LAST_VALUE 関数の引数へ b 列を与えて実行してみましょう。

SELECT a, b, LAST_VALUE(b) OVER(ORDER BY a)
FROM afTest

00060

しかし、結果は全体としての最後の値ではなく、b列と同じ値が取得されてしまっていることを確認できます。これは、現在の行が最後の行と見なされてしまっているために発生しています。これを解決するには、ウィンドウ操作をする必要があります。

2．LAST_VALUE 関数は、次のように OVER 句でウィンドウ操作を利用して、実行する必要があります。

SELECT a, b
      , LAST_VALUE(b)
         OVER(ORDER BY a
            ROWS BETWEEN UNBOUNDED PRECEDING
                 AND UNBOUNDED FOLLOWING )
FROM afTest

00061

OVER 句では、ROWS または RANGE で、BETWEEN .. AND を利用して、ウィンドウを作ることができ、UNBOUNDED PRECEDING でウィンドウの開始点、UNBOUNDED FOLLOWING でウィンドウの終了点を最後の行にすることができます。これで、全体として最後の値を取得することができます。

3．次に、c 列でパーティション化して、最後の行を取得してみましょう。

SELECT a, c, b
      , LAST_VALUE(b)
         OVER(PARTITION BY c ORDER BY a
            ROWS BETWEEN UNBOUNDED PRECEDING
                 AND UNBOUNDED FOLLOWING )
FROM afTest

00062

PARTITION BY でパーティション化を行うことで、UNBOUNDED PRECEDING でパーティションの最初の行をウィンドウの開始点、UNBOUNDED FOLLOWING でパーティションの最後の行をウィンドウの終了点にすることができます。

◆ PERCENT_RANK（ランキング）

次に、PERCENT_RANK 関数を試してみましょう。この関数は、RANK 関数のパーセント表示版で、指定した列のランキングを取得することができます。

1．まずは、ORDER BY 句に a列を指定して実行してみましょう。

SELECT a, b
, RANK() OVER(ORDER BY a)
, PERCENT_RANK() OVER(ORDER BY a)
FROM afTest

00063

RANK 関数では、1、2、3位とランキングが表示されるのに対して、PERCENT_RANK 関数では、0～1 の間のパーセンテージ（1位が0%、0.166は16.6%、最下位は1で100%）でランキングが表示されていることを確認できます。

2．次に、ORDER BY 句に b 列を指定して、PERCENT_RANK を実行してみましょう。

SELECT a, b
, RANK() OVER(ORDER BY b)
, PERCENT_RANK() OVER(ORDER BY b)
FROM afTest

00064

RANK 関数と同様、同じ値がある場合には飛び番が発生していること（RANK 関数では 1位が2つあるので、2位がなくて 3位となる、PERCENT_RANK 関数では 0% が 1位なので、これが 2つ、3位が 33.3%となっていること）を確認できます。

◆ CUME_DIST（累積分布値）

1．次に、COME_DIST 関数を試してみましょう。これは累積分布値を取得できる関数です。

SELECT a, b
    , RANK() OVER(ORDER BY a)
    , PERCENT_RANK() OVER(ORDER BY a)
    , CUME_DIST() OVER(ORDER BY a)
FROM afTest

00065

0～1 の間のパーセンテージで累積分布値（最大値が 1 で、0.143（14.3%））ずつ累積された値）を取得できていることを確認できます。

◆ 中央値の取得（PERCENTILE_CONT、PERCENTILE_DISC）

次に、PERCENTILE_CONT と PERCENTILE_DISC 関数を試してみましょう。この 2つは、中央値（メジアン）を取得する場合に便利な関数です。

1．中央値を取得するには、次のように引数に 0.5（50%）を指定して実行します。

SELECT a, c, b
, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY b) OVER(PARTITION BY c)
, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY b) OVER(PARTITION BY c)
FROM afTest

00066

PERCENTILE_CONT と PERCENTILE_DISC 関数では、WITHIN GROUP で ORDER BY 句を指定する必要があり、ここでは b 列で並べ替えるように指定しています。OVER 句では、c 列でパーティション化しているので、c列でグループ化されて、その中の中央値を取得しています。c列が 1のグループは、b列が 111、222、555 なので中央値が 222、c列が 2のグループは、b列が 333、444 なので中央値が 388.5、PERCENTILE_DISC では、最小値を取るので 333 となっていることを確認できます。