In SQL (Netezza) I have a group of ~25,000 cases that I would like to match with 3 controls per case from a pool of ~82,000,000 potential controls. I need the controls to match on several categorical variables and to be within a range (e.g., +/- 365 days) of several numerical variables. I have done this in SQL using a left join and a window function that assigns random numbers to each potential control for each case and selects 3 of those controls per case.
So far, so good, but there is a very small group of cases (~130) that receive the same control. I have tried adjusting the matching criteria so that they are more liberal, but I'm at the limits of that approach and still have this small number of cases that aren't receiving 3 controls that are distinct from the controls assigned to other cases. Essentially, I need to match without replacement, but I cannot figure out how to assign a sufficiently unique identifier to each control so that I can ensure it's not being reused after it has been assigned to a case.
Here is some example code with a very small toy data set. In this example, there are multiple matches for Sam and Pam - I want the match to be:
--------------
|Pam | Bird |
|Pam | Snake |
|Sam | Goat |
|Sam | Pig |
--------------
Not
--------------
|Pam | Bird |
|Pam | Snake |
|Sam | Snake |
|Sam | Pig |
--------------
The example is not working in Rextester, despite being copied exactly from my database program - I can't find any guidelines for Rextester so don't know if I have incorrectly specified the use of Netezza in Rextester (or if that's actually possible). Rextester says there is a syntax problem near the partition function, but it runs fine in my database program, except for the problem I am trying to solve. I am using the assignment of a random row number to limit the number of matches for each case (2 controls per case in the example below, 3 controls per case in my actual data).
#MySQL 5.7.12
#please drop objects you've created at the end of the script
#or check for their existance before creating
#'\\' is a delimiter
select version() as 'netezza';
create table owners (Name NVARCHAR(4), Energy INT, Walking INT, Swimming INT, Running INT);
insert into owners (Name, Energy, Walking, Swimming, Running)
values
('Tom', 4, 1, 1, 0),
('Tim', 7, 1, 1, 1),
('Pam', 1, 0, 0, 0)
('Sam', 1, 0, 0, 0);
create table animals (Name NVARCHAR(10), Energy INT, Walking INT, Swimming INT, Running INT);
insert into animals (Name, Energy, Walking, Swimming, Running)
values
('Cat', 5, 1, 1, 0),
('Horse', 4, 1, 1, 0),
('Rat', 3, 1, 1, 0),
('Pit', 7, 1, 1, 1),
('Pug', 5, 1, 1, 1),
('Fish', 1, 1, 1, 1),
('Bird', 2, 0, 0, 0),
('Snake', 1, 0, 0, 0),
('Goat', 3, 0, 0, 0)
('Pig', 1, 0, 0, 0);
create table pets as
select Owner_Name, Animal_Name, Owner_Energy, Animal_Energy,
Owner_Walking, Animal_Walking, Owner_Swimming, Animal_Swimming,
Owner_Running, Animal_Running
from
(select a.Name as Owner_Name, b.Name as Animal_Name,
a.Energy as Owner_Energy, b.Energy as Animal_Energy,
a.Walking as Owner_Walking, b.Walking as Animal_Walking,
a.Swimming as Owner_Swimming, b.Swimming as Animal_Swimming,
a.Running as Owner_Running, b.Running as Animal_Running,
row_number() over(partition by Owner_Name order by random()) as random_number
from owners as a
left join animals as b
on a.walking = b.walking and a.swimming = b.swimming and a.running = b.running
where a.energy between b.energy - 2 and b.energy + 2) as foo
where random_number < 3;
select * from pets;