SAMPLE Clause Syntax | SQL SELECT Statements | Teradata Vantage - SAMPLE Clause Syntax - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
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.