MariaDB how to make an RMS function?

Question

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 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:

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!)

MariaDB how to make an RMS function?

Sign up