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

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
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.