在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
);

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

所以这个查询应该执行以下操作:

  1. 计算每个consultant_profile_id的总工作年限。
  2. 对这些数据进行分组,以显示有多少个具有相同总工作年限的consultant profiles?

在PostgreSQL中有没有办法做到这一点?

数据库示例链接

英文:

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-2.html
匿名

发表评论

匿名网友

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

确定