Stichproben

Stichproben dienen dazu, zufällig eine Teilmenge eines Datensatzes auszuwählen.

Beispiele

Eine Stichprobe von genau 5 Zeilen aus tbl mit reservoir-Sampling auswählen:

SELECT *
FROM tbl
USING SAMPLE 5;

Eine Stichprobe von ungefähr 10 % der Tabelle mit system-Sampling auswählen:

SELECT *
FROM tbl
USING SAMPLE 10%;

Warnung Wenn Sie einen Prozentsatz angeben, wird standardmäßig jeder Vektor mit dieser Wahrscheinlichkeit in die Stichprobe aufgenommen. Enthält Ihre Tabelle weniger als etwa 10.000 Zeilen, ist es sinnvoll, stattdessen die Sampling-Option bernoulli anzugeben, die die Wahrscheinlichkeit unabhängig auf jede Zeile anwendet. Auch dann erhalten Sie manchmal mehr und manchmal weniger als den angegebenen Prozentsatz der Zeilenanzahl, aber es ist deutlich unwahrscheinlicher, dass Sie gar keine Zeilen erhalten. Um genau 10 % der Zeilen zu erhalten (bis auf Rundung), müssen Sie die Sampling-Option reservoir verwenden.

Eine Stichprobe von ungefähr 10 % der Tabelle mit bernoulli-Sampling auswählen:

SELECT *
FROM tbl
USING SAMPLE 10 PERCENT (bernoulli);

Eine Stichprobe von genau 10 % (bis auf Rundung) der Tabelle mit reservoir-Sampling auswählen:

SELECT *
FROM tbl
USING SAMPLE 10 PERCENT (reservoir);

Eine Stichprobe von genau 50 Zeilen der Tabelle mit Reservoir-Sampling und festem Seed (100) auswählen:

SELECT *
FROM tbl
USING SAMPLE reservoir(50 ROWS)
REPEATABLE (100);

Eine Stichprobe von ungefähr 20 % der Tabelle mit system-Sampling und festem Seed (377) auswählen:

SELECT *
FROM tbl
USING SAMPLE 20% (system, 377);

Eine Stichprobe von ungefähr 20 % von tbl vor dem Join mit tbl2 auswählen:

SELECT *
FROM tbl TABLESAMPLE reservoir(20%), tbl2
WHERE tbl.i = tbl2.i;

Eine Stichprobe von ungefähr 20 % von tbl nach dem Join mit tbl2 auswählen:

SELECT *
FROM tbl, tbl2
WHERE tbl.i = tbl2.i
USING SAMPLE reservoir(20%);

Syntax

Stichproben erlauben es, zufällig eine Teilmenge eines Datensatzes zu extrahieren. Stichproben sind nützlich, um einen Datensatz schneller zu erkunden, weil Sie oft nicht an den exakten Antworten auf Abfragen interessiert sind, sondern nur an groben Hinweisen darauf, wie die Daten aussehen und was in den Daten enthalten ist. Stichproben ermöglichen schnellere Näherungsantworten, weil sie die Datenmenge reduzieren, die durch die Query Engine laufen muss.

DuckDB unterstützt drei verschiedene Sampling-Methoden: reservoir, bernoulli und system. Standardmäßig verwendet DuckDB reservoir-Sampling, wenn eine genaue Zeilenzahl gesampelt wird, und system-Sampling, wenn ein Prozentsatz angegeben ist. Die Sampling-Methoden werden unten im Detail beschrieben.

Stichproben benötigen eine Stichprobengröße, die angibt, wie viele Elemente aus der Gesamtpopulation gesampelt werden. Stichproben können entweder als Prozentsatz (10% oder 10 PERCENT) oder als feste Zeilenzahl (10 oder 10 ROWS) angegeben werden. Alle drei Sampling-Methoden unterstützen das Sampling über einen Prozentsatz, aber nur Reservoir-Sampling unterstützt das Sampling einer festen Zeilenzahl.

Stichproben sind probabilistisch, das heißt, Stichproben können zwischen Läufen unterschiedlich sein, sofern der Seed nicht ausdrücklich angegeben ist. Die Angabe des Seeds garantiert nur, dass die Stichprobe gleich ist, wenn Multithreading nicht aktiviert ist (d. h. SET threads = 1). Wenn mehrere Threads über eine Stichprobe laufen, sind Stichproben auch mit festem Seed nicht unbedingt konsistent.

Sampling-Methoden

reservoir

