Wüst Jim Wüst Jim - 8 days ago 5
MySQL Question

MariaDB how to make an RMS function?

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


The problem is that the SQL standard Common Table Expressions (CTEs) is not supported for my version (10.1.18-MariaDB). The last one on the Archarm Linux the things is fix for the version 10.2.2: MDEV-8308

And I don't know how to do the same work in a different way. If someone can help.

Answer

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:

  • The id being defined in the CREATE TABLE will be in addition to value coming from the `SELECT.
  • This assumes 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!)