SQLite3でWindow関数を試してみた!|sitateru tech blog

シタテルの技術やエンジニアの取り組みを紹介するテックブログです。

2018年11月15日木曜日

SQLite3でWindow関数を試してみた!

SQLite3

はじめまして、DevOpsチームの甲斐です。
何かブログのネタはないかなーと思っていたところ、たまたま以下のニュースを発見しました。

ウィンドウ関数サポートが加わった「SQLite 3.25」公開

Window関数というとちょっと前までフリーなRDBMSでサポートしているのはPostgreSQLの一択でしたので、
「SQLiteでWindow関数が使えるようになったのか!」とちょっとした驚きでした。
(ちなみに、MySQLでもバージョン8からWindow関数が正式に実装されたようです)

そこで、今回はこの新機能である「SQLiteでWindow関数」を試していきたいと思います。

SQLiteのインストール

何はともあれSQLiteをインストールしましょう。
SQLiteでWindow関数を試すには9月にリリースされた3.25以上が必要ですので、最新のSQLiteをインストールします。
以下は、Macでの手順です。Windowsな方は適宜インストールして下さい。
また、Macなエンジニアの方はすでにHomebrewはインストール済みかと思いますので、Homebrewのインストール手順は割愛します。

% brew install sqlite
% echo 'export PATH="/usr/local/opt/sqlite/bin:$PATH"' >> ~/.zshrc  # bashな方は.bashrcで
% exec -l $SHELL

SQLiteのバージョンが3.25以上であればOKです。

% sqlite3 --version
3.25.3 2018-11-05 20:37:38 89e099fbe5e13c33e683bef07361231ca525b88f7907be7092058007b75036f2

SQLiteでWindow関数を使ってみる

それでは、SQLiteでWindow関数を試してみたいと思います。

Window関数とは

そもそもWindow関数とは何かというと、

SQL において、窓関数もしくはウィンドウ関数 (英: window function) は結果セットを部分的に切り出した領域に集約関数を適用できる、拡張された SELECT ステートメントである。SQL:2003 以降の標準SQLで規定されている。分析関数やOLAP機能と呼ばれる場合もある。

(by Wikipedia)

とあります。
いまいち要領を得ませんが、要は分析に使えるSQL関数ということですね。
エンジニアの皆さんもご自身でちょっとした分析などをやることもあるかと思いますが、その際にWindow関数を知っていると結構便利です。

日経平均株価の移動平均を求めてみる

今回はサンプルとして日経平均株価の移動平均をSQLiteのWindow関数で求めてみたいと思います。
「移動平均」をWikipediaで検索すると、

移動平均は、時系列データ(より一般的には時系列に限らず系列データ)を平滑化する手法である。音声や画像等のデジタル信号処理に留まらず、金融(特にテクニカル分析)分野、気象、水象を含む計測分野等、広い技術分野で使われる。有限インパルス応答に対するローパスフィルタ(デジタルフィルタ)の一種であり、分野によっては移動積分とも呼ばれる。

主要なものは、単純移動平均と加重移動平均と指数移動平均の3種類である。普通、移動平均といえば、単純移動平均のことをいう。

(by Wikipedia)

とあります。
もう少し具体的に説明すると、例えば3日間移動平均を求めるとは以下のような感じになります。

date        sales   d3_moving_average
2018/11/01  100     100.0
2018/11/02  110     105.0
2018/11/05  120     110.0               # 11/01-053営業日のsalesの平均
2018/11/06  80      103.333333333333    # 11/02-063営業日のsalesの平均
2018/11/07  150     116.666666666667    # 11/05-073営業日のsalesの平均
2018/11/08  200     143.333333333333    # 11/06-083営業日のsalesの平均
2018/11/09  100     150.0               # 11/07-093営業日のsalesの平均

これを素のSQLだけでやるのはけっこう大変なのですが、Window関数を使うと簡単にできます。
それでは実際に日経平均株価の5日間移動平均をWindow関数で求めていきたいと思います。

  1. 日経平均株価のデータをダウンロードします
% curl -LO 'https://indexes.nikkei.co.jp/nkave/historical/nikkei_stock_average_daily_jp.csv'

注: ダウンロードしたデータの最終行に著作権が明記されています。取扱いにはご注意下さい。

  1. ファイルがWindows仕様になっているのでMac(Unix)仕様に変換します
# 文字コードをShift-JISからUTF-8へ変換
% iconv -f cp932 -t utf-8 nikkei_stock_average_daily_jp.csv > nikkei_stock_average_daily_jp-utf8.csv

# 改行コードをdos(CRLF)からunix(LF)に変換
% perl -i.bak -pe 's/\cM//g' nikkei_stock_average_daily_jp-utf8.csv

# ヘッダが日本語なので英語に変換
% perl -i.bak -pe 's/^.*$/date,end,start,high,low/ if $. == 1' nikkei_stock_average_daily_jp-utf8.csv

# 最終行の著作権の文言を削除
% perl -i.bak -ne 'print $_ if !eof' nikkei_stock_average_daily_jp-utf8.csv
  1. CSVファイルをSQLiteにインポート
% sqlite3
sqlite> .mode csv
sqlite> .import ./nikkei_stock_average_daily_jp-utf8.csv nikkei
sqlite> .schema nikkei
CREATE TABLE nikkei(
  "date" TEXT,
  "end" TEXT,
  "start" TEXT,
  "high" TEXT,
  "low" TEXT
);
  1. 2018/09/01以降の日経平均株価の5日間移動平均を求めてみる
sqlite> .headers on
sqlite> select
   ...>   date,
   ...>   end,
   ...>   avg(end) over(
   ...>     order by date
   ...>     rows between 4 preceding and current row
   ...>   ) as d5_moving_average
   ...> from
   ...>   nikkei
   ...> where
   ...>   date >= '2018/09/01'
   ...> ;
date,end,d5_moving_average
:
(snip)
:
2018/11/02,*****.**,21691.772
2018/11/05,*****.**,21841.61
2018/11/06,*****.**,21979.702
2018/11/07,*****.**,22012.77
2018/11/08,*****.**,22172.624
2018/11/09,*****.**,22173.942

注: 日経平均株価は著作権により転載が禁止されているためマスクして表示しています

avg(end) over(...)の部分がWindow関数部分です。
over()の部分でグループ化を行い、そのグループに対してavg()を適用するという感じになります。
ここでは、全体の集合(select date,end from nikkei where date >= '2018/09/01')に対して、
まずdateでソートして、現在行より4行前(4 preceding)から現在行(current row)までの5日間の平均(avg(end))を
一日ずつずらしながら順次求めていることになります。

この他にもWindow関数には便利な機能が沢山ありますので、是非いろいろと調べてみて下さい。
また、最近のSQLiteはJSON型もサポートされいていたり結構高機能ですので、こちらも是非ご活用下さい!

, ,