本页尚未翻译

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

Ranking

Choose records with explicit ranking rules and deterministic tie handling.

Applies to: Embedded ❌, Enterprise ✅

Window functions calculate values across related rows while keeping each row in the result. Use them to rank documents, keep one record per item, or compare a record with the one before it.

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.

Keep one record per document

row_number() numbers rows within each group. PARTITION BY defines the group, and ORDER BY determines which record comes first. This query selects the latest version of each document, using row_id to break ties:

WITH ranked AS (
    SELECT *, row_number() OVER (
        PARTITION BY doc_id ORDER BY version DESC, row_id DESC
    ) AS rn
    FROM curation_documents
)
SELECT row_id, doc_id, version, caption
FROM ranked
WHERE rn = 1
ORDER BY doc_id;

This returns row IDs 2, 3, 5 and 6. For document 30, rows 4 and 5 have the same version; the example deliberately chooses the larger row_id. Without a complete ordering, the selected row among ties is not determined by the query.

Use rank() instead of row_number() when tied records should all survive, and order only by the fields that define a tie. Adding a unique tie-breaker would make every rank distinct. The outer query filters the calculated rank because window functions are evaluated after the inner query's WHERE clause.

Select the highest-scoring records per class

Deduplicate first, then rank the labeled documents within each class:

WITH latest AS (
    SELECT *, row_number() OVER (
        PARTITION BY doc_id ORDER BY version DESC, row_id DESC
    ) AS version_rank
    FROM curation_documents
), ranked AS (
    SELECT d.doc_id, l.label, d.score,
           row_number() OVER (
               PARTITION BY l.label ORDER BY d.score DESC, d.doc_id
           ) AS class_rank
    FROM latest AS d
    JOIN curation_labels AS l ON l.doc_id = d.doc_id
    WHERE d.version_rank = 1
)
SELECT doc_id, label, score
FROM ranked
WHERE class_rank <= 1
ORDER BY label;

The result is document 10 for cat and document 20 for dog. Change the cap from 1 to the maximum number you want per class. A class with fewer records cannot supply the full cap.

This selects by score, not at random. For a random subset, use sampling instead of ordering by score or ID.

Training and evaluation splits

Ranking can divide ordered records into groups, but ordering by ID does not make those groups random or representative. ntile() produces approximately equal-sized buckets within a partition; small classes may not populate every bucket.

Choose the split policy for the task. Keep related records, such as revisions of one document or frames from one video, in the same split to avoid leakage. A time-based evaluation may instead require a cutoff date. Save the final assignments or resulting datasets so later updates do not silently change the evaluation population.

Compare neighboring records

lag() and lead() read neighboring rows in the specified order. For example, inspect how scores change across a document's records:

SELECT doc_id, row_id, score,
       lag(score) OVER (
           PARTITION BY doc_id ORDER BY version, row_id
       ) AS previous_score
FROM curation_documents
ORDER BY doc_id, version, row_id;

The first record in each document has no previous score. In this dataset, row 2 follows a score of 0.80 and row 5 follows a score of 0.70.

Window query performance

Window operations can require sorting. Filter out records you do not need before ranking, and inspect the query plan when cost matters.

For cumulative counts, sums and other running summaries, use an aggregate with an explicit window order and frame. The window reference covers those expressions.

Continue with the curated data

Profile the selected records and save the result for downstream use. See Window functions for the full syntax and function list.