SQL Server のことなら SQL Quality 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
int PRIMARY KEY
 ,int
 ,int )
-- データ 7追加
INSERT INTO afTest VALUES(11111)
INSERT INTO afTest VALUES(22221)
INSERT INTO afTest VALUES(33332)
INSERT INTO afTest VALUES(44442)
INSERT INTO afTest VALUES(55551)
INSERT INTO afTest VALUES(61113)
INSERT INTO afTest VALUES(72223)
-- データ確認
SELECT FROM afTest

00051

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

◆ LAG 関数(前の行を取得)

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

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

SELECT abLAG(bOVER(ORDER BY a)
FROM afTest

00052

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

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

SELECT abLAG(b2OVER(ORDER BY a)
FROM afTest

00053

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

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

SELECT acbLAG(bOVER(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 abLEAD(bOVER(ORDER BY a)
FROM afTest

00055

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

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

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

00056

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

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

SELECT acbLEAD(bOVER(PARTITION BY c ORDER BY a)
FROM afTest

00057

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

◆ FIRST_VALUE(最初の値を取得)

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

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

SELECT abFIRST_VALUE(bOVER(ORDER BY a)    
FROM afTest

00058

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

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

SELECT acbFIRST_VALUE(bOVER(PARTITION BY c ORDER BY a)    
FROM afTest

00059

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

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

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

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

SELECT abLAST_VALUE(bOVER(ORDER BY a)
FROM afTest

00060

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

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

SELECT ab
      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 acb
      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 ab
    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 ab
    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 ab
    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 acb
    PERCENTILE_CONT(0.5WITHIN GROUP (ORDER BY bOVER(PARTITION BY c)
    PERCENTILE_DISC(0.5WITHIN GROUP (ORDER BY bOVER(PARTITION BY c)
FROM afTest

00066

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

以上が分析関数の基本的な利用方法になります。

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

事例1

MPNロゴ


SQLQualityは執筆とセミナーを通じて技術の啓蒙やエンジニアの育成支援も行っています
最新刊
SQL Server 2012 の教科書
SQL Server 2012 の教科書(ソシム)

弊社オリジナル制作の
SQL Server 2012 自習書も
マイクロソフトのサイトで公開中!
ロングセラー
ASP.NET でいってみよう  SQL Server 2000 でいってみよう
ASP.NET でいってみよう
第7刷 16,500 部発行
SQL Server 2000 でいってみよう
第12刷 28,500 部発行


セミナー風景
セミナー風景

弊社執筆の
SQL Server 2012 自習書
マイクロソフトのサイトで公開中
全30冊
ダウンロードはこちら
弊社執筆の
SQL Server 2008 R2 自習書
マイクロソフトのサイトで公開中
全30冊
目次はこちら
松本美穂のコラム
(公開活動などのお知らせ)

第38回: SQL Server 2014 CTP2 の公開
第37回: SQL Server 2014 CTP1 の自習書をご覧ください
第36回: SQL Server 2014 CTP1 のクラスター化列ストア インデックスを試す
第35回: SQL Server 2014 CTP1 のインメモリ OLTP の基本操作を試す
第34回: GeoFlow for Excel 2013 のプレビュー版を試す
第33回:iPad と iPhone からの SQL Server 2012 Reporting Servicesのレポート閲覧
第32回:PASS Summit 2012 参加レポート
第31回:SQL Server 2012 Reporting Services 自習書のお知らせ
第30回:SQL Server 2012(RTM 版)の新機能 自習書をご覧ください
第29回:書籍「SQL Server 2012の教科書 開発編」のお知らせ
第26回:SQL Server 2012 の Power View 機能のご紹介
第25回:SQL Server 2012 の Data Quality Services
第24回:SQL Server 2012 自習書のご案内と初セミナー報告
第23回:Denali CTP1 が公開されました
第22回 チューニングに王道あらず
第21回 Microsoft TechEd 2010 終了しました
第20回 Microsoft TechEd Japan 2010 今年も登壇します
第19回 SQL Server 2008 R2 RTM の 日本語版が公開されました
第18回 「SQL Azure 入門」自習書のご案内
第17回 SQL Server 2008 自習書の追加ドキュメントのお知らせ
第16回 SQL Server 2008 R2 自習書とプレビュー セミナーのお知らせ
第15回 SQL Server 2008 R2 Reporting Services と新刊のお知らせ
第14回 TechEd 2009 のご報告と SQL Server 2008 R2 について
第13回 SQL Server 2008 R2 の CTP 版が公開されました
第12回 MVP Summit 2009 in Seattle へ参加

技術コミュニティでも活動中

Microsoft MVP for SQL Server

松本崇博 Blog(SQL Server Tips)
松本美穂ブログ(SQL Serverノート)