Assigning values randomly to percentage of records
I'm creating a table of customer records that are split into multiple segments, and I need to randomly assign 0 or 1 to a proportion of these.
5% of each of the 8 category combinations should have 1 assigned (my control group) and 95% should have 0 assigned (mailed group).
My table looks like this at the moment:
Account_number INT (e.g. 123456)
Category1 INT (1 or 0)
Category2 INT (1 or 0)
Category3 INT (1 or 0)
Category4 INT (1 or 0)
Ideally I will just have an additional field in the table 'Controlflag' which will have a 0 or a 1. But I want to ensure the split is fair and proportionate, as the amount of customers in each combination of categories differs.
cat 1 cat 2 cat 3 CUSTOMERS PROPORTION 5% CONTROL
NONE 0 0 0 13,279,830 26% 663,992
CAT 2 ONLY 0 1 0 7,044 0% 352
ALL 1 1 1 1,514,488 3% 75,724
CAT 1 AND 3 1 0 1 1,351,439 3% 67,572
CAT 3 ONLY 0 0 1 28,989,956 57% 1,449,498
CAT 1 ONLT 1 0 0 29,637 0% 1,482
CAT 2 AND 3 0 1 1 5,410,809 11% 270,540
CAT 1 AND 2 1 1 0 12,044 0% 602
50,595,247 2,529,762
(I'm using DB2 SQL)
I think I use the RAND function and the IDX function but not sure how to write it. (I'm using IBM DB2 SQL)
Thanks in advance!