# Oversample or draw with replacement

Shape / rows

A variant of [Sample rows](https://meridian.online/operations/sample-rows).

Draw rows with replacement, or synthesise new minority-class rows, so rows can appear more than in the input.

Page: <https://meridian.online/operations/sample-rows/oversample-or-draw-with-replacement>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

draw rows with replacement, and synthesise new minority rows

```sql
CREATE TEMP TABLE points AS SELECT * FROM (VALUES
  (1, 'major', 1.0, 5.0), (2, 'major', 2.0, 6.0), (3, 'major', 3.0, 4.0), (4, 'major', 4.0, 7.0),
  (5, 'major', 5.0, 5.0), (6, 'major', 6.0, 6.0), (7, 'major', 7.0, 4.0), (8, 'major', 8.0, 7.0),
  (9, 'minor', 0.0, 0.0), (10, 'minor', 1.0, 1.0), (11, 'minor', 10.0, 10.0)
) t(id, class, x1, x2);
-- 1. draw with replacement: eight rows from the three minority rows, each pick a seeded hash of its draw number
CREATE TEMP TABLE minor AS SELECT *, row_number() OVER (ORDER BY id) AS k FROM points WHERE class = 'minor';
CREATE TEMP TABLE drawn AS
SELECT d.draw, m.id, m.class, m.x1, m.x2
FROM range(8) d(draw)
JOIN minor m ON m.k = 1 + hash(d.draw::VARCHAR || '-draw-3') % (SELECT count(*) FROM minor);
-- 2. synthesise: each new row sits on the line from a minority row to its nearest minority neighbour (SMOTE)
CREATE TEMP TABLE neighbour AS
SELECT a.id AS seed_id, b.id AS neighbour_id, a.x1 AS a1, a.x2 AS a2, b.x1 AS b1, b.x2 AS b2
FROM minor a JOIN minor b ON a.id <> b.id
QUALIFY row_number() OVER (PARTITION BY a.id ORDER BY (a.x1 - b.x1) ^ 2 + (a.x2 - b.x2) ^ 2, b.id) = 1;
CREATE TEMP TABLE seeds AS SELECT *, row_number() OVER (ORDER BY seed_id) AS k FROM neighbour;
CREATE TEMP TABLE synthetic AS
SELECT d.draw, s.seed_id, s.neighbour_id,
       s.a1 + u * (s.b1 - s.a1) AS x1, s.a2 + u * (s.b2 - s.a2) AS x2
FROM (SELECT draw, 0.05 + 0.9 * (hash(draw::VARCHAR || '-gap-9') % 1000) / 1000.0 AS u FROM range(5) t(draw)) d
JOIN seeds s ON s.k = 1 + d.draw % (SELECT count(*) FROM seeds);
SELECT draw, id FROM drawn ORDER BY draw;
SELECT * FROM synthetic ORDER BY draw;
SELECT CASE WHEN ((SELECT count(*) FROM drawn) = 8 AND (SELECT count(DISTINCT id) FROM drawn) BETWEEN 2 AND 7 AND (SELECT bool_and(id IN (9, 10, 11)) FROM drawn) AND (SELECT list((seed_id, neighbour_id) ORDER BY seed_id) FROM neighbour) = [(9, 10), (10, 9), (11, 10)] AND (SELECT count(*) FROM synthetic) = 5 AND (SELECT count(*) FROM synthetic s JOIN points p ON p.x1 = s.x1 AND p.x2 = s.x2) = 0 AND (SELECT bool_and(x1 BETWEEN 0 AND 10 AND x2 BETWEEN 0 AND 10 AND x1 = x2) FROM synthetic) AND (SELECT 3 + count(*) FROM synthetic) = (SELECT count(*) FROM points WHERE class = 'major')) THEN 'as expected' ELSE error('the draw was not eight rows with repeats from the minority class, or the synthetic rows were not five new points between a minority row and its nearest neighbour') END AS verdict;
```

The statement builds its own data.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- Alteryx Designer: [`Oversample Field`](https://help.alteryx.com/current/en/designer/tools/preparation/oversample-field-tool.html) (read 2026-09-27)
- KNIME Analytics Platform: [`Bootstrap Sampling`](https://api.hub.knime.com/nodes/*i1MQOr0EbxLu4c1B?details=full) (read 2026-09-27)
- KNIME Analytics Platform: [`SMOTE`](https://api.hub.knime.com/nodes/*YznaX_v45d3OzPEV?details=full) (read 2026-09-27)
- RapidMiner Studio: [`SMOTE Upsampling`](https://docs.rapidminer.com/latest/studio/operators/extensions/Operator%20Toolbox/blending/smote.html) (read 2026-09-27)
- RapidMiner Studio: [`Sample (Bootstrapping)`](https://docs.rapidminer.com/latest/studio/operators/blending/examples/sampling/sample_bootstrapping.html) (read 2026-09-27)