# Estimate density from data

Model / stats

A variant of [Use a probability distribution](https://meridian.online/operations/use-a-probability-distribution).

Fit a density to a sample, by kernels or a density tree, and read it at query points.

Page: <https://meridian.online/operations/use-a-probability-distribution/estimate-density-from-data>

## Status

Runs as a SQL step.

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

## The DuckDB step

estimate a density by Gaussian kernels and by a density tree, and read it at query points

```sql
CREATE TEMP TABLE sample AS SELECT x::DOUBLE AS x FROM (VALUES (1.0), (2.0), (2.5), (3.0), (7.0), (8.0), (8.5), (15.0)) t(x);
CREATE TEMP TABLE query_points AS SELECT q::DOUBLE AS q FROM (VALUES (2.0), (4.0), (7.5), (12.0), (20.0)) t(q);
-- kernels: every query point against the whole sample, so a cross join and an aggregate; bandwidth 1.0
CREATE TEMP TABLE kernel AS
SELECT q.q, sum(exp(-0.5 * power((q.q - s.x) / 1.0, 2)) / sqrt(2 * pi())) / (count(*) * 1.0) AS density
FROM query_points q CROSS JOIN sample s GROUP BY q.q;
-- density tree: split the domain [0, 16) at the median of the points in each part, twice; a leaf has density count / (n * width)
CREATE TEMP TABLE leaves AS
WITH RECURSIVE tree(lo, hi, depth) AS (
  SELECT 0.0::DOUBLE, 16.0::DOUBLE, 0
  UNION ALL
  SELECT c.lo, c.hi, p.depth + 1
  FROM (SELECT t.lo, t.hi, t.depth, median(s.x) AS mid FROM tree t JOIN sample s ON s.x >= t.lo AND s.x < t.hi WHERE t.depth < 2 GROUP BY t.lo, t.hi, t.depth) p,
       LATERAL (VALUES (p.lo, p.mid), (p.mid, p.hi)) c(lo, hi))
SELECT lo, hi, (SELECT count(*) FROM sample s WHERE s.x >= lo AND s.x < hi) / ((SELECT count(*) FROM sample) * (hi - lo)) AS density
FROM tree WHERE depth = 2;
CREATE TEMP TABLE result AS
SELECT k.q, k.density AS kernel_density, coalesce(l.density, 0) AS tree_density
FROM kernel k LEFT JOIN leaves l ON k.q >= l.lo AND k.q < l.hi;
SELECT * FROM result ORDER BY q;
-- the kernel values are the closed form sum of normal densities at these points; the tree has four leaves of two points each, whose
-- densities times widths sum to 1, and a point outside the domain has density 0
SELECT CASE WHEN ((SELECT list(round(kernel_density, 9) ORDER BY q) = [0.154368819, 0.0543116, 0.118264870, 0.000679981, 0.000000186] FROM result)
  AND (SELECT list(round(tree_density, 9) ORDER BY q) = [0.111111111, 0.090909091, 0.076923077, 0.032258065, 0.0] FROM result)
  AND (SELECT count(*) = 4 AND abs(sum(density * (hi - lo)) - 1) < 1e-12 FROM leaves)) THEN 'as expected' ELSE error('the kernel and tree densities at 2, 4, 7.5, 12 and 20 should match the reference values, and the four leaves should hold the whole sample') 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).

- mlpack: [`mlpack_det`](https://www.mlpack.org/doc/user/bindings/cli.html) (read 2026-09-27)
- mlpack: [`mlpack_kde`](https://www.mlpack.org/doc/user/bindings/cli.html) (read 2026-09-27)