2011-11-14

移動累計を求める相関サブクエリ

SQL

http://d.hatena.ne.jp/psappho/20111107/1320687388
この日の日記に書いていた移動累計を求めるSQLの意味がやっとわかりました。

作者: ミック
出版社/メーカー: 翔泳社
発売日: 2008/02/07
メディア: 単行本（ソフトカバー）
購入: 54人クリック: 1,004回
この商品を含むブログ (78件) を見る

この本で紹介されているSQLで、

SELECT
    prc_date,
    A1.prc_amt,
    (SELECT SUM(prc_amt)
        FROM accounts A2
        WHERE A1.prc_date >= A2.prc_date
            AND (SELECT COUNT(*)
                        FROM accounts A3
                        WHERE
                            A3.prc_date BETWEEN A2.prc_date AND A1.prc_date
                        ) <= 3
        HAVING COUNT(*) = 3
    ) AS mvg_sum
FROM accounts A1
ORDER BY prc_date
;

この、赤字にした部分を"<= 3"から"= 3"に変えるとどうしてNULLばかり返ってきてしまう（SUMするレコードがヒットしない）のか解らなかったのですが、以下のSQLを書いて実行したら理解できました。
ちなみに、accountsテーブルに入っているデータは以下です。

mysql> select * from accounts;
+------------+---------+
| prc_date   | prc_amt |
+------------+---------+
| 2011-10-26 |   12000 |
| 2011-10-28 |    2500 |
| 2011-10-31 |  -15000 |
| 2011-11-03 |   34000 |
| 2011-11-04 |   -5000 |
| 2011-11-06 |    7200 |
| 2011-11-11 |   11000 |
+------------+---------+
7 rows in set (0.00 sec)

[僕が書いた検証用SQL 青字部分は必要な分だけに結果を絞る為に追加]

SELECT
    A1.prc_date as date1,
    A2.prc_date as date2,
    A2.prc_amt as amt,
    (
        SELECT COUNT(*) FROM accounts A3
        WHERE A3.prc_date BETWEEN A2.prc_date AND A1.prc_date
    ) X
FROM
    accounts A1,
    accounts A2
WHERE
    A1.prc_date >= A2.prc_date
    AND A1.prc_date <= "2011-11-03"
ORDER BY
    A1.prc_date,
    A2.prc_date
;

結果はこうなります。

+------------+------------+--------+------+
| date1      | date2      | amt    | X    |
+------------+------------+--------+------+
| 2011-10-26 | 2011-10-26 |  12000 |    1 |
| 2011-10-28 | 2011-10-26 |  12000 |    2 |
| 2011-10-28 | 2011-10-28 |   2500 |    1 |
| 2011-10-31 | 2011-10-26 |  12000 |    3 |
| 2011-10-31 | 2011-10-28 |   2500 |    2 |
| 2011-10-31 | 2011-10-31 | -15000 |    1 |
| 2011-11-03 | 2011-10-26 |  12000 |    4 |
| 2011-11-03 | 2011-10-28 |   2500 |    3 |
| 2011-11-03 | 2011-10-31 | -15000 |    2 |
| 2011-11-03 | 2011-11-03 |  34000 |    1 |
+------------+------------+--------+------+
10 rows in set (0.00 sec)

それぞれの行に対してCOUNTを算出してXとして返しています。
このSQLの処理順序は

A1, A2をJOINする。 -- これをPとする。
A3の各行をPの行と照合して条件に一致する(A1の日付とA2の日付の間にある)COUNTを取る。
結果を返す。

こうなるみたいです。

pythonっぽく書くと

P = Table.join(A1, A2)
for x in P:
    for y in A3:
        filtr = lambda p: y.prc_date >= p.date1\
                        and y.prc_date <= p.date2
        x.count = sum((0 if filtr(z) else 1 for z in P))

こんな感じかな。
つまり、相関サブクエリを使ったら、サブクエリで指定した方の集合(A3)の全部の行とメインクエリの集合(P)を照合していくことになります。
そして、この照合作業がメインクエリの行数ぶん実行されることになります。
さくっと書いた簡潔なSELECT文だけど、裏ではものすごい仕事が行われているんですね！

2011-11-14

今日の振り返り

今日はついに今まで理解できなかった「相関サブクエリ」がわかりました！
めっちゃ嬉しいです!

仕事のこと

