Chapter 35

The SAMPLE Function

“The universe extends beyond the mind of man, and is more complex than the small sample one can study,”

– Kenneth L. Pike

   Table of Contents Chapter 35 – The SAMPLE Function

– The SAMPLE Function and Syntax

– SAMPLE Function Examples

– A SAMPLE Example that asks for Multiple Samples

– A SAMPLE Example with the SAMPLEID

– A SAMPLE Example WITH REPLACEMENT

– A SAMPLE Example with Four 10% Samples

– A Randomized SAMPLE

– A SAMPLE with Conditional Logic

– Aggregates and A SAMPLE using a Derived Table

– Random Number Generator

– Using Random to SELECT a Percentage of Rows

– Using Random and Aggregations

The SAMPLE Function and Syntax

The syntax for the SAMPLE function:

The Sampling function (SAMPLE) permits a SELECT to randomly return rows from a Teradata database table. It allows the request to specify either an absolute number of rows or a percentage of rows to return. Additionally, it provides an ability to return rows from multiple samples.

SAMPLE Function Examples

Bring back 5 rows

Bring back 25% of the rows

The above example uses the SAMPLE to get a random sample of the sales table. Notice that five rows came back because we asked for a SAMPLE 5 in the first example. In the second example we got a percentage of 25% of the rows.

A SAMPLE Example that asks for Multiple Samples

Sometimes, a single sampling of the data is not sufficient. The SAMPLE function can be used to request more than one sample by listing either the number of rows or the percentage of the rows to be returned. The above example uses the SAMPLE function to request multiple samples:

A SAMPLE Example with the SAMPLEID

Although multiple samples were taken, the rows came back as a single answer set consisting of 5 rows, 5 rows and then 4 rows of the data. The SAMPLEID column name can be used to distinguish between each sample. The last sample only brought back 4 rows because there are only 14 rows in the table and there will be no duplicates.

A SAMPLE Example WITH REPLACEMENT

At the same time, you may wish for rows to be available for all samples. The above example uses the SAMPLE WITH REPLACEMENT function with the SAMPLEID to request multiple samples and denote which sample each row came from.

A SAMPLE Example with Four 10% Samples

The above example uses the SAMPLE function with the SAMPLEID to request multiple samples as a percentage and denote which sample each row came from. Although 10% of 14 rows is 1.4, it can only return a whole row and therefore, 1 row is returned per sample. Also, since SAMPLEID is a column, it can be used as the sort key.

A Randomized SAMPLE

By default the SAMPLE function does a proportional sampling across all AMPs in the system. Therefore it is not a simple random sample across the entire population of rows. If you wish a random sample across the entire population, use the RANDOMIZED ALLOCATION as seen above.

A SAMPLE with Conditional Logic

The above query brings back two Samples with one row per sample if the Course_ID is <= 200. Else it bring back two Samples with two rows each if the Course_ID > 200. This means it will attempt to bring back six records total in four different samples.

Aggregates and A SAMPLE using a Derived Table

A second run of the same SELECT might very well yield these results:

Although they look like Aggregates, they are not normally compatible with them in the same SELECT list. As demonstrated here, aggregation can be performed, however, they must be calculated in a temporary or derived table.

The above example uses the SAMPLE function to request multiple samples to create a derived table (See Temporary Tables chapter). Then, the unique rows will be counted to show the random quality of the SAMPLE function:

Random Number Generator

The example below uses the RANDOM function to return a random number between 1 and 20:

SELECT RANDOM(1, 20);

The RANDOM function generates a random number that is inclusive for the numbers specified in the SQL that is greater than or equal to the first argument and less than or equal to the second argument.

The RANDOM function may be used in the SELECT list, in a CASE, in a WHERE clause, in a QUALIFY, in a HAVING and in an ORDER BY. The RANSOM function can be used creatively to provide some powerful functionality within SQL.

Using Random to SELECT a Percentage of Rows

The next SELECT uses RANDOM to randomly select 5% of the rows from the table:

There is roughly a 5% (1 out of 100) chance that a row will be returned using RANDOM in the WHERE clause, completely at random. Since SAMPLE randomly selects rows out of spool, currently RANDOM will be faster than SAMPLE. However, SAMPLE will be more accurate regarding the number of rows being returned with both the percent and row count.

Using Random and Aggregations

This example uses RANDOM to randomly generate a number that will determine which rows from the aggregation will be returned:

This last example uses RANDOM to randomly generate a number that will determine which rows from the aggregation will be returned. Whenever a random number is needed within the SQL, RANDOM is a great tool.