带有组的SQL随机样本

我想确保样本代表人口,所以希望包括相同比例的课程,例如

我可以使用以下方法执行此操作：

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()

但是我们有数百个课程代码,所以这将耗费时间,我希望能够将这些代码重用于不同的样本大小,并且不特别想要查看查询并对样本大小进行硬编码.

任何帮助将不胜感激

解决方法

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)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!