如何在PostgreSQL中计算一列中值的百分比

Question

我正试图创建一个表，从techn_support列中获得"否"/"是"的百分比。我一直被这个提供原始数据的查询所困扰，但我如何计算一列中值的百分比。查询得到的表格如下屏幕截图所示。我正在使用Postgres/PGadmin4。

select c.customer_id, age, l.state, s.tech_support
from customer as c
join location as l
on c.location_id = l.location_id
join services as s
on c.customer_id = s.customer_id
where age > 25 and age < 42 and state = 'NC'

表结果

Answer 1

您可以将SUM()和CASE组合使用条件聚合。例如，要获得"是"与总行数的比率，可以执行以下操作：

with
q as (
-- your query here
)
select
1.0 * sum(case when tech_support = 'Yes' then 1 else 0 end) 
/
count(*)
from q

编辑：

如果你只想得到比率，而不是其他数据，你可以做：

select 1.0 * sum(case when tech_support = 'Yes' then 1 else 0 end)
/ count()
from customer as c
join location as l
on c.location_id = l.location_id
join services as s
on c.customer_id = s.customer_id
where age > 25 and age < 42 and state = 'NC'

如果你想保留所有的数据"；"照原样"；并且希望将该比率添加为可以使用OVER()的所有行的额外列。例如：

select c.customer_id, age, l.state, s.tech_support,
1.0 * sum(case when tech_support = 'Yes' then 1 else 0 end) over()
/ count() over()
from customer as c
join location as l
on c.location_id = l.location_id
join services as s
on c.customer_id = s.customer_id
where age > 25 and age < 42 and state = 'NC'