エンジニアになったもののまだまだSQLに苦手意識があります。
先日業務で学んだ副問合せとwindow関数を使った一括アップデートの方法を、備忘録としてまとめます。
前提
以下のような社員（employee）テーブルがありました。
company_idは属するグループ会社のID
employee_numberはグループ会社ごとの社員ナンバーでhihre_date（入社日）を基準に採番されています。
demo=# select * from employee order by id asc;
id | company_id | employee_number | name | hire_date
----+------------+-----------------+---------+------------
1 | 1 | 1 | taro | 2008-04-01
2 | 1 | 2 | mihoko | 2011-04-01
3 | 1 | 3 | hanako | 2012-04-01
4 | 2 | 1 | takeru | 2013-06-01
5 | 2 | 2 | musashi | 2014-05-01
6 | 2 | 3 | hiroshi | 2012-04-01
7 | 3 | 1 | tomoki | 2015-04-01
8 | 3 | 2 | masako | 2018-04-01
(7 rows)
問題発生
エンジニアの更新ミスで、社員テーブルが以下のような状態となってしまいました。
本来、会社ごとの入社日順に採番されている社員ナンバーが全て1になってしまっています。
さてどのようなクエリをなげれば修正できるでしょう？
id | company_id | employee_number | name | hire_date
----+------------+-----------------+---------+------------
1 | 1 | 1 | taro | 2008-04-01
2 | 1 | 1 | mihoko | 2011-04-01
3 | 1 | 1 | hanako | 2012-04-01
4 | 2 | 1 | takeru | 2013-06-01
5 | 2 | 1 | musashi | 2014-05-01
6 | 2 | 1 | hiroshi | 2015-04-01
7 | 3 | 1 | tomoki | 2015-04-01
8 | 3 | 1 | masako | 2018-04-01
解決策
8件程度のレコードなら、手作業でhire_dateを見比べながら、employee_numberの値を手作業で更新することも出来るのですが、もし10000レコード等あった場合、さすがに無理です。
そんな時便利なのが、windows関数と副問い合わせです。
答えを先に書くと以下クエリで正常な状態に戻せます。
update employee
set employee_number = fix_emp.number
from (select
id,
row_number()
over (
partition by company_id
order by hire_date asc ) as number
from employee) fix_emp
where id = fix_emp.id;
実行後、確認すると。。
demo=# select * from employee order by id asc;
id | company_id | employee_number | name | hire_date
----+------------+-----------------+---------+------------
1 | 1 | 1 | taro | 2008-04-01
2 | 1 | 2 | mihoko | 2011-04-01
3 | 1 | 3 | hanako | 2012-04-01
4 | 2 | 1 | takeru | 2013-06-01
5 | 2 | 2 | musashi | 2014-05-01
6 | 2 | 3 | hiroshi | 2015-04-01
7 | 3 | 1 | tomoki | 2015-04-01
8 | 3 | 2 | masako | 2018-04-01
無事各社員は、会社ごとの入社日昇順のemployee_Numberに戻っていますね。
解説
ここで使っている技術がupdate set fromと副問合せと、制御window関数です。
以下順に説明します。
update set from
普通のupdateは以下のような構文となっています。
update 対象テーブル名 set 更新するカラム = 変更値 where 条件
これにfromを追加すると、別テーブルの値で更新することができます。
さらにwhereで絞ることで、更新対象のテーブルと、別テーブルのデータで照合することで、
あたかも内部結合したかのように更新対象テーブルのデータを変更できます。
update 対象テーブル名 set 更新するカラム = 別テーブル.値のカラム
from 別テーブル
where 対象テーブルのカラム = 別テーブルのカラム -- idなど
今回の例では以下で説明する副問合せで、ここでいう別テーブルを作成しています。
副問合せ（サブクエリ）
解決策のSQLを見ると、fromの後に()で囲まれたselect文があるのがわかりますね。
これが副問合せです。
SQL文の内部で別にSQLを発行しています。
その結果を用いてupdateを行っています。
副問合せを行うと、副問い合わせのselectの実行結果があたかも、テーブルのように外側のSQLで使用することができます。
今回の例だと、内部のsqlの実行結果は以下のような結果となります。これをfromで参照するテーブルとして取り扱っているイメージですね。
company_id | number
------------+--------
1 | 1
1 | 2
1 | 3
2 | 1
2 | 2
2 | 3
3 | 1
3 | 2
window関数
postgresにはwindow関数という、関数群が定義されています。
Window関数はテーブルを区間ごとに集計する機能です。集約関数 (GROUP BY) に似ていますが、Window関数では複数の行がまとめられることはなく、行それぞれが返却されます。また、処理中の行以外の行の値を読み取ることも可能です。
Let's postgreSQL
とのことです。
構文は、
-- 区間ごとに分割
関数() over (partition by ...)
-- 区間ごとに並び替え
関数() over (order by ...)
となります。
ここでは、row_numberという関数を使用しています。
row_numberは連番を付与する関数です。
ここでは、以下のように利用しているので、
company_idでまとめて、hire_dateでの昇順に並び替えた値に対して、連番をふるという操作になります。
row_number() over (partition by company_id, order by hire_date asc)
window関数を利用するとかなり、複雑な問い合わせも簡易にかけそうですね。
まとめ
すべて合わせると、最初のSQL文になります。
update employee
-- employee_numberを副問合せで作成したfix_empのnumberで更新
set employee_number = fix_emp.number
from (select
id,
-- 連番を付与
row_number()
over (
-- company_idでまとめて、hire_dateの昇順に並び替え
partition by company_id
order by hire_date asc ) as number
from employee) fix_emp
-- employeeのidとfix_empのidで合わせてjoin
where id = fix_emp.id;
SQLはまだまだわからないことだらけなので、引き続き気づいたことをまとめていきたいです。
もし、こっちのほうが効率的！とかありましたらコメントで指摘お願いします。