英文:
Grouping by calculated experience in PostgreSQL
问题
在我的PostgreSQL数据库中,我有以下表格:
CREATE TABLE public.experiences (
id bigint NOT NULL,
consultant_profile_id bigint NOT NULL,
position character varying NOT NULL,
years INTEGER NOT NULL
);
--顾问档案#1的经验:
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);
--顾问档案#2的经验:
INSERT INTO experiences (id, consultant_profile_id, position, years) VALUES (3, 2, '实习生', 1);
INSERT INTO experiences (id, consultant_profile_id, position, years) VALUES (4, 2, '数据分析师', 1);
我需要一个查询来以以下方式表示数据:
total_years_of_experience_per_consultant | count_of_consultant_profiles
5 | 1
2 | 1
因此,查询应该执行以下操作:
- 计算每个consultant_profile_id的总工作经验年数
- 对该数据进行分组,以显示具有相同总工作经验年数的顾问档案有多少个?
在PostgreSQL中有没有办法做到这一点?
https://www.db-fiddle.com/f/iiwSYyitSeZFoupCs3Jcf/1
英文:
In my PostgreSQL database, I have the following table:
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);
I need a query that will represent data in following way:
---------------------------------------------------------------------------------
total_years_of_experience_per_consultant | count_of_consultant_profiles
---------------------------------------------------------------------------------
5 | 1
2 | 1
So the query should do the following:
- Calculate totay years of experience for every consultant_profile_id
- Group that data to present information how many consultant profiles with the same total years of experience are present?
Is there any way to do that in PostgreSQL?
答案1
得分: 2
我们可以使用两层聚合方法:
WITH cte AS (
SELECT SUM(years) AS total_years
FROM experiences
GROUP BY consultant_profile_id
)
SELECT
total_years AS total_years_of_experience_per_consultant,
COUNT(*) AS count_of_consultant_profiles
FROM cte
GROUP BY total_years
ORDER BY total_years DESC;
CTE(公共表达式)用于找到每个顾问的总工作年限,第二个查询按照这些年限进行聚合以找到数量。
英文:
We can use a two tier aggregation approach:
<!-- language: sql -->
WITH cte AS (
SELECT SUM(years) AS total_years
FROM experiences
GROUP BY consultant_profile_id
)
SELECT
total_years AS total_years_of_experience_per_consultant,
COUNT(*) AS count_of_consultant_profiles
FROM cte
GROUP BY total_years,
ORDER BY total_years DESC;
The CTE finds total years of experience per consultant, and the second query aggregates by those years to find the counts.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论