sql - sql 表中的随机行，列值的百分比

Question

我正在尝试从下面的数据中随机选择行

--drop table #sampletable
create table #sampletable (id int identity(1,1), userid int, statusid int, createddt datetime)

insert into #sampletable (userid, statusid, createddt) values (1001, 2, DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 86400), '2021-01-29'))
insert into #sampletable (userid, statusid, createddt) values (1001, 2, DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 86400), '2021-01-29'))
insert into #sampletable (userid, statusid, createddt) values (1001, 3, DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 86400), '2021-01-29'))
insert into #sampletable (userid, statusid, createddt) values (1001, 2, DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 86400), '2021-01-29'))
insert into #sampletable (userid, statusid, createddt) values (1001, 3, DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 86400), '2021-01-29'))
insert into #sampletable (userid, statusid, createddt) values (1001, 2, DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 86400), '2021-01-29'))
insert into #sampletable (userid, statusid, createddt) values (1001, 2, DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 86400), '2021-01-29'))

insert into #sampletable (userid, statusid, createddt) values (1002, 2, DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 86400), '2021-01-29'))
insert into #sampletable (userid, statusid, createddt) values (1002, 3, DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 86400), '2021-01-29'))
insert into #sampletable (userid, statusid, createddt) values (1002, 2, DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 86400), '2021-01-29'))
insert into #sampletable (userid, statusid, createddt) values (1002, 3, DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 86400), '2021-01-29'))
insert into #sampletable (userid, statusid, createddt) values (1002, 3, DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 86400), '2021-01-29'))

insert into #sampletable (userid, statusid, createddt) values (1003, 2, DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 86400), '2021-01-29'))
insert into #sampletable (userid, statusid, createddt) values (1003, 2, DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 86400), '2021-01-29'))
insert into #sampletable (userid, statusid, createddt) values (1003, 3, DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 86400), '2021-01-29'))
insert into #sampletable (userid, statusid, createddt) values (1003, 2, DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 86400), '2021-01-29'))
insert into #sampletable (userid, statusid, createddt) values (1003, 2, DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 86400), '2021-01-29'))
insert into #sampletable (userid, statusid, createddt) values (1003, 3, DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 86400), '2021-01-29'))
insert into #sampletable (userid, statusid, createddt) values (1003, 2, DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 86400), '2021-01-29'))
insert into #sampletable (userid, statusid, createddt) values (1003, 2, DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 86400), '2021-01-29'))
insert into #sampletable (userid, statusid, createddt) values (1003, 2, DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 86400), '2021-01-29'))
insert into #sampletable (userid, statusid, createddt) values (1003, 2, DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 86400), '2021-01-29'))

我想为每个用户从 statusid 3 中选择 70%，从 statusid 2 中选择 10%。
我尝试使用以下代码，但无法让所有用户可靠地获得分发

WITH randomizedtable AS (
    SELECT *, NEWID() as random FROM #sampletable where statusid = 3
),
groupedtable AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY userid ORDER BY random) AS rowcnt FROM randomizedtable
)
SELECT  TOP 70 PERCENT * FROM groupedtable ORDER BY NEWID();

WITH randomizedtable2 AS (
    SELECT *, NEWID() as random FROM #sampletable where statusid = 2
),
groupedtable2 AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY userid ORDER BY random) AS rowcnt FROM randomizedtable2
)
SELECT  TOP 10 PERCENT * FROM groupedtable2 ORDER BY NEWID();

例如下面的数量是可以接受的

drop table #sampletablecounts
create table #sampletablecounts (userid int, status2 int, status3 int, status2percent int, status3percent int)

insert into #sampletablecounts (userid, status2, status3, status2percent, status3percent) 
select userid, 
sum(case when statusid = 2 then 1 else 0 end) status2, 
sum(case when statusid = 3 then 1 else 0 end) status3, 
floor(sum(case when statusid = 2 then 1 else 0 end) * .3) status2percent, 
floor(sum(case when statusid = 3 then 1 else 0 end) * .7) status3percent
from #sampletable 
group by userid
order by userid

Answer 1

I want to select 70% from statusid 3 and 10% from statusid 2 for each user.

如果我理解正确，请使用窗口函数:

select st.*
from (select st.*,
             row_number() over (partition by userid, statusid order by newid()) as seqnum,
             count(*) over (partition by userid, statusid) as cnt
      from #sampletable st
      where statusid in (2, 3)
     ) st
where (statusid = 2 and seqnum <= 0.1 * cnt) or
      (statusid = 3 and seqnum <= 0.7 * cnt);