sql - 分组并计算每个不同值的百分比

Question

Postgresql 9.6 有两个表:

      id  | name  |
   -------+-------+
      1   | Mars  |
      2   | Pluto |
      3   | Moon  |
      4   | Venus |


     id | p_id | action |
   -------+-------+-----+
     1  |  1   | LANDED |
     2  |  1   | UNSEEN |
     3  |  1   | SEEN   |
     4  |  1   | SEEN   |
     5  |  2   | LANDED |
     6  |  3   | SEEN   |
     7  |  3   | SEEN   |
     8  |  3   | UNSEEN |
     9  |  3   | LANDED |
    10  |  3   | LANDED |
    11  |  3   | LANDED |

我无法找到一个查询来获取显示每个操作百分比的表，
例如:

      p_id | name  | SEEN | UNSEEN | LANDED |
   --------+-------+------+--------+--------+
       1   | Mars  |  10% |   30%  |   60%  |
       2   | Pluto |   0% |    0%  |  100%  |
       3   | Moon  |  25% |   35%  |   30%  |
       4   | Venus |   0% |    0%  |    0%  |

如有任何帮助，我们将不胜感激。
谢谢，
佩拉兹

Answer 1

您可以使用带过滤器的 count(*) 计算每个类别的实例数:

select 
    n.id, name, 
    count(*) filter (where action = 'SEEN') as seen,
    count(*) filter (where action = 'UNSEEN') as unseen,
    count(*) filter (where action = 'LANDED') as landed,
    count(*)::dec as total
from names n
left join actions a on a.p_id = n.id
group by n.id
order by n.id;

 id | name  | seen | unseen | landed | total 
----+-------+------+--------+--------+-------
  1 | Mars  |    2 |      1 |      1 |     4
  2 | Pluto |    0 |      0 |      1 |     1
  3 | Moon  |    2 |      1 |      3 |     6
  4 | Venus |    0 |      0 |      0 |     1
(4 rows)

Venus 的

Total 不正确 (1)，因为左连接。事实上，没关系，因为我们可以避免在下一步中除以 0。

在派生表(或 CTE)中使用上述查询来计算百分比:

select
    id, name,
    round(seen/ total* 100, 0) as seen,
    round(unseen/ total* 100, 0) as unseen,
    round(landed/ total* 100, 0) as landed
from (
    select 
        n.id, name, 
        count(*) filter (where action = 'SEEN') as seen,
        count(*) filter (where action = 'UNSEEN') as unseen,
        count(*) filter (where action = 'LANDED') as landed,
        count(*)::dec as total
    from names n
    left join actions a on a.p_id = n.id
    group by n.id
    ) s
order by id;

 id | name  | seen | unseen | landed 
----+-------+------+--------+--------
  1 | Mars  |   50 |     25 |     25
  2 | Pluto |    0 |      0 |    100
  3 | Moon  |   33 |     17 |     50
  4 | Venus |    0 |      0 |      0
(4 rows)