DWHPro Insights

Understanding Teradata’s SAMPLE Function: Randomly Select Rows and More

By Roland Wenzlofsky ·

Understanding Teradata’s SAMPLE Function: Randomly Select Rows and More

What is the Teradata Sample Function?

The SAMPLE function returns a set of randomly selected rows. Here are the characteristics:

  • Ask for a sample with an absolute number of rows
  • Ask for a sample that 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
  • Without replacement means a physical input row is not selected repeatedly by that sample. MULTISET input can still contain duplicate value tuples; WITH REPLACEMENT permits repeated selection of an input row.

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]

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

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;

Without replacement means a physical input row is not selected repeatedly by that sample. MULTISET input can still contain duplicate value tuples; WITH REPLACEMENT permits repeated selection of an input row.

SELECT * FROM Customer SAMPLE .1,.1;

2 Samples with 10 Rows and SAMPLEID

SELECT    t01.* ,   SAMPLEID FROM Customer t01 SAMPLE 10,10;
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;

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

1 thought on “Understanding Teradata’s SAMPLE Function: Randomly Select Rows and More”

  1. hi how can i set a number variable within the function SAMPLE, par exemple : sel * from employeeTable sample N.
    thanks an advance

    Reply

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam