查询数据

Timescale Cloud：性能、规模、企业级

自托管产品

MST

将原始实时报价数据转换为聚合K线图是金融数据用户的常见任务。TimescaleDB 包含hyperfunctions，您可以使用它们更轻松地存储和查询金融数据。Hyperfunctions 是 TimescaleDB 中的 SQL 函数，可让您以更少的代码行在 PostgreSQL 中更轻松地操作和分析时序数据。

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

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

创建连续聚合

要查看 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 crypto_ticks
    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 将结果绘制为K线图。为此，您需要将 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 将图表保存到仪表板。