tmp.sql

--- Welford's method with CTE

WITH RECURSIVE t3iter AS (
  SELECT
    LEAD(ts, 1)
      OVER (ORDER BY (ts, fingerprint)) AS next_ts,
    LEAD(fingerprint, 1)
      OVER (ORDER BY (ts, fingerprint)) AS next_fingerprint,
    t3.ts,
    t3.fingerprint,
    t3.mean,
    t3.squared_diff,
    t3.count
  FROM
    t3
  ORDER BY
    (t3.ts, t3.fingerprint)
),
reduce AS (
  (
    SELECT
      t3iter.next_ts,
      t3iter.next_fingerprint,
      t3iter.ts,
      t3iter.fingerprint,
      t3iter.mean,
      t3iter.squared_diff,
      t3iter.count
    FROM
      t3iter
    ORDER BY
      (t3iter.ts, t3iter.fingerprint)
    LIMIT 1
  )

UNION ALL

  (
    SELECT
      t3iter.next_ts,
      t3iter.next_fingerprint,
      t3iter.ts,
      t3iter.fingerprint,
      (t3iter.mean * t3iter.count::FLOAT + reduce.mean * reduce.count::FLOAT) / (t3iter.count + reduce.count)::FLOAT
        AS mean,
      (t3iter.squared_diff + reduce.squared_diff) + ((POWER(t3iter.mean - reduce.mean, 2) * (t3iter.count * reduce.count)::FLOAT) / (t3iter.count + reduce.count)::FLOAT)
        AS squared_diff,
      t3iter.count + reduce.count AS count
    FROM
      t3iter
    JOIN
      reduce
    ON
      t3iter.ts = reduce.next_ts AND
      t3iter.fingerprint = reduce.next_fingerprint
    WHERE
      t3iter.ts IS NOT NULL OR
      t3iter.fingerprint IS NOT NULL
    ORDER BY
      (t3iter.ts, t3iter.fingerprint)
  )
)

SELECT * FROM reduce ORDER BY (ts, fingerprint);