SQL入門
分析関数(OVER句,WINDOW句)

SQLの初心者向け入門解説、VBAからデータベースを扱うためのSQLを解説
最終更新日:2019-12-25

分析関数(OVER句,WINDOW句)


分析関数はグループ内の指定された範囲で集計を行い、結果を集計元の各行に戻すことができます。
分析関数はSQLとしては比較的新しい機能で、ウィンドウ(Window)関数とも呼ばれます。。
サブクエリーを複数組み合わせなければできなかったものが比較的簡単なSQL文で実現できるようになります。


分析関数とは

集計関数(SUM,AVG,MAX,MIN等)はグループ全体に対して集計を行い集計された結果を戻すだけですが、
分析関数はグループ内の指定された範囲で集計し、集計元の各行に戻すことができます。
また、分析関数はレコードの順序の概念を持つので、指定行位置のデータを取得することができます。

分析関数を使うメリットは、速度の向上、SQLの簡素化にあります。
簡潔なSQL文で複雑な分析処理を従来よりも高速に処理可能となります。

分析関数を使うには、分析関数にOVER句を指定します。
OVER句に以下を指定することで集計範囲を各種指定できます。
PARTITION BY ・・・ グループ分け
ORDER BY ・・・ 順序指定
ROWS BETWEEN ・・・ レコード範囲
さらにOVER句の前にFILTER句を指定することで、条件を満たしたレコードのみで処理を行うこともできます。

分析関数の種類
以下は、データベース全般でサポートされていて良く使われる分析関数になります。

COUNT関数 ・・・ 件数
SUM関数 ・・・ 合計
AVG関数 ・・・ 平均
MAX関数 ・・・ 最大
MIN関数 ・・・ 最小
RANK関数 ・・・ 順位
DENSE_RANK関数 ・・・ 順位
LAG関数 ・・・ n個前
LEAD関数 ・・・ n個後

各データベースで、より豊富な分析関数が用意されていますので、それぞれのリファレンスを参照してください。

OVER句

SELECTの選択リストに記述します。

分析関数 OVER(ORDER BY カラム名,・・・)
並べ替えを指定します。
分析関数 OVER(PARTITION BY カラム名,・・・)
集計範囲を指定します。
分析関数 OVER(ROWS BETWEEN 開始位置 AND 終了位置)
開始位置、終了位置には、以下を指定します。
UNBOUNDED PRECEDING ・・・ 最初のレコード
n PRECEDING ・・・ n個前のレコード
CURRENT ROW 現在のレコード
n FOLLOWING ・・・ n個後のレコード
UNBOUNDEE FOLLOWING ・・・ 最後のレコード

ROWSではなくRANGEもあります。
ROWSは単純なレコード位置での範囲ですが、
RANGEはORDER BYで指定したカラムの値で範囲指定します。

SQLiteで何通りか試したところ、
RANGE must use only UNBOUNDED or CURRENT ROW
このエラーが発生しました。
UNBOUNDED または CURRENTだけを指定すると確かに動作しますが、
これしか使えないとすると、用途がかなり限定されてしまいます。
このような事情があるので、今回は具体的な解説を省略しています。

WINDOW句

OVER句の()括弧内の記述に対して、WITH句を使ってエイリアスを付けるイメージをしてもらえれば良いでしょう。
使い方としては、WITH句と同様に考えると分かり易いと思います。

分析関数 OVER(・・・)
↓これをWINDOW句を使って書き直しができます。
分析関数 OVER ウインドウ名
・・・
FROM ・・・
WINDOW ウインドウ名 AS (・・・)
データベースによってかなり違いがありますが、
少なくともSQLiteにおいては、上記のように単純な使い方としてとらえて良いでしょう。

WINDOW句のSQLないでの記述位置
SELECT ・・・
FROM ・・・
WHERE ・・・
GROUP BY ・・・
WINDOW ・・・
ORDER BY ・・・
WINDOW句を使う場合、GROUP句やORDER句はあまり使う必要がありませんが指定順に気を付けてください。

FILTER句

OVER句の前にFILTER句を記述することで、条件を満たしたレコードのみで集計処理を行います。

分析関数 FILTER(WHERE 条件式) OVER(・・・)

条件式は、通常のWHERE句と同様になります。

分析関数の使用例

以下の使用例で使用するテーブル定義は以下になります。
全テーブル定義とテーブル自動作成VBA
データベースの正規化とマスタの作成で作成した全テーブル定義と、テーブル名変更と列追加(ALTERTABLE)とテーブル自動作成で作成したテーブル自動作成VBAを掲載しておきます。エクセルVBAでデータベースを扱うためのSQL入門です。前回までにテーブルを作成したり、削除したりできるようになりました。
以下の使用例のSQL実行では処理時間がかかるため、実行する前に下記のインデックスを作成しておいてください。
t_sales.code + t_sales.item_code

