Random rows from sql table with a percent from column values

user_1549435 0
Viewed 54

I am trying to get random rows selected from the data below

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

I want to select 70% from statusid 3 and 10% from statusid 2 for each user. I tried with below code but I am unable to get all users getting the distribution reliably

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

Quantities as below for example is acceptable

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
tsql sql-server sql
1 Answers

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

If I understand this correctly, use window functions:

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);
user_11440350
Related