Teradata Sample Function

The Teradata SAMPLE function returns some specific amount of data from a table or view. It can be used when a smaller or more manageable data is desirable than the entire set of data from the table.

Teradata Sample Function Syntax

SELECT * FROM table sample n;

Here n can either of below two.

• An actual number of rows.
• A percentage of tables. (n can be any value between 1.00 and .00 )

If the number n is greater than the number of rows in the table, sample n will return all rows from the table.

Consider the following employee table.

Example

Select sample 5 records from the Employee table.

SELECT * FROM TERADATAPOINT.EMPLOYEE Sample 5;

*** Query completed. 5 rows found. 6 columns returned.
 *** Total elapsed time was 1 second.


      Empid   First_Name  Last_Name    Joining_Date       salary      dept_id
-----------  ----------  ----------  ------------  -----------  -----------
        107  Alexis      Bull          2015-01-12        36000           18
        109  Adam        Vollman       2013-05-17        19000           10
        103  Sarath      Sewall        2013-01-12        23000           15
        100  Gerald      Cambault      2001-03-16        25000           12
        105  Britney     Everett       2013-05-05        45000           14

Select 30% sample records from the Employee table.

SELECT * FROM TERADATAPOINT.EMPLOYEE Sample .30;

*** Query completed. 3 rows found. 6 columns returned.
 *** Total elapsed time was 1 second.

Empid       First_Name   Last_Name    Joining_Date       salary      dept_id
-----------  ----------  ----------  ------------  -----------  -----------
        107  Alexis      Bull          2015-01-12        36000           18
        103  Sarath      Sewall        2013-01-12        23000           15
        105  Britney     Everett       2013-05-05        45000           14

We have total 10 rows in the Employee table. 30 percent of total row is 3.33. So, the output is 3 rows.

Fractional result less than .4999 will not add any row.

SAMPLE using SAMPLEID

SAMPLEID is an extension for SAMPLE to get multiple set of sample in single query.

Example

SELECT Empid, SAMPLEID
FROM TERADATAPOINT.EMPLOYEE
SAMPLE  4,4,4
ORDER BY 2,1;

*** Query completed. 10 rows found. 2 columns returned.
 *** Warning: 7473 Requested sample is larger than table rows. All rows returned
 *** Total elapsed time was 1 second.

  Empid        SampleId
-----------  -----------
        103            1
        105            1
        107            1
        109            1
        101            2
        102            2
        104            2
        106            2
        100            3
        108            3

Example

SELECT Empid, SAMPLEID
FROM TERADATAPOINT.EMPLOYEE
SAMPLE .5,.2,.3
ORDERBY 2; 

*** Query completed. 10 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

     Empid      SampleId
-----------  -----------
        107            1
        109            1
        100            1
        106            1
        102            1
        103            2
        105            2
        108            3
        104            3
        101            3