Chapter 21 – 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
The SAMPLE Function and Syntax
The syntax for the SAMPLE function:
SAMPLE [ WITH REPLACEMENT ]
[ RANDOMIZED ALLOCATION ]
[ WHEN <condition> THEN ]
{<number-of-rows> | <percentage>}
[…,<number-of-rows> | <percentage>]
[ ELSE {<number-of-rows> | <percentage }
END ]
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
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
Student_ID |
Course_ID |
125634 |
100 |
125634 |
220 |
231222 |
210 |
231222 |
220 |
260000 |
400 |
322133 |
220 |
322133 |
300 |
324652 |
200 |
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
Bring back 4 Samples with each sample having 10% of the rows.
SELECT Student_ID
,Course_ID
,SAMPLEID
FROM Student_Course_Table
SAMPLE .1, .1, .1, .1
ORDER BY SAMPLEID ;
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
Bring back 4 Samples with each sample having 10% of the rows and do a random sample across the entire population.
SELECT Student_ID
,Course_ID
,SAMPLEID
FROM Student_Course_Table
SAMPLE RANDOMIZED ALLOCATION .1, .1, .1, .1;
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
SELECT count(distinct(Course_ID))
FROM (SEL Course_ID
FROM Student_Course_Table
SAMPLE 5) TeraTom ;
COUNT(Distinct(Course_ID))
4
A second run of the same SELECT might very well yield these results:
COUNT(Distinct(Course_ID))
5
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 syntax for RANDOM is:
RANDOM(<low-literal-value>, <high-literal-value>)
The example below uses the RANDOM function to return a random number between 1 and 20:
SELECT RANDOM(1, 20);
Random(1,20)
14
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:
SELECT *
FROM Sales_Table
WHERE RANDOM (1, 100) = 5;
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:
SELECT Product_ID, COUNT(Daily_Sales)
FROM Sales_Table
GROUP BY 1
HAVING COUNT(Daily_Sales) > RANDOM(1, 10) ;
Product_ID |
Count(Daily_Sales) |
2000 |
7 |
3000 |
7 |
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.