AutoCorrelation.sql

-- Using DuckDb
-- Example is Lag 1

CREATE TEMP TABLE t1 (
	SERIES DECIMAL(18,2)
);
INSERT INTO t1 VALUES(895);
INSERT INTO t1 VALUES(432);
INSERT INTO t1 VALUES(282);
INSERT INTO t1 VALUES(879);
INSERT INTO t1 VALUES(492);
INSERT INTO t1 VALUES(392);
INSERT INTO t1 VALUES(525);
INSERT INTO t1 VALUES(390);
INSERT INTO t1 VALUES(671);
INSERT INTO t1 VALUES(792);
INSERT INTO t1 VALUES(362);
INSERT INTO t1 VALUES(383);
INSERT INTO t1 VALUES(493);
INSERT INTO t1 VALUES(431);
INSERT INTO t1 VALUES(645);
INSERT INTO t1 VALUES(657);
INSERT INTO t1 VALUES(286);
INSERT INTO t1 VALUES(553);
INSERT INTO t1 VALUES(527);
INSERT INTO t1 VALUES(636);
INSERT INTO t1 VALUES(333);
INSERT INTO t1 VALUES(866);
INSERT INTO t1 VALUES(936);
INSERT INTO t1 VALUES(309);
INSERT INTO t1 VALUES(937);


-- Center the values by subtracting each value from the mean
WITH c AS (
SELECT
	row_number() OVER() AS ID,
	SERIES,
	SERIES-(
	SELECT
		AVG(SERIES)
	FROM
		t1) AS CENTERED
FROM
	t1),

-- Lag the centered values
l AS (
SELECT 
	ID,
	SERIES,
	CENTERED,
	-- SET LAG HERE
	lag(CENTERED,1) OVER (
	ORDER BY ID) AS LCENTERED
FROM
	c),

-- Calculate numerator, which is the sum of products of centered and lagged values 
n AS (
SELECT 
		SUM(CENTERED * LCENTERED) AS NUMERATOR,
FROM
	l
WHERE
	LCENTERED IS NOT NULL
),

-- Calculate denominator, which is the sum of the squares of the centered values
d AS (
SELECT
	SUM(POWER(CENTERED, 2)) AS DENOMINATOR
FROM
	c
)

-- Calculate Autocorrelation
SELECT 
	n.NUMERATOR / d.DENOMINATOR AS AUTOCORRELATION
FROM
	n,
	d;