Back Story

我有一个Employee表，其中每个员工都分配了一个或多个工作角色，对于每个分配的角色，它都显示了分配的年份.我有一个查询，它构建一个矩阵表格，每个员工一行，如下所示:

Summary Columns

我需要一些额外的列，这些列 for each 员工计算以下内容:

1. 分配给员工的角色数(计数？)
2. 分配的最早角色的年份(最少)
3. 最近分配的角色所在的年份(最大)

Desired Results

此表说明了我的分析所需的结果:

My Mixed Results

对于第role_oldest列和第role_newest列，我可以使用以下语句:

UPDATE employee_roles
  SET role_oldest = LEAST(role1, role2, role3, role4, role5);

UPDATE employee_roles
  SET role_newest = GREATEST(role1, role2, role3, role4, role5);

然而，在迭代try 计算Role_count时，我try 了COUNT、ARRAY、UNNEST和STRING_TO_ARRAY函数的各种组合，但没有成功，如下所示:

UPDATE employee_roles
  SET role_count = COUNT(role1, role2, role3, role4, role5);

UPDATE employee_roles
  SET role_count = COUNT(UNNEST(role1, role2, role3, role4, role5));

UPDATE employee_roles
  SET role_count = COUNT(UNNEST(ARRAY(role1, role2, role3, role4, role5)));

UPDATE employee_roles
  SET role_count = COUNT(UNNEST(ARRAY(STRING_TO_ARRAY(role1, role2, role3, role4, role5))));

Climbing Out of the Rabbit Hole

很明显，我在这个练习中走错了路，所以我退出了，并在这方面寻求您的帮助.我相信确实有一个优雅的解决方案，它就在我的眼皮底下，我希望有人能帮我找到它.

Similar Questions with Complicated Answers

我在so上找到了许多其他类似的帖子，每个帖子都包含需要大量PSQL体操的疯狂解决方案，我发现很难相信没有一个简单的函数可以做到这一点.

Code for Creating Sample Table

以下代码块将帮助您快速创建本练习的示例表:

create table employee_roles
(
    name        text,
    role1       integer default null,
    role2       integer default null,
    role3       integer default null,
    role4       integer default null,
    role5       integer default null,
    role6       integer default null,
    role_count  integer default null,
    role_oldest integer default null,
    role_newest integer default null
);

insert into employee_roles (name,role1,role2,role3,role4,role5)
    values ('Bunny, Bugs',null,null,null,null,2022);
insert into employee_roles (name,role1,role2,role3,role4,role5)
    values ('Coyote, Wiley',2018,null,null,2018,null);
insert into employee_roles (name,role1,role2,role3,role4,role5)
    values ('Pig, Porky',null,null,2017,null,null);
insert into employee_roles (name,role1,role2,role3,role4,role5)
    values ('Mouse, Mickey',null,null,null,2020,null);
insert into employee_roles (name,role1,role2,role3,role4,role5)
    values ('Panther, Pink',2019,null,null,null,null);
insert into employee_roles (name,role1,role2,role3,role4,role5)
    values ('Cheese, Chuckey',null,null,2021,2017,null);
insert into employee_roles (name,role1,role2,role3,role4,role5)
    values ('Duck, Donald',2021,null,null,2021,null);
insert into employee_roles (name,role1,role2,role3,role4,role5)
    values ('Devil, Taz',null,2019,null,null,null);
insert into employee_roles (name,role1,role2,role3,role4,role5)
    values ('Brown, Charlie',null,null,2021,null,2019);
insert into employee_roles (name,role1,role2,role3,role4,role5)
    values ('Flintstone, Fred',2019,null,2011,2016,null);