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 的解决方案。

谢谢 ！！！！

标签： sqlpostgresql

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