Shape / inspect · All operations

Measure correlation

Correlation or association matrix, contingency table.

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
several clauses of SQL, or a window

The DuckDB step

Correlation of two columns

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 round(corr(amount, order_id), 4) AS r, round(covar_samp(amount, order_id), 2) AS cov,
       round(corr(amount, amount * 2 + 1), 4) AS with_itself_scaled FROM orders;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT r BETWEEN -1 AND 1 AND with_itself_scaled = 1 AND r = 0.1683 FROM result)) THEN 'as expected' ELSE error('the correlation is out of range, or a column does not correlate 1 with a linear copy of itself') 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 correlation is out of range, or a column does not correlate 1 with a linear copy of itself”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

  • Cross-tabulate two columns

    Count the rows for each combination of values in two or more categorical columns, with association tests such as chi-square.

  • Build a correlation matrix

    Correlate every pair of chosen columns at once, giving a matrix of coefficients, covariances or mutual information.

Measure covariance

Compute the population or sample covariance of two numeric columns.

Other association measures

Measure internal consistency across columns, or count how often each value follows another.

Correlate two columns

Compute one correlation coefficient, Pearson or rank-based, between two numeric columns.

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 (4)
Apache DataFusion (3)
  • corrread 2026-09-27

    Aggregate: Coefficient of correlation between two numeric values

  • covar_popread 2026-09-27

    Aggregate: Population covariance of number pairs

  • covar_sampread 2026-09-27

    Aggregate: Sample covariance of number pairs; alias covar

AWS Glue (1)
DuckDB (3)
  • corrread 2026-09-27

    Returns the correlation coefficient for non-NULL pairs in a group.

  • covar_popread 2026-09-27

    Returns the population covariance of input values.

  • covar_sampread 2026-09-27

    Returns the sample covariance for non-NULL pairs in a group.

KNIME Analytics Platform (5)
  • Cronbach Alpharead 2026-09-27

    Compute Cronbach's alpha, an internal-consistency measure, across numeric columns

  • Crosstabread 2026-09-27

    Cross-tabulate two columns into a contingency table with chi-square statistics

  • Linear Correlationread 2026-09-27

    Compute pairwise correlation coefficients for numeric and nominal columns

  • Rank Correlationread 2026-09-27

    Compute pairwise rank-based correlation (Spearman and related measures) between columns

  • Standardized Cronbach Alpharead 2026-09-27

    Compute standardised Cronbach's alpha from a correlation model

Polars (1)
  • DataFrame.corrread 2026-09-27

    Pairwise Pearson correlation matrix of the columns

RapidMiner Studio (4)

Background

In statistics, correlation is a relationship between two variables, and usually refers to how far a pair of quantities are linearly related.[1] A correlation coefficient ranges from −1 to +1: +1 for a perfect increasing linear relationship and −1 for a perfect decreasing one.[1] More generally, an arbitrary relationship between variables is called an association.[1]

When to use it

Correlations are useful because they can indicate a predictive relationship that can be exploited in practice.[1] The presence of a correlation is not sufficient to infer a causal relationship, which is often stated as correlation does not imply causation.[1] Treat a strong correlation as a reason to ask how the two columns came to move together, not as the answer. (Meridian's view)

A Pearson correlation coefficient of 0 does not imply that two variables are independent, because the coefficient detects only linear dependence.[1] Anscombe's quartet, created by Francis Anscombe, is four pairs of variables whose y variables share the same mean, variance, correlation and regression line, though their distributions are very different.[1] In one pair of Anscombe's quartet the relationship is perfectly linear except for one outlier, which is enough to lower the correlation coefficient from 1 to 0.816.[1] A correlation coefficient, as a summary statistic, cannot replace visual examination of the data.[1]

Covariance has units of measurement, so changing the units, for example from metres to millimetres, changes the covariance value proportionally.[2] Reach for covariance when the next calculation needs the columns' own units, and for correlation when one pair's strength is to be compared with another's. (Meridian's view)

The method

Pearson's correlation coefficient can be calculated by dividing the covariance of two variables by the product of their standard deviations.[1] The Pearson correlation is defined only if both standard deviations are finite and positive.[1] In DuckDB, corr(y, x) is computed as covar_pop(y, x) divided by the product of stddev_pop(x) and stddev_pop(y).[3] In DuckDB, covar_pop gives the population covariance without bias correction, and covar_samp gives the sample covariance with Bessel's bias correction.[3] Karl Pearson developed the Pearson correlation coefficient from a related idea introduced by Francis Galton in the 1880s, and Auguste Bravais had derived and published its mathematical formula in 1844.[4]

Spearman's rank correlation coefficient is named after Charles Spearman and equals the Pearson correlation between the rank values of two variables.[5] Pearson's correlation assesses linear relationships, while Spearman's correlation assesses monotonic relationships, whether linear or not.[5] The Spearman correlation is less sensitive than the Pearson correlation to strong outliers in the tails of both samples, because Spearman's coefficient limits an outlier to the value of its rank.[5] Pick the coefficient for the relationship you expect: Pearson's for a straight line, Spearman's when you expect only that one column rises as the other does. (Meridian's view)

Cronbach's alpha, devised by the American psychometrician Lee Cronbach, is a reliability coefficient and a measure of the internal consistency of tests and measures.[6] Lee Cronbach described the coefficient as coefficient alpha in his initial 1951 publication.[6]

Compared with the alternatives

Rank correlation coefficients measure a different type of relationship from the Pearson product-moment coefficient, and are best seen as measures of a different type of association rather than as an alternative measure of the population correlation coefficient.[1] The correlation coefficient normalises the covariance to a value between -1 and 1, which allows the strength of association to be compared between pairs of variables that do not share units.[2] In DuckDB, regr_r2(y, x) is the squared Pearson correlation coefficient between y and x, which is also the coefficient of determination of a linear regression of y on x.[3] Mutual information measures the mutual dependence between two random variables and, unlike the correlation coefficient, is not limited to real-valued variables and linear dependence.[7] Claude Shannon defined and analysed mutual information in A Mathematical Theory of Communication, though the term mutual information was coined later by Robert Fano.[7]

Sources

  1. [1]Correlation - Wikipediasecondary source
  2. [2]Covariance - Wikipediasecondary source
  3. [3]Aggregate Functions – DuckDBdocumentation
  4. [4]Pearson correlation coefficient - Wikipediasecondary source
  5. [5]Spearman's rank correlation coefficient - Wikipediasecondary source
  6. [6]Cronbach's alpha - Wikipediasecondary source
  7. [7]Mutual information - Wikipediasecondary source