sql - 从大型数据集中随机抽样

我从一个很大的数据库中提取了一个研究人群。为了进行比较，我想选择一个具有相似特征的对照组。我想匹配的两个标准是年龄和性别。查询给我我想要匹配的数字是

select sex, age/10 as decades,COUNT(*) as counts
        from
        (
            select distinct m.patid
                ,m.sex,DATEPART(year,min(c.admitdate)) -m.yrdob as Age
                from members as m
                inner join claims as c on c.patid=m.PATID
                group by m.PATID, m.sex,m.yrdob
        )x group by sex, Age/10

结果集看起来像

这个时代的几十年专栏由以下表达式给出

(DATEPART(year,min(c.admitdate)) -m.yrdob)/10

并使用整数除法查找20-29岁，30-39岁等年龄段的人。例如，我想从更大的数据集中选择507位20多岁的女性。查找较大数据集特征的查询为

select distinct m.patid
        ,m.sex
        ,(DATEPART(year,min(c.admitdate)) -m.yrdob)/10 as decades
        from members as m
        inner join claims as c on c.patid=m.PATID
        group by m.PATID, m.sex,m.yrdob

编辑:来自第二个查询的结果

因此，我需要第二个查询中几十个列的sum与第一个查询中的counts相等。下面是我尝试过的结果(返回零结果)。我要怎么做才能适应这些年龄？

查询运行，但不返回结果:

select x.PATID--,x.sex,x.decades,y.counts
    from
    (

    select distinct m.patid
        ,m.sex
        ,(DATEPART(year,min(c.admitdate)) -m.yrdob)/10 as decades
        from members as m
        inner join claims as c on c.patid=m.PATID
        group by m.PATID, m.sex,m.yrdob
    ) as x
    inner join
    (

        select sex, age/10 as decades,COUNT(*) as counts
        from
        (
            select distinct m.patid
                ,m.sex,DATEPART(year,min(c.admitdate)) -m.yrdob as Age
                from members as m
                inner join claims as c on c.patid=m.PATID
                group by m.PATID, m.sex,m.yrdob
        )x group by sex, Age/10
    ) as y on x.sex=y.sex and x.decades=y.decades
    group by y.counts,x.PATID,x.sex,y.sex
    having SUM(x.decades)=y.counts and x.sex=y.sex

最佳答案

select
   T1.sex,
   T1.decades,
   T1.counts,
   T2.patid

from (

   select
      sex,
      age/10 as decades,
      COUNT(*) as counts
   from (

      select  m.patid,
         m.sex,
         DATEPART(year,min(c.admitdate)) -m.yrdob as Age
      from members as m
      inner join claims as c on c.patid=m.PATID
      group by m.PATID, m.sex,m.yrdob
   )x
   group by sex, Age/10
) as T1
join (
   --right here is where the random sampling occurs
    SELECT TOP 50--this is the total number of peolpe in our dataset
      patid
      ,sex
      ,decades

   from (
      select  m.patid,
         m.sex,
         (DATEPART(year,min(c.admitdate)) -m.yrdob)/10 as decades
      from members as m
      inner join claims as c on c.patid=m.PATID
      group by m.PATID, m.sex, m.yrdob

   ) T2
      order by NEWID()
) as T2
on T2.sex = T1.sex
and T2.decades = T1.decades

编辑:我发布了另一个与此类似的问题，在该问题中，我发现我的结果实际上并不是随机的，但它们只是TOP N个结果。我已经在最外面的查询中按newid()进行了排序，所有操作都围绕着完全相同的结果集进行了改组。从一个现已结束的问题中，我发现我需要在上述查询的注释行中将TOP关键字与order by newid()一起使用。

关于sql - 从大型数据集中随机抽样，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13627486/