2018年11月15日木曜日
SQLite3でWindow関数を試してみた!
はじめまして、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-05の3営業日のsalesの平均
2018/11/06 80 103.333333333333 # 11/02-06の3営業日のsalesの平均
2018/11/07 150 116.666666666667 # 11/05-07の3営業日のsalesの平均
2018/11/08 200 143.333333333333 # 11/06-08の3営業日のsalesの平均
2018/11/09 100 150.0 # 11/07-09の3営業日のsalesの平均
これを素のSQLだけでやるのはけっこう大変なのですが、Window関数を使うと簡単にできます。
それでは実際に日経平均株価の5日間移動平均をWindow関数で求めていきたいと思います。
- 日経平均株価のデータをダウンロードします
% curl -LO 'https://indexes.nikkei.co.jp/nkave/historical/nikkei_stock_average_daily_jp.csv'
注: ダウンロードしたデータの最終行に著作権が明記されています。取扱いにはご注意下さい。
- ファイルが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
- 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
);
- 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型もサポートされいていたり結構高機能ですので、こちらも是非ご活用下さい!
2018年11月13日火曜日
Macでの作業をちょっと楽にする7つ道具
こんにちは、シタテルの朝野です。
DevOpsやインフラ部分を担当しています。
今回はMacBookでの作業をちょっと楽にするアプリを紹介したいと思います。
本格的に開発をMacでするようになって2年ほどなのでまだまだMac界を知らないところもありますが、役に立てばうれしいです。
7つ道具とは!
Karabiner
https://pqrs.org/osx/karabiner/
キーの入れ替えや修飾キーがらみの動作をカスタマイズできます。
ちなみに私は
- 左CapsLock -> 左Command
- 左Command -> 左Control
- 左Control -> 左CapsLock
という修飾キーローテーションをしていたりします。
AppCleaner
http://freemacsoft.net/appcleaner/
アプリケーションを「完全に削除」するアプリです。
アプリ本体に加え、設定ファイルやユーザーデータ等も一気に削除できます。
掃除をするなら徹底的にしたいですね。
Alfred
Mac使いにはおなじみの帽子マークのアプリです。
アプリケーションの起動やファイル検索、Web検索、電卓など機能を素早く呼び出せます。
ほぼアプリケーションランチャーとしてだけ使っているのでもう少し使いこなしを覚えたいです。
Clipy
クリップボードにコピーした履歴を保存して、呼び出せます。
いろんなところから何度もコピペをしていると、コピー元がどこだったかわからなくなることあるますよね?
GIPHY CAPTURE
https://giphy.com/apps/giphycapture
デスクトップの任意の領域を録画してgifアニメーションのファイルに保存できるツールです。
アプリケーションの修正した部分の動作なんかをキャプチャしてプルリクエストに添付しておくとレビューがはかどります。
Macs Fan Control
https://www.crystalidea.com/macs-fan-control
温度管理アプリです。
回転数や基準温度を設定して冷却ファンの動作を調整したり、メニューバーにCPU等の温度を表示できます。
MacBookで目玉焼きが作れそうなときにぜひ。
FlagSwitcher
https://itunes.apple.com/jp/app/flagswitcher/id1157313107?mt=12
入力言語を切り替える際にチラッと国旗を表示するだけのアプリです。
だけなんですが、今入力モードを何にしたのかがわかると少しだけタイピングの効率が良くなります!💪🏻
まとめ
今回はMacの基本操作やシステム系のアプリを紹介しました。
普段のちょっとした作業を楽にするのもDevOps!
とまでは言いませんが、開発系のアプリ紹介なども考えているのでまた記事にできればと思います!