By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798699 fiddles created (41871 in the last week).
CREATE TABLE Employees (
Employee
VARCHAR(30) NOT NULL
PRIMARY KEY
);
CREATE TABLE Roles (
Role
VARCHAR(30) NOT NULL
PRIMARY KEY
);
CREATE TABLE AppSystemCategories (
Category
VARCHAR(30) NOT NULL
PRIMARY KEY
);
CREATE TABLE AppSystems (
AppSystem
VARCHAR(30) NOT NULL
PRIMARY KEY,
Category
VARCHAR(30) NOT NULL
REFERENCES AppSystemCategories(Category)
);
CREATE TABLE EmployeeAppSystems (
Employee
VARCHAR(30) NOT NULL
REFERENCES Employees(Employee),
AppSystem
VARCHAR(30) NOT NULL
REFERENCES AppSystems(AppSystem),
PRIMARY KEY (Employee, AppSystem)
);
CREATE TABLE RoleAppSystems (
Role
VARCHAR(30) NOT NULL
REFERENCES Roles(Role),
AppSystem
VARCHAR(30) NOT NULL
REFERENCES AppSystems(AppSystem),
PRIMARY KEY (Role, AppSystem)
);
INSERT INTO Employees(Employee)
VALUES ('bibi'),('tee'),('mimi'),('lee'), ('min');
INSERT INTO Roles(Role)
VALUES ('Data Analyst'), ('Engineer'), ('Business');
INSERT INTO AppSystemCategories(Category)
VALUES ('Tech'), ('Biz');
INSERT INTO AppSystems(AppSystem, Category)
VALUES ('Redshift','Tech'), ('Redash','Tech'), ('Jira','Tech'), ('Tableau','Tech'), ('Figma','Tech'), ('Office','Tech'),
('MoneyForward','Biz'), ('Salesforce','Biz'), ('Notion','Biz');
INSERT INTO RoleAppSystems(Role, AppSystem)
VALUES ('Data Analyst','Redshift'), ('Data Analyst','Redash'), ('Data Analyst','Tableau'), ('Data Analyst','MoneyForward'), ('Data Analyst','Notion'),
('Engineer','Figma'), ('Engineer','Jira'), ('Engineer','MoneyForward'), ('Engineer','Notion'),
('Business','Notion'), ('Business','MoneyForward'), ('Business','Office');
-- MoneyForward and Notion are used by all roles
INSERT INTO EmployeeAppSystems(Employee, AppSystem)
VALUES ('bibi','Redshift'), ('bibi','Redash'), ('bibi','MoneyForward'), ('bibi','Notion'),
-- Partial match for Data Analyst, match for Biz systems
('tee','Redshift'), ('tee','Redash'), ('tee','Tableau'), ('tee','MoneyForward'), ('tee','Notion'),
-- Perfect match for Data Analyst
('mimi','Figma'), ('mimi','Jira'), ('mimi','MoneyForward'), ('mimi','Notion'), ('mimi','Tableau'),
-- Over qualified for Engineer (uses extra systems)
('lee','Notion'), ('lee','Salesforce'),
-- No tech skills
('min','Redshift'), ('min','Redash'), ('min','Jira'), ('min','Tableau'), ('min','Figma'), ('min','Office'), ('min','MoneyForward'), ('min','Salesforce'), ('min','Notion');
-- min Has it all
-- all candidates use notion
✓
✓
✓
✓
✓
✓
5 rows affected
3 rows affected
2 rows affected
9 rows affected
12 rows affected
25 rows affected
hidden batch(es)
-- 複雑になってしまっている例
-- データアナリスト用システムのみを利用する社員
with
/* データアナリスト用システムを取得して、
全ての社員と cross join して直積結合を作ります。*/
data_analyst_systems as (
select
*
from
(select
distinct(AppSystem) as DataAnalystAppSystems
from
RoleAppSystems
where
Role = 'Data Analyst'
) as app_systems
cross join
Employees
),
/* データアナリスト用システムを使わない社員を取得します。*/
employee_not_using_da_systems as (
select
das.Employee
from
data_analyst_systems as das
left join EmployeeAppSystems as eas
on eas.AppSystem = das.DataAnalystAppSystems
and eas.Employee = das.Employee
where
eas.Employee is null
group by
das.Employee
),
/* 全ての社員からデータアナリスト用システムを使わない社員を除外し、
データアナリスト用システムを利用する社員を取得します。*/
employee_uses_da_systems as (
select
e.Employee
from
Employees as e
left join employee_not_using_da_systems as n
on e.Employee = n.Employee
where
n.Employee is null
),
/* データアナリスト用システムもほかのロール用システムも利用する社員を取得します。 */
employee_uses_notonly_da_systems as (
select
da.Employee
from
employee_uses_da_systems as da
left join EmployeeAppSystems as eas
on da.Employee = eas.Employee
left join (select * from RoleAppSystems where Role = 'Data Analyst') as ras
on eas.AppSystem = ras.AppSystem
where
ras.Role is null
group by
da.Employee
),
/* データアナリスト用システムを利用する社員から他のロール用システムも利用する社員を除外します。*/
employee_uses_only_da_systems as (
select
e.Employee
from
employee_uses_da_systems as e
left join employee_uses_notonly_da_systems as n
on e.Employee = n.Employee
where
n.Employee is null
)
select * from employee_uses_only_da_systems;
employee
tee
…
hidden batch(es)
SELECT *
FROM AppSystems;
SELECT *
FROM AppSystemCategories;
SELECT *
FROM Employees;
SELECT *
FROM EmployeeAppSystems;
SELECT *
FROM Roles;
SELECT *
FROM RoleAppSystems;
