sql - postgresql 中带有计数的分层随机样本

Question

我通过 calculate_table() 过程生成了下表:

table(
  id integer,
  type integer
)

我想做一个分层随机样本，我选择一个随机 ID，按类型随机化，并返回类型计数和 ID 计数。

所以在下面的例子中:

id,type
1,1
2,1
3,1
4,1
5,2
6,2
7,2
8,3
9,4

随机化可以选择以下内容:

chosen_type: 2
-- how many unique types are there overall
type_count: 4
chosen_id: 6
-- of the type matching chosen_id, how many 
-- instances are there.
id_count: 3

所以会有 25% 的机会得到类型 2，如果选择类型 2，则有 33% 的机会得到 id 6。

以下是行不通的，因为它是从所有 ID 中随机选择的，与它们的类型无关，这不是我想要的。

select * from calculate_table()
order by random()
limit 1;

我在尝试避免多次调用 calculate_table() 过程和/或将内容存储在数组中时遇到问题。我该怎么做？

Answer 1

with t(id,type) as (values
    (1,1),(2,1),(3,1),(4,1),(5,2),(6,2),(7,2),(8,3),(9,4)
), dt as (
    select type, id
    from t
    group by 1,2
    order by random()
    limit 1
)
select
    type as chosen_type,
    (select count(distinct type) from t) as type_count,
    id as chosen_id,
    (select count(distinct id) from t where type = dt.type) as id_count
from dt;
 chosen_type | type_count | chosen_id | id_count 
-------------+------------+-----------+----------
           2 |          4 |         6 |        3