sql - 选择 n 个随机行，其中 n 与每个值占总人口的百分比成比例

Question

我有一个包含 5800 万条客户记录的表。每个客户都有一个市场值(value)(英语、美国、法语等)

我正在尝试选择一个 10 万个样本集，其中包含来自每个市场的客户。样本中每个市场的客户比例必须与实际表中的比例相符。

因此，如果英国客户占客户表中记录的 15%，则 10 万个样本集中必定有 15,000 个英国客户，每个市场也是如此。

有办法做到这一点吗？

Answer 1

首先，简单的随机样本应该能够很好地代表市场规模。您要求的是分层样本。

获得此类样本的一种方法是对数据进行随机排序，并在每组中分配一个序号。然后将序列号标准化为 0 到 1 之间，最后按标准化值排序并选择前“n”行:

select top 100000 c.*
from (select c.*,
             row_number() over (partition by market order by rand(checksum(newid()))
                               ) as seqnum,
             count(*) over (partition by market) as cnt
      from customers c
     ) c
order by cast(seqnum as float) / cnt

如果您查看数据，可能会清楚发生了什么。考虑从以下位置抽取 5 个样本:

1    A
2    B
3    C
4    D
5    D
6    D
7    B
8    A
9    D
10   C

第一步在每个市场内随机分配一个序列号:

1    A      1
2    B      1
3    C      1
4    D      1
5    D      2
6    D      3
7    B      2
8    A      2   
9    D      4
10   C      2

接下来，标准化这些值:

1    A      1      0.50
2    B      1      0.50
3    C      1      0.50
4    D      1      0.25
5    D      2      0.50
6    D      3      0.75
7    B      2      1.00
8    A      2      1.00
9    D      4      1.00
10   C      2      1.00

现在，如果您取前 5 个值，您将获得前 5 个值，这是一个分层样本。