Reservoir-Sampling ist eine Stream-Sampling-Technik, die eine Zufallsstichprobe auswählt, indem sie ein Reservoir in der Größe der Stichprobe hält und Elemente zufällig ersetzt, wenn weitere Elemente eintreffen. Reservoir-Sampling erlaubt es, genau anzugeben, wie viele Elemente in der resultierenden Stichprobe sein sollen (durch die Größe des Reservoirs). Dadurch gibt Reservoir-Sampling immer dieselbe Anzahl von Elementen aus, im Gegensatz zu System- und Bernoulli-Sampling.

Reservoir-Sampling wird nur für kleine Stichprobengrößen empfohlen und ist nicht für die Verwendung mit Prozentangaben empfohlen. Der Grund ist, dass Reservoir-Sampling die gesamte Stichprobe materialisieren und Tupel innerhalb der materialisierten Stichprobe zufällig ersetzen muss. Je größer die Stichprobe, desto höher der Performance-Einbuße durch diesen Prozess.

Reservoir-Sampling verursacht außerdem eine zusätzliche Performance-Strafe bei Multiprocessing, weil das Reservoir zwischen den Threads geteilt werden muss, um unverzerrtes Sampling zu gewährleisten. Das ist bei einem sehr kleinen Reservoir kein großes Problem, wird aber bei einer großen Stichprobe teuer.

Best Practice Vermeiden Sie Reservoir-Sampling mit großen Stichprobengrößen, wenn möglich. Reservoir-Sampling erfordert, dass die gesamte Stichprobe im Speicher materialisiert wird.

bernoulli

Bernoulli-Sampling kann nur verwendet werden, wenn ein Sampling-Prozentsatz angegeben ist. Es ist recht einfach: Jede Zeile der zugrunde liegenden Tabelle wird mit einer Chance gleich dem angegebenen Prozentsatz aufgenommen. Daher kann Bernoulli-Sampling eine unterschiedliche Anzahl von Tupeln zurückgeben, auch wenn derselbe Prozentsatz angegeben ist. Die erwartete Zeilenzahl entspricht dem angegebenen Prozentsatz der Tabelle, es gibt aber eine gewisse Varianz.

Weil Bernoulli-Sampling vollständig unabhängig ist (es gibt keinen gemeinsamen Zustand), gibt es keine Strafe für die Verwendung von Bernoulli-Sampling zusammen mit mehreren Threads.

system

System-Sampling ist eine Variante von Bernoulli-Sampling mit einem entscheidenden Unterschied: Jeder Vektor wird mit einer Chance gleich dem Sampling-Prozentsatz aufgenommen. Das ist eine Form von Cluster-Sampling. System-Sampling ist effizienter als Bernoulli-Sampling, weil keine Auswahl pro Tupel durchgeführt werden muss.

Die erwartete Zeilenzahl entspricht weiterhin dem angegebenen Prozentsatz der Tabelle, aber die Varianz ist vectorSize-mal höher. Daher eignet sich System-Sampling nicht für Datensätze mit weniger als etwa 10.000 Zeilen, bei denen es vorkommen kann, dass alle Zeilen herausgefiltert werden oder alle Daten aufgenommen werden, selbst wenn Sie 50 PERCENT anfordern.

Tabellenstichproben

Die Klauseln TABLESAMPLE und USING SAMPLE sind hinsichtlich Syntax und Wirkung identisch, mit einem wichtigen Unterschied: Tabellenstichproben sampeln direkt aus der Tabelle, für die sie angegeben sind, während die Sample-Klausel sampelt, nachdem die gesamte From-Klausel aufgelöst wurde. Das ist relevant, wenn im Abfrageplan Joins vorhanden sind.

Die TABLESAMPLE-Klausel entspricht im Wesentlichen dem Anlegen einer Unterabfrage mit der USING SAMPLE-Klausel, d. h. die folgenden beiden Abfragen sind identisch:

20 % von tbl vor dem Join sampeln:

SELECT *
FROM
    tbl TABLESAMPLE reservoir(20%),
    tbl2
WHERE tbl.i = tbl2.i;

20 % von tbl vor dem Join sampeln:

SELECT *
FROM
    (SELECT * FROM tbl USING SAMPLE reservoir(20%)) tbl,
    tbl2
WHERE tbl.i = tbl2.i;

20 % nach dem Join sampeln (d. h. 20 % des Join-Ergebnisses):

SELECT *
FROM tbl, tbl2
WHERE tbl.i = tbl2.i
USING SAMPLE reservoir(20%);