本页尚未翻译

本页内容仍为英文原文，源自官方文档 sql/dql/sampling。中文翻译正在分批进行中。

Random sampling

TABLESAMPLE: draw a random subset of a table, by rows or by fragments.

Applies to: Embedded ❌, Enterprise ✅

Use TABLESAMPLE to explore a subset of a table or estimate aggregate statistics. The sampling method determines how much data is read and whether the table's physical layout influences the sample.

SELECT * FROM documents TABLESAMPLE (10)        -- 10 percent
SELECT * FROM documents TABLESAMPLE (500 ROWS)  -- 500 rows

A bare number is a percentage. Add ROWS for a count instead.

Rows or fragments

The standard calls these two methods of one clause. They are different operations, and the difference matters on Lance:

SELECT * FROM documents TABLESAMPLE BERNOULLI (10)
SELECT * FROM documents TABLESAMPLE SYSTEM (10)

Method	Aliases	What it picks
BERNOULLI	ROW	rows, each considered independently
SYSTEM	BLOCK	Lance fragments, and takes whatever rows they hold

BERNOULLI gives a genuinely uniform sample and reads across the whole table to get it. SYSTEM reads far less (it skips entire fragments), but the sample is only as uniform as the data's layout. If rows were written in an order that correlates with what you are measuring, a fragment sample inherits that correlation.

Use SYSTEM for a quick look at the data and BERNOULLI when you need independent row sampling. Omitting the method gives BERNOULLI.

Repeatability

Without a seed, every execution resamples: running the same query twice gives different rows. REPEATABLE fixes the draw:

SELECT * FROM documents TABLESAMPLE (10) REPEATABLE (42)

Use the same seed and table snapshot to reproduce a sampling run. For a fixed evaluation set, save the selected row IDs or materialize the sample and retain its version. A seed alone does not preserve membership after source changes or changes to the physical layout.

Where the clause can go

It attaches to a table in FROM, including inside a subquery, a CTE, or either side of a join:

SELECT * FROM (SELECT * FROM documents TABLESAMPLE (10)) d

SELECT * FROM a TABLESAMPLE (5 ROWS) JOIN b ON a.id = b.id

SELECT * FROM t a TABLESAMPLE (10) JOIN t b TABLESAMPLE (10) ON a.id = b.id

Each side of a join samples independently. Sampling both sides of a join shrinks the result far more than either fraction suggests, because a row only survives if both its sides were drawn.

Unsupported sampling forms

Sampling is semantics, not a hint. A clause that were read and then not applied would be a wrong answer rather than a missing feature, so anything that cannot be honoured exactly is an error:

TABLESAMPLE (101)	a percentage outside 0..=100 is refused, not clamped
TABLESAMPLE (1.5 ROWS)	a row count must be whole
TABLESAMPLE (BUCKET 1 OUT OF 4)	bucket sampling is not supported
(SELECT ...) d TABLESAMPLE (10)	a subquery's result cannot be sampled; sample the table inside it
the SAMPLE spelling	use TABLESAMPLE
the OFFSET modifier	not supported

说明

TABLESAMPLE is also refused on a WAL-backed table, where the rows a sample would draw from are not all in the fragments yet.

Sampling outside SQL

For a training loop rather than a query, the SDKs read a table in a shuffled order without going through SQL at all. See Training, where a permutation drives the data loader.