查询数据

对于处理金融数据的用户来说，将原始的实时tick数据转换为聚合的蜡烛图视图是一项常见的任务。 TimescaleDB 包含 hyperfunctions，您可以使用它们更轻松地存储和查询您的金融数据。 Hyperfunctions 是 TimescaleDB 中的 SQL 函数，可以更轻松地在 PostgreSQL 中操作和分析时序数据，并且代码行数更少。

有三个对于计算蜡烛图值至关重要的 hyperfunctions：time_bucket()、FIRST() 和 LAST()。 time_bucket() hyperfunction 帮助您根据时间戳值将记录聚合到任意时间间隔的桶中。 FIRST() 和 LAST() 帮助您计算开盘价和收盘价。要计算最高价和最低价，您可以使用标准的 PostgreSQL 聚合函数 MIN 和 MAX。

在 TimescaleDB 中，创建蜡烛图视图最有效的方法是使用连续聚合。在本教程中，您将为蜡烛图时间桶创建一个连续聚合，然后使用不同的刷新策略查询该聚合。最后，您可以使用 Grafana 将您的数据可视化为蜡烛图。

创建连续聚合

要查看 OHLCV 值，最有效的方法是创建连续聚合。在本教程中，您将创建一个连续聚合，以聚合每天的数据。然后，您将聚合设置为每天刷新，并聚合最近两天的价值数据。

创建连续聚合

连接到包含 Twelve Data 加密货币数据集的 Timescale 数据库。

在 psql 提示符下，创建连续聚合以每分钟聚合数据


CREATE MATERIALIZED VIEW one_day_candle
WITH (timescaledb.continuous) AS
    SELECT
        time_bucket('1 day', time) AS bucket,
        symbol,
        FIRST(price, time) AS "open",
        MAX(price) AS high,
        MIN(price) AS low,
        LAST(price, time) AS "close",
        LAST(day_volume, time) AS day_volume
    FROM stocks_real_time
    GROUP BY bucket, symbol;

当您创建连续聚合时，它默认刷新。

设置刷新策略以每天更新连续聚合，如果最近两天的超表中存在新数据


SELECT add_continuous_aggregate_policy('one_day_candle',
    start_offset => INTERVAL '3 days',
    end_offset => INTERVAL '1 day',
    schedule_interval => INTERVAL '1 day');

查询连续聚合

当您设置好连续聚合后，您可以查询它以获取 OHLCV 值。

查询连续聚合

连接到包含 Twelve Data 加密货币数据集的 Timescale 数据库。

在 psql 提示符下，使用此查询按时间桶选择过去 14 天的所有比特币 OHLCV 数据


SELECT * FROM one_day_candle
WHERE symbol = 'BTC/USD' AND bucket >= NOW() - INTERVAL '14 days'
ORDER BY bucket;

查询结果如下所示


bucket         | symbol  |  open   |  high   |   low   |  close  | day_volume
------------------------+---------+---------+---------+---------+---------+------------
 2022-11-24 00:00:00+00 | BTC/USD |   16587 | 16781.2 | 16463.4 | 16597.4 |      21803
 2022-11-25 00:00:00+00 | BTC/USD | 16597.4 | 16610.1 | 16344.4 | 16503.1 |      20788
 2022-11-26 00:00:00+00 | BTC/USD | 16507.9 | 16685.5 | 16384.5 | 16450.6 |      12300

绘制 OHLCV 数据图表

当您提取了原始 OHLCV 数据后，您可以使用它在 Grafana 中绘制蜡烛图。为此，您需要设置 Grafana 以连接到您的 TimescaleDB 数据库。

绘制 OHLCV 数据图表

确保您已安装 Grafana，并且您正在使用包含 Twelve Data 股票数据集的 TimescaleDB 数据库设置为数据源。
在 Grafana 中，从 Dashboards 菜单中，单击 New Dashboard。在 New Dashboard 页面中，单击 Add a new panel。
在右上角的 Visualizations 菜单中，从列表中选择 Candlestick。确保您已将 Twelve Data 股票数据集设置为您的数据源。
单击 Edit SQL 并粘贴您用于获取 OHLCV 值的查询。
在 Format as 部分中，选择 Table。
根据需要调整表格元素，然后单击 Apply 以将您的图表保存到仪表板。