PostgreSQL如何以基于列的条件进行计数?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (37)

我有一个表格称为admin_descriptions具有以下列:

description_id, value, admin_id, valid_at

并且该表admins具有以下列:

state

示例数据:

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

在不考虑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'

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

提问于
用户回答回答于

使用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;
用户回答回答于

允许检查每个分组ID的valid_at。

SELECT
  COUNT(ad.id)
FROM admin_description ad
  INNER JOIN admins ON admins.id = ad.admin_id
    AND admins.state = 'active'
WHERE
  1 = 1
  -- AND valid_at -- However you want to handle valid_at
GROUP BY ad.id;

假设每个admin_id / description_id组合总是唯一的。

扫码关注云+社区

领取腾讯云代金券