分析関数（OVER句,WINDOW句）

公開日：2019-12-25　最終更新日：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

・テーブル：m_customer ・テーブル：m_item ・テーブル：t_sales ・テーブル自動作成

以下の使用例のSQL実行では処理時間がかかるため、実行する前に下記のインデックスを作成しておいてください。

t_sales.code + t_sales.item_code

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

グループの最大/最小/平均を明細に付加
FILTER句で条件指定
明細にレコード番号（連番）を振る
前回販売日を取得
RANK関数,DENSE_RANK関数
LAG関数,LEAD関数

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

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句）

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

1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.繰り返し処理（For Next)｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.ブック・シートの選択（Select,Activate）｜VBA入門

ホーム
その他
SQL入門
分析関数（OVER句,WINDOW句）

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

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

SQL入門
分析関数（OVER句,WINDOW句）