SAMPLE / TABLESAMPLE¶

Syntax¶

SELECT ...
FROM ...
  { SAMPLE | TABLESAMPLE } [ samplingMethod ]
[ ... ]

Where:

samplingMethod ::= { { BERNOULLI | ROW } ( { <probability> | <num> ROWS } ) |
                     { SYSTEM | BLOCK } ( <probability> ) [ { REPEATABLE | SEED } ( <seed> ) ] }

Copy

Parameters¶

{ BERNOULLI | ROW } or . { SYSTEM | BLOCK }

Specifies the sampling method to use:

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

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

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

In addition to using literals to specify probability or num ROWS, you can also use session or bind variables.

{ REPEATABLE | SEED ( seed ) }

Specifies a seed value to make the sampling deterministic. Can be any integer between 0 and 2147483647 inclusive. This parameter only applies to SYSTEM and BLOCK sampling.

In addition to using literals to specify seed, you can also use session or bind variables.

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 it is close to this value.

  • 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 (seed) aren’t 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 SEED (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 doesn’t reduce the number of rows joined and doesn’t reduce the cost of the join. The Examples section includes an example of sampling the result of a join.

• Both the LIMIT clause and the SAMPLE clause return a subset of rows from a table. When you use the LIMIT clause, Snowflake returns the specified number of rows in the fastest way possible. When you use the SAMPLE clause, Snowflake returns rows based on the sampling method specified in the clause.

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 might be slower than equivalent fraction-based sampling because fixed-size sampling prevents some query optimization.

Examples¶

The following examples use the SAMPLE clause.

SELECT * FROM testtable SAMPLE (10);

SELECT * FROM testtable TABLESAMPLE BERNOULLI (20.3);

SELECT * FROM testtable TABLESAMPLE (100);

SELECT * FROM testtable SAMPLE ROW (0);

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

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

SELECT *
  FROM (
       SELECT *
         FROM t1 JOIN t2
           ON t1.a = t2.c
       ) SAMPLE (1);

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

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

SELECT * FROM testtable SAMPLE (10 ROWS);