本页尚未翻译

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

Statistical Sampling

Draw a random subset of a table with SQL TABLESAMPLE, by rows or by fragments.

Applies to: Embedded ❌, Enterprise ✅

Statistical sampling draws a random subset of a table's rows using SQL's TABLESAMPLE clause, attached directly to a table in FROM. Use it to explore unfamiliar data, run a smaller experiment, or estimate aggregate statistics without reading every row. For a random subset through the Python SDK instead -- in Embedded or Enterprise -- see Permutation. To select specific, known rows rather than a random fraction, see Selective Sampling.

Example data

Connect with an Enterprise SQL client. The examples use a small document table and a separate label table. Expand the setup below to create them.

Create the example tables

curation_documents has six records describing four documents. curation_labels has one label for each of three documents. row_id identifies a record; doc_id identifies the document it describes.

Run each statement separately in a scratch database or namespace. Create these tables once for the whole section, using fresh names if they already exist.

CREATE TABLE curation_documents (
    row_id BIGINT, doc_id BIGINT, version BIGINT,
    source VARCHAR, caption VARCHAR, score DOUBLE
);
INSERT INTO curation_documents VALUES
    (1, 10, 1, 'web', NULL, 0.80),
    (2, 10, 2, 'web', 'A cat on a chair', 0.95),
    (3, 20, 1, 'books', 'A dog in a field', 0.90),
    (4, 30, 1, 'web', NULL, 0.70),
    (5, 30, 1, 'web', 'A cat by a window', 0.85),
    (6, 40, 1, 'books', 'A bird in a tree', 0.60);

CREATE TABLE curation_labels (doc_id BIGINT, label VARCHAR);
INSERT INTO curation_labels VALUES
    (10, 'cat'), (20, 'dog'), (30, 'cat');

score is a sample quality score, not a search result score. Document 10 has two versions. Document 30 has two records at the same version. Document 40 has no label. These cases let you check the effect of each operation.

Basic sampling

A percentage selects a fraction of the source; ROWS requests a row count:

SELECT row_id, doc_id FROM curation_documents TABLESAMPLE (50);
SELECT row_id, doc_id FROM curation_documents TABLESAMPLE (3 ROWS);

A percentage sample does not guarantee an exact number of rows. Use a count when the desired sample size is the main constraint, and inspect the result rather than assuming it contains every category you need.

TABLESAMPLE requires a table that is not WAL-backed. See the sampling reference for this restriction; do not change a production table's write configuration just to try an example.

Sampling methods

Choose a method according to how records should be selected:

Method	Selection	When to use it
BERNOULLI (default)	Select individual rows	When records should be sampled independently of their storage fragments
SYSTEM	Select whole Lance fragments	When reducing reads matters and grouping by fragment is acceptable

SELECT row_id, doc_id
FROM curation_documents TABLESAMPLE BERNOULLI (50);

SELECT row_id, doc_id
FROM curation_documents TABLESAMPLE SYSTEM (50);

If write order groups records by label, date or source, a fragment sample can reflect that grouping. On a tiny table stored in one fragment, it may include all records or none. Row sampling also needs an adequate sample size to represent rare groups; check that the resulting sample covers the groups you need.

Repeatable samples

A seed requests a repeatable draw:

SELECT row_id, doc_id
FROM curation_documents TABLESAMPLE (50) REPEATABLE (42);

Keep the source data and sampling configuration fixed when comparing draws. For a lasting evaluation set, save the selected records rather than relying on a seed to preserve membership as the source changes:

CREATE TABLE curation_eval AS
SELECT * FROM curation_documents TABLESAMPLE (50) REPEATABLE (42);

Create this output once with a fresh name and keep it unchanged. Record the source version and selection policy. Sampling records independently can place revisions of the same document into different splits; decide the grouping policy before using the result for training or evaluation.

Sampling with joins

Sampling both input tables independently changes which pairs can match. For a one-to-one match, independent 10% row samples retain a given pair with about 1% probability, not 10%.

To sample documents while retaining their available labels, sample only the document table:

SELECT curation_documents.row_id, curation_documents.doc_id, l.label
FROM curation_documents TABLESAMPLE (50)
JOIN curation_labels AS l ON l.doc_id = curation_documents.doc_id;

This samples source records, not distinct documents. If you need to sample the result of a join or deduplication, first save that result as a table suitable for sampling. TABLESAMPLE cannot be applied directly to a subquery result.

Apply a Function to a sample

Sample first, then call a Function only on the rows you drew -- useful for trying an expensive Function on a subset before running it over the whole table. Register caption_quality first:

from lancedb import udf

@udf
def caption_quality(caption: str) -> float:
    ...  # score the caption, e.g. with a lightweight heuristic or model

db.create_function_async(caption_quality).wait()

SELECT row_id, caption, caption_quality(caption) AS quality
FROM curation_documents TABLESAMPLE (50)
WHERE caption IS NOT NULL;

The Function runs only on the sampled rows, not the whole table -- a quick way to check a Function's output, cost or latency before committing to a full column refresh.

Related guides

Count the sampled rows and inspect their distribution before using them. Preserve the selected data when the exact membership matters. See SQL sampling for the complete syntax and restrictions, and Permutation for a random subset through the Python SDK.