「複数行に同じ処理」を一瞬で終わらせるアプローチ
はじめに
泥臭い系の話です。
結論、正規表現とsedを使えると効率化がめちゃくちゃ捗ります。
具体的な事例
例えばこんなケース:
- 特定の顧客の売上金額をDBから集計したい。
- DBにはsalesテーブルがあり、顧客IDと売上金額が記載されている。
- 特定の顧客リストはSpreadsheetにリスト化されている。
まずはこんなクエリを書きます。
SELECT
customer_id, SUM(price)
FROM
sales
WHERE
customer_id IN (
--- ここに顧客IDを入れる
)
GROUP BY
customer_id
課題 : INの中どうしよう…
クエリの文法に則ると、単純にリストをコピペするだけではできません。
まず、各顧客IDの末尾に「,」が必要です。
さらに面倒なことにcustomer_idは文字列型のため、各顧客IDをクオテーションで囲む必要があります。
つまり、以下のような形で記載します。
SELECT
customer_id, SUM(price)
FROM
sales
WHERE
customer_id IN (
'hoge',
'fuga',
'piyo',
'foo',
'bar',
...
)
GROUP BY
customer_id
数行ならさておき、顧客リストが数百、数千、数万件となったら、
一つ一つ書いていくのは現実的ではありません。
なので、いくつかのアプローチを検討します。
アプローチ1. Sublime Textのエディタの機能を使う
一瞬でできるのでおすすめです。
※Sublime Textは軽さがウリのテキストエディタです。
- SpreadsheetからリストをコピーしてSublime Textに貼り付け
- command + A (全選択)
- Shift + command + L (全行がそれぞれ選択された状態になります)
「'」を入力 (全行をクオテーションで囲みます)- command + → (全行で行末に移動)
「,」を入力 (全行末にカンマを打ちます)
※ショートカットキーはMacの場合
アプローチ2. Spreadsheetで文字列連結する
こちらはシンプルです。
Spreadsheetの顧客IDリストの隣の列に
="'"&A2&"',"
のような関数を入れ、リスト全行にオートフィルしてから、その列全体をコピペします。
アプローチ1と同じくらいあっさりできますが、
個人的には全行選択が使いやすいのでアプローチ1でやることが多いです。
アプローチ3. sed + 正規表現で書き換える
アプローチ1も2も、数千行までは特に問題ないのですが、
件数が増えて数万〜数十万規模になると、SpreadsheetやSublime Textの動作が遅くなったり、固まったりしてしまうことがあります。
そのような場合、ターミナルからsedコマンドを使う方法もあります。(VM環境などでLinuxを使えるならそちらでも可)
例えば今回だと、Spreadsheetから顧客IDリストをコピペしてテキストエディタで保存したあとに、
以下のようなコマンドを実行し、結果をクエリに使います。
# パターンA. クオテーションとカンマを追記したテキストファイルを書き出し
$ sed -r "s/^(.*?)$/\'\1\',/" [filepath] > [new_filepath]
# パターンB. もしくはファイルを直接書き換えるなら-iコマンドを付加
$ sed -i -r "s/^(.*?)$/\'\1\',/" [filepath]
簡単に解説すると、
- -r オプション : 正規表現を使用する
- “s/xxx/yyy/” : xxxに正規表現パターン、yyyに置換結果を書く
- xxx部分 :
^(.*?)$: 各行の頭から、最短の行の末尾までを取得 - yyy部分 :
\'\1\',:\1で前項で取得した値を出力(()で囲まれた部分)。クオテーションで囲んでカンマが最後につくように出力。
という構成です。
※そもそも数万行のクエリを書くべきかという話もあるのですが、今回は割愛…
注意
念の為、今回紹介した内容だと厳密にはクエリにコピペしたのち、
最後の行の「,」は削除しておく必要があります。(でないと構文エラーになります)
まとめ
以上、かなり細かい話をしました。状況に応じて使い分けましょう。
こういう作業はなるべく少しでも簡単にできるように工夫していきたいところです。
アプローチ3で使ったsedと正規表現は、少しでも使えるようになっておくと便利なので覚えておきたいところです。
他にいいやり方があればぜひコメントなどで教えていただけると嬉しいです。