I try to make an RMS function on My MariaDB. I have found this topic: Link.
With this code:
WITH nums AS
(
SELECT value, ROW_NUMBER() OVER (ORDER BY orderer) AS rn
FROM source
)
SELECT SQRT(AVG(POWER(np.value - nn.value, 2)))
FROM nums np
JOIN nums nn
ON nn.rn = np.rn + 1
Simulate ROW_NUMBER()
by creating an actual table with an AUTO_INCREMENT
that is the PRIMARY KEY
. Then you can do a "self join" of that table to itself. It's only a few keystrokes more than your CTE attempt:
CREATE TABLE nums (
rn INT AUTO_INCREMENT PRIMARY KEY
) ENGINE=InnoDB
SELECT value FROM source ORDER BY orderer;
SELECT SQRT(AVG(POWER(np.value - nn.value, 2)))
FROM nums AS np
JOIN nums AS nn ON nn.rn = np.rn + 1;
Notes:
id
being defined in the CREATE TABLE
will be in addition to value
coming from the `SELECT.auto_increment_increment = 1
. If you are using Galera (etc), change the +1
to + @@auto_increment_increment
.CREATE TEMPORARY TABLE
won't work because (until recently) you could not use that kind of temp table twice in the same query. (Why? Beats me!)