はじめに
仕事中の息抜きや、暇つぶし用にちょっとした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などの環境で以下のSQLを流してください。
（動作確認は、DB Fiddle上のPostgreSQL 11で行いました）
DB Fiddleで試す場合、左側のSchema SQLというペインに下記のSQLをコピー&ペーストし、右側のQuery SQLというペインにクエリを記述します。画面上部ナビバーのRunをクリックすると結果が表示されます。
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の実現方法は色々あると思いますが、以下に一例を示します。
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)と再帰的に辿っていった結果となります。
再帰クエリの部分を詳しくみてみます。
with recursive dept_tree(
department_id, parent_department_id,
department_name
) as (
-- select文(1)
union all
-- select文(2)
)
という構造になっています。
with recursiveの後にはワークテーブル名、その後の括弧内にはワークテーブルの列名が並びます。
asの後ろの括弧内の前半のselect文(1)は最初のレコードを返すサブクエリです。
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)は、再帰的にレコードを求めるサブクエリです。
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()を使用します。
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の実行はRDBMSごとに異なります。
PostgreSQLの場合、以下のようにプリペアド文で作成したSQLをexecuteで実行します。
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);
おわりに
暇つぶしになったでしょうか？
よい別解があったら是非コメント欄で教えてください！