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.