2014/09/22 SQL 学習用の参考サイトを申し訳程度に追加
要約
今回の記事は
- プログラミングをあまりしたことのない (文系の) 学生で
- 卒論 (もしくは修論も?) で回帰分析など, 定量的な研究をしてみたいと考えている
人を読者層に想定して書いた. では何を書くかというと,
- ウィンドウズのDOSコマンドで簡単に分析用のデータを整形する方法
になる. 残念ながら研究テーマや具体的な分析手法は教えられない. しかし, 卒論では授業の課題で出されるような, きちんと整形されたデータを Stata や SPSS に放り込み, ダイアログボックスで最小二乗法を指定するだけ, と言うふうにはいかないだろう. 自分でデータを見つけてきて, 整形する必要がある. この整形の過程が結構時間がかかることがある. 例えば,
- サンプルサイズが数万, 数十万, ……とあるデータ
- PDFやHTMLファイルに掲載されている, 綺麗に整列していない数字の羅列
- データが複数のファイルに分割されている
ような場合, エクセルだけで統計ソフトが読み込める構造のデータにするのは骨が折れる. R や SAS なんかは分析だけでなくデータを読み込んで整形する作業もできるのだが, 今回はそういうソフトも使ったことがない, と言う人向けである.
DOS コマンドと UNIX シェル
UNIX シェルというものがある. Mac や Linux で使えるスクリプトである. 複雑な作業を1行の命令文に収めることに命を懸ける人たちを, 「シェル芸人」という.
マウスも使わず、ソースコードも残さず、GUIツールを立ち上げる間もなく、あらゆる調査・計算・テキスト処理をCLI端末へのコマンド入力一撃で終わらすこと。あるいはそのときのコマンド入力のこと。 (シェル芸の定義バージョン1.1より)
この引用にあるように, UNIX,シェルはテキストファイルを加工するのに便利なスクリプトである. 最近はマックユーザーも増えてきたが, UNIXシェルはネット上の情報がすでに質・量ともに充実しているため, 今回はあえて, UNIX.シェルに対抗して, ウィンドウズのコマンドプロンプトを使ってシェル芸のようなことをしたいと思う. そこで, 以降, これを DOSを芸と呼ぶことにする. とはいえ本家のシェル芸人の業に比べれば非常に初歩的なことばかりなので, 初心者は安心して欲しい.
UNIXシェルに比べてDOSコマンドの情報が少ないのは, サーバに利用されるOSが Windows より Linux のほうが圧倒的に多いこと, UNIXシェルには強力な "sed" と "awk" があり, DOSにはないということが大きいと思う. ただし, sed, awk はGNU版をウィンドウズにインストールすることができる. 厳密にはDOSコマンドではないため邪道だが, 初心者を対象としているので簡便さを優先する.
下準備
実際にやって見るため, 以下のサンプルデータをそれぞれ, test1.csv, test2.csv, test3.csv ファイルとして保存する.
test1.csv
"AA",2 "Ba",2 "AB",11 "C2",3 "DB",4 "EE",5 "F1",6
test2.csv
"Aa",10 "Ba",21 "Ca",6 "Da",20 "Ea",51 "Fa",53
test3.csv
"A3",1 "B3",2 "CA", "d3",a "e1",5 "F1",0
保存先は, 特に指定しないが, とりあえずマイドキュメントにしておく.
コマンドプロンプトを立ち上げる
スタートメニューから, コマンドプロンプトをクリックするか, cmd.exe
と打って起動する.
次に, cd
コマンドでフォルダをマイドキュメントへ移動しよう.
cd フォルダパス
Tabキーを押すと自動でフォルダ名を補完してくれることがある.
あるいは別のパーティションやドライブに保存したなら,
cd /d フォルダパス
としなければならない. /d
はオプションで, 他のコマンドでも一般にオプションはスラッシュ+文字と言う形で書く.
また, フォルダ名にスペースが含まれている場合は,""で囲む必要があるので要注意である.
ここでフォルダを移動したのは, そこで作業をする, ということをパソコンに指示するためである. こうすることで, これ以降ファイルを指定する時にわざわざパスを書く必要がなくなり, ファイル名を書くだけでよくなる.
ファイルをコマンドプロンプトに表示する
type
コマンドでファイルの中身を表示できる.
type test1.csv
とは言え,これだけでは大したことはできないだろう.
行を抜き出す
特定の値を持つ行だけを抜き出す方法を紹介する. test1.csv から "A1"
の行だけを抜き出すには, findstr
を使う.
findstr "A1" test1.csv
findstr
は, 行単位で検索するという機能のため, 行列状のデータを加工するのに向いている.
指定するファイルは複数でもよい: test1.csv, test2.csv からそれぞれ抜き出すには,
findstr "A1" test1.csv test2.csv
とする. さらに多くのファイルを指定する場合,
findstr "A1" test*
とすると, test
で始まるファイル全てを対象とする.
また, findstr にもオプションが存在し, 例えば
findstr /I "a" test*
とすると, "a" だけでなく "A" も抜き出す. つまり大文字小文字を区別しないオプションである. また, findstr
は基本的に正規表現を使う*1. 正規表現を使わず検索したい場合は, /L
オプションを付ける. 今回は正規表現であってもなくても結果が同じになるが, 正規表現を使ってないということをわかりやすくするため, 以降は正規表現を使わない場合には /L
と書いておく.
findstr /I /L "a" test*
その他のオプションについては,
help findstr
とすることでヘルプを見ることができる. findstr
以外でも help
コマンドは使用できる.
間違ったデータを排除する
より実用的な例を紹介する. サンプルのcsvファイルでは, 2列目を数値にすべきところが文字になっている行がある. これは打ち間違いであり,そのまま読み込むとエラーの原因になる. よって, findstr で排除してしまおう.
findstr ",[0-9]" test*
これは正規表現を使った検索で, 2列目が数値になっている行だけを抜き出している. 逆に,
findstr ",[^0-9]" test*
とすると, 数値になっていない行が抜き出される.
ただし, csv の列の数が増えると正規表現も複雑になるので, 行列を意識した操作は awk
やあとで紹介する Q のほうがやりやすいかもしれない.
結果を保存する
findstr
で抜き出した結果は, コマンドプロンプト上に表示されるだけである. これをコピペして保存するのも面倒だし, 処理するファイルの行が多くなると大変なことになる. 抜き出した結果をファイルへ直接保存する方法は簡単である.
findstr /L "A1" test* > result.txt
である. >
はリダイレクトと呼ばれる. これを実行すると, 今度はコマンドプロンプトに結果が表示されない代わりに, フォルダに result.txt というファイルが作られ, その中に結果が書かれている. これは, コマンドプロンプト画面の代わりに指定したファイルに結果を「リダイレクト」するという命令である.
リダイレクトにはもう1つ, >>
というものがある.
findstr /L "A1" test* >> result.txt
は, リダイレクト先のファイルの末尾に結果を書き込む, という命令になる. よって, result.txt の中身は同じ結果が2回繰り返されているはずである. なお, >
はファイルを上書きするため, >
でリダイレクトを何度繰り返してもファイルの中身は変わらない.
パイプ
次に, リダイレクトに似た, パイプの概念を紹介する. パイプは, リダイレクトのように結果をファイルに渡す代わりに, 別のコマンドに渡す. つまり, 例えば findstr "A1" test1.csv
で得られた結果を, また別のコマンドにインプットし, さらに加工できるのだ. 例えば, 今回のサンプルファイルではデータの並びに規則がない. そのため, 抜き出した結果も雑然としている. これをソートして見やすくしよう. 行をソートするコマンドは sort
である.
findstr /L "a" | sort
sort
コマンドは本来, sort ファイル名
というふうに入力ファイル名を書かなければならないが, パイプを使っった場合は入力を渡しているため, 書かなくても良い.
しかし,この方法では行の先頭の test1.csv:... という部分も文字の一部として見られるので, 意図したとおりに並び替えられないし,この結果をそのまま保存しても,正しいcsvファイルにならない. findstr
にも先頭のファイル名を非表示にする機能はないため,別のコマンドが必要となる. そこで,この問題を解決するために sed をインストールする.
sed を使う
sed
はもともと UNIXのコマンドなので, 邪道だと感じるかもしれない. しかし今回は初心者向けの記事であり, より簡単な方法を紹介することがより重要である.
ウィンドウズ版 sed をここの
Complete package, except sources
という行から .exe ファイルをダウンロードし, インストールする. このとき, インストールした sed.exe
のあるフォルダのパスを覚えておこう. Windows 7 ならば, 普通は C:\Program Files (x86)\GnuWin32\bin
になると思う.
次に, 環境変数を設定する. エクスプローラから「コンピュータ」を右クリックし, 「プロパティ」を選択する. 左に「システムの詳細設定」があるので開き, 「詳細設定」タブにある「環境設定」を押す. ここから,システム環境変数の Path
を編集し, 末尾に ;
を書き加え, その後ろにさらに sed.exe
をインストールしたフォルダパスを書き加える. このとき, すでに書かれている Path を消さない用に注意する. 書き換えたら,環境変数はその直後から有効になるので,一旦コマンドプロンプトを閉じて実行し直す.
なぜ環境変数を書き加えるのかというと, それは最初に cd コマンドでフォルダを移動したのと同じで, これを登録することで sed コマンドを実行した時に OS にどこに .exe ファイルがあるかを教えるためである. path に書かなかった場合, 実行のたびに "c:\program files\...\sed.exe" ....
というふうに書くか, .exe ファイルのあるフォルダに移動して使わなければならないので面倒である. すでに書かれている環境変数は, 何か別の .exe ファイルの場所を教えるために書かれているので, これを消してしまうと他の何かを実行した時に問題が発生するので, 消さないように注意しなければならない. セミコロンは環境変数に書かれている複数のフォルダパスを区切るために必要である.
環境変数の設定には『Windows 環境変数 Path の設定方法』なども参考にすると良い.
sed
も findstr
同様, 行単位で検索と置換をするため, 結果をイメージしやすい*2. awk
も行単位で処理するコマンドだが, 改行コードと認識する文字を自由に変えられたり, 条件分岐を組み込めたりするので, より複雑な構造のデータを処理することもできる. しかし,今回は話が冗長になるので, awk は使わない*3.
sed
を findstr
と sort
の間に挟めば, 先ほどの問題は解決する.
sed -e "s/a/b/ 入力ファイル名"
という風にすると, 入力ファイルの各行ごとに, 最初の "a" を "b" に置き換えたものを出力するので,
findstr /L "a" test* | sed -r -e "s/^.+\.csv://" | sort
とすることで, 各行の先頭のファイル名を削除できる. 2列目が文字になっている行を排除するコマンドも同様に,
findstr ",[0-9]" test*| sed -r -e "s/^.+\.csv://"
というふうに書ける.
sed
はもともと UNIX で使われていたので, オプションはスラッシュ /
ではなく ハイフン -
をつける. -r
は拡張正規表現 (ERE) を使うオプションで, -e
は「その直後に正規表現の置換ルールを記述した」という意味がある. 正規表現や sed の使用法についても, 詳しく書くと長くなるため, Wikipedia の記事やsed | テキストの置換,正規表現のメタ文字や後方参照の方法など などを参考にしてほしい. Wikiの方に書いてあるように, Perl の正規表現とはまた異なるので, SRE もしくは ERE の正規表現を解説しているところを見たほうが良い. ただし, 2つ目のリンク先にも若干紛らわしい表現があり, 「"+"や"(" をエスケープして使う必要がある」というのは -r
を使わない場合の話しである.上の例でも\+
と書かずに +
と書いている.
この結果をファイルに出力したいなら, これまでどおりに
findstr /L "a" test* | sed -r -e "s/^.+\.csv://" | sort > result.csv
でいい.
Q を使う
もし SQL を知っている or 勉強する予定があるのなら, Q という便利なプログラムがある. Qについての日本語の情報は UNIX/Linux環境でcsv/tsvにクエリを投げることができるqが便利すぎて生きるのが辛い がある. リンク先では Linux で使用しているが,基本は同じである.
Windows版も同様に, コマンドプロンプト上で q "SQLクエリ文"
と書くだけで SQL を実行してくれるコマンドである. GitHub の harelba/q からダウンロードしたWindows版のインストーラを実行し, sed のときのように path を書き足すだけで使用できる. python 2.5 以上のインストールが必要なのだが, なぜかインストールしなくても起動できた. Windows版はpython なしでも動作する?
Q は SQLITE を用いているので, サブクエリが使えないなど機能に一部制約があるが, SQLに慣れた人間にとってはかなり使い勝手が良いと思う.
SQL を知らない人間にとっては投げっぱなしになるので, SQL 学習に役立ちそうなサイトを紹介しておく
- ひと通りの命令文は SQL講座|Smart で.
- 結合関係は, 初見では抽象的でわかりにくいので, Visual Representation of SQL Joins を見るとよい. こちらは英語だが, ベン図でSQLの結合概念を説明しているので分かりやすい.
参考文献
- 上田ブログ 『シェル芸の定義』
- GNU (2010) "sed for Windows"
- Someone in the TERRA (2014) 『Windows 環境変数 Path の設定方法』
- Wikipedia 『正規表現』
- SUN jianqiang (2008) 『sed | テキストの置換,正規表現のメタ文字や後方参照の方法など』 バイオインフォマティクス
- harelba "q" GitHub
- Serima Blog (2014) 『UNIX/Linux環境でcsv/tsvにクエリを投げることができるqが便利すぎて生きるのが辛い』
- rhythmfactory Ltd. 『SQL講座|Smart』
- Christopher-Moffatt "Visual Representation of SQL Joins" Code Project