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]
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 bookHere 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.
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
hi how can i set a number variable within the function SAMPLE, par exemple : sel * from employeeTable sample N.
thanks an advance