Categories:

Query Syntax

SAMPLE / TABLESAMPLE¶

Returns a subset of rows sampled randomly from the specified table. The following sampling methods are supported:

  • Sample a fraction of a table, with a specified probability for including a given row. The number of rows returned depends on the size of the table and the requested probability. A seed can be specified to make the sampling deterministic.

  • Sample a fixed, specified number of rows. The exact number of specified rows is returned unless the table contains fewer rows.

SAMPLE and TABLESAMPLE are synonymous and can be used interchangeably.

Syntax¶

SELECT ...
FROM ...
  { SAMPLE | TABLESAMPLE } [ samplingMethod ] ( { <probability> | <num> ROWS } ) [ { REPEATABLE | SEED } ( <seed> ) ]
[ ... ]
Copy

Where:

samplingMethod ::= { { BERNOULLI | ROW } |
                     { SYSTEM | BLOCK } }
Copy
BERNOULLI | ROW or . SYSTEM | BLOCK

Specifies the sampling method to use:

  • BERNOULLI (or ROW): Includes each row with a probability of p/100. Similar to flipping a weighted coin for each row.

  • SYSTEM (or BLOCK): Includes each block of rows with a probability of p/100. Similar to flipping a weighted coin for each block of rows. This method does not support fixed-size sampling.

Sampling method is optional. If no method is specified, the default is BERNOULLI.

probability or . num ROWS

Specifies whether to sample based on a fraction of the table or a fixed number of rows in the table, where:

  • probability specifies the percentage probability to use for selecting the sample. Can be any decimal number between 0 (no rows selected) and 100 (all rows selected) inclusive.

  • num specifies the number of rows (up to 1,000,000) to sample from the table. Can be any integer between 0 (no rows selected) and 1000000 inclusive.

REPEATABLE | SEED ( seed )

Specifies a seed value to make the sampling deterministic. Can be any integer between 0 and 2147483647 inclusive.

Usage Notes¶

  • The following keywords can be used interchangeably:

    • SAMPLE | TABLESAMPLE

    • BERNOULLI | ROW

    • SYSTEM | BLOCK

    • REPEATABLE | SEED

  • The number of rows returned depends on the sampling method specified:

    Fraction-based:
    • For BERNOULLI | ROW sampling, the expected number of returned rows is (p/100)*n.

    • For SYSTEM | BLOCK sampling, the sample might be biased, in particular for small tables.

    Note

    For very large tables, the difference between the two methods should be negligible.

    Also, because sampling is a probabilistic process, the number of rows returned is not exactly equal to (p/100)*n rows, but is close.

    • If no seed is specified, SAMPLE generates different results when the same query is repeated.

    • If a table does not change, and the same seed and probability are specified, SAMPLE generates the same result. However, sampling on a copy of a table might not return the same result as sampling on the original table, even if the same probability and seed are specified.

    Fixed-size:
    • If the table is larger than the requested number of rows, the number of requested rows is always returned.

    • If the table is smaller than the requested number of rows, the entire table is returned.

    • SYSTEM | BLOCK and seed are not supported for fixed-size sampling. For example, the following queries produce errors:

      select * from example_table sample system (10 rows);
      
      select * from example_table sample row (10 rows) seed (99);
      
      Copy
  • Sampling with a seed is not supported on views or subqueries. For example, the following query produces an error:

    select * from (select * from example_table) sample (1) seed (99);
    
    Copy
  • Sampling the result of a JOIN is allowed, but only when all of the following are true:

    • The sample is row-based (Bernoulli).

    • The sampling does not use a seed.

    The sampling is done after the join has been fully processed. Therefore, sampling does not reduce the number of rows joined and does not reduce the cost of the JOIN. The Examples section includes an example of sampling the result of a JOIN.

  • In addition to using literals to specify probability | num ROWS and seed, session or bind variables can also be used.

Performance Considerations¶

  • SYSTEM | BLOCK sampling is often faster than BERNOULLI | ROW sampling.

  • Sampling without a seed is often faster than sampling with a seed.

  • Fixed-size sampling can be slower than equivalent fraction-based sampling because fixed-size sampling prevents some query optimization.

Examples¶

Fraction-based Row Sampling¶

Return a sample of a table in which each row has a 10% probability of being included in the sample:

SELECT * FROM testtable SAMPLE (10);
Copy

Return a sample of a table in which each row has a 20.3% probability of being included in the sample:

SELECT * FROM testtable TABLESAMPLE BERNOULLI (20.3);
Copy

Return an entire table, including all rows in the table:

SELECT * FROM testtable TABLESAMPLE (100);
Copy

Return an empty sample:

SELECT * FROM testtable SAMPLE ROW (0);
Copy

This example shows how to sample multiple tables in a join:

SELECT i, j
    FROM
         table1 AS t1 SAMPLE (25)     -- 25% of rows in table1
             INNER JOIN
         table2 AS t2 SAMPLE (50)     -- 50% of rows in table2
    WHERE t2.j = t1.i
    ;
Copy

The SAMPLE clause applies to only one table, not all preceding tables or the entire expression prior to the SAMPLE clause. The following JOIN operation joins all rows of t1 to a sample of 50% of the rows in table2; it does not sample 50% of the rows that result from joining all rows in both tables:

SELECT i, j
    FROM table1 AS t1 INNER JOIN table2 AS t2 SAMPLE (50)
    WHERE t2.j = t1.i
    ;
Copy

To apply the SAMPLE clause to the result of a JOIN, rather than to the individual tables in the JOIN, apply the JOIN to an inline view that contains the result of the JOIN. For example, perform the JOIN as a subquery, and then apply the SAMPLE to the result of the subquery. The example below samples approximately 1% of the rows returned by the JOIN:

select *
   from (
         select *
            from t1 join t2
               on t1.a = t2.c
        ) sample (1);
Copy

Fraction-based Block Sampling (with Seeds)¶

Return a sample of a table in which each block of rows has a 3% probability of being included in the sample, and set the seed to 82:

SELECT * FROM testtable SAMPLE SYSTEM (3) SEED (82);
Copy

Return a sample of a table in which each block of rows has a 0.012% probability of being included in the sample, and set the seed to 99992:

SELECT * FROM testtable SAMPLE BLOCK (0.012) REPEATABLE (99992);
Copy

Note

If either of these queries are run again without making any changes to the table, they return the same sample set.

Fixed-size Row Sampling¶

Return a fixed-size sample of 10 rows in which each row has a min(1, 10/n) probability of being included in the sample, where n is the number of rows in the table:

SELECT * FROM testtable SAMPLE (10 ROWS);
Copy