執筆で一山越えたけど、僕が主担当ではない部分でテコ入れが必要だとわかった。
一緒に仕事をしている人の意見を聞いて、僕の考えを追記しようと思う。
でもうまく考えがまとまらないっていうか、意味のある話がまだ構成できない状態。
時間に余裕がないけれど頑張ろう。

あとは、最近喘息の症状が収まってきた。
ひとりで抱え込んで苦しむのをやめたことと、
先月に僕が不安や不満に思っていたことを一緒に仕事をしている人達と
ちゃんと話し合ったことでだんだん気持ちが楽になったからかもしれない。

今はまた頑張ろうっていう気持ちになれる。
あと、やっぱり何よりも仕事と関係なく僕の精神的な支えになってくれる存在のお陰なのかな。
わからない。

2011-11-13

コメントとdocstring

Python

1ヶ月後でも読めるソースコードの書き方 - 何気に大変
この記事の中で、「読みやすいコード」の要件として「コメントが少ない」というのがあってちょっと気になりました。

役に立たないコメントを残すと後々害になる

結論としては、言っていることは妥当で、コメントが「少ない」っていうよりは
「役に立たないコメントを書かない」ことが大事だって言いたいんだと思いました。

名前を適切に付ければコメントはかなり減らせると思います。
コメントというものは得てしてほとんど(あるいはまったく)メンテされません。
メンテされないコメントほどアテにならないものはありません。
いつでも動いているコードの方が正しいので
「コメントがアテにならないならコードをコメントにすればいいじゃない」というわけです。

僕が見たコメントの中で嫌だなと思ったのは、

単純で簡潔な処理なのに１行ずつ丁寧に処理を説明するコメント

z = x + y #変数zに引数の合計を代入する

とか、いくら何でも「それはわかってる」って思うもの。
これはコメントと本当のコードで2倍に行数が増えるだけの無駄だと思う。

コードの変更履歴を残す為のコメントアウト

これひどいｗｗ

def foo(x, y):
    # modified at 2005/07/08 by da.re.ka
    # return x * y
    return x ** y

こういう、前のコードを残しておくコメント。
僕がJavaとかストアドプロシージャのコードを触るお仕事をしていた頃、
これがどんどん増えていって本当のコードの数倍の行数を変更履歴コメントが占めていたことがありました。
当時、あまりにも処理が理解しづらくてわざわざコメントを消すスクリプトを書いた覚えがあります。

いまどきこんなばかなことをする現場はないと思うけど、
こういうのはバージョン管理システムを使って管理して欲しいですよね。

他人に自分の意図を伝えるためのコメントは大切

逆にコメントを付けるべきところは、
カリカリにチューニングしていて一見してなにをしているのか分からないコードや、
理由があってわざと効率の悪い方法や無駄なことをしているところなどです。
後者などは後々リファクタリングの名のもとに削除されかねませんので、
コメントを付ける意味があります。

これもまったくその通りだな〜って思いました。
僕も変に見える処理を書いたときは意図をコメントに残します。

docstringからdoctest

doctestのコンセプトを翻訳があったのでちょっと目を通しました。
Bitbucket | The Git solution for professional teams

docstringには関数やクラスの概要を書くけど、その中にUsageを書いておくと役立つという話。
そしてそれが実際にテストとして実行可能だとなお良しというのがdoctestのコンセプトみたいです。

昨日僕が読んでいたActive StateのPython Recipeも、doctestが書いてありましたね。
Temporary substitution of object in modules via with statement « Python recipes « ActiveState Code

    Example::

        >>> from StringIO import StringIO
        >>> capture = StringIO()
        >>> with substitute('sys.stdout', capture):
        ...     print('foo')
        >>> capture.getvalue()
        'foo\\n'

こういうやつですね。
これはちゃんと

if __name__ == '__main__':
    import doctest
    doctest.testmod()

こういうのを書いておくとテストとして実行されるんです！
すごい！

ただ、doctestに何でも書くとドキュメント部分も本当の処理の部分も読みにくくなる上、テストがむしろやりにくいという難点もあります。
僕の今までの経験と見聞してきたことから判断すると、

境界値テストとか、マトリクスを作って沢山のパターンを検証するテスト
mock, stub, dummyなどを使うテスト

こういうものは素直にunittestを書いた方がいいんじゃないかな〜と思います。

