こんにちは。CX事業本部Delivery部のakkyです。

Grafanaにはローソク足チャート(Candlestick)を表示するパネルが付属していますが、集計を行う幅などは設定できないため、開始値、終了値、最高値、最低値をそれぞれ渡さなければなりません。

初めからそのようになっている株価のようなデータであれば問題ありませんが、単なる時系列データでは、自分でそれらの値を計算する必要があります。 今回は、Amazon Timestreamをデータソースとして、時系列データをSQLで加工してローソク足チャートを表示してみましたので、ご紹介します。

今回の内容は、Amazon Managed Grafanaで検証しましたが、通常のGrafanaにAmazon Timestreamプラグインを入れた場合でも使えるはずです。

時系列データをそのまま使った場合

単に時系列データをローソク足チャートにすると、以下のようにグラフの上下に合わせて陰線と陽線が表示されるだけです。単位期間の最大値や最小値は計算してくれません。

変数の設定

ローソク足チャートの単位期間の幅を設定するために、Grafanaの変数機能を使います。変数はダッシュボードに設定でき、テキストボックスやドロップダウンボックスでクエリやグラフのパラメータを変更することができる機能です。

まず、ダッシュボード右上のメニューから、設定ボタンをクリックします。

左側のメニューからVariablesをクリックし、Add valueをクリックします。

変数の設定画面になりますので、Nameにperiodと入力し、TypeはIntervalを選択します。名前はSQLで使用する名前になります。 Valuesにはデフォルトで値が入力されていますが、よく使用する範囲をカンマ区切りで追加することもできます。

なお、ここで使用する単位は、Amazon Timestreamのドキュメントをご覧ください。

設定が終わったらUpdateをクリックし、その後画面左上の←矢印からダッシュボードに戻ります。

Timestreamのクエリ

ダッシュボードからパネルを新規作成し、データソースにTimestream、グラフにCandlestickを選択した後、以下のクエリを入力してください。

${period}が変数に置換されます。

クエリの内容としては、まずWindow関数で指定した範囲の開始値と終了値を作り(open_close)、次にGROUP BYで最大値と最小値を作ります(high_low)。 最後にこれらを結合して完成です。なお、high_lowでは平均値averageも一緒に作っています。

なお、測定値はmeasure_value::double型としていますので、使用するデータの内容によって書き換えてください。

WITH 
open_close AS (
  SELECT 
    time, 
    first_value(measure_value::double) OVER(PARTITION BY bin(time, ${period}) ORDER BY time ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS open,
    last_value(measure_value::double) OVER(PARTITION BY bin(time, ${period}) ORDER BY time ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS close
  FROM $__database.$__table
  WHERE $__timeFilter and measure_name = '$__measure'
  ORDER BY time ASC),
high_low AS (
  SELECT 
    min(time) AS time_,
    max(measure_value::double) AS high,
    min(measure_value::double) AS low,
    avg(measure_value::double) AS average
  FROM $__database.$__table
  WHERE $__timeFilter and measure_name = '$__measure'
  GROUP BY bin(time, ${period})
  ORDER BY min(time) ASC
)
SELECT time, open, close, high, low, average
FROM open_close  JOIN high_low ON open_close.time = high_low.time_
ORDER BY time ASC

ちなみに、Timestreamデータソースでは、マクロとしてデータベース名やテーブル名、メジャー名が選べますが、これは$__database, $__table, $__measureに入っています。また、表示する時間範囲は$__timeFilterにまとめて入っています。

なお、先ほど追加した平均値averageは、右側のグラフオプションのCandlestick→Additional fieldsからIncludeを選択すると一緒に表示できます。

おわりに

久しぶりにSQLを書いたので苦労しましたが、うまく動かすことができました。timeで集約する都合上、GROUP BYとWindow関数が同時に使えず苦しみました。短くかける方法があれば教えてください！

Grafanaの変数機能はこれ以外にも便利に使えそうなので、今後も便利な使用方法をご紹介したいと思います。