エンジニアになったもののまだまだ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はまだまだわからないことだらけなので、引き続き気づいたことをまとめていきたいです。
もし、こっちのほうが効率的！とかありましたらコメントで指摘お願いします。

参考

[PostgreSQL]SELECTした結果でUPDATEする
 PostgreSQL 9.4.5文書