Shape / inspect · All operations

See a column's distribution

Histogram, quantiles of one column.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
a table describing the input rather than holding its rows
Level
one clause of SQL

The DuckDB step

Quantiles and a histogram

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
CREATE TEMP TABLE result AS
SELECT quantile_cont(amount, [0.25, 0.5, 0.75]) AS quartiles, histogram(amount) AS hist FROM orders;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT quartiles = [80.5, 120.0, 300.0] AND hist[510.00] = 2 FROM result)) THEN 'as expected' ELSE error('the quartiles or the count of the repeated amount is wrong') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the quartiles or the count of the repeated amount is wrong”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Count values in bins

Show how often a column's values fall in each range or category, as a histogram, density or facet.

Summarise with quantiles and moments

Reduce a column to statistics of its distribution, such as quantiles, median, mode, skewness, kurtosis or entropy.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (2)
  • Histogramread 2026-09-27

    Plot the frequency of a numeric field over value ranges, with optional density curve

  • Violin Plotread 2026-09-27

    Show the kernel-smoothed density of a numeric field, optionally by group

AWS Glue (1)
  • Distributionread 2026-09-27

    Analyzer: frequency distribution of a column, binned for numeric columns

DuckDB (17)
  • approx_quantileread 2026-09-27

    Computes the approximate quantile using T-Digest.

  • approx_top_kread 2026-09-27

    Finds the k approximately most occurring values in the data set

  • entropyread 2026-09-27

    Returns the log-2 entropy of count input-values.

  • histogramread 2026-09-27

    Returns a LIST of STRUCTs with the fields bucket and count.

  • histogramread 2026-09-27

    histogram table function

  • histogram_exactread 2026-09-27

    Returns a LIST of STRUCTs with the fields bucket and count matching the buckets exactly.

  • histogram_valuesread 2026-09-27

    histogram_values table function

  • kurtosisread 2026-09-27

    Returns the excess kurtosis (Fisher’s definition) of all input values, with a bias correction according to the sample size

  • kurtosis_popread 2026-09-27

    Returns the excess kurtosis (Fisher’s definition) of all input values, without bias correction

  • madread 2026-09-27

    Returns the median absolute deviation for the values within x. NULL values are ignored. Temporal types return a positive INTERVAL.

  • medianread 2026-09-27

    Returns the middle value of the set. NULL values are ignored. For even value counts, interpolate-able types (numeric, date/time) return...

  • moderead 2026-09-27

    Returns the most frequent value for the values within x. NULL values are ignored.

  • quantileread 2026-09-27

    Returns the exact quantile number between 0 and 1 . If pos is a LIST of FLOATs, then the result is a LIST of the corresponding exact qu...

  • quantile_contread 2026-09-27

    Returns the interpolated quantile number between 0 and 1 . If pos is a LIST of FLOATs, then the result is a LIST of the corresponding i...

  • quantile_discread 2026-09-27

    Returns the exact quantile number between 0 and 1 . If pos is a LIST of FLOATs, then the result is a LIST of the corresponding exact qu...

  • reservoir_quantileread 2026-09-27

    Gives the approximate quantile using reservoir sampling, the sample size is optional and uses 8192 as a default size.

  • skewnessread 2026-09-27

    Returns the skewness of all input values.

OpenRefine (11)
Polars (1)
  • Expr.histread 2026-09-27

    Bin the values of a column and count how many fall in each bin

Tableau Prep (1)
  • Profile paneread 2026-09-27

    Show per field the distribution of values, unique values and data type

Background

A histogram represents the distribution of quantitative data by dividing the range of values into intervals and counting how many values fall into each.[1] Quantiles are cut points that divide the observations in a sample into groups of equal size, such as quartiles (four groups) and percentiles (100 groups).[2] The median is the 2-quantile, and its value may not be uniquely determined for a set of even size.[2]

When to use it

