Question 312031 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL: CAST combined with window function causes syntax error

I want to condense my database, which now contains a large number of values, to 1/12 of the original size by averaging twelve consecutive values.

To do this, I form the mean value using the types TIMESTAMP and FLOAT(4,2). In order to average the TIMESTAMP properly, I first wanted to cast the TIMESTAMP into an INT, then calculate the average value with AVG() and finally cast the average value back into a DATETIME. During this operation, however, I get a syntax error that I cannot explain.

That is the respective query I’ve tried:

INSERT INTO condensed_12_current(time_mean, current_mean)
SELECT
  CAST(AVG(CAST(time as INTEGER)) AS DATETIME) OVER (
    ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
  ) AS time_mean,
  AVG(current) OVER (
    ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
  ) AS current_mean
FROM current
WHERE id % 12 = 0;

Caused Error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTEGER)) AS DATETIME) OVER (
    ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
  ' at line 3

I have tried many different versions of the CAST instruction, but none of them have produced the desired result. The only CAST statement that was syntactically correct was when I cast the AVG() value back to the original data type.

Table current has the following structure:
time as TIMESTAMP
current as FLOAT(4,2)
id as INT AUTO INCREMENT

Table condensed_12_current has the following structure:
time as TIMESTAMP
current as FLOAT(4,2)

MYSQL Ver 8.2.0 for Linux on x86_64 (MySQL Community Server – GPL)

Tags:

2

Answers


  1. 0

    You should tell your database how to order the rows or it will process them in arbitrary order. For example, to order rows by the ID column:

    avg(current) over (
    order by id rows between 11 preceding and current row
    ) as current_mean

    Example at DB Fiddle

    MySQL doesn’t seem to support casting a timestamp to an integer. You can use from_unixtime() and unix_timestamp() instead:

    from_unixtime(avg(unix_timestamp(ts)) over
            (rows between 1 preceding and current row))

    Example at DB Fiddle

    Login or Signup to reply.
  2. 0

    CAST() is not a windowing operation, so we have an error looking at this expression:

    CAST(AVG(CAST(time as INTEGER)) AS DATETIME) OVER (
    ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
)

    Here, the OVER clause relative to the CAST() is not correct. That is, you must cast the entire result of the window. You need to take the average over a given window rather than a cast over the window:

    CAST(   AVG(CAST(time as INTEGER)) OVER (
            ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
        )  
    AS DATETIME)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search