子查询上的多个ID以计算计数 [英] Multiple ids on subquery to calculate counts

问题描述

我有以下查询：

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;

它成功给出了 description_id 使用该说明的管理员人数。只要 admin_descriptions 表中具有 description_id 的行，就可以认为是对管理员的描述， NOT NULL值，并且 valid_at 是最新的，比该值 NULL 的任何其他条目最新$ c> description_id 。

which successfully given a description_id it gives me the count of admins that are using that description. A description for an admin is considered used as long as there is a row in the admin_descriptions table that has the description_id, a NOT NULL value and the valid_at is most recent than any other entry that has a NULL value for that description_id.

因此，对于这种类型的数据：

So, for this type of data:

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.因为即使在第一行中值都不为空，所以存在第二行，对于相同的 admin_id， valid_at 最近code>及其价值e为NULL。

The count when querying for description_id = 1 would be 1. It would just consider row 3. Because even tho there is the first row where value is NOT NULL, there is row 2 which valid_at is more recent for the same admin_id and its value is NULL.

现在，我正在尝试修改查询，以便可以查询多个 description_ids 而且我不知道如何修改查询。我想要的是一组看起来像这样的结果： description_id，count 。

Now, I am trying to modify the query so that I can query it for multiple description_ids and I can't figure out how to modify the query. What I would like is a set of results that look like: description_id, count.

我尝试修改内部查询是这样的：

I have tried modifying the inner query like this:

select count(*) as num_valid_admins
from (select distinct on (ad.admin_id) ad.*
     from admin_descriptions ad
     where ad.description_id IN (x,y,z)
     order by ad.admin_id, ad.valid_at desc
    ) ad
where ad.value is not null
GROUP BY ad.description_id

但没有返回我通过 description_id 进行计数。

But is not returning me the counts by description_id.

推荐答案

您需要添加描述ID到顺序和区别于：

You need to add the description id to the order by and distinct on:

select ad.description_id, count(*) as num_valid_admins
from (select distinct on (ad.description_id, ad.admin_id) ad.*
     from admin_descriptions ad
     where ad.description_id IN (x, y, z)
     order by ad.description_id, ad.admin_id, ad.valid_at desc
    ) ad
where ad.value is not null
group by ad.description_id;

这篇关于子查询上的多个ID以计算计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！