sql - PostgreSQL 基于列的条件计数

Question

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

description_id, value, admin_id, valid_at

admins 表包含以下列:

状态

我想要一个给定 description_id 的查询，它会告诉我正在使用该描述的事件管理员的数量。只要 admin_descriptions 表中有一行具有 description_id，NOT，管理员的描述就被视为已使用 NULL 值 并且 valid_at 比任何其他具有该 description_id 的 NULL 值 的条目最新。

因此，示例数据:

Row 1:
description_id: 1
value: 'foo'
admin_id: 2
valid_at: '2010-01-10'

Row 2:
description_id: 1
value: NULL
admin_id: 2
valid_at: '2012-01-10'

Row 2:
description_id: 1
value: 'some value'
admin_id: 4
valid_at: '2014-01-10'

查询 description_id = 1 时的计数将为 1。它只会考虑第 3 行。因为即使第一行的值不为 NULL，也有第 2 行的 valid_at同一 admin_id 的更新时间更近，其 value 为 NULL。

我已经能够在不考虑 valid_at 的情况下通过以下方式获得计数:

SELECT COUNT(DISTINCT(ad.id)) AS COUNT
FROM admin_descriptions ad
JOIN admins ON admins.id = ad.admin_id
WHERE ad.description_id = 1
AND admins.state = 'active'

我如何修改此查询以考虑每个 admin_id/description_id 组合的 valid_at？

Answer 1

我会使用 distinct on 来获取最新的管理员描述，然后从那里进行聚合:

select count(*) as num_valid_admins
from (select distinct on (ad.admin_id) ad.*
     from admin_descriptions ad
     where ad.description_id = XXX
     order by ad.admin_id, ad.valid_at desc
    ) ad
where ad.value is not null;