SAMPLE Clause Syntax | SQL SELECT Statements | Teradata Vantage - SAMPLE Clause Syntax - Analytics Database - Teradata Vantage

SAMPLE
  [ WITH REPLACEMENT ]
  [ RANDOMIZED LOCALIZATION ]
  { { fraction_description | count_description } [,...] |
    when_clause ]
  }

Syntax Elements

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_clause

WHEN condition THEN { fraction_description | count_description } [,...] END

condition

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

You can only specify a scalar UDF for 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.