在我的PostgreSQL数据库中,我有以下表格:
CREATE TABLE public.experiences (
id bigint NOT NULL,
consultant_profile_id bigint NOT NULL,
position character varying NOT NULL,
years INTEGER NOT NULL
);
--Consultant Profile #1 experiences:
INSERT INTO experiences (id, consultant_profile_id, position, years) VALUES (1, 1, 'CEO', 3);
INSERT INTO experiences (id, consultant_profile_id, position, years) VALUES (2, 1, 'CTO', 2);
--Consultant Profile #2 experiences:
INSERT INTO experiences (id, consultant_profile_id, position, years) VALUES (3, 2, 'Intern', 1);
INSERT INTO experiences (id, consultant_profile_id, position, years) VALUES (4, 2, 'Data Analyst', 1);
我需要一个以以下方式表示数据的查询:
---------------------------------------------------------------------------------
total_years_of_experience_per_consultant | count_of_consultant_profiles
---------------------------------------------------------------------------------
5 | 1
2 | 1
因此,查询应该执行以下操作:
-
计算每位顾问的总工作年限_profile_id
-
将该数据分组以提供信息有多少具有相同总经验的顾问档案?
在PostgreSQL中有什么方法可以做到这一点吗?
https://www.db-fiddle.com/f/iiwSYyitSeZFoupCs3Jcf/1
