How to sample from different values in a column but only return records that are unique from another column?

user_10263702 0
Viewed 162

I am struggling with a sampling issue using Teradata

Below is the format of the data

ID    Group     Rank
1     dog       1 
1     cat       1 
1     lion      1  
1     elephant  2 
2     dog       1 
2     cat       1 
2     lion      1 
2     elephant  1 
3     dog       1
3     cat       2 
3     lion      1 
3     elephant  1 
4     dog       2 
4     cat       1 
4     lion      1 
4     elephant  1 
...

I would ideally like to return a sample number for each entry in Group but with only unique values from ID.

Below is the current query I produced but this returns duplicates for ID

SELECT ID, Group FROM Table 
WHERE rank = 1 
SAMPLE 
 WHEN group = 'dog' then 10
 WHEN group = 'cat' then 10
 WHEN group = 'elephant' then 5
 WHEN group = 'lion' then 5
END
teradata sql
2 Answers
with cte as
 (
   SELECT ID, Group,
      random(1,10000) as rnd -- RANDOM can't be directly used in OLAP-functions
   FROM Table 
   WHERE rank = 1 
 )
SELECT ID, Group
FROM cte
QUALIFY 
   ROW_NUMBER() -- get one random row per ID
   OVER (PARTITION BY ID 
         ORDER BY rnd) = 1
SAMPLE 
 WHEN group = 'dog' then 10
 WHEN group = 'cat' then 10
 WHEN group = 'elephant' then 5
 WHEN group = 'lion' then 5
END
user_25279050

Assuming you have enough records, choose a random row for each id and then choose the appropriate numbers from that:

select t.*
from (select t.*,
             row_number() over (partition by group order by seqnum) as sequm_g
      from (select t.*,
                   row_number() over (partition by id order by random(1, 1000000))
            from t
           ) t
      where seqnum = 1
     ) t
where (group in ('dog', 'cat') and seqnum_g <= 10) or
      (group in ('elephant', 'lion') and seqnum_g <= 5) ;

This doesn't guarantee that the groups will be big enough in the result set. But if you have enough data relative to the size of the groups, then it should work.

user_11440350
Related