私と私の猫の他は誰でも隠し事を持っている

野良プログラマ発、日々のアウトプット

「INSERT … SELECT … WHERE NOT EXIST …」を少し深掘りしてみた

MySQL で INSERT する際に、所定条件のレコードが既に存在しない時のみ行いたい、ってのはよくある要求のようです。調べたら、こんなハック↓で何とかなる模様。

stackoverflow.com

成る程、INSERT … SELECT 構文の SELECT を工夫する訳か。ただ自分の場合、上記記事の例に倣うと

INSERT INTO table_listnames (name, address, tele)
SELECT * FROM (SELECT 'Rupert', '', '') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'Rupert'
) LIMIT 1;


みたいに、複数列に同じ値をセットしたかったのですが、それだと「ERROR 1060 (42S21): Duplicate column name ''」と怒られちゃいます。これには

INSERT INTO table_listnames (name, address, tele)
SELECT * FROM (SELECT 'Rupert' AS `name`, '' AS `address`, '' AS `tele`) AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'Rupert'
) LIMIT 1;

と、きっちり列名を付与してやれば良いみたいです。さて問題は解決したのですが、この SQL、SELECT 文が二重になってて何だか分かったような分かんないような。フツーに

SELECT 'Rupert' AS `name`, '' AS `address`, '' AS `tele`
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'Rupert'
) LIMIT 1;

じゃダメなの? と試してみるとエラー。FROM 句が無いと WHERE 句は使えないのか… それじゃ

SELECT 'Rupert' AS `name`, '' AS `address`, '' AS `tele` FROM tmp
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'Rupert'
) LIMIT 1;

では? 「tmp なんてテーブルねーぞ」と怒られるなぁ(当たり前)。ここで「MySQL テーブル ダミー」等とぐぐってみたら、MySQL には DUAL というダミーテーブルがあるようです↓。

rnk.mitelog.jp

という訳で

INSERT INTO table_listnames (name, address, tele)
SELECT 'Rupert' AS `name`, '' AS `address`, '' AS `tele` FROM DUAL
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'Rupert'
) LIMIT 1;

で上手く行きました! 少しシンプルになったぞいww

第6世代 iPod Classic のバッテリー交換したった件

愛用の10年モノ iPod Classic が、さすがにバッテリーがヘタってきて持続時間が2〜3時間程になってしまったので、自力でバッテリー交換してみたです。幸い、ネットにこの種の作業に関連した情報がたくさんあったのでそれらを参考に行いましたが、それでもハマってしまった箇所を少し記録に残しておこうかと。

当方 iPod の型番は MB565J/A。所謂第6世代というブツらしいので、Amazon からこいつを購入。

思ったより早く、2日で届いたので作業開始!

f:id:mariyudu:20190106200538p:plain
愛機とバッテリー交換キット

iPod Classic の裏ブタ開けは大変だと聞いてましたが、確かにここでハマりました(所要時間30分程)。隙間にカッターの刃を差し込んで云々というブログ記事を読んで真似したのだけど、さっぱりダメで、最終的には精密ドライバ(↓)の一番細いものを捩じ込んでスキマを作り、さらに太いドライバー→バッテリー付属のオープナー、という手順でこじ開けました。

f:id:mariyudu:20190106200713p:plain
精密ドライバ

最初は、下記のようなイメージでスマートに開けるのだと思ってたんですが、どうやらこれが間違ってたみたい。

f:id:mariyudu:20190106200745p:plain
裏ブタ開けイメージ(✗)

実際は裏ブタ縁をぐいっと変形させつつ、力ワザで多少強引にやっちゃうのが正解だったみたいです(↓)。

f:id:mariyudu:20190106200824p:plain
裏ブタ開けイメージ(○)

フタを開けちゃえばもう勝ったようなもの。バッテリーを裏ブタから剥がして新しいものを貼り付け。

f:id:mariyudu:20190106200926p:plain
バッテリ交換後の図

最後にバッテリーのコードがコネクタに挿さらない! と焦りましたが、参考記事をよく読むとコネクタ部の黒い凹の字型のストッパ(↓)を上に数ミリ引き上げてから挿すのが正解ですた(要ピンセット)。

f:id:mariyudu:20190106201017p:plain
バッテリコネクタ部

フタを閉める前に、起動確認を。おお、ニューバッテリーって7割程充電済みなのね。

f:id:mariyudu:20190106201108p:plain
バッテリ交換後の起動確認

以上、新年早々の iPod Classic 延命作戦という訳で現場からは以上です。尚、参考にさせて頂いた記事は以下のとおりです。

iPod Classic 160Gの液晶・バッテリ交換
iPod classic (Late 2009)の分解&HDD交換【前編】 | デカの日常
iPod Classic Rubber Bumpers Replacement - iFixit Repair Guide
【iPod Classic修理】ipod Classic分解方法 - YouTube

