基于基线行计算百分比/聚合
基于基线行计算百分比/聚合
提问于 2018-10-22 02:03:05
我想计算一个销售团队的生产力与一个特定的团队成员相比。
考虑到这个查询：
with t1 (rep_id, place_id, sales_qty) as (values
(0, 1, 3),
(1, 1, 1),
(1, 2, 2),
(1, 3, 4),
(1, 4, 1),
(2, 2, 1),
(2, 3, 3)
)
select
rep_id,
count(distinct place_id) as qty_places,
sum(sales_qty) as qty,
sum(sales_qty) / count(place_id) as productivity
from
t1
group by
rep_id结果：
rep_id | qty_places | qty_sales | productivity
---------------------------------------------
0 | 1 | 6 | 6
1 | 4 | 22 | 5
2 | 2 | 9 | 4我希望基于rep_id = 1的生产力来获得团队的生产力，所以我希望有这样的内容：
rep_id | qty_places | qty_sales | productivity | productivity %
--------------------------------------------------------------
0 | 1 | 6 | 6 | 1.2
1 | 4 | 22 | 5 | 1 <- Baseline
2 | 2 | 9 | 4 | 0.8如何使用PostgreSQL上的SQL实现这一目标？
回答 3
Stack Overflow用户
回答已采纳
发布于 2018-10-22 05:03:13
这应该能起作用
with t1 (rep_id, place_id, sales_qty) as (values
(0, 1, 3),
(1, 1, 1),
(1, 2, 2),
(1, 3, 4),
(1, 4, 1),
(2, 2, 1),
(2, 3, 3)
),
cte as (select
rep_id,
count(distinct place_id) as qty_places,
sum(sales_qty) as qty,
sum(sales_qty) / count(place_id) as productivity
from
t1
group by
rep_id)
select rep_id, qty_places, qty, productivity,
productivity::numeric/(select productivity::numeric from cte where rep_id = 1)
as productivity_percent from cteStack Overflow用户
发布于 2018-10-22 02:09:24
我们可以尝试在单独的CTE中计算rep_id = 1数字，然后将它交叉到当前的表中：
WITH cte AS (
SELECT SUM(CASE WHEN rep_id = 1 THEN sales_qty ELSE 0 END) /
COUNT(CASE WHEN rep_id = 1 THEN 1 END) AS baseline
FROM t1
)
SELECT
rep_id,
COUNT(DISTINCT place_id) AS qty_places,
SUM(sales_qty) AS qty,
SUM(sales_qty) / COUNT(place_id) AS productivity,
(1.0*SUM(sales_qty) / COUNT(place_id)) / t2.baseline AS productivity_pct
FROM t1
CROSS JOIN cte t2
GROUP BY
t1.rep_id, t2.baseline;
Stack Overflow用户
发布于 2018-10-22 11:35:30
只需使用条件聚合。我会使用一个子查询来完成这个任务：
select t.*,
productivity / max(productivity) filter (where rep_id = 1) over ()
from (select rep_id,
count(distinct place_id) as qty_places,
sum(sales_qty) as qty,
sum(sales_qty)::numeric / count(place_id) as productivity
from t1
group by rep_id
) t这里是db<>fiddle。
请注意，您实际上可以在没有子查询的情况下表示这一点，但我认为这只会使查询更加复杂。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/52921529
复制相关文章
相似问题
腾讯云开发者
