分析関数(OVER句,WINDOW句)最終更新日:2019-12-25分析関数(OVER句,WINDOW句)分析関数はグループ内の指定された範囲で集計を行い、結果を集計元の各行に戻すことができます。分析関数はSQLとしては比較的新しい機能で、ウィンドウ(Window)関数とも呼ばれます。サブクエリーを複数組み合わせなければできなかったものが比較的簡単なSQL文で実現できるようになります。分析関数はグループ内の指定された範囲で集計し、集計元の各行に戻すことができます。分析関数はレコードの順序の概念を持つので、指定行位置のデータを取得することができます。分析関数を使うメリットは、速度の向上、SQLの簡素化にあります。簡潔なSQL文で複雑な分析処理を従来よりも高速に処理可能となります。分析関数を使うには、分析関数にOVER句を指定します。OVER句に以下を指定することで集計範囲を各種指定できます。レコード範囲さらにOVER句の前にFILTER句を指定することで、条件を満たしたレコードのみで処理を行うこともできます。以下は、データベース全般でサポートされていて良く使われる分析関数になります。各データベースで、より豊富な分析関数が用意されていますので、それぞれのリファレンスを参照してください。OVER句SELECTの選択リストに記述します。カラム名,・・・)並べ替えを指定します。カラム名,・・・)集計範囲を指定します。最後のレコード※ROWSではなくRANGEもあります。BYで指定したカラムの値で範囲指定します。ROW」このエラーが発生しました。CURRENTだけを指定すると確かに動作しますが、これしか使えないとすると、用途がかなり限定されてしまいます。このような事情があるので、今回は具体的な解説を省略しています。WINDOW句OVER句の()括弧内の記述に対して、WITH句を使ってエイリアスを付けるイメージをしてもらえれば良いでしょう。使い方としては、WITH句と同様に考えると分かり易いと思います。OVER(・・・)↓これをWINDOW句を使って書き直しができます。少なくともSQLiteにおいては、上記のように単純な使い方としてとらえて良いでしょう。・・・WINDOW句を使う場合、GROUP句やORDER句はあまり使う必要がありませんが指定順に気を付けてください。FILTER句OVER句の前にFILTER句を記述することで、条件を満たしたレコードのみで集計処理を行います。OVER(・・・)条件式は、通常のWHERE句と同様になります。以下の使用例で使用するテーブル定義は以下になります。全テーブル定義とテーブル自動作成VBAデータベースの正規化とマスタの作成で作成した全テーブル定義と、テーブル名変更と列追加(ALTERTABLE)とテーブル自動作成で作成したテーブル自動作成VBAを掲載しておきます。エクセルVBAでデータベースを扱うためのSQL入門です。前回までにテーブルを作成したり、削除したりできるようになりました。以下の使用例のSQL実行では処理時間がかかるため、実行する前に下記のインデックスを作成しておいてください。t_sales.item_code分析関数の使用例として、以下のサンプルを用意しました。分析関数の使用例:グループの最大/最小/平均を明細に出力codeとitem_codeでグループ化し、各グループの金額の最大/最小/平均を各明細に出力します。以下の処理順となります。code,item_code)最後のOVER句+WINDOW句を使ったSQLが可読性や保守性を考えると最も良いでしょう。上の「グループの最大/最小/平均を明細に付加」では、金額が0も含めて最大/最小/平均が算出されてしまいます。そこで、以下では金額が0は集計から除外しています。code,item_code)OVER句の前に、FILTER句を追加しただけの違いです。分析関数の使用例:明細にレコード番号(連番)を振る金額ベスト100に通し番号を振って出力します。以下の処理順となります。・金額降順で並べ替え・1から順に通し番号を振る同じ金額かどうかは関係なく出力順通りの通し番号にします。100※SQLite以外のデータベースでは最初からレコード番号を取得できる機能が用意されているものが多いです。ROW_NUMBERまたはROWNUM等を使う事で、簡単にレコード番号を取得できます。SQLiteにはこの機能がないため、サンプル例題としています。分析関数の使用例:前回販売日を取得code,item_code別に、前回の販売日(カレントレコードより前の最後の販売日)を取得します。単純に直前のレコード取得では、意図した結果になりません。※当初はRANGEを使ったSQLサンプルを予定していました。前述したとおり、SQLiteではRangeがエラーとなってしまい目的のSQLが作成できませんでした。RANGEでPRECEDINGを指定できれば、かなり簡単に取得できるはずでした。以下のSQLは代替えとして作成したものになりますが、かなり複雑なSQLとなってしまいました。したがってこのSQLは、あくまで練習用SQLとしてお考え下さい。それでも、分析関数を使っているのでこの程度で済んでいますが、使わなければさらに複雑なSQLになるはずです。INNER以降のサブクエリで、code,item_code,sales_dateごとの前回販売日を取得しています。先頭日付では、前回の販売日はnullになります。その後は、これをJOINしているだけになります。値が同じ場合は同じ番号が振られる、エクセルのシート関数と同じ一般的なランク番号になります。同値の場合は同じ番号が振られ、その分は抜け番になります。DENSE_RANK関数RANK関数との違いは、同値の時に抜け番にせず連続番号の数値を振るところです。1,2,2,3,4,4,4,5,・・・それぞれ、OVER句と合わせて使用します。DESC)LAG関数,LEAD関数LAG関数はカレント行より前、LEAD関数はカレント行より後ろの行のデータを取得します。OVER(・・・)「デフォルト」は参照レコードが存在しない場合に返される値です。code,sales_date分析関数の最後に分析関数は、SQLとしてはかなり新しく追加された機能です。各データベースがバージョンアップとともにサポートしてきたものです。したがって、いずれのデータベースでも古いバージョンでは動作しません。分析関数と言うくらいで、データ分析するときにはとても強力な機能になります。ここで紹介したもの以外にもまだまだ沢山ありますので、使用するデータベースごとに調べてみてください。分析関数の情報はWEBではまだかなり少ないと感じられました。ブログ等での紹介は限られているようで、まとまった情報があまり見当たりませんでした。これから徐々に増えてくるとは思います。分析関数はとても便利な機能です。他の方法ではとても面倒な処理が、分析関数を使うといとも簡単に実現できる場合も多くあります。分析関数、ぜひ積極的に使用してみてください。分析関数(OVER句,WINDOW句)このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。分析関数このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。