SAMPLE
[ WITH REPLACEMENT ]
[ RANDOMIZED LOCALIZATION ]
{ { fraction_description | count_description } [,...] |
when_clause ]
}
Syntax Elements
- WITH REPLACEMENT
- Specifies that sampling is done by returning each sampled row to the table for possible redundant sampling.
- If you specify WITH REPLACEMENT, you can request more samples than there are rows in the table.
- If you omit WITH REPLACEMENT, sampled rows are not resampled.
- RANDOMIZED ALLOCATION
- Specifies that rows are sampled randomly across AMPS.
- If you omit RANDOMIZED ALLOCATION, rows are sampled proportionate to the number of qualified rows per AMP (proportional allocation).
- Proportional allocation provides a random sample stratified by AMPs, not a simple random sample of the entire population, but is much faster, especially for large samples.
- 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 the UDF is invoked within an expression and returns a value expression.
- To specify expressions that contain LOBs in a search condition, do one of the following:
- Cast the LOB to a non-LOB type (for example, cast BLOB to BYTE or VARBYTE, cast CLOB to CHARACTER or VARCHAR)
- Pass the LOB to a function whose result is not a LOB.
- To specify the value for a row-level security constraint in a search condition, use 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.