December 15

0 comments

The Teradata Sample Function

By Roland Wenzlofsky

December 15, 2019


What is the Teradata Sample Function?

The SAMPLE function returns a number of rows that were randomly selected. These are the features:

  • Ask for a sample with an absolute number of rows
  • Ask for a sample which contains a certain percentage of the table rows 
  • Ask for more than one sample at the same time
  • Include the column SAMPLEID to determine from which sample a row originates
  • Ask for samples with or without duplicates

?

The Syntax of the Sample Function

SAMPLE [WITH REPLACEMENT]
[RANDOMIZED ALLOCATION]
[WHEN <condition> THEN] {<number of row> | <percentage>}
[...,<number-of-rows> | <percentage>]
[ELSE {<number of rows> | <percentage }
END]

Here are some examples of how to use the SAMPLE function:

100 Random Rows

SELECT * FROM Customer
SAMPLE 100;

10% of Table Rows

SELECT * FROM Customer
SAMPLE .10;

2 Samples with 10% of Table Rows (no Duplicates)

SELECT *
FROM Customer
SAMPLE .1,.1
;

2 Samples with 10 Rows and SAMPLEID

SELECT
   t01.* ,
   SAMPLEID
FROM Customer t01
SAMPLE 10,10
;

2 Samples with 10% of Table Rows (with Duplicates)

SELECT *
FROM Customer
SAMPLE WITH REPLACEMENT .1,.1
;


2 Samples with Conditional Logic Applied 

SELECT * 
FROM Customer
SAMPLE
WHEN CustomerId > 1000
THEN .1,.1
ELSE .3,.3 END
;

Roland Wenzlofsky


Roland Wenzlofsky is a graduated computer scientist and Data Warehouse professional working with the Teradata database system for more than 20 years. He is experienced in the fields of banking and telecommunication with a strong focus on performance optimization.

You might also like

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Never miss a good story!

 Subscribe to our newsletter to keep up with the latest trends!

>