Top
1GB超のCSVファイルを編集する(postgreSQL編)
2013年 03月 28日 *
とりあえずMySQLはサブクエリが遅いため使用を断念し、次にフリー系で有名なpostgreSQLを使ってみる。
そもそも読み方がわからないこのソフトだが、重要なのはやりたい事ができるかどうかだ。


postgreSQLをインストール

postgreSQLをダウンロードし、インストール画面で適当にOKをクリックしていくとインストールは完了する。
あとはC:\Program Files\PostgreSQL\9.2\binフォルダにあるPSQLをコマンドプロンプトで起動すれば使えるはずだ。
これはMySQLのノリと同じである。
しかし、スーパーユーザーでログインする最初の場面でつまづいてしまった。
色々調べていくと、どうやらユーザー名はrootではなく慣習的にpostgresになるらしい。
これはわからなかった…。
あとデータベースを作成し、テーブルを作成したものの、それがどこにあるかがわからなかった。
色々調べたところ、どうやらスーパーユーザーは何も指定しないとpostgresというデータベースを使用するらしく。
自分の作ったテーブルはその中に保存されていた。

CSVをインポート

postgreSQLのコマンドはMySQLとは少し異なる部分がある。
MySQLで通用したSQL文がpostgreSQLでは通用しなかったりする。
SQLの解説サイトにあるSQL文がそのまま使えないこともあるので、その場合は若干修正が必要となる。

CSVのインポートはこのコマンドで実行できた。

COPY テーブル名 FROM 'csvの保存場所' WITH CSV;

うまくいくと、次のようなメッセージが表示される。

クエリーは、成功しました: 5293104 行の影響があり, 実行時間は、63570 ミリ秒でした。

インポートにかかる時間はMySQLよりも若干はやかったが、ほとんど変わらない感じ。

select * from テーブル名 limit 5;

このコマンドも叩いたと同時に結果が表示され、MySQLと変わらない。
次に並び替えを行った場合の時間を計測するため、下記のコマンドを入力。

select * from テーブル名 order by 列名 limit 5;

結果は下記のようになった。

クエリ全体 実行時間:3580 ms. 5 行検索しました

約3秒。MySQLと比較して若干速い結果になった。

それではお待ちかね、MySQLの苦手?なサブクエリを叩いてみよう。
コマンドはMySQLと同じ。

select * from テーブル名 where 列名 in (
select min(列名) from テーブル名 group by 列名
)
order by id limit 5;

結果は以下のようになった。

クエリ全体 実行時間:38400 ms.5 行検索しました

おお、わずか1分弱でちゃんと結果が返ってきているではないか!
MySQLは30分経っても返ってこなかったのに。
やはりpostgreSQLは使える!

と、まずまずの結果に満足だったが、連番の振り方はどうすればいいのだろう。
MySQLではユーザー定義関数を使用することで並び替え後の連番の振り直しが実現できたが、postgreSQLで同じコマンドを叩いてもエラーになってしまう。
仕方なく別の方法を探すことにした。

調べると下記の方法でテーブルの列に連番を振れることがわかった。

一つ目は相関サブクエリを使う方法。
二つ目はROW_NUMBER()を使う方法。

相関サブクエリが何であるかは解説サイトを読んでもわからなかったが、重複データに対してちゃんと連番が振れるかどうか不安に感じた。
例えば成績テーブルから得点順に並び替えて連番を振ることを考えた時に、100点の人が2人いると2人とも順位は1位という状態になるのではないだろうか。
現実世界ならそれでOKだが、自分が欲しい結果は、2人いる1位をどうにかして1位と2位に区別して番号を振りたいのだ。

相関サブクエリを使用した場合に得られる結果(予想)
(順位、得点、名前)
1,100,Aさん
1,100,Cさん
3, 98,Eさん
4, 96,Bさん

自分が欲しい結果
(順位、得点、名前)
1,100,Aさん
2,100,Cさん
3, 98,Eさん
4, 96,Bさん

あくまで予想のレベルだが、得点が一意であるテーブルなら相関サブクエリを使用して連番を振ってもいいが、得点が一意でない場合はもう一工夫、回避方法が必要な感じ。

それでは2番目の方法はどうか。
postgreSQLにはROW_NUMBER()という便利な関数が用意されている。
これをそのまま列にコピーするSQLが書ければ目標は達成できる。
しかし、下記のようなコマンドを叩いてもエラーになってしまう。

update テーブル名 set 列 =ROW_NUMBER();

これについて調べてみると、以下のように相関サブクエリ?を使用することで回避できることがわかった。

update テーブル名 set 列 =
SeqTbl.列3
FROM (SELECT 列,
ROW_NUMBER() OVER (ORDER BY 列2) AS 列3
FROM テーブル名) SeqTbl
WHERE テーブル名.列 = SeqTbl.列;

列には連番を振る列を指定。
SeqTblは適当に付けられている仮の名前なので好きに付けていい。
列3も好きな名前でOK。
列2には並び替えしたい列を入力。

そして結果は…

クエリーは、成功しました: 5293104 行の影響があり, 実行時間は、889959 ミリ秒でした。

所要時間は約15分…。待てない時間ではないが、連番を振る動作に関してはMySQLの方が速い。
これなら並び替えをしたCSVファイルをエクスポートしてMySQLで連番を振った方がトータルだと早い。
まあ邪道ではあるが…。
[PR]
by nochoice1 | 2013-03-28 10:58 | Comments(0) *
<< 1GB超のCSVファイルを編集... ページトップ 1GB超のCSVファイルを編集する >>
The Original by Sun&Moon