计算包含0的值按另一列分组的百分比

Question

我正在尝试计算每个月以0作为值的团队的百分比。我当前的表如下所示

+-------------+---------------+-----------+
date            team             Value
+-------------+---------------+-----------+
01/01/2018        sales             0
02/01/2018        engineering       3
03/01/2018        Sales             3
04/01/2018        executives        0
01/02/2018        sales             3
02/02/2018        engineering       0
03/02/2018        executives        0
04/02/2018        engineering       2
01/03/2018        sales             5
02/03/2018        engineering       0
03/03/2018        executives        3
04/03/2018        Sales             2

我已经尝试了查询to_char(date,'YYYY')||''|| to_char(date,'Month') as month,team,100*SUM(CASE WHEN Value = 0 THEN 1 ELSE 0 END)/COUNT(Value). from table，但没有给出所需的输出。

我想要的输出应该类似于下面的内容

+-------------+---------------+----------------------+---------------+
month           Sales            engineering           executives      
+-------------+---------------+----------------------+---------------+
01/2018           50%            0%                     50%     
02/2018            0%            50%                    50%     
03/2018            0%            100%                     0%     

Answer 1

我更喜欢将month列保留为日期，因此我将使用：

select date_trunc(month, date) as yyyymm,
       sum(val) filter (where team = 'sales') * 1.0 / sum(val) as sales,
       sum(val) filter (where team = 'engineering') * 1.0 / sum(val) as engineering,
       sum(val) filter (where team = 'executives') * 1.0 / sum(val) as executives
from t
group by yyyymm;

如果你真的想要一个字符串，你可以使用像to_char(date, 'YYYY-MM')或任何你喜欢的格式。只需要一个表达式。

编辑：

以上解决了错误的问题！我认为这是价值的一部分。而是0的比例：

select date_trunc(month, date) as yyyymm,
       avg( (val = 0)::int ) filter (where team = 'sales') as sales,
       avg( (val = 0)::int ) filter (where team = 'engineering') as engineering,
       avg( (val = 0)::int ) filter (where team = 'executives') as executives
from t
group by yyyymm;