VSCode + Remote FS で netrw っぽくリモートマシンのファイルを編集する

仕事柄、ちょこちょこっとリモートマシンのファイルを確認・編集したいことが多いのですが、そんな時は主に MacVim + netrw で ssh 越し作業してます。ローカル・リモートともに特別な設定無しで、ファイラでディレクトリを渡り歩きながらの操作が楽ちんなのでつ。

こういう作業を最近流行りの AtomVSCode 等の Electron ベースなエディタで行おうとすると、お薦めとされている拡張機能のほとんどがリモートマシンに rmate が必要だったりプロジェクト毎に諸元設定しなきゃだったりで、多機能ではあっても小回りが効かないのが不満でした。そんな中、みつけたのが Remote FS という VSCode 拡張機能。これは設定で、

"remotefs.remote": {
  "dev": {
    "scheme": "sftp",
    "host": "host",
    "username": "username",
    "rootPath": "/path/to/somewhere"
  }
}

等とリモートサーバ諸元を設定しておけば、あとはコマンドラインから

code --folder-uri sftp://dev/

と叩くだけで下記のように、エクスプローラにファイラが表示されてリモートマシンのディレクトリやファイルを直に操作できます。イイと思いません?

f:id:mariyudu:20181201165526p:plain
Remote FS

残念なのは、~/.ssh/config に登録したホスト情報が使えないことでしょうか。ていうか設定さえも面倒なので更に netrw っぽく、コマンドラインから URI を叩くだけで済むようにならないかなーなんて思います。

CloudWatch コンソールログイン通知でメールが来ない件

小ハマり系小ネタでつ。先日 AWS セキュリティ強化の一環で今さらですが、コンソールログインのメール通知を

dev.classmethod.jp

を読んで実施したのですが、ログインしてみてもさっぱりメールが来ない。CloudTrail にはちゃんとイベントが記録されているのに… と、イベントをよく見たらリージョンが us-east-1 となっている?

f:id:mariyudu:20181115153633p:plain:w500

どゆこと? と、「ConsoleLogin イベント リージョン」あたりでぐぐるとまたもクラスメソッドさんのページがヒット(助かってますぅ)。

dev.classmethod.jp

これによるとコンソールのリージョン設定やサインイン時の URL に依存するようです。当方コンソールは東京にセットしてありますが、サインイン URL を https://ap-northeast-1.console.aws.amazon.com/ec2/v2/home にしても https://s3.console.aws.amazon.com/s3/home にしても CloudTrail にはリージョンは全て us-east-1 で記録されてます。うーむ…

結局、理由はよく分からないまま、メール通知設定での SNS トピック作成および CloudWatch ルール作成の時だけリージョンをバージニア北部に変えてやりなおしたところ、無事メールが届くようになりましたとさ。ちゃんちゃん。

気象庁の各地気圧データから箱ひげ図を描いてみる

昨年末あたりから Jupyter Notebook を触り始めてすっかりハマってます。Python の豊富な DS ライブラリを使ってのデータ加工・集計・分析・可視化等を Web U/I 上でトライ&エラーできる柔軟さが素晴らしすぐる。発想にまかせて自由気ままにデータいじりするにはまだまだスキル不足なので、何かしらお題を設けてはアウトプットするルーティンワークを続けていこうかと。今回はその一環で、気象庁サイトから日本各地の1年間の気圧データ(CSV)から、月単位の箱ひげ図を作成して、各地毎の気圧変動特性を概観してみようという試み。

気圧データ取得は、気象庁過去の気象データ・ダウンロードにて、下記要領で。

  1. 「地点を選ぶ」にて、地図上から目的の都市をクリックしてひとつ選択。
  2. 「項目を選ぶ」にて、データの種類=時別値・項目=現地気圧、と選択。
  3. 「期間を選ぶ」にて、連続した期間で表示する(2017年1月1日〜2017年12月31日)と選択。
  4. CSV ファイルをダウンロード」をクリック。

今回は、札幌・仙台・東京・名古屋・大阪・松山・福岡・那覇の8地点をそれぞれデータ取得しました。それを Jupyter からアップロードして、下記コードを実行!

import pandas as pd
import matplotlib.pyplot as plt
%matplotlib inline

# データファイル
datafiles = ['kiatsu-sapporo.csv', 'kiatsu-sendai.csv', 'kiatsu-tokyo.csv', 'kiatsu-nagoya.csv', 'kiatsu-osaka.csv', 'kiatsu-matsuyama.csv', 'kiatsu-fukuoka.csv', 'kiatsu-naha.csv']

# データファイル数に合わせた大きさのグラフ領域を作成
rows = len(datafiles)
fig = plt.figure(figsize=(8,3*rows), dpi=100)
yrange = [960, 1040]