参考リンク

doctestの誕生についてを翻訳してみた - logging.info(self)

2011-11-13

今日の振り返り

自分のブログにツッコミを入れてくれる人がいて嬉しかった。
(逆にツッコミを入れてくれた人のブログにツッコミを入れたりして、ちょっとうざかったかも^^)
僕自身の備忘録みたいなものだけど、読んでくれている人っているんだな〜って思った。

今日はとにかく沢山ブログの記事を読んだ。
昨日もたくさん読んだ。
平日に大量のフィードを消化するのはきついから、やっぱり休日に時間取ってやりたいな。

日曜日といえばニチアサ大文字先輩大好き♡

あと、そういえば今日は日曜日でしたね。
日曜日といえばニチアサですよね☆
僕は仮面ライダーフォーゼがとても気に入っていて、毎週見ている。
http://news.2chblog.jp/archives/51653155.html
このまとめブログのとおり、魔女になりたいキャラの女の子がめっちゃかわいいです！

でも僕は大文字先輩の方がいいな^^
http://subcultureblog.blog114.fc2.com/blog-entry-4809.html

HaskellのSTM

Real World Haskell―実戦で学ぶ関数型言語プログラミング

作者: Bryan O'Sullivan,John Goerzen,Don Stewart,山下伸夫,伊東勝利,株式会社タイムインターメディア
出版社/メーカー: オライリージャパン
発売日: 2009/10/26
メディア: 大型本
購入: 8人クリック: 245回
この商品を含むブログ (76件) を見る

この本を買った。
理由は、STMの事を調べたかったから。
Haskellは理系過ぎて僕には理解できない。たぶん僕がマスターするのは無理だろう。

で、HaskellのSTMっていうか、STMはさっきPostgreSQLで出てきたMVCCと同じ仕組み。
Haskellの場合、

atomicallyという関数に渡すとそこで競合が起きたら劣後するスレッドは最初から処理をやり直す。
retryという関数を呼ぶとatomicallyの中の処理が全部終わるのを待たずに競合の有無を調べる。
orElseという関数を呼ぶと競合していたときに他のことをさせられる。

ここまで調べてやめた。
(難しすぎるので「ちゃんと理解しよう」とか「たくさん覚えよう」とか目指すと死ぬから)

本を読んでいて僕がわかっていないと思ったことは、MonadPlusってものが何なのかってことだった。
何なんだろうねこれ。
STMはMonadPlusをimplement(Java的に言うと)しているっぽいことが書いてあった。

2011-11-13

PostgreSQLのVACUUM

PostgreSQL

本屋さんに行ってPostgreSQLの本を探した。

PostgreSQL徹底入門第3版

作者: 笠原辰仁,北川俊広,坂井潔,坂本昌彦,佐藤友章,石井達夫
出版社/メーカー: 翔泳社
発売日: 2011/02/02
メディア: 大型本
購入: 3人クリック: 28回
この商品を含むブログ (6件) を見る

この本を買おうかな〜と迷いつつ、とりあえずVACUUM機能のところだけ読んで棚に戻したww

追記型・多版型同時実行制御(MVCC)

PostgreSQLは追記型のデータ管理をしている。
Wikipediaを見れば書いてあるけど、つまり更新を行う際、
ひとつのレコードをそれぞれのトランザクションで共有するのではなく、
全部別々のレコードとして保持しておいて、最も正しい状態のものだけをアクティブにしておくやり方。
(古い更新データは全部削除マークをつけて管理する。)
これをMutiple Version Concurrency Control(多版型同時実行制御)というらしい。

VACUUMはゴミデータをゴミ箱に移動させるイメージ

こういう風に何でも物理的に消去しないとどんどんデータファイルは肥大化して、
削除マークがついたがいっぱい入っている状態は無駄なので、
VACUUMという文を実行してFree Space Map（FSM）という領域に返す。
FSMに入ったデータはゴミなので、普通にSELECT文を実行したときとかは引っかからない。
FSMはデスクトップOSの「ゴミ箱」みたいなものみたい。
ただ、ちょっと違うのは、次から更新が入ってきたときはまずFSMからメモリを取るらしい。

FSMを解放する為にはVACUUM FULLかCLUSTER文を実行する

