Time Series Supplemental Material

Help Procedural SQL
1

Hi,
I’d like to introduce MemSQL to my students at UCLA Anderson.
I was planning to use the Time series example provided online.

When I try creating the Stored Proc, I get an error.
ERROR 1064 ER_PARSE_ERROR: You have an error in your SQL syntax;

I was wondering if you have the SQL available in a file?

Thank you

2

I think what happened is that you’re using MemSQL 7.0 and in that release we introduced built-in functions for FIRST(), LAST(), and TIME_BUCKET(). The blog you’re referencing pre-dated 7.0. So I would recommend that you just use the built-ins. But if you want to try the stored procedure and user-defined aggregate versions, you can just rename the functions to FIRST2, LAST2 and TIME_BUCKET2 or something similar.

Here’s the documentation for the new time series functions in 7.0:

Make sense?

3

It makes sense yes. The reason I tried to create the SP and function in the first place is because I was getting an error when trying to use the time_bucket function :

ERROR 2405 UNKNOWN_ERR_CODE: Leaf Error (10.0.0.178:3306): Invalid time bucket specification

WITH ranked AS
   (SELECT symbol, ts,
    RANK() OVER w as r,
    MIN(price) OVER w as min_pr,
    MAX(price) OVER w as max_pr,
    FIRST_VALUE(price) OVER w as first,
    LAST_VALUE(price) OVER w as last
 
   FROM tick
   WINDOW w AS (PARTITION BY symbol, time_bucket('3 minute', ts)
        ORDER BY ts
        ROWS BETWEEN UNBOUNDED PRECEDING
                AND UNBOUNDED FOLLOWING))
 
SELECT symbol, time_bucket('3 minute', ts), min_pr, max_pr,
first, last
FROM ranked
WHERE r = 1
ORDER BY 1, 2;
4

For 3 minutes, you’d use ‘3m’ as the format string for TIME_BUCKET(). The interval formats supported use the ISO-8601 formats, as listed here, and are not case-sensitive, so you can use lowercase: Page Error | Digi International

The largest unit supported as of 7.0 is week. We also support the MySQL-style intervals like in:

time_bucket(INTERVAL 3 MINUTE, now())