在PostgreSQL中按计算的经验进行分组

huangapple go评论70阅读模式
英文:

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

因此,查询应该执行以下操作:

  1. 计算每个consultant_profile_id的总工作经验年数
  2. 对该数据进行分组,以显示具有相同总工作经验年数的顾问档案有多少个?

在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:

  1. Calculate totay years of experience for every consultant_profile_id
  2. 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?

https://www.db-fiddle.com/f/iiwSYyitSeZFoupCs3Jcf/1

答案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.

huangapple
  • 本文由 发表于 2023年8月9日 14:04:23
  • 转载请务必保留本文链接:https://go.coder-hub.com/76864969.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定