FSMに占有されたメモリが大きくなりすぎたらVACUUM FULLかCLUSTER文を実行してOSにメモリを返す。
VACUUM FULLとCLUSTERはどちらもFSMをクリアして、かつインデックスを再作成する。
両者の違いはCLUSTER文には最初の1回めはインデックスの名前を指定すること？
(ごめんなさい、この辺いい加減な理解です..)

ちなみにインデックスを再作成するだけならばREINDEXという文がちゃんとある。
あと、VACUUM FULLもCLUSTERも、実行中はそのテーブルに対して排他ロックをかけるっぽい。
(SELECT文さえも実行できないということ)

最近のPostgreSQL(Ver.9)は自動VACUUM機能がある

昔のPostgreSQLはVACUUMを手動で実行しなければならなかったらしい。
でも最近のバージョンでは設定ファイルに閾値を定義しておけばそれを超えたときに自動でVACUUMしてくれるそうだ。
あと、昔はスキーマ全体でひとつのFSMを持っていたけど、テーブルごとにFSMを持つよう改められたらしい。
[ThinkIT] 第3回：VACUUMの活用によるチューニング (1/2)

最近のPostgreSQLはレプリケーションもできるようになったらしい。
あと、昔からPostgreSQLはxmlを列として保持して、SELECT文でxpathで検索できるんだって！
MySQLばっかり使っていたけど、これは熱い！覚えよう！！

僕がとってもためになるな〜と思ったページ

http://www.geocities.jp/sugachan1973/doc/funto60.html
ここのページにPostgreSQLの仕組みがとっても詳しく解説されている。
何か、著者の人は自分のことを「事務員さん」って言っているけど、本当なのかなぁ？
プログラマなんじゃないかって思ってしまうくらい丁寧に、解らないことがあったら全部調べて解説してくれている。
職業はプログラマじゃなくてもプログラマの魂は持っている人なのかもしれない。

2011-11-12

contextlibとかimportとかfunctoolsとか

Python

Temporary substitution of object in modules via with statement « Python recipes « ActiveState Code
Active StateのPython Recipesですごく勉強になるレシピを見つけた。
これは、with文で任意のモジュールの属性を一時的に書き換えてしまう例。

このレシピを読むと、

contextlib, with文の利用例
__import__ マジックメソッドの使い方
functools.partialの使い方

が一度に学べる(学ぶきっかけが得られる？)。
これはとてもお得！

contextlib: with文を活用する

「コンテキストマネージャ」というものを作ってwith文で使える。
組み込みのコンテキストマネージャもいくつかあるみたい。
超有名なのはclosingかな♡

よくファイルとかデータベースのコネクションのcloseメソッドをtry-finallyブロックを使って呼ぶけど、それをwith文で書けるのがclosing。
http://www.python.jp/doc/nightly/library/contextlib.html#contextlib.closing

with closing(open("takumi.txt", "r")) as f:
    print f.read()

必ずcloseが呼ばれます☆
こんな風に、with文をうまく活用するための仕組みがcontextlibモジュールみたい。
Python Recipeの例では、モジュールの差し替えをやらせている。

importマジックメソッド

http://d.hatena.ne.jp/perezvon/20080129/1201623922
ここでは__import__のことが詳しく考察されているけど(ちょっと僕のレベルだと意味が解らない..)、

__import__を使うとモジュールの名前を文字列で渡してインポートできる。
インポートは最初の１回やったら再度ロードし直さない。
でもreload関数を使うと何度もインポートし直せる。

ってことは判った。
それで、Python Recipeでは

def _findattr(mod, rest):
    parent, dot, rest = rest.partition('.')
    if rest:
        return _findattr(getattr(mod, parent), rest)
    else:
        return mod, parent

def find_in_module(fullname):
    modname, dot, rest = fullname.partition('.')
    module, objname = _findattr(__import__(modname), rest)
    return getattr(module, objname), partial(setattr, module, objname)

こういう風に、モジュールをインポートして望みの属性に辿りつくまでgetattrを繰り返すんだね。

functools.partial: 引数の部分適用をする

pythonの関数は第１級オブジェクトなので、関数を引数に取ったり関数を返す関数を作ったりできる。
そして、関数の引数を部分適用した関数を作ることもできる。

例えば、

def foo(x, y):
    return x + y

import functools
bar = functools.partial(foo, 1)
print bar(8) #=> 9

