AWS Timestream: How to select OHLC values?

Question

I'm trying to use AWS Timestream as the datasource for a POC of a stock analytics application. I plan to save stock data at the lowest interval available in the DB and then query the desired timeframes.

What I tried so far is to use window functions.

But I just can't get it to work. I already tried the following queries:

SELECT BIN(time, 15m) as binned_timestamp,
    max(measure_value::double) as high,
    first_value(measure_value::double) over (partition by BIN(time, 15m)) as open,
    last_value(measure_value::double) over (partition by BIN(time, 15m)) as close,
    min(measure_value::double) as low
FROM mydb.mytable
GROUP BY BIN(time, 15m),
    first_value(measure_value::double),
    last_value(measure_value::double)
ORDER BY binned_timestamp

Results in

line 3:2: '"first_value"(measure_value::double) OVER (PARTITION BY "BIN"(time, INTERVAL '15' MINUTE))' must be an aggregate expression or appear in GROUP BY clause

SELECT BIN(time, 15m) as binned_timestamp,
    max(measure_value::double) as high,
    first_value(measure_value::double) over (partition by BIN(time, 15m)) as open,
    last_value(measure_value::double) over (partition by BIN(time, 15m)) as close,
    min(measure_value::double) as low
FROM mydb.mytable
GROUP BY BIN(time, 15m),
    first_value(measure_value::double),
    last_value(measure_value::double)
ORDER BY binned_timestamp

Results in

line 8:2: GROUP BY clause cannot contain aggregations, window functions or grouping operations: ["first_value"(measure_value::double) OVER (PARTITION BY "BIN"(time, INTERVAL '15' MINUTE))]

These two are a bit contradictory. Both say do it the other way, but the other way says do it as you did previously..

So I'm a bit stuck here and my SQL skills aren't outrageous. Any help is appreciated.

Hey, did you find a solution? I have the same issue right now — Vincent Caggiari, May 14 '22 at 14:35
@VincentCaggiari No I didn't find a solution yet. I abandoned the project a while ago.. If I get back to it, I might find one. In the meantime, you can try the answer of Mike, but I'm unsure if it helps. — Sebastian Grunow, Aug 25 '22 at 17:02

score 0 · Answer 1 · answered Jun 04 '22 at 21:59

I tried with something like:

WITH ohlc_list AS (
  select bin(time, 180s) as ohlc_time,
    first_value(open) over (partition by bin(time, 180s)) as ohlc_open,
    max(high) over(partition by bin(time, 180s)) as ohlc_high,
    min(low) over(partition by bin(time, 180s)) as ohlc_low,
    last_value(close) over (partition by bin(time, 180s)) as ohlc_close,
    row_number() over (partition by bin(time, 180s)) as ohlc_row_number
  from "coinstrue_engine_dev"."ohlc"
)
SELECT *
FROM ohlc_list
where ohlc_row_number=1
order by ohlc_time ASC

It's not optimal but at least, this version should work for your POC.

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Jun 05 '22 at 00:10

AWS Timestream: How to select OHLC values?

1 Answers1