All Forums Database
Yunfei Zhao 8 posts Joined 08/15
08 Aug 2016
How to do a random sample?

I want to do a simple random sample in Teradata. I have a table, My_Table, it has 2 columns, SSN and Tran_ID. This is for a bank, like Citibank, which has millions of customers (SSN), and each customer has hundres of transactions (Tran_ID). I want to do a simple random sample of 10% of customers (SSN). And get all the transactions for these random 10% customers. Also, I want to repeat this random sample, and each time I can get an identical sample. Can you please do me a favor by providing the syntax for doing this? Below is what I am thinking about, but am not sure it will achieve what I want, and produce identical random sample each time I run it. Thank you very much!
 
Select
c.SSN
,d.Tran_ID
From
(Select b.SSN
From
(Select DISTINCT a.SSN
From My_Table a) b
Sample 0.1 ) c
Left Outer Join My_Table d
on c.SSN=d.SSN
 
 
 
 

Glass 225 posts Joined 04/10
09 Aug 2016

Random sample will return different results each time.
You could insert  results into a work table and join to that each additional time.
 
Rglass

You must sign in to leave a comment.