sql - 计算 postgres 中每组至少出现 k 次的唯一值的数量

Question

我有一个包含 3 列的表格，如下所示:

ID | obs_type | Value
1      A        0.1  
1      A        0.2
1      B        0.4
2      B        0.5
2      C        0.2
2      C        0.3
3      B        0.1

我想计算每个组类型中至少有 k 个观察值的 ID。

在上面的示例中，如果 k = 2(要计算同一 ID 的至少 2 个观察值)，我希望:

obs_type | count
A            1
B            0
C            1

因为有一个 ID 有两个类型 A 的观察和一个 ID 有两个类型 C 的观察。

不存在具有两个类型 B 的观察值的 ID。

对于 k = 1，我只是这样做:

SELECT obs_type, COUNT(DISTINCT ID ) FROM table_x GROUP BY obs_type;

但我正在寻找适用于任意 k 的解决方案。

谢谢!!!!

Answer 1

分两步进行聚合:

这里 k = 2:

select count(case when cnt >= 2 then cnt end), obs_type
from
(
    select count(*) cnt, obs_type
    from table_x
    group by id, obs_type
) dt
group by obs_type

派生表(子查询)返回:

             cnt obs_type
================ ========
               2 A
               1 B
               1 B
               2 C
               1 B

然后用case表达式做条件聚合，你会得到:

SQL>select count(case when cnt >= 2 then cnt end), obs_type
SQL&from
SQL&(
SQL&    select count(*) cnt, obs_type
SQL&    from table_x
SQL&    group by id, obs_type
SQL&) dt
SQL&group by obs_type;
                     obs_type
==================== ========
                   1 A
                   0 B
                   1 C

                  3 rows found