分析関数の使用例として、以下のサンプルを用意しました。

分析関数の使用例:グループの最大/最小/平均を明細に出力

codeとitem_codeでグループ化し、各グループの金額の最大/最小/平均を各明細に出力します。
以下の処理順となります。
・GROUP BY code,item_code
・MAX,MIN,AVG
・元の明細と列出力

サブクエリだけのSQL

SELECT
  T.code,T.sales_date,T.item_code
 ,T.item_count * T.item_price AS item_amount
 ,TT.max_amount,TT.min_amount,TT.avg_amount
FROM t_sales T
     INNER JOIN
    (SELECT code,item_code
      ,MAX(item_count * item_price) AS max_amount
      ,MIN(item_count * item_price) AS min_amount
      ,AVG(item_count * item_price) AS avg_amount
       FROM t_sales
       GROUP BY code,item_code) TT
     ON T.code = TT.code AND T.item_code = TT.item_code

OVER句を使ったSQL

SELECT
  code,sales_date,item_code
 ,item_count * item_price AS item_amount
 ,MAX(item_count * item_price) OVER(PARTITION BY code,item_code) AS max_amount
 ,MIN(item_count * item_price) OVER(PARTITION BY code,item_code) AS min_amount
 ,AVG(item_count * item_price) OVER(PARTITION BY code,item_code) AS avg_amount
  FROM t_sales

OVER句+WINDOW句

SELECT
  code,sales_date,item_code
 ,item_count * item_price AS item_amount
 ,MAX(item_count * item_price) OVER PART AS max_amount
 ,MIN(item_count * item_price) OVER PART AS min_amount
 ,AVG(item_count * item_price) OVER PART AS avg_amount
  FROM t_sales
  WINDOW PART AS (PARTITION BY code,item_code)
最後のOVER句+WINDOW句を使ったSQLが可読性や保守性を考えると最も良いでしょう。

分析関数の使用例:FILTER句で条件指定

上の「グループの最大/最小/平均を明細に付加」では、金額が0も含めて最大/最小/平均が算出されてしまいます。
そこで、以下では金額が0は集計から除外しています。

SELECT
  code,sales_date,item_code
 ,item_count * item_price AS item_amount
 ,AVG(item_count * item_price)
    FILTER(WHERE item_count * item_price <> 0)
    OVER PART AS item_avg
  FROM t_sales
  WINDOW PART AS (PARTITION BY code,item_code)
OVER句の前に、FILTER句を追加しただけの違いです。

分析関数の使用例:明細にレコード番号(連番)を振る

金額ベスト100に通し番号を振って出力します。
以下の処理順となります。
・金額降順で並べ替え
・1から順に通し番号を振る
同じ金額かどうかは関係なく出力順通りの通し番号にします。

WITH TT AS
  (SELECT *, 1 AS nn,item_count * item_price AS item_amount
     FROM t_sales)
