Draw a stratified sample inside the database

The database version of draw_stratified_sample(), for populations that are too large to load into R (the paragraphs of a cleaned text table, the candidate pairs of a matching step). The draw is done by DuckDB: within each stratum, rows are ordered by the MD5 hash of their identifier and the seed, and the first ones are kept.

Usage

draw_stratified_sample_db(
  con,
  from,
  id_col,
  strata = NULL,
  n_per_stratum = NULL,
  n = NULL,
  allocation = c("equal", "proportional"),
  seed,
  columns = NULL
)

Arguments

con

A DBI connection, or the path of a .duckdb file.

from

Character. A table name, or a SQL query between parentheses (e.g. "(SELECT * FROM pair WHERE accepted)").

id_col

Character. Column identifying each row (unique).

strata

Character or NULL. Columns defining the strata.

n_per_stratum, n, allocation

As in draw_stratified_sample().

seed

Integer or string. Seed of the draw.

columns

Character or NULL. Columns returned; NULL for all.

Value

A data.table with the sampled rows and .stratum, .population_n, .sample_n, .weight.

Details

Ordering by md5(id || '|' || seed) gives a draw that is reproducible on any machine and any DuckDB version (unlike DuckDB's random() or hash()), and that does not depend on the order of the rows. It is a simple random sample within each stratum as long as the identifiers are unrelated to the hash, which MD5 guarantees in practice. The same seed gives a different sample from draw_stratified_sample(), which uses R's random numbers.

See also

Other evaluation: cohen_kappa(), draw_stratified_sample(), margin_of_error(), precision_recall(), read_verdicts(), wilson_interval(), write_judge_chunks()

Examples

con <- db_connect(tempfile(fileext = ".duckdb"))
#> duckdb is storing downloaded extensions and secrets under ~/.duckdb:
#> ℹ /home/agoutsmedt/.duckdb
#> This persists across sessions and is shared with the DuckDB CLI and other clients.
#> ℹ Run duckdb(shared_home = FALSE) to use a temporary directory instead.
#> ℹ See ?duckdb_storage for details and alternatives.
DBI::dbWriteTable(con, "pair", data.frame(
  pair_id = sprintf("p%03d", 1:200),
  rule = rep(c("doi", "exact_title", "fuzzy_title"), c(150, 40, 10))
))
s <- draw_stratified_sample_db(con, "pair", "pair_id", "rule", n_per_stratum = 5, seed = 7)
s[, .N, by = .(.stratum, .population_n, .weight)]
#>       .stratum .population_n .weight     N
#>         <char>         <int>   <num> <int>
#> 1:         doi           150      30     5
#> 2: exact_title            40       8     5
#> 3: fuzzy_title            10       2     5
db_disconnect(con)