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> ) ]
[ ... ]
```

Where:

```samplingMethod ::= { { BERNOULLI | ROW } |
{ SYSTEM | BLOCK } }
```
`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);
```
• 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);
```
• 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);
```

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);
```

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

```SELECT * FROM testtable TABLESAMPLE (100);
```

Return an empty sample:

```SELECT * FROM testtable SAMPLE ROW (0);
```

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
;
```

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
;
```

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);
```

### 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);
```

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);
```

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);
```
{"ft_lang_label":"__label__en","ft_lang_prob":0.8129676,"math_prob":0.9394561,"size":6563,"snap":"2023-14-2023-23","text_gpt3_token_len":1551,"char_repetition_ratio":0.17334959,"word_repetition_ratio":0.12522203,"special_character_ratio":0.23998171,"punctuation_ratio":0.114914425,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9904518,"pos_list":[0,1,2],"im_url_duplicate_count":[null,null,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2023-05-28T13:32:59Z\",\"WARC-Record-ID\":\"<urn:uuid:9f1401b3-9e34-47fb-bd8f-39cd37f2efa6>\",\"Content-Length\":\"340290\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:c415167d-dde9-452a-8e73-e8ea6d7b30af>\",\"WARC-Concurrent-To\":\"<urn:uuid:8f5dc41f-c712-4d65-8b84-2836f9997e0a>\",\"WARC-IP-Address\":\"18.165.83.32\",\"WARC-Target-URI\":\"https://docs.snowflake.com/en/sql-reference/constructs/sample\",\"WARC-Payload-Digest\":\"sha1:FYQZ4R2U3DY7UZDKQR7U5WKVYZOKB3V2\",\"WARC-Block-Digest\":\"sha1:VW3X6KEMRSGPFYMU67FLK6IDDY4DDIM3\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2023/CC-MAIN-2023-23/CC-MAIN-2023-23_segments_1685224643784.62_warc_CC-MAIN-20230528114832-20230528144832-00225.warc.gz\"}"}