Database u2y@5ktji6y-u

【SQLite3】窓(Window)関数とは？使いこなして単純移動平均を求めるまでの道のり

PCで仕事をしている人

テーブル内部に格納されているデータを分析対象として、結果を求める際に、窓関数（以後：Window関数）は非常備便利です。

しかし、一方で理解しにくく、動作もイメージしにくい。という難点があります。

そう思っているのは、私だけではないようで、Qiitaを検索してみると先輩方も同じように悩み、乗り越えてきたようです。

そろそろSQLのウィンドウ関数を理解したい – 連載1/3話
禄太
私なんかは業務でPythonもSQLも使わないベンダなので、皆さんの足元にも及びませんが、、、
ちなみに、リンクを貼ったQiitaの記事をご参照いただければ、私のブログは見る必要ないんじゃないかと自信を無くしています。
もし戻ってきていただけたら嬉しいです（ぼそぼそ）

さて、前置きが長くなってしまいましたが、本日はWindow関数についてまとめていきたいと思います。

記事要約
  1. Window関数の定義について解説しています。
  2. 使い道と特徴（特にOVER句）について解説しています。
  3. サンプルと動作確認を行います。

目次

Window関数とは？定義について解説

Window関数の定義

Wikipedeiaを検索すると、以下のような記述がヒットします。

SQL において、窓関数（まどかんすう）もしくはウィンドウ関数 (ウィンドウかんすう、英: window function) は結果セットを部分的に切り出した領域に集約関数を適用できる、拡張された SELECT ステートメントである。

Wikipedia(窓関数 (SQL))
禄太
文面ではわかりにくいのですが、何らかのデータが並んでいたときに、それを指定した範囲のみ抽出することができる機能が、Window関数です！

例えば、こんなことができます（単純移動平均を求める）

単純移動平均とは？

移動平均とは、株価の終値の平均値を算出して、大まかな株価のトレンドを示したものです。

平均値をつなぎ合わせて線にすることで、大まかなトレンド（売られすぎ？過熱気味？）を把握することができます。

移動平均線にはいくつかの種類があり、５日移動平均線や、１３週移動平均線など、投資家の投資スタイルに合わせて利用されます。

例えば、５日移動平均線の算出方法は、以下の通りです。

５日移動平均線の算出
  1. 算出したい日を含めて、過去５営業日の終値を足す。
  2. 他した値を５で割って、平均値を出す。
  3. 翌日以降も同様の手順で平均値を出していき、複数営業日分の値が算出できたら、線で繋ぐ。

移動平均を算出するときのイメージ

おそらく、SQLでもExcelなどの表計算ツールでも、「全体の終値の平均値を算出しましょう。」と言う問題であれば、割と解決しやすいのではないかと思います。

禄太
Excel：=AVERAGE()
S Q L：avg(列名)

で、求めることができます！

しかし、前述の移動平均のように、算出対象美を含んだ、過去N日間の平均値を算出する場合どうしたらいいでしょうか？

ふと、そこで手が止まるのではないかと思います。

私は、Window関数を知らなかったときに、どうしたらよいかわかりませんでした。

【実現したいことのイメージ】

禄太
こういったとき、誰にも聞けない。質問ができない。という状況は非常に困ります。
独学の苦しいところは、こういったところになるのではないかと思います。

Window関数の動作イメージ

特定の日付の終値だけを抽出して、平均値を算出する

上の図でオレンジに網掛けをしたセルを、ご覧ください。

５日移動平均を算出するときは、算出したい日を含んだ５営業日の終値を足し算する必要があります。

Window関数を利用することによって、羅列されているデータに対して条件を指定して抽出をし、平均値を算出することが可能です。

ここで2点注目していただきたいところがあります！

Window関数による算出
  1. 算出対象の中に、20220104以前の値が入っていたとしても、エラーになりません。

    2. 例えば、20220104と、20220105の行には、５営業日前の終値が存在しません。
    しかし、エラーにはならず、結果を出力することができます。
    20220104：3763.0
    20220105:3689.0

  2. 指定した期間のみが算出対象になります。

    3. 左の表と右の表それぞれ見ていただくと、５営業日分の値で計算されています。前後にデータがあったとしても、指定した範囲を超えて算出をすることはありません。

基本構文とサンプル

Window関数の特徴（OVER句）

Window関数では、OVER句によってデータの集約が行われます。

【SQL抜粋】

#closing_priceを算出対象として、平均(AVG)を求める
AVG(closing_price)

#ここが大事！
#OVER() のカッコの中に、Order by を指定することで日付ごとにソートし、それぞれに対してWindows関数が実行されます。
#PRECENDING：先行のレコードを指定しています。4を指定することで、4レコード前からを算出対象にしています。
#CURRENT ROW：現在の行を示しています。
    OVER(ORDER BY trading_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)
    as Moving_AVG
    from WINDOW;
禄太
Partition by によるグルーピングや、他のWindow関数については別途ご説明します。

サンプルコード全体と、動作確認

テストデータ

create table WINDOW(trading_date,closing_price,MA5);

insert into window (trading_date, closing_price) values
(20220104,3763.0),
(20220105,3615.0),
(20220106,3502.0),
(20220107,3477.0),
(20220111,3635.0),
(20220112,3636.0),
(20220113,3613.0),
(20220114,3473.0),
(20220117,3492.0),
(20220118,3497.0),
(20220119,3568.0),
(20220120,3481.0),
(20220121,3377.0),
(20220124,3244.0),
(20220125,3063.0),
(20220126,3144.0),
(20220127,2986.0),
(20220128,3007.0),
(20220131,3062.0),
(20220201,3154.0),
(20220202,3367.0),
(20220203,3298.0),
(20220204,3159.0),
(20220207,3100.0),
(20220208,3100.0),
(20220209,3133.0),
(20220210,3188.0),
(20220214,3211.0),
(20220215,3299.0),
(20220216,3333.0),
(20220217,3188.0),
(20220218,3188.0),
(20220221,3088.0),
(20220222,3000.0),
(20220224,2877.0),
(20220225,3040.0),
(20220228,3144.0),
(20220301,3277.0),
(20220302,3311.0),
(20220303,3311.0),
(20220304,3111.0),
(20220307,3011.0),
(20220308,2999.0),
(20220309,2900.0),
(20220310,2944.0);

サンプルコード

select trading_date, closing_price, 
    round(AVG(closing_price)
    OVER(ORDER BY trading_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW),2)
    as Moving_AVG from WINDOW;

実行結果

EOF

Window関数について、今回は動作のイメージを中心にまとめました。

文面で解説することがなかなか難しく、、、ここまでお付き合いいただいて、ありがとうございました。

内容については今後もリライトしていくつもりですので、引き続きご参照いただけますと幸いです。

禄太
本日も、一緒に学習してくださって、ありがとうございました！

また、次回！

RECOMMENDこちらの記事も人気です。