There is no best number of bins for a histogram, and different bin sizes can reveal different features of the data.[1] Depending on the data and the goals of the analysis, different bin widths may be appropriate, so experimentation is usually needed to determine one.[1] Draw the same column at two or three bin widths before reading anything into its shape. (Meridian's view)

Reporting values as somewhat arbitrarily rounded numbers is a common phenomenon when collecting data from people.[1] In the US 2000 census, more people reported a travel time to work of at least 30 but less than 35 minutes than reported the five-minute intervals either side of it.[1] Treat a spike at a round number as a question about how the values were recorded before reading it as a real peak. (Meridian's view)

In OpenRefine, a numeric log facet uses a base-10 logarithm and can represent unevenly distributed data, with a long tail extending into different magnitudes, better than a simple numeric facet.[3] In OpenRefine's example of mammal body weights, a numeric facet puts almost all the cells in its first bucket, because a sperm whale's 35,000 kg is many magnitudes above the rest.[3] When a column spans several orders of magnitude, bin its logarithm rather than its raw values. (Meridian's view)

For the ten values 3, 6, 7, 8, 8, 10, 13, 15, 16 and 20, any value from 8 through to 10 could be taken to be the median, and averaging the fifth and sixth values gives 9.[2] Modern statistical packages rely on a number of techniques to estimate quantiles, and Hyndman and Fan compiled a taxonomy of nine algorithms used by various packages.[2] Of the nine methods in their taxonomy, Hyndman and Fan recommend the eighth (R-8), but most statistical software packages have chosen the sixth or seventh (R-6 or R-7) as the default.[2] When the quartiles from two tools disagree, check which quantile method each one uses before suspecting the data. (Meridian's view)

In DuckDB, median averages the two middle values when there is an even count of quantitative values, and returns the lower value for ordinal values.[4] In PostgreSQL, mode computes the most frequent value, arbitrarily choosing the first one if there are multiple equally-frequent values.[5] Report the count of the most frequent value beside the mode, so a near-tie is visible rather than hidden. (Meridian's view) The sample median is a more robust estimator than the sample mean.[2] For a column with outliers or a long tail, lead with the median and quartiles rather than the mean. (Meridian's view)

The method

The bins of a histogram are usually consecutive, non-overlapping intervals, adjacent to one another and typically, but not necessarily, of equal size.[1] Karl Pearson first introduced the term histogram in lectures delivered in 1892 at University College London.[1] The technique of using a bar graph to represent statistical measurements was devised by the Scottish economist William Playfair, in his Commercial and political atlas (1786).[1]

Grouping data is at least as old as Graunt's work in the 17th century, but no systematic guidelines for the number of bins were given until Sturges's work in 1926.[1] Sturges's formula sets the number of bins to the base-2 logarithm of the number of values, rounded up, plus one, and implicitly assumes an approximately normal distribution.[1] Sturges's formula can perform poorly with fewer than 30 values, and may overestimate bin width for very large datasets, resulting in oversmoothed histograms.[1] The Freedman–Diaconis rule bases the bin width on the interquartile range, which is less sensitive to outliers than the standard deviation used in Scott's rule.[1] Scott's normal reference rule is the default rule used in Microsoft Excel.[1]

In DuckDB, histogram given a list of boundaries counts the values in left-open, right-closed bins, each keyed by its upper boundary.[4] DuckDB's histogram_values table macro returns the upper boundary of each bin and its count, with a binning technique of auto, sample, equi-height, equi-width or equi-width-nice.[4]

Of the nine sample quantile methods Hyndman and Fan describe, the first three are piecewise constant, while the last six use linear interpolation between data points.[2] In DuckDB, quantile_disc returns one of the column's own values and is Type 1 in Hyndman & Fan (1996).[4] In DuckDB, percentile_cont and percentile_disc written with WITHIN GROUP (ORDER BY ...) are converted to quantile_cont and quantile_disc.[4] Use the discrete quantile when the answer must be a value the column holds, such as a date or a code, and the interpolated one for measurements. (Meridian's view)

Approximate quantiles can be computed efficiently from a stream of values using compressed data structures, and t-digest is one of the two most popular methods.[2] The t-digest maintains a data structure of bounded size, using an approach motivated by k-means clustering to group similar values.[2] In DuckDB, approx_quantile calculates an approximate quantile using T-Digest, and reservoir_quantile uses reservoir sampling with a default sample size of 8192.[4]

In DuckDB, mad returns the median absolute deviation, computed as the median of the absolute differences between each value and the median.[4] In DuckDB, kurtosis returns the excess kurtosis (Fisher's definition) with a bias correction for the sample size, while kurtosis_pop applies no bias correction.[4]

Compared with the alternatives

In a histogram each bin is for a different range of values, while in a bar chart each bar is for a different category of observations.[1] A frequency distribution groups data into mutually exclusive classes with the number of occurrences in each, and is used for both qualitative and quantitative data.[6] For a column of categories, count each distinct value; bins only make sense when the values have an order and a range. (Meridian's view)

A histogram can be thought of as a simplistic kernel density estimation, which smooths frequencies over the bins and will in general more accurately reflect the underlying distribution.[1]

For any probability distribution with a mean and variance, the median is never more than one standard deviation from the mean.[2]

Sources

  1. [1]Histogram - Wikipediasecondary source
  2. [2]Quantile - Wikipediasecondary source
  3. [3]Exploring facets | OpenRefinedocumentation
  4. [4]Aggregate Functions – DuckDBdocumentation
  5. [5]PostgreSQL: Documentation: 18: 9.21. Aggregate Functionsdocumentation
  6. [6]Frequency (statistics) - Wikipediasecondary source