再帰SQLで部署階層別に集計する

TL;DR

特定文字（「_」アンダーバーとか）で区切られた部署名で階層別にデータ集計することがありました。
部署階層を特定文字で表現する事の正否は別に置いておくとして、再帰SQLで部署名を階層毎にレコードにするSQLを書きました。

とりあえず環境

PostgreSQL 9.6.11

書いたSQL

WITH 
  RECURSIVE recursive_departments (id, name) AS  (
    --root
    SELECT 
      id, 
      name
    FROM departments
    WHERE name LIKE '%\_%'
    --recursive
    UNION ALL
    SELECT 
      CASE 
      WHEN position('_' IN recursive_departments.name) > 0 THEN departments.id
      ELSE NULL
      END,
      left(recursive_departments.name, (length(regexp_replace(recursive_departments.name, '^.*_', '_'))) * -1)
    FROM departments, recursive_departments
    WHERE departments.id = recursive_departments.id
  )
SELECT recursive_departments.name, count(*)
FROM recursive_departments
INNER JOIN employees
 ON recursive_departments.id = employees.department_id
WHERE recursive_departments.id IS NOT NULL 
GROUP BY recursive_departments.name

解説１(RECURSIVE)

WITH句内で RECURSIVE をつけるとその中のSQLが再帰処理されます
UNION ALL以前が初回検索に使われるクエリで取得した結果で UNION ALL 以降のクエリが再帰実行されます。

ぶっちゃけ RECURSIVE は分かりやすい解説の記事が沢山あるのでそちらを見たほうがよい気がする。

解説２(CASE & position)

positionで区切り文字の _（アンダーバー）の位置を調べます。
_（アンダーバー）が存在しない場合には 0 が返却されます。
0が返却された場合は、上位の階層は存在しないので再帰処理を止めるために CASE文で分岐させて null を返却します。

解説３(left & length & regexp_replace)

left は文字列から左から指定文字数を返却する関数ですが、指定文字数の引数に負の数値を渡すと右から指定文字数を削った文字列を返却してくれます。
なので、最下層の部署名の文字数を -1 で掛けて引数を設定しています。
lengthは文字列の長さを返却してくれる関数なので、最下層の部署名 + _（アンダーバー）を引数として渡します。
regexp_replaceは正規表現での文字列置き換え処理です。
最後の _（アンダーバー）以前の文字列を _（アンダーバー）に置き換えることで _（アンダーバー）より手前の文字列を削除しています。

まとめ

最後は emplyees テーブルと結合して集計処理して終わりです。
もっとキレイなやり方があるかもしれないですが、集計結果を社内で共有する程度なら十分かと思います。