つ 息抜きにSQLクイズでも (2)

# はじめに
仕事中の息抜きや、暇つぶし用にちょっとしたSQLクイズをどうぞ。

# データの説明
`departments`テーブルには部署情報が入っています。
`parent_department_id`は組織階層の上位部署のIDです(ルートの部署の場合、`null`)。

| department_id | department_name | parent_department_id |
| ------------- | --------------- | -------------------- |
| 1             | 株式会社SQL         |                      |
| 2             | DB事業部           | 1                    |
| 3             | 営業部             | 2                    |
| 4             | 開発部             | 2                    |
| 5             | 営業1課            | 3                    |
| 6             | 営業２課            | 3                    |
| 7             | 開発1課            | 4                    |

`users`テーブルにはユーザー(社員)情報が入っています。

| user_id | user_name | department_id | position_name |
| ------- | --------- | ------------- | ------------- |
| 11      | 愛川        | 1             | 社長            |
| 21      | 犬山        | 2             | 事業部長          |
| 31      | 宇野        | 3             | 部長            |
| 32      | 江口        | 3             | 社員            |
| 41      | 小野        | 4             | 部長            |
| 51      | 鎌田        | 5             | 課長            |
| 52      | 北野        | 5             | 社員            |
| 61      | 栗原        | 6             | 課長            |
| 62      | 剣崎        | 6             | 社員            |
| 71      | 小出        | 7             | 課長            |
| 72      | 櫻井        | 8             | 社員            |

# 問題
```
ワークフローの申請アプリケーションを想定します。
申請者 --> 課長 --> 部長
という承認フローに対して、申請者のユーザーIDを入力とし、承認者を一覧表示するSQLを書いてください。
```

## 前提事項
* 直接所属する部署だけでなく、上位部署のユーザーも承認者候補となります。
* 同一のラインにおいて同じ役職を持ったユーザーは複数人存在しないものとします

## 制約事項
* できるだけ標準SQLベースで書いてください。

## 期待結果
### 申請者が北野さん(user_id=52)の場合

| 順番  | 部署   | 承認者 | 役職  |
| --- | ---- | --- | --- |
| 1   | 営業1課 | 鎌田  | 課長  |
| 2   | 営業部  | 宇野  | 部長  |

### 申請者が鎌田さん(user_id=51)の場合

| 順番  | 部署   | 承認者 | 役職  |
| --- | ---- | --- | --- |
| 1   | 営業部  | 宇野  | 部長  |

