まえおき
データメンテなどでスプレッドシートをもとにデータメンテ用のSQLを作る場合があります。
その場合元となるスプレッドシートからデータメンテ用のSQLを作ろうとしても、 スプレッドシート力（≒エクセル力） が足りずに余計に時間がかかるぅぅぅRubyだったら速攻なのにぃぃぃ…となってしまう、そんな私向けの記事です。
作るもの
たとえば、ユーザ毎にメールアドレスがあるシステムで、データメンテで削除するメールアドレスIDを👇のようなスプレッドシートでもらったとしましょう。
そこから以下のようなある程度行数の多いデータメンテ用のSQLを作りたいとします。
--
-- UserID: 100
--
-- 事前確認
select count(*) from emails
where id in (1,2)
and deleted_at is not null;
-- 更新確認
update emails set deleted_at = now()
where user_id = 100
and id in (1,2)
-- 事後確認
select count(*) from users
where id in (1,2)
and deleted_at is not null;
...次はUeriD: 101で繰り返す...
今回の例であればスプレッドシートだけでやるのもそれほど難しくないかもですが、これ以上複雑になると心が折れるのでこういうものはRubyでやりましょう。
やりかたとコード
めっちゃ単純です。以下のように元データをクリップボードにコピーして…
ただただCSV.newのデータのところに張り付けるだけ。なお、エディタや環境によって列のseparatorが異なるかおしれませんのでうまくいかない場合はcol_sepは環境に合わせて修正してください。ちなみに私が試したWindows11, Chrome+VSC環境だと、タブ区切りでセットされました。
コード
require 'erb'
require 'csv'
# スプレッドシートからコピペでデータセットできるように調整する
csv = CSV.new(<<~'CSV', headers: true, header_converters: :symbol, col_sep: "\t")
user_id email_id
100 1
100 2
101 1
102 1
102 2
102 3
CSV
# 扱いやすいデータ形式に変換
email_ids_by_user_id = csv.group_by { |r| r[:user_id] }
.map { |user_id, rows| [user_id, rows.map { |r| r[:email_id] }] }
puts ERB.new(DATA.read).result(binding)
__END__
<% email_ids_by_user_id.each do |user_id, email_ids| %>
--
-- UserID: <%= user_id %>
--
-- 事前確認
select count(*) from emails
where id in (<%= email_ids.join(',') %>)
and deleted_at is not null;
-- 更新確認
update emails set deleted_at = now()
where user_id = <%= user_id %>
and id in (<%= email_ids.join(',') %>)
-- 事後確認
select count(*) from users
where id in (<%= email_ids.join(',') %>)
and deleted_at is not null;
<% end %>
コード的にはシンプルで特筆すべきところは、DATA定数を使って1ファイルで実装しているところくらいでしょうか。でも、スプレッドシートの関数をこねくり回すより、Ruby脳の方のほうが早い方には参考にはなるのかも？
なお、このコードで使っているDATA定数はirbではつかえないので、面倒でもファイルに書き出して実行してください。
実行すれば以下のようなSQLを出力してくれます。
実行結果
--
-- UserID: 100
--
-- 事前確認
select count(*) from emails
where id in (1,2)
and deleted_at is not null;
-- 更新確認
update emails set deleted_at = now()
where user_id = 100
and id in (1,2)
-- 事後確認
select count(*) from users
where id in (1,2)
and deleted_at is not null;
--
-- UserID: 101
--
-- 事前確認
select count(*) from emails
where id in (1)
and deleted_at is not null;
-- 更新確認
update emails set deleted_at = now()
where user_id = 101
and id in (1)
-- 事後確認
select count(*) from users
where id in (1)
and deleted_at is not null;
--
-- UserID: 102
--
-- 事前確認
select count(*) from emails
where id in (1,2,3)
and deleted_at is not null;
-- 更新確認
update emails set deleted_at = now()
where user_id = 102
and id in (1,2,3)
-- 事後確認
select count(*) from users
where id in (1,2,3)
and deleted_at is not null;
おしまい。スプレッドシート難しすぎるんじゃー