こんな感じかな。
このコードのbarみたいに、fooの第１引数を１で固定した別の関数を作るっていうのが部分適用。

Python Recipeでは

partial(setattr, module, objname)

こんなことをしている。
最初見たときすぐには意味が解らなかったんだけど、settattr関数は「オブジェクト・属性名・属性値」の順に引数を取って、対象のオブジェクトの属性に代入を行うので、「オブジェクト・属性名」まで束縛した新しい別の関数を作っているってことなんだね。
そして後ろの行でpartialで作った関数に差し替えたい値を渡している。

それと、文字列にpartitionっていうメソッドがあることも初めて知った。
色々ためになるな〜と思った。

2011-11-12

pep8とpyflakesのvimプラグインを入れた

Python vim

pythonのプログラムを書いているとき、pep8とpyflakesはかけておきたいと思うことがあります。
pep8はあくまで形式を規格化したものだけど、これを守れというモヒカンはけっこう多いようです。
pyflakesは使っていない変数とかインポートを検出して指摘してくれるので割と実用的です。

それで、vimにプラグインがあることは知っていたけど、今まで面倒くさがっていれていなかったので、今日入れてみました。

pep8用のプラグイン: vim-pep8

githubにあります。
GitHub - nvie/vim-pep8: This project is superseded by vim-flake8!

導入はこんな感じでやりました。

$ cd ~/.vim
$ git clone https://github.com/nvie/vim-pep8.git
$ mkdir ftplugin; cp vim-pep8/ftplugin/python_pep8.vim ftplugin

vimもemacsも使わないでいつもPyDevをただのエディタとして使っているのでよくわからないのですが、
python用vim設定 - ラシウラ
ここを参考に、.vimrcとpython.vimを編集したら、ちゃんと*.pyを編集中にF6を押すとpep8を実行してくれるようになりました。

このスクリーンショットだと警告なしだけど、警告がでたときはqを押すと警告リストから抜けられるみたいです。

pyflakes用のプラグイン: vim-pyflakes

これもgithubにあります。
GitHub - nvie/vim-pyflakes: This project is superseded by vim-flake8!
これもgitのリポジトリが違うだけ(以下)で、ftpluginの下にプラグインファイルを置いておけばいいみたい。

$ git clone https://github.com/nvie/vim-pyflakes.git
$ cp vim-pyflakes/ftplugin/python_pyflakes.vim ftplugin

こっちはF7で起動するみたいです。

今までコマンドラインからpep8とかpyflakesを手で実行していたけど、
やっぱりエディタのプラグインにしておくと便利ですね。

追記: vim プラグインは他にもあるらしい

トラックバックを頂いたid:heavenshell さんの記事によれば、僕が入れたものとは違うプラグインがあるみたいです。
http://d.hatena.ne.jp/heavenshell/20111113/1321180937

何か、僕が入れたものはF6、F7を押すと動いて、qで閉じるんですけど、単純にカラーリングしてくれるものがあるようです。
(こっちの方が使いやすいかも…)
でもまあ、僕はちょっとしかコーディングしないのでここに書いたvim-pep8とかでもいいかなって思います。

参考リンク

vimでpep8を実行 - yuitowest's blog
Vimで文法チェック (python編) - Study03.net 対シンバシ専用
 python用vim設定 - ラシウラ

仕事のこと

役に立たないコメントを残すと後々害になる

単純で簡潔な処理なのに１行ずつ丁寧に処理を説明するコメント

コードの変更履歴を残す為のコメントアウト

他人に自分の意図を伝えるためのコメントは大切

docstringからdoctest

参考リンク

日曜日といえばニチアサ 大文字先輩大好き♡

HaskellのSTM

追記型・多版型同時実行制御(MVCC)

VACUUMはゴミデータをゴミ箱に移動させるイメージ

FSMを解放する為にはVACUUM FULLかCLUSTER文を実行する

最近のPostgreSQL(Ver.9)は自動VACUUM機能がある

僕がとってもためになるな〜と思ったページ

contextlib: with文を活用する

__import__マジックメソッド

functools.partial: 引数の部分適用をする

pep8用のプラグイン: vim-pep8

pyflakes用のプラグイン: vim-pyflakes

追記: vimプラグインは他にもあるらしい

参考リンク

日曜日といえばニチアサ大文字先輩大好き♡

importマジックメソッド

追記: vim プラグインは他にもあるらしい