时间戳

首页 > 解决方案 > AWS Timestream：如何选择 OHLC 值？

sql - AWS Timestream：如何选择 OHLC 值？

问题描述

我正在尝试使用 AWS Timestream 作为股票分析应用程序的 POC 的数据源。我计划以数据库中可用的最低间隔保存股票数据，然后查询所需的时间范围。

到目前为止，我尝试的是使用窗口函数。

但我就是无法让它工作。我已经尝试了以下查询：

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

结果是

第 3:2 行：'"first_value"(measure_value::double) OVER (PARTITION BY "BIN"(time, INTERVAL '15' MINUTE))' 必须是聚合表达式或出现在 GROUP BY 子句中

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

结果是

第 8:2 行：GROUP BY 子句不能包含聚合、窗口函数或分组操作：["first_value"(measure_value::double) OVER (PARTITION BY "BIN"(time, INTERVAL '15' MINUTE))]

这两个有点矛盾。两者都说以另一种方式做，但另一种方式说像以前一样做..

所以我有点卡在这里，我的 SQL 技能并不离谱。任何帮助表示赞赏。

标签： sqlamazon-web-servicesamazon-timestream

解决方案

推荐阅读