15.10 - SAMPLE Clause - Teradata Database

Teradata Database SQL Data Manipulation Language

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1146-151K

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.

where:

 

Syntax element …

Specifies …

SAMPLE

an 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

whether rows are sampled randomly across AMPS (RANDOMIZED ALLOCATION) or 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.

No more than 16 samples can be requested per fraction description.

count_description

a 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.

No more than 16 samples can be requested per count description.

WHEN

to test a set of conditions for truth.

search_condition

an 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

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

ELSE

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

END

the termination of the WHEN … THEN … ELSE clause.

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

The rules and restrictions are:

  • 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.
  • No more than 16 samples can be requested per fraction description or count description.
  • 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.
  • If an INSERT … SELECT statement specifies a SAMPLE clause that selects a set of rows from a source MULTISET table, but inserts them into a target SET table, and the sampled row set contains duplicates, the number of rows inserted into the target SET table might be fewer than the number requested in the SAMPLE clause.
  • The condition occurs because SET tables reject attempts to insert duplicate rows into them. The result is that the INSERT portion of the INSERT … SELECT statement inserts only distinct rows into SET target tables. As a result, the number of rows inserted into the target table can be fewer than the number specified in the SAMPLE clause.

    For example, if an INSERT … SELECT statement SAMPLE clause requests a sample size of 10 rows, and there are duplicate rows from the MULTISET source table in the collected sample, the system rejects the duplicate instances when it attempts to insert the sampled rows into the SET table and inserts only the distinct rows from the sample set. In other words, you could request a sample of 10 rows, but the actual number of rows inserted into the target table could be fewer than 10 if there are duplicate rows in the sampled row set.

    The system does not return any warning or information message when this condition occurs.

  • You cannot specify a SAMPLE clause in a WHERE, HAVING, QUALIFY, or ON clause of a subquery.
  • You cannot specify the SAMPLE clause in a SELECT statement that uses the set operators UNION, INTERSECT, or MINUS.
  • 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 a SAMPLE clause in a query that specifies the GROUP BY clause and any of the following extended grouping options.
  • CUBE
  • ROLLUP
  • GROUPING SETS
  • An exception is a query where the GROUP BY clause and extended grouping option appear in a derived table or view and the SAMPLE clause appears in the outer query.

  • A SELECT statement that includes the TOP n operator cannot also specify the SAMPLE clause.
  • 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 user specification.
  • You can specify sampling either with or without replacement.
  • You can specify sample allocation as either randomized or proportional.
  • You can use the SAMPLEID expression to identify the samples to which the rows belong. See “SAMPLEID Expression” on page 165.
  • Simple random sampling is a procedure in which every possible set of the requested size has an equal probability of being selected.

    Stratified random sampling, sometimes called proportional or quota random sampling, is a sampling method that divides a heterogeneous population of interest into homogeneous subgroups, or strata, and then takes a random sample from each of those subgroups.

    The result of this homogeneous stratification of the population is that stratified random sampling represents not only the overall population, but also key subgroups. For example, a retail application might divide a customer population into subgroups composed of customers who pay for their purchases with cash, those who pay by check, and those who buy on credit.

    The WITH REPLACEMENT option specifies that sampling is to be done with replacement. The default is sampling without replacement. Sampling without replacement is assumed implicitly if you do not specify WITH REPLACEMENT explicitly.

    When sampling with replacement, a sampled row, once sampled, is returned to the sampling pool. As a result, a row might be sampled multiple times. Because of this, it is possible to sample more rows than the number of rows in the input. When multiple samples are requested, all the samples are from the whole population and therefore not mutually exclusive.

    Sampling without replacement is analogous to selecting rows from a SET table in that each row sampled is unique, and once a row is sampled, is not returned to the sampling pool. As a result, requesting a number of samples greater than the cardinality of the table returns an error or warning. Whenever multiple samples are requested, they are mutually exclusive.

    The magnitude of the difference of the results obtained by these two methods varies with the size of the sample relative to the population. The smaller the sample relative to the population, the less the difference in the results of sampling with or without replacement.

    Randomized allocation means that the requested rows are allocated across the AMPs by simulating random sampling. This is a slow process, especially for large sample sizes, but it provides a simple random sample for the system as a whole.

    The default row allocation method is proportional. This means that the requested rows are allocated across the AMPs as a function of the number of rows on each AMP. This method is much faster than randomized allocation, especially for large sample sizes. Because proportional allocation does not include all possible sample sets, the resulting sample is not a simple random sample, but it has sufficient randomness to suit the needs of most applications.

    Note that simple random sampling, meaning that each element in the population has an equal and independent probability of being sampled, is employed for each AMP in the system regardless of the specified allocation method.

    One way to decide on the appropriate allocation method for your application is to determine whether it is acceptable to stratify the sampling input across the AMPs to achieve the corresponding performance gain, or whether you need to consider the table as a whole.

    The SAMPLEID value is simply 1, 2, 3, … n across n specified samples regardless of stratification. That is, for the following SAMPLE clause,