当前，我有以下查询：
SELECT DISTINCT t."Resource" AS "Email",
t."project id" AS "project_id",
sum(t."Hours Logged") AS "total hours"
FROM
(SELECT DISTINCT "Resource",
"Hours Logged",
"proposals"."Clickup Id" AS "project id"
FROM "daily hours",
proposals AS "proposals"
WHERE "When Logged" >= (CURRENT_DATE - 7)
AND ("Space Id" = "proposals"."Clickup Id"
OR "Folder Id" = "proposals"."Clickup Id"
OR "List Id" = "proposals"."Clickup Id")) AS t
GROUP BY (t."project id",
t."Resource")
此查询的输出为：
| Email | project_id | total hours |
| ---------------| -------------- | ------------|
| mail@mail.com | 1 | 6 |
| mail2@mail.com | 2 | 5 |
| mail3@mail.com | 1 | 7 |
| mail4@mail.com | 2 | 3 |
| mail@mail.com | 3 | 4 |
所以现在我想写一个查询，对total hours求和，得到overall total hours，用project_id分组，然后用这个公式"total hours" / "overall total hours" * 100求百分比
因此输出将是
| Email | project_id | total hours | overall total hours | percentage |
| ---------------| -------------- | ------------| --------------------| -----------|
| mail@mail.com | 1 | 6 | 13 | 46.15
| mail2@mail.com | 2 | 5 | 8 | 62.5
| mail3@mail.com | 1 | 7 | 13 | 53.84
| mail4@mail.com | 2 | 3 | 8 | 37.5
| mail@mail.com | 3 | 4 | 4 | 100
提前感谢
您可以将窗口函数应用于聚合，demo:
with t(Resource,project_id,hours) as (
-- emulates result of the subquery
select 'mail@mail.com ', 1, 6 union all
select 'mail2@mail.com', 2, 5 union all
select 'mail3@mail.com', 1, 7 union all
select 'mail4@mail.com', 2, 3 union all
select 'mail@mail.com ', 1, 6 union all
select 'mail2@mail.com', 2, 5 union all
select 'mail3@mail.com', 1, 7 union all
select 'mail4@mail.com', 2, 3 union all
select 'mail@mail.com ', 3, 4
)
select t.Resource AS "Email",
t.project_id ,
sum(t.hours) AS "total hours",
sum(sum(t.hours)) over (partition by t.project_id) "overall total hours",
sum(t.hours) / sum(sum(t.hours)) over (partition by t.project_id) "percentage"
from t
GROUP BY t.project_id, t.Resource
order by t.Resource,t.project_id