# DDL/DML
ローカル環境や[DB Fiddle](https://www.db-fiddle.com)などの環境で以下のSQLを流してください。
（動作確認は、DB Fiddle上のPostgreSQL 11で行いました）

> DB Fiddleで試す場合、左側のSchema SQLというペインに下記のSQLをコピー&ペーストし、右側のQuery SQLというペインにクエリを記述します。画面上部ナビバーのRunをクリックすると結果が表示されます。

```sql
    CREATE TABLE departments (
      department_id integer,
      department_name varchar(50),
      parent_department_id integer,
      PRIMARY KEY (department_id)
    );
    CREATE TABLE users (
      user_id integer,
      user_name varchar(50),
      department_id integer,
      position_name varchar(50),
      PRIMARY KEY (user_id)
    );
    INSERT INTO departments VALUES (1, '株式会社SQL', null);
    INSERT INTO departments VALUES (2, 'DB事業部', 1);
    INSERT INTO departments VALUES (3, '営業部', 2);
    INSERT INTO departments VALUES (4, '開発部', 2);
    INSERT INTO departments VALUES (5, '営業1課', 3);
    INSERT INTO departments VALUES (6, '営業２課', 3);
    INSERT INTO departments VALUES (7, '開発1課', 4);
    INSERT INTO users VALUES (11, '愛川', 1, '社長');
    INSERT INTO users VALUES (21, '犬山', 2, '事業部長');
    INSERT INTO users VALUES (31, '宇野', 3, '部長');
    INSERT INTO users VALUES (32, '江口', 3, '社員');
    INSERT INTO users VALUES (41, '小野', 4, '部長');
    INSERT INTO users VALUES (51, '鎌田', 5, '課長');
    INSERT INTO users VALUES (52, '北野', 5, '社員');
    INSERT INTO users VALUES (61, '栗原', 6, '課長');
    INSERT INTO users VALUES (62, '剣崎', 6, '社員');
    INSERT INTO users VALUES (71, '小出', 7, '課長');
    INSERT INTO users VALUES (72, '櫻井', 8, '社員');
```
    
# 解答例
SQLの実現方法は色々あると思いますが、以下に一例を示します。

```sql
with recursive dept_tree(
  department_id, parent_department_id, 
  department_name
) as (
  select 
    d.department_id, 
    d.parent_department_id, 
    d.department_name 
  from 
    departments d 
  where 
    d.department_id = (
      select 
        department_id 
      from 
        users 
      where 
        user_id = $1
    ) 
  union all 
  select 
    d.department_id, 
    d.parent_department_id, 
    d.department_name 
  from 
    departments d 
    inner join dept_tree t on d.department_id = t.parent_department_id
) 
select 
  ROW_NUMBER() over() as 順番, 
  t.department_name as 部署, 
  u.user_name as 承認者, 
  u.position_name as 役職 
from 
  users u 
  inner join dept_tree t on u.department_id = t.department_id 
where 
  u.position_name in ('課長', '部長') 
  and u.user_id <> $1 
order by 
  case u.position_name when '課長' then 1 when '部長' then 2 end;
```

以下、上記SQLの解説となります。

## `with recursive`を使った再帰クエリ

前半部分は`with recursive`を用いた再帰クエリでワークテーブルを定義しています。
具体的には申請ユーザーの所属部署を起点とし、部署の親子関係を上方向に辿って上位部署の一覧を取得します。
例えば北野さん(user_id=52)でSQLを実行した場合、ワークテーブルのデータは以下のようになります。

| department_id | parent_department_id | department_name |
| ------------- | -------------------- | --------------- |
| 5             | 3                    | 営業1課            |
| 3             | 2                    | 営業部             |
| 2             | 1                    | DB事業部           |
| 1             |                      | 株式会社SQL         |

北野さんの所属部署(department_id=5)を起点として、その上位部署(3)、さらにその上位(2)、そのまた上位(1)と再帰的に辿っていった結果となります。

再帰クエリの部分を詳しくみてみます。

```sql
with recursive dept_tree(
  department_id, parent_department_id, 
  department_name
) as (
  -- select文(1)
  union all 
  -- select文(2)
) 
```

という構造になっています。
`with recursive`の後にはワークテーブル名、その後の括弧内にはワークテーブルの列名が並びます。
`as`の後ろの括弧内の前半のselect文(1)は最初のレコードを返すサブクエリです。

```sql
  select 
    d.department_id, 
    d.parent_department_id, 
    d.department_name 
  from 
    departments d 
  where 
    d.department_id = (
      select 
        department_id 
      from 
        users 
      where 
        user_id = $1
    ) 
  union all
```

申請者のユーザーIDはバインド変数で渡しています。バインド変数の書き方はRDBMS依存になります。
上記の`$1`はPostgreSQLの場合の例です。他のRDBMSの場合、`:user_id`などと適宜置き換えてください。

`union all`の後のselect文(2)は、再帰的にレコードを求めるサブクエリです。

```sql
  union all 
  select 
    d.department_id, 
    d.parent_department_id, 
    d.department_name 
  from 
    departments d 
    inner join dept_tree t on d.department_id = t.parent_department_id
```

自分自身(`dept_tree`)と結合するのがポイントです。

## ワークテーブルからのレコード抽出

`順番`を表示するためにウィンドウ関数`ROW_NUMBER()`を使用します。

```sql
select 
  ROW_NUMBER() over() as 順番, 
  t.department_name as 部署, 
  u.user_name as 承認者, 
  u.position_name as 役職 
from 
  users u 
  inner join dept_tree t on u.department_id = t.department_id 
```

`課長`または`部長`の役職をもつユーザーに絞り込みます。また、自分自身は除外します。

```sql
where 
  u.position_name in ('課長', '部長') 
  and u.user_id <> $1 
```

`課長`、`部長`の順でソートをかけます。

```sql
order by 
  case u.position_name when '課長' then 1 when '部長' then 2 end;
```

## 補足：実行方法
バインド変数を使ったSQLの実行はRDBMSごとに異なります。
PostgreSQLの場合、以下のようにプリペアド文で作成したSQLを`execute`で実行します。

```sql
prepare get_approvers(int) as 
with recursive dept_tree(
-- ...(略)...
order by 
  case u.position_name when '課長' then 1 when '部長' then 2 end;

execute get_approvers(51);
```

# おわりに
暇つぶしになったでしょうか？
よい別解があったら是非コメント欄で教えてください！

# バックナンバー
[つ 息抜きにSQLクイズでも](https://qiita.com/yonetty/items/9413c0b35eef5773d4b00)