for (n, datafile) in enumerate(datafiles):
    # CSV を読み出してデータフレーム作成(1列目を datetime 型のインデックスにするのがミソ)
    df = pd.read_csv(datafile, skiprows=5, header=None, names=['datetime', 'pressure','dmy1','dmy2'], index_col=0, parse_dates=True)
    datas = []
    # 月毎の2次元配列に纏める
    for m in range(1, 13):
        datas.append(df[df.index.month == m].pressure)
    # 箱ひげ図プロット
    ax = fig.add_subplot(rows, 1, n+1)
    bp = ax.boxplot(datas, whis='range') # 外れ値描画なしで
    plt.title(datafile)
    plt.grid()
    plt.subplots_adjust(hspace=0.4)
    plt.ylim(yrange)

結果、作画した箱ひげ図はこんな感じ↓。matplotlib の boxplot() はデフォルトだと、四分位範囲 ✕ 1.5 外の値を外れ値にするようなのですが、今回のデータは偶然誤差の類を考慮しなくても良いかなと思い、ひげ長をレンジ全体にしました。
f:id:mariyudu:20180516205938p:plain

家族に頭痛持ちがいて低気圧の日は辛そうなので、日本のどの辺が気圧変動が穏やかなのかなー、と思ったのが動機でした。九州以北は概ね冬季の変動が大きいのに較べて沖縄は比較的穏やかそう。どこも10月にレンジが広いのは台風のせいですが、それでも東京が沖縄より振れ幅が大きいのは意外でした。

さーて次は何をやろうかな…

コンパクトな PHP テンプレートエンジン、Latte を使ってみた

Yet Another PHP Template Engine な話。とある PHP システムを作っていて、動的に与えられるテンプレート構文にデータを適用させて、断片的な HTML コードを生成させたい、という要件がありまして、

  • Twig や Blade だと大袈裟というか、かさばりそうなのでなるだけコンパクトなやつ
  • ちゃんとメンテされてて PHP7 でも安心して使えそうなの
  • コードがモダンで、PHP5.4 未満な残滓が無いと嬉しい

なテンプレートエンジンを探してたら Latte という OSS プロダクトを発見。どうやらこれ、Nette という MVC フレームワークの構成ライブラリですが、単体でも問題なく使えるようです。

Latte - amazing template engine for PHP | Nette Framework

で、上記ページを読みながら評価してみました。インストールは compser いっぱつ。

composer require latte/latte

テスト用にこんなスクリプトを書いてみたです(↓)。

<?php
require 'vendor/autoload.php';

$duos = [
	'Simon & Garfunkel' => ['Paul Simon', 'Art Garfunkel'],
	'Peter & Gordon' => ['Peter Assher', 'Gordon Waller'],
];

$template = '<dl n:foreach="$duos as $name => $duo">
	<dt>{$name}</dt>
	<dd>
		<ul>
			<li n:foreach="$duo as $member">{$member}</li>
		</ul>
	</dd>
</dl>';

$latte = new Latte\Engine;
$latte->setLoader(new Latte\Loaders\StringLoader([
	'main' => $template
]));
$latte->render('main', ['duos' => $duos]);

はい、ちゃんとレンダリングされました(↓)。

<dl>
	<dt>Simon &amp; Garfunkel</dt>
	<dd>
		<ul>
			<li>Paul Simon</li>
			<li>Art Garfunkel</li>
		</ul>
	</dd>
</dl><dl>
	<dt>Peter &amp; Gordon</dt>
	<dd>
		<ul>
			<li>Peter Assher</li>
			<li>Gordon Waller</li>
		</ul>
	</dd>
</dl>

デフォルトで HTML エンティティ変換されてるのがイイですね。テンプレートでおや?と思うのが、Vue.js テンプレートのディレクティブみたいな書き方ができること。これを Latte ではマクロと呼ぶようです。分岐やループは

<?php
$template = '{foreach $duos as $name => $duo}
	<dl>{$name}</dl>
	<dd>
		<ul>
			{foreach $duo as $member}
				<li>{$member}</li>
			{/foreach}
		</ul>
	</dd>
{/foreach}';

のように Twig っぽい書き方もできるのすが、マクロを使うことでよりコンパクトなテンプレートが書けますね。

それから Latte の特色として、コンテキスト・アウェアネス指向な件。要は、レンダリングの際に埋め込み場所に応じたセキュリティ対策が考慮されている、ということらしいです。

<?php
$latte->setLoader(new Latte\Loaders\StringLoader([
	'main' => '<p>param is {$param}</p>
<a href="{$param}">Click Here</a>'
]));
$latte->render('main', ['param' => "localtion.href='http://google.com'"]);

を実行すると、

