CodeDump
Wüst Jim Wüst Jim - 5 months ago 42
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.

Rick James Rick James
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!)
Share