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:
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.
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.
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,
|
|
SAMPLE WHEN state = 'CA' THEN
|
0.3,
|
0.2
|
ELSE
|
0.5,
|
0.2
|
|
the SAMPLEID correspondence would be: |
1 |
2 |
|
3 |
4 |
|