Model / stats · All operations

Fit a regression

Linear, logistic or generalised linear regression as statistics.

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

Simple linear regression as aggregates

SQL
CREATE TEMP TABLE result AS
WITH pts(x, y) AS (SELECT i, 3.0 * i + 2 FROM range(1, 11) t(i))
SELECT regr_slope(y, x) AS slope, regr_intercept(y, x) AS intercept, regr_r2(y, x) AS r2, regr_count(y, x) AS n FROM pts;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT abs(slope - 3) < 1e-9 AND abs(intercept - 2) < 1e-9 AND abs(r2 - 1) < 1e-9 AND n = 10 FROM result)) THEN 'as expected' ELSE error('a line of slope 3 and intercept 2 was not recovered') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “a line of slope 3 and intercept 2 was not recovered”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Fit a curve

Fit a polynomial or a user-given function to the data points.

Fit a linear regression

Fit a straight-line relationship between a numeric target and one or more predictors.

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)
  • Count Regressionread 2026-09-27

    Fit Poisson, quasi-Poisson or negative binomial regression for a count target

  • Gamma Regressionread 2026-09-27

    Fit a gamma regression for a strictly positive, skewed target

  • Linear Regressionread 2026-09-27

    Fit a linear regression of a numeric target on predictors

  • Logistic Regressionread 2026-09-27

    Fit a logistic regression giving the probability of each of two outcomes

Apache DataFusion (9)
  • regr_avgxread 2026-09-27

    Aggregate: Average of the independent variable over non-null pairs

  • regr_avgyread 2026-09-27

    Aggregate: Average of the dependent variable over non-null pairs

  • regr_countread 2026-09-27

    Aggregate: Number of non-null paired data points

  • regr_interceptread 2026-09-27

    Aggregate: Y-intercept of the linear regression line

  • regr_r2read 2026-09-27

    Aggregate: Square of the correlation coefficient of the pairs

  • regr_sloperead 2026-09-27

    Aggregate: Slope of the linear regression line

  • regr_sxxread 2026-09-27

    Aggregate: Sum of squares of the independent variable

  • regr_sxyread 2026-09-27

    Aggregate: Sum of products of paired data points

  • regr_syyread 2026-09-27

    Aggregate: Sum of squares of the dependent variable

DuckDB (9)
  • regr_avgxread 2026-09-27

    Returns the average of the independent variable for non-NULL pairs in a group, where x is the independent variable and y is the depende...

  • regr_avgyread 2026-09-27

    Returns the average of the dependent variable for non-NULL pairs in a group, where x is the independent variable and y is the dependent...

  • regr_countread 2026-09-27

    Returns the number of non-NULL number pairs in a group.

  • regr_interceptread 2026-09-27

    Returns the intercept of the univariate linear regression line for non-NULL pairs in a group.

  • regr_r2read 2026-09-27

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

  • regr_sloperead 2026-09-27

    Returns the slope of the linear regression line for non-NULL pairs in a group.

  • regr_sxxread 2026-09-27

    regr_sxx aggregate

  • regr_sxyread 2026-09-27

    Returns the population covariance of input values

  • regr_syyread 2026-09-27

    regr_syy aggregate

EasyMorph (1)
  • Linear regressionread 2026-09-27

    Fit a simple linear regression over a series and interpolate or extrapolate values

KNIME Analytics Platform (3)
RapidMiner Studio (1)
  • Function Fittingread 2026-09-27

    Fit the parameters of a user-given numeric function to the data points

Background

Regression analysis estimates the relationship between a dependent variable, the outcome, and one or more independent variables, the predictors.[1] The most common form of regression analysis is linear regression, which finds the line or hyperplane that most closely fits the data by a chosen criterion.[1] Regression analysis is widely used for prediction and forecasting, and in some situations to infer causal relationships between the variables.[1]

When to use it

