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.

Empid First_Name Last_Name Joining_Date salary dept_id
107 Alexis Bull 1/12/2015 36000 18
109 Adam Vollman 5/17/2013 19000 10
103 Sarath Sewall 1/12/2013 23000 15
108 Adam Landry 12/11/2013 42000 18
104 Anthony Gietz 6/12/2014 13000 12
100 Gerald Cambault 3/16/2001 25000 12
101 Renske Ladwig 5/25/2003 10000 12
106 Jennifer Cabrio 6/16/2012 29000 10
102 Janette King 12/10/2010 26000 15
105 Britney Everett 5/5/2013 45000 14

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
 

As you can see from the above example, we have selected 3 samples. In the Employee table, we have 10 rows, so for the third sample there is no enough row to return. So, we have received the warning message in BTEQ environment.

 

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

Post Views: 12,890

You may also like this!