<p>param is localtion.href='http://google.com'</p>
<a href="">Click Here</a>

となり、p タグ内には展開される文字列が、a タグの href 属性内ではオミットされて、スクリプトインジェクションを抑止できます。どうですか? 自分はカナーリ気に入りました、Latte。

PHP Trader 関数を使ってみる - ATR トレーリングストップの巻

忘れた頃にやってくる PHP Trader 関数シリーズ。今回は ATR (真の値幅の移動平均)を算出する trader_atr() を取り上げるのですが、ただ計算させるだけじゃつまらないので計算結果を元に損切りラインを導出して売却指示をさせてみます。所謂「ATR トレーリングストップ」ですね。

ATR トレーリングストップについては言葉はよく聞くのですが、計算ロジックまで明示された情報は少ないのですな。あれこれ調べた結果、保持中銘柄の損切りラインは以下のように算出するようです(間違ってたらすんまそん)。

  • 当該銘柄について、直近の所定日数(10日くらいが定石みたい)の真の値幅平均(以下 ATR)をとる。
  • 当該銘柄について、保持期間中の最高値を得る。
  • 最高値 - ATR × 係数をその日の損切りラインとする(係数は3〜4くらいが定石みたい)。

という訳で以下、サンプルコードです。いつものように k-db.com さんから頂いた今年の日経平均日足データを元に、ATR が算出可能になった日を買付日と想定します。んで、以後の日付毎に「終値損切りラインを割ったら翌日に売却」のルールで指示を出力する、というものです。

<?php
define('ATR_DAYS', 10); // ATR 日数
define('ATR_N', 4); // ATR 係数

// CSV ファイルから株価データを読み込んで、配列に格納
$data = file('indices_I101_1d_2017.csv', FILE_IGNORE_NEW_LINES);

//見出し行を省く
unset($data[0]);

// 各行のカンマ区切りを配列に分割
$data = array_map(function($item){ return explode(',', $item); }, $data);

// 日付昇順でソート
usort($data, function($a, $b){ return ($a[0] < $b[0]) ? -1 : 1; });

// ATR 算出
$atrs = trader_atr(
	array_column($data, 2), // 高値
	array_column($data, 3), // 安値
	array_column($data, 4), // 終値
	ATR_DAYS
);

// トレーリングストップによる売買指示を出力
$highest = 0;
$losscutLine = null;
echo "日付,最高値,終値,損切りライン,指示\n";
foreach ($atrs as $idx => $atr) {
	$price = $data[$idx];
	$highest = max($highest, $price[2]); // 最高値更新
	$losscutLine = round($highest - $atr * ATR_N, 2); // 損切りライン算出
	echo implode(",", [
		$price[0],
		$highest,
		$price[4],
		$losscutLine,
		$price[4] < $losscutLine ? '売却' : '保持'
	]) . "\n";
}

実行結果はこんな感じ↓。

日付,最高値,終値,損切りライン,指示
2017-01-19,19122.39,19072.25,18271.88,保持
2017-01-20,19176.86,19137.91,18356.74,保持
2017-01-23,19176.86,18891.03,18335.46,保持
2017-01-24,19176.86,18787.99,18366.58,保持
2017-01-25,19176.86,19057.5,18309.53,保持
2017-01-26,19405.23,19402.39,18485.54,保持
2017-01-27,19486.68,19467.4,18625.24,保持
2017-01-30,19486.68,19368.85,18642.75,保持
2017-01-31,19486.68,19041.34,18596.14,保持
2017-02-01,19486.68,19148.08,18589.35,保持
(中略)
2017-04-03,19668.01,18983.23,18884.87,保持
2017-04-04,19668.01,18810.25,18851.34,売却
2017-04-05,19668.01,18861.27,18865.81,売却
2017-04-06,19668.01,18597.06,18814.58,売却
2017-04-07,19668.01,18664.63,18792.61,売却
2017-04-10,19668.01,18797.88,18805.68,売却
(後略)

2ヶ月半ほど保持の指示が続いた後で、4月4日に売却指示が出ました。1月19日に始値(19,082.83円)で買付して、売却指示翌日の4月5日の始値(18,900.70円)で処分したとして、約1%の損失です。実際は銘柄毎に直近期間をバックテストして係数をフィッティングするようなので、お仕着せな係数ではまぁこんなもんかも知れません。

ちなみにこの trader_atr() 関数ですが、計算結果が単純移動平均では無さそうなのでソースコードを確認してみたのですが、平均値 = (前日の平均値 × (日数 - 1) + 当日の値幅) / 日数、という加重計算がされているようです。この関数では trader_ma() のように移動平均の種類が指定できる訳ではないので、その辺が不満な場合は trader_tr() 関数で真の値幅を得てから好きな方式の平均値を求めるのが良いかと。