How to generate random 0 and 1 with 80-20 probability in sql server-CodePudding

I have a Table with 10 records, I have a column (name:RandomNumber) ,that its data type is bit . now I want to insert data in to this column randomly in such a way that 80 percent of record (8 record) get 0 randomly and 20 percent (2 record) get 1.

For Example Like this:

Id	RandomNumber
1	0
2	0
3	0
4	1
5	0
6	0
7	0
8	1
9	0
10	0

CodePudding user response：

One way is use ORDER BY NEWID() to assign 1 to two rows (20%) and assign 0 to others (remaining 80%) by excluding those assigned 1.

CREATE TABLE dbo.Example(
    Id int NOT NULL CONSTRAINT PK_Test PRIMARY KEY
);
INSERT INTO dbo.Example VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

WITH ones AS (
    SELECT TOP (2) Id, 1 AS RandomNumber
    FROM dbo.Example
    ORDER BY NEWID()
)
SELECT Id, 0 AS RandomNumber
FROM dbo.Example
WHERE Id NOT IN(SELECT Id FROM ones)
UNION ALL
SELECT Id, 1 AS RandomNumber
FROM ones
ORDER BY Id;

Alternatively, use ROW_NUMBER() OVER(ORDER BY NEWID()) and a CASE expression:

WITH example AS (
    SELECT Id, ROW_NUMBER() OVER(ORDER BY NEWID()) AS rownum
    FROM dbo.Example
)
SELECT Id, CASE WHEN rownum <= 2 THEN 1 ELSE 0 END AS RandomNumber
FROM example
ORDER BY Id;