Measure autocorrelation or a spectrum

-- a series that repeats every four steps, twelve values long; the length is read from the series wherever it is needed
CREATE TEMP TABLE series AS SELECT t, [0, 1, 0, -1][t % 4 + 1]::DOUBLE AS x FROM range(12) s(t);
-- autocorrelation: how the series covaries with itself shifted by each lag, over its variance
CREATE TEMP TABLE acf AS
WITH m AS (SELECT avg(x) AS mu FROM series)
SELECT lag, sum((a.x - mu) * (b.x - mu)) / (SELECT sum(power(x - mu, 2)) FROM series, m) AS r
FROM range(0, 7) l(lag), series a JOIN series b ON b.t = a.t + lag, m GROUP BY lag;
-- spectrum: the periodogram at each frequency k in cycles per series length, the squared size of the discrete Fourier transform over n
CREATE TEMP TABLE spectrum AS
SELECT k, (power(sum(x * cos(2 * pi() * k * t / n)), 2) + power(sum(x * sin(2 * pi() * k * t / n)), 2)) / n AS power
FROM series, (SELECT count(*) AS n FROM series) c, range(0, 7) f(k) GROUP BY k, n;
-- load cycles: rainflow counting (ASTM E1049 three-point rule) over sequences of peaks and valleys; a full cycle counts 1, a half cycle 0.5.
-- Sequence 1 is the textbook one. Sequence 2 swings less each time, so nothing closes and all nine ranges are left as half cycles at the end.
CREATE TEMP TABLE reversals AS
SELECT s, generate_subscripts(v, 1) AS i, unnest(v) AS v
FROM (VALUES (1, [-2, 1, -3, 5, -1, 3, -4, 4, -2]), (2, [10, -9, 8, -7, 6, -5, 4, -3, 2, -1])) t(s, v);
CREATE TEMP TABLE walk AS
WITH RECURSIVE steps(s, step, next_i, stack, cycle_range, cycle_count) AS (
  SELECT DISTINCT s, 0, 1, []::INTEGER[], NULL::INTEGER, NULL::DOUBLE FROM reversals
  UNION ALL
  SELECT s, step + 1,
         CASE WHEN reduce THEN next_i ELSE next_i + 1 END,
         CASE WHEN reduce AND len(stack) = 3 THEN stack[2:]
              WHEN reduce THEN list_concat(stack[:len(stack) - 3], [stack[len(stack)]])
              ELSE list_append(stack, (SELECT v FROM reversals r WHERE r.s = e.s AND r.i = e.next_i)) END,
         CASE WHEN reduce THEN abs(stack[len(stack) - 1] - stack[len(stack) - 2]) END,
         CASE WHEN reduce THEN CASE WHEN len(stack) = 3 THEN 0.5 ELSE 1.0 END END
  FROM (SELECT *, len(stack) >= 3 AND abs(stack[len(stack)] - stack[len(stack) - 1]) >= abs(stack[len(stack) - 1] - stack[len(stack) - 2]) AS reduce FROM steps) e
  WHERE reduce OR next_i <= (SELECT count(*) FROM reversals r WHERE r.s = e.s))
SELECT * FROM steps;
-- what the stack still holds at the end counts as half cycles, one per step between neighbours, however long the stack is
CREATE TEMP TABLE cycles AS
SELECT s, cycle_range AS range, cycle_count AS n FROM walk WHERE cycle_range IS NOT NULL
UNION ALL
SELECT s, unnest(list_transform(range(1, len(stack)), lambda j: abs(stack[j + 1] - stack[j]))), 0.5
FROM (SELECT s, stack FROM walk QUALIFY row_number() OVER (PARTITION BY s ORDER BY step DESC) = 1);
CREATE TEMP TABLE bins AS SELECT s, range, sum(n) AS cycles FROM cycles GROUP BY s, range;
SELECT * FROM acf ORDER BY lag;
SELECT * FROM spectrum ORDER BY k;
SELECT * FROM bins ORDER BY s, range;
-- the lag-4 autocorrelation is 2/3 and lag-2 is -5/6 (fewer pairs at longer lags); all the power sits at k = 3, the four-step cycle;
-- the textbook sequence gives one full cycle of range 4 and half cycles of ranges 3, 4, 6, 8, 8 and 9; the second gives nine half cycles of ranges 19, 17, ... 3
SELECT CASE WHEN ((SELECT list(round(r, 9) ORDER BY lag) = [1.0, 0.0, -0.833333333, 0.0, 0.666666667, 0.0, -0.5] FROM acf)
  AND (SELECT list(round(power, 9) ORDER BY k) = [0.0, 0.0, 0.0, 3.0, 0.0, 0.0, 0.0] FROM spectrum)
  AND (SELECT map_from_entries(list((range, cycles) ORDER BY range)) = MAP {3: 0.5, 4: 1.5, 6: 0.5, 8: 1.0, 9: 0.5} FROM bins WHERE s = 1)
  AND (SELECT sum(cycles) = 4.0 FROM bins WHERE s = 1)
  AND (SELECT list(range ORDER BY range DESC) = [19, 17, 15, 13, 11, 9, 7, 5, 3] AND min(cycles) = 0.5 AND max(cycles) = 0.5 FROM bins WHERE s = 2)) THEN 'as expected' ELSE error('the series should show autocorrelation of -5/6 at lag 2 and 2/3 at lag 4, all its power at frequency 3, the rainflow counts of the textbook sequence, and nine half cycles for the sequence that never closes') END AS verdict;