sql - 根据基线行计算百分比/聚合

Question

我想计算一个销售团队与特定团队成员相比的生产力。

鉴于此查询：

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 实现这一点？

标签： sqlpostgresql

Answer 1

这应该可以解决问题

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 cte