分析関数（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入門」の記事

サブクエリのネストとSQLコメント&整形
 WITH句（共通テーブル式）
取得行数を限定するLIMIT句
分析関数（OVER句,WINDOW句）
「ADO + VBA」でSQLを実行するときのVBAサンプル
 SQL基礎問題1：最大在庫数を持つ製品の在庫金額
 SQL基礎問題2：文字列「-nn-」のnnが偶数のみ抽出
 SQL基礎問題3：文字列の一部をキーにして集計
 SQL基礎問題4：2つのテーブルの不一致を抽出
 SQL基礎問題5：複数のマスタテーブルの結合
 SQL基礎問題6：成績表から教科ごとの点数ベスト3を抽出

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

1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門

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

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

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.

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