Sample Function

Description

Reduces the number of rows to be considered for further processing by returning mutually exclusive samples of rows specified either as a list of fractions of the total number of rows or as a list of numbers of rows from the SELECT query.

Syntax:

Sample(fractional_description [count_description])

fractional_description

Represents any number of floating point constants in the closed interval (0, 1) and separated by a comma. This is a list of fractions, the sum of which must not exceed 1.

count_description

Represents a positive integer constant list of row counts. A warning is returned if there are not enough rows in the result to satisfy the sampling request completely.

Usage Notes

No more than 16 samples can be requested per SELECT statement.

SAMPLE operates on the evaluated output of the table expression, which can include a WHERE clause and GROUP BY, HAVING, or QUALIFY clauses, sampling the result according to use specification.

A sampling request cannot be repeated. The identical sampling query run twice against the same data will report different rows in the result.

Sampling can be used in a derived table, view, or INSERT-SELECT to reduce the number of rows to be considered for further computation.

You cannot use SAMPLE in a subquery.

If a fraction_description results in no rows being returned, a warning is generated.

If a count_description cannot be completely satisfied, a warning is generated and the sample size is reduced to the number of remaining rows.