On its own, a regression only reveals relationships between a dependent variable and a collection of independent variables in a fixed dataset.[1] To use a regression for prediction or to infer a cause, a researcher must justify why the relationships found have predictive power in a new context, or why a relationship has a causal interpretation.[1] Treat a fitted slope as a description of the rows you fitted, and be able to say why it should hold beyond them before you forecast with it or claim an effect. (Meridian's view)

Prediction within the range of the data used to fit a regression model is known informally as interpolation, and prediction outside that range as extrapolation.[1] The further an extrapolation goes outside the data, the more room there is for a regression model to fail.[1] Check the range of each predictor before reading a value off the fitted line, and flag any prediction that falls outside it. (Meridian's view)

The coefficient of determination, R squared (R²), is the proportion of the variation in the dependent variable that is predictable from the independent variables.[2] In least squares regression on typical data, R² is at least weakly increasing as predictors are added, so R² alone cannot meaningfully compare models with very different numbers of predictors.[2] A non-zero correlation between two variables is not, on its own, evidence that changing the value of one would change the other.[2] Read R² as how closely the fit tracks these rows, not as proof that the model is right or that the predictors cause the outcome. (Meridian's view)

The method

The method of least squares was published by Legendre in 1805, and by Gauss in 1809.[1] Legendre and Gauss both applied least squares to determining, from astronomical observations, the orbits of bodies about the Sun.[1] The term regression was coined by Francis Galton in the 19th century, for the tendency of the heights of descendants of tall ancestors to regress down towards a normal average.[1] Galton's work on regression was later extended by Udny Yule and Karl Pearson to a more general statistical context.[1]

The method of ordinary least squares (OLS) computes the unique line or hyperplane that minimises the sum of squared differences between the data and that line or hyperplane.[1] A linear regression with exactly one independent variable is a simple linear regression, and one with two or more is a multiple linear regression.[3] In a simple linear regression fitted by OLS, the slope of the fitted line equals the correlation between the two variables corrected by the ratio of their standard deviations.[4] The intercept of a simple linear regression fitted by OLS is such that the line passes through the centre of mass of the data points.[4] Linear regression was the first type of regression analysis to be studied rigorously, partly because models that depend linearly on their unknown parameters are easier to fit.[3]

In DuckDB, regr_count(y, x) is the number of non-NULL pairs, and regr_avgx and regr_avgy average the independent and dependent variables over the non-NULL pairs.[5] In DuckDB, regr_r2(y, x) returns the squared Pearson correlation coefficient between y and x.[5] In a linear least squares regression with a single predictor and a fitted intercept, R² equals the squared Pearson correlation coefficient between the dependent variable and the predictor.[2]

Linear regression can model curved relationships, because the linear refers to linearity in the parameters, so with polynomial terms the fitted line can take a variety of non-linear shapes.[1] In RapidMiner, polynomial regression is treated as a special case of multiple linear regression, because its regression function is linear in the unknown parameters.[6] RapidMiner's Function Fitting operator fits a user-given parametrised function to data points by minimising the sum of squared differences between the function's values and the observed values.[7] RapidMiner's Function Fitting operator minimises that objective with either Bound Optimization BY Quadratic Approximation (BOBYQA) or Covariance Matrix Adaptation Evolution Strategy (CMA-ES).[7]

Compared with the alternatives

Ridge regression and the lasso deliberately introduce bias into the estimates to reduce their variability, and generally have lower mean squared error than OLS, particularly when overfitting is a problem.[3] Least absolute deviation (LAD) regression is less sensitive to outliers than OLS, but less efficient than OLS when no outliers are present.[3] The Theil–Sen estimator takes as its slope the median of the slopes of the lines through pairs of sample points, and is much less sensitive to outliers than simple linear regression.[3] When a few extreme rows could be pulling the line, fit a robust estimator alongside OLS and see whether the slope moves. (Meridian's view)

Logistic regression fitted by maximum likelihood plays a similarly basic role for binary responses as linear regression fitted by OLS plays for scalar responses.[8]

Sources

  1. [1]Regression analysis - Wikipediasecondary source
  2. [2]Coefficient of determination - Wikipediasecondary source
  3. [3]Linear regression - Wikipediasecondary source
  4. [4]Simple linear regression - Wikipediasecondary source
  5. [5]Aggregate Functions – DuckDBdocumentation
  6. [6]Polynomial Regression - Rapidminer Documentationdocumentation
  7. [7]Function Fitting - Rapidminer Documentationdocumentation
  8. [8]Logistic regression - Wikipediasecondary source