sql - 基于 foreign_key 的百分比

Question

我有一个名为 animals 的表，其中包含以下列:

id: INT
state: VARCHAR

另一个名为 houses 的表具有以下列:

id: INT
animal_id: INT
color: VARCHAR

我正在尝试获取至少有一个房子(颜色为“红色”)的给定集合内的动物(哪个状态为“活着”)及其 ID 的百分比。如果一只动物有不止一个红色的房子，没关系，应该只计算一次。

我知道如何获得符合这些条件的动物数量:

SELECT COUNT(*)
FROM animals
WHERE state = 'living'
AND id IN (10,11,12)

而且我知道如何获得符合这些条件的房屋:

SELECT COUNT(*)
FROM houses
WHERE color = 'red'

我不确定如何结合这两个查询来获得动物的百分比。

示例数据:

animals (id, state)

1, 'living'
2, 'dead'
3, 'living'
4, 'living'
5, 'dead'
6, 'living'


houses (id, animal_id, color)

1, 1, 'red'
2, 2, 'red'
3, 1, 'blue'
4, 6, 'red'
5, 4, 'red'
6, 1, 'red'  

我们希望将动物的范围缩小到只有 ID 为 1、2、3 和 5 的动物。

因此，由于 id 为 2 和 5 的动物已经死亡，因此不应将它们算作分母的一部分。现在，这给我们留下了动物 1 和 3，但是只有 id 为 1 的动物有至少一个红色的房子(注意它有两个，但我们不在乎)，这意味着百分比是:

(至少有一个阅览室的 1 只动物)/(2 只活着的动物)= 50%

Answer 1

您可以使用 LEFT JOIN 来组合表。然后可以使用 AVG() 得到你想要的比例:

SELECT AVG( (h.color = 'red')::int ) as ratio_red
FROM animals a LEFT JOIN
     houses h
     ON a.id = h.animal_id
WHERE a.state = 'living'
      a.id IN (10, 11, 12);

对于修改后的计算，您可以使用:

SELECT COUNT(DISTINCT CASE WHEN h.color = 'red' THEN h.animal_id END) / COUNT(DISTINCT a.id) as ratio_red
FROM animals a LEFT JOIN
     houses h
     ON a.id = h.animal_id
WHERE a.state = 'living'
      a.id IN (10, 11, 12);