how to segment groups based on different criteria

Question

I'm trying to assign test and control groups based on A to F columns values to the table below.

Eventually, I want a table look like below. If different zips have the same values for all columns, then assign half zips to test and half to control. If the total number of zips cannot be equally assigned, then give the extra zip to control.

Answer 1

I think a stratified sample will do what you want:

select t.*,
       (case when mod(row_number() over (order by a, b, c, d, e, f), 2) = 1
             then 'C' else 'T'
        end) as test_group
from t;

This is not exactly how you phrased the question, but it should have the same effect of splitting rows with the same values in the columns evenly in the two groups. When there are odd numbers, sometimes the extra will go to test and sometimes to control.

It is unclear from the question whether you want balanced control and test groups -- which is what I would expect. If you actually want all groups with odd numbers to go to control (as you suggest), then all the onesies will be in the control and that seems biased to me.