# Evaluate a clustering

Model / evaluate

A variant of [Evaluate a model](https://meridian.online/operations/evaluate-a-model).

Score a clustering by density, distance, entropy or agreement with known labels.

Page: <https://meridian.online/operations/evaluate-a-model/evaluate-a-clustering>

## Status

Runs as a SQL step.

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

## The DuckDB step

score a clustering by density, distance, entropy and agreement with known labels

```sql
-- ten points with the cluster a model gave each and the label they really carry
CREATE TEMP TABLE pts AS SELECT * FROM (VALUES
  (1, 1.0, 1.0, 0, 'a'), (2, 1.2, 0.8, 0, 'a'), (3, 0.9, 1.1, 0, 'a'), (4, 1.1, 1.2, 0, 'a'), (5, 3.0, 3.0, 0, 'b'),
  (6, 5.0, 5.0, 1, 'b'), (7, 5.2, 4.8, 1, 'b'), (8, 4.9, 5.1, 1, 'b'), (9, 5.1, 5.2, 1, 'b'), (10, 3.2, 3.0, 1, 'a')) t(id, x, y, cluster, label);
CREATE TEMP TABLE centroid AS SELECT cluster, avg(x) AS cx, avg(y) AS cy FROM pts GROUP BY cluster;
CREATE TEMP TABLE radial AS SELECT p.cluster, p.id, sqrt(power(p.x - c.cx, 2) + power(p.y - c.cy, 2)) AS r FROM pts p JOIN centroid c USING (cluster);
-- distance: within-cluster sum of squares, and the silhouette of every point (mean distance to the other clusters against its own)
CREATE TEMP TABLE silhouette AS
WITH d AS (SELECT a.id, a.cluster = b.cluster AS same, sqrt(power(a.x - b.x, 2) + power(a.y - b.y, 2)) AS dist FROM pts a JOIN pts b ON a.id <> b.id),
     m AS (SELECT id, avg(dist) FILTER (WHERE same) AS a, avg(dist) FILTER (WHERE NOT same) AS b FROM d GROUP BY id)
SELECT avg((b - a) / greatest(a, b)) AS mean_silhouette FROM m;
-- density: points per unit of area of the circle round the centroid that reaches the farthest member
CREATE TEMP TABLE density AS SELECT cluster, count(*) / (pi() * power(max(r), 2)) AS points_per_area FROM radial GROUP BY cluster;
-- entropy of the true labels inside each cluster (bits), weighted by cluster size; 0 would be a pure cluster
CREATE TEMP TABLE entropy AS
WITH share AS (SELECT cluster, label, count(*)::DOUBLE / sum(count(*)) OVER (PARTITION BY cluster) AS p, sum(count(*)) OVER (PARTITION BY cluster) AS n FROM pts GROUP BY cluster, label)
SELECT sum(n * -p * log2(p)) / (SELECT count(*) FROM pts) AS weighted_entropy FROM share;
-- agreement with the labels: the Rand index and the adjusted Rand index from the cluster-by-label table
CREATE TEMP TABLE agreement AS
WITH cell AS (SELECT cluster, label, count(*) AS n FROM pts GROUP BY cluster, label),
     tot AS (SELECT sum(n * (n - 1) / 2) AS together, (SELECT sum(n * (n - 1) / 2) FROM (SELECT cluster, sum(n) AS n FROM cell GROUP BY cluster)) AS in_cluster,
                    (SELECT sum(n * (n - 1) / 2) FROM (SELECT label, sum(n) AS n FROM cell GROUP BY label)) AS in_label, (SELECT count(*) * (count(*) - 1) / 2 FROM pts) AS pairs FROM cell)
SELECT (pairs + 2 * together - in_cluster - in_label) / pairs AS rand_index,
       (together - in_cluster * in_label / pairs) / ((in_cluster + in_label) / 2 - in_cluster * in_label / pairs) AS adjusted_rand FROM tot;
CREATE TEMP TABLE result AS
SELECT (SELECT sum(r * r) FROM radial) AS within_ss, (SELECT mean_silhouette FROM silhouette) AS silhouette,
       (SELECT list(round(points_per_area, 9) ORDER BY cluster) FROM density) AS densities,
       (SELECT weighted_entropy FROM entropy) AS entropy, rand_index, adjusted_rand FROM agreement;
SELECT * FROM result;
-- reference values were computed outside DuckDB: the two cluster means are (1.44, 1.42) and (4.68, 4.62); each cluster has entropy 0.7219 bits
-- (four labels of one kind and one of the other); tolerance 1e-9 is for float rounding only
SELECT CASE WHEN ((SELECT abs(within_ss - 12.456) < 1e-9 AND abs(silhouette - 0.6295099535) < 1e-9
    AND densities = [0.322829499, 0.330553591] AND abs(entropy - 0.7219280949) < 1e-9
    AND abs(rand_index - 0.6444444444) < 1e-9 AND abs(adjusted_rand - 0.28) < 1e-9 FROM result)) THEN 'as expected' ELSE error('the clustering should score 12.456 within-cluster squares, a silhouette of 0.6295, entropy 0.7219 bits, a Rand index of 0.6444 and an adjusted Rand index of 0.28') 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: [`K-Centroids Diagnostics`](https://help.alteryx.com/current/en/designer/tools/predictive-grouping/k-centroids-diagnostics-tool.html) (read 2026-09-27)
- KNIME Analytics Platform: [`Entropy Scorer`](https://api.hub.knime.com/nodes/*ViVacy0amF1ou_Oq?details=full) (read 2026-09-27)
- RapidMiner Studio: [`Cluster Count Performance`](https://docs.rapidminer.com/latest/studio/operators/validation/performance/segmentation/cluster_count_performance.html) (read 2026-09-27)
- RapidMiner Studio: [`Cluster Density Performance`](https://docs.rapidminer.com/latest/studio/operators/validation/performance/segmentation/cluster_density_performance.html) (read 2026-09-27)
- RapidMiner Studio: [`Cluster Distance Performance`](https://docs.rapidminer.com/latest/studio/operators/validation/performance/segmentation/cluster_distance_performance.html) (read 2026-09-27)
- RapidMiner Studio: [`Item Distribution Performance`](https://docs.rapidminer.com/latest/studio/operators/validation/performance/segmentation/item_distribution_performance.html) (read 2026-09-27)
- RapidMiner Studio: [`Map Clustering on Labels`](https://docs.rapidminer.com/latest/studio/operators/validation/performance/segmentation/map_clustering_on_labels.html) (read 2026-09-27)