How to do a random sample?

Database
Enthusiast

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

1 REPLY
Enthusiast

Re: How to do a random sample?

Random sample will return different results each time.

You could insert  results into a work table and join to that each additional time.

Rglass