Select n amount of random rows where n is proportionate to each value's % of total population

random, sql, sql-server

Solution

First, a simple random sample should do pretty well on representing the market sizes. What you are asking for is a stratified sample.

One way to get such a sample is to order the data randomly and assign a sequential number in each group. Then normalize the sequential number to be between 0 and 1, and finally order by the normalized value and choose the top "n" rows:

select top 100000 c.*
from (select c.*,
             row_number() over (partition by market order by rand(checksum(newid()))
                               ) as seqnum,
             count(*) over (partition by market) as cnt
      from customers c
     ) c
order by cast(seqnum as float) / cnt

It may be clear what is happening if you look at the data. Consider taking a sample of 5 from:

1    A
2    B
3    C
4    D
5    D
6    D
7    B
8    A
9    D
10   C

The first step assigns a sequential number randomly within each market:

1    A      1
2    B      1
3    C      1
4    D      1
5    D      2
6    D      3
7    B      2
8    A      2   
9    D      4
10   C      2

Next, normalize these values:

1    A      1      0.50
2    B      1      0.50
3    C      1      0.50
4    D      1      0.25
5    D      2      0.50
6    D      3      0.75
7    B      2      1.00
8    A      2      1.00
9    D      4      1.00
10   C      2      1.00

Now, if you take the top 5, you will get the first five values which is a stratified sample.

Problem

I have a table of 58 million customer records. Each customer has a market value (EN, US, FR etc.) I'm trying to select a 100k sample set which contains customers from every market. The ratio of customers per market in the sample must match the ratios in the actual table. So if UK customers account for 15% of the records in the customer table then there must be 15k UK customers in the 100k sample set and the same then for each market. Is there a way to do this?

Original source