SAMPLE Clause - Teradata Database - Teradata Vantage NewSQL Engine

Purpose

Reduces the number of rows to be considered for further processing by returning one or more 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

Syntax Elements

SAMPLE

Introduction to a clause that permits sampling of rows in the SELECT statement.

WITH REPLACEMENT

Whether sampling is done by returning each sampled row to the table for possible redundant sampling or by withholding sampled rows from resampling.

If you specify WITH REPLACEMENT, then it is possible to request more samples than there are rows in the table.

Sampling without replacement is the default. You select it implicitly by not specifying WITH REPLACEMENT.

RANDOMIZED ALLOCATION

Rows are sampled randomly across AMPS. Otherwise, rows are sampled proportionate to the number of qualified rows per AMP (proportional allocation).

The proportional allocation option does not provide a simple random sample of the entire population. It provides a random sample stratified by AMPs, but it is much faster, especially for very large samples.

Proportional is the default. You select it implicitly by not specifying RANDOMIZED ALLOCATION.

fraction_description

Any set of unsigned floating point constant numbers in the closed interval (0,1) that specifies the percentage of rows to be sampled for a true search condition.

This is a comma-separated list of fractions, the sum of which must not exceed 1.

The value set specifies the percentage of the homogeneous subgroup defined by search_condition to be sampled for the report.

Up to 16 samples can be requested per fraction description.

count_description

Set of positive integer constants that specifies the number of rows to be sampled for a true search condition.

A warning is returned if there are not enough rows in the result to satisfy the sampling request completely.

Up to 16 samples can be requested per count description.

WHEN

Test a set of conditions for truth.

search_condition

Evaluation predicate that defines each homogeneous subgroup in the sample set.

You can only specify a scalar UDF for search_condition if it is invoked within an expression and returns a value expression.

You cannot specify expressions that contain LOBs in a search condition unless you first cast them to another type or pass them to a function whose result is not a LOB, for example, casting a BLOB to BYTE or VARBYTE or casting a CLOB to CHARACTER or VARCHAR.

If you specify the value for a row-level security constraint in a search condition, it must be expressed in its encoded form.

THEN

Apply the specified sampling fraction description or count description to the sample.

ELSE

Apply the specified sampling fraction description or count description to the sample if none of the WHEN condition predicates evaluates to true.

END

Termination of the WHEN … THEN … ELSE clause.

ANSI Compliance

The SAMPLE clause is a Teradata extension to the ANSI SQL:2011 standard.