SQL按组抽取随机样本

Question

我有一个大学毕业生数据库，并希望提取大约1000条数据的随机样本。

我希望确保样本代表人群，因此希望包括相同比例的课程，例如下图:

我可以使用以下方法实现这一点：

select top 500 id from degree where coursecode = 1 order by newid()
union
select top 300 id from degree where coursecode = 2 order by newid()
union
select top 200 id from degree where coursecode = 3 order by newid()

但我们有数百个课程代码，因此这将是耗时的。 我希望能够重用此代码以适用于不同的样本大小，并且不想通过查询和硬编码样本大小。

非常感谢任何帮助。

- Simon

---

那么如何确保我在样本中获得正确的比例呢？ - Simon

你如何计算样本大小？是基于人口百分比吗？ - Paresh J

样本将用于问卷调查，因此样本的大小取决于我们有多少预算...我知道这不是非常科学！ - Simon

Answer 1

您希望获得分层样本。我建议按课程代码对数据进行排序，并进行第n个样本。如果您有一个较大的人口规模，以下方法最有效：

select d.*
from (select d.*,
             row_number() over (order by coursecode, newid) as seqnum,
             count(*) over () as cnt
      from degree d
     ) d
where seqnum % (cnt / 500) = 1;

编辑：

您还可以动态计算每个组的人口大小：

select d.*
from (select d.*,
             row_number() over (partition by coursecode order by newid) as seqnum,
             count(*) over () as cnt,
             count(*) over (partition by coursecode) as cc_cnt
      from degree d
     ) d
where seqnum < 500 * (cc_cnt * 1.0 / cnt)

- Gordon Linoff

---

太棒了，谢谢。我刚刚用我的数据进行了快速检查，样本和总体比例之间最大的差异是0.748％，这是完全可接受的。 - Simon

分层抽样 - Clint Pachl

如果您想要保证恰好有一定数量的行（仍然是比例分层抽样），在这种情况下是1,000，但在我的使用情况中是20，该怎么办？ - JD136

Answer 2

不必对人口进行任何分区。

如果你从成百上千的课程代码中选取1000个样本，理所当然的是其中许多课程代码在任何一个采样中都不会被选择。

如果人口是均匀的（比如，连续的学生ID序列），那么均匀分布的样本将自动代表按课程代码加权的人口。由于newid()是均匀的随机采样器，因此可以直接使用。

唯一可能遇到的问题是，如果一个学生ID与多个课程代码相关联。在这种情况下，制作一个包含顺序ID、学生ID和课程代码的唯一列表（临时表或子查询），从中采样顺序ID，通过学生ID进行分组以删除重复项。

- Michael Eastwood

Answer 3

添加一个用于存储人口数据的表格。

我认为应该是这样的：

SELECT *
FROM (
    SELECT id, coursecode, ROW_NUMBER() OVER (PARTITION BY coursecode ORDER BY NEWID()) AS rn
    FROM degree) t
    LEFT OUTER JOIN
    population p ON t.coursecode = p.coursecode
WHERE
    rn <= p.SampleSize

- shA.t

Answer 4

我以前做过类似的查询（但不是在MS SQL上），使用了ROW_NUMBER的方法：

select ...
from 
 ( select ...
     ,row_number() over (partition by coursecode order by newid()) as rn
   from degree
 ) as d 
join sample size as s
on d.coursecode = s.coursecode
and d.rn <= s.samplesize

- dnoeth