SELECT
  SUM(nn) OVER pre AS No
 ,code,sales_date,item_code,item_amount
  FROM TT
  WINDOW pre AS 
   (ORDER BY item_count * item_price DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
  LIMIT 100
※SQLite以外のデータベースでは
最初からレコード番号を取得できる機能が用意されているものが多いです。
ROW_NUMBERまたはROWNUM等を使う事で、簡単にレコード番号を取得できます。
SQLiteにはこの機能がないため、サンプル例題としています。

分析関数の使用例:前回販売日を取得

code,item_code別に、前回の販売日(カレントレコードより前の最後の販売日)を取得します。
ただし、code,item_code,sales_dateが同一のレコードが複数あることが前提ですので、
単純に直前のレコード取得では、意図した結果になりません。

当初はRANGEを使ったSQLサンプルを予定していました。
前述したとおり、SQLiteではRangeがエラーとなってしまい目的のSQLが作成できませんでした。
RANGEでPRECEDINGを指定できれば、かなり簡単に取得できるはずでした。
以下のSQLは代替えとして作成したものになりますが、かなり複雑なSQLとなってしまいました。
したがってこのSQLは、あくまで練習用SQLとしてお考え下さい。
それでも、分析関数を使っているのでこの程度で済んでいますが、使わなければさらに複雑なSQLになるはずです。

INNER以降のサブクエリで、code,item_code,sales_dateごとの前回販売日を取得しています。
先頭日付では、前回の販売日はnullになります。
その後は、これをJOINしているだけになります。

WITH TT AS 
  (SELECT code,item_code,sales_date
     FROM t_sales
     GROUP BY code,item_code,sales_date)
SELECT 
  T.code,T.item_code,T.sales_date,MT.last_date
  FROM t_sales T
       INNER JOIN 
       (SELECT
          code,item_code,sales_date
         ,MAX(sales_date) OVER pre AS last_date
          FROM TT
          WINDOW pre AS
           (PARTITION BY code,item_code
            ORDER BY code,item_code,sales_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
        ) MT
        ON  T.code = MT.code
        AND T.item_code = MT.item_code
        AND T.sales_date = MT.sales_date
  ORDER BY T.code,T.item_code,T.sales_date

RANK関数,DENSE_RANK関数

RANK関数

先のレコード番号とは違い、
値が同じ場合は同じ番号が振られる、エクセルのシート関数と同じ一般的なランク番号になります。
1,2,2,4,5,5,5,8,・・・
同値の場合は同じ番号が振られ、その分は抜け番になります。

DENSE_RANK関数

RANK関数との違いは、同値の時に抜け番にせず連続番号の数値を振るところです。
1,2,2,3,4,4,4,5,・・・
それぞれ、OVER句と合わせて使用します。
連番の範囲(1にリセット)は、OVER句のPARTITION BYで指定します。

全体でRANK関数

SELECT
  RANK() OVER (ORDER BY item_count * item_price DESC)
 ,code,sales_date,item_code
 ,item_count * item_price AS item_amount
  FROM t_sales

codeごとにDENSE_RANK関数

SELECT
  DENSE_RANK() OVER rnum
 ,code,sales_date,item_code
 ,item_count * item_price AS item_amount
  FROM t_sales
WINDOW rnum AS 
  (PARTITION BY code ORDER BY code,item_count * item_price DESC)

LAG関数,LEAD関数

LAG関数はカレント行より前、LEAD関数はカレント行より後ろの行のデータを取得します。

LAG(カラム名, n個前, デフォルト値) OVER(・・・)
LEAD(カラム名, n個後, デフォルト値) OVER(・・・)

「デフォルト」は参照レコードが存在しない場合に返される値です。

SELECT
  code,sales_date,item_count
 ,SUM(item_count * item_price) AS item_amount
 ,LAG(SUM(item_count * item_price),1,0) OVER(ORDER BY code,sales_date) AS lag_amounr
 ,LEAD(SUM(item_count * item_price),1,0) OVER(ORDER BY code,sales_date) AS lead_amount
  FROM t_sales
  GROUP BY code,sales_date

分析関数の最後に

分析関数は、SQLとしてはかなり新しく追加された機能です。
各データベースがバージョンアップとともにサポートしてきたものです。
したがって、いずれのデータベースでも古いバージョンでは動作しません。

分析関数と言うくらいで、データ分析するときにはとても強力な機能になります。
ここで紹介したもの以外にもまだまだ沢山ありますので、使用するデータベースごとに調べてみてください。
ただし、分析関数の情報はWEBではまだかなり少ないと感じられました。
ブログ等での紹介は限られているようで、まとまった情報があまり見当たりませんでした。
これから徐々に増えてくるとは思います。

分析関数はとても便利な機能です。
他の方法ではとても面倒な処理が、分析関数を使うといとも簡単に実現できる場合も多くあります。
分析関数、ぜひ積極的に使用してみてください。



同じテーマ「SQL入門」の記事

データの更新(UPDATE)
データの削除(DELETE)
他のテーブルのデータで追加/更新/削除
インデックスを作成して高速化(CREATE INDEX)
トランザクション処理
VBAクラスの全コード:トランザクション処理
サブクエリ(副問合せ)
サブクエリのネストとSQLコメント&整形
WITH句(共通テーブル式)
取得行数を限定するLIMIT句
分析関数(OVER句,WINDOW句)


新着記事NEW ・・・新着記事一覧を見る

多階層フォルダ(ディレクトリ)の作成|VBAサンプル集(7月31日)
VBAのインデントについて|VBA技術解説(7月16日)
「VBA Match関数の限界」についての誤解|エクセル雑感(7月15日)
省略可能なVariant引数の参照不可をラップ関数で利用|VBA技術解説(7月12日)
100桁の正の整数値の足し算|エクセル雑感(7月9日)
LSetとユーザー定義型のコピー(100桁の足し算)|VBA技術解説(7月9日)
Variant仮引数のByRefとByValの挙動違い|エクセル雑感(7月5日)
Variant仮引数にRange.Valueを配列で渡す方法|エクセル雑感(7月5日)
Variantの数値型と文字列型の比較|エクセル雑感(7月1日)
VBAのVariant型について|VBA技術解説(6月30日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.繰り返し処理(For Next)|VBA入門
7.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