コンテンツにスキップ

4.15 DBクエリ（SQL）

ここでは、DBクエリ（SQL）について説明します。

4.15.1 DBクエリの概要#

DBクエリサービスを起動すると、時系列データベースに対して Web-API（HTTP）経由でSQL（Structured Query Language）による問い合わせが可能です。 構文は、SpeeDBee Synapse独自のものですが、一般的なRDBで使用されるSQLと近い構文仕様となっています。

4.15.2 DBクエリの詳細#

ここでは、Web-API仕様および構文について説明します。

4.15.2.1 Web-API仕様#

DBクエリサービスのWeb-API仕様は、こちらをご覧ください。

4.15.2.2 構文仕様#

4.15.2.2.1 SELECT#

SELECT句は、時系列データベース内に登録されているデータを参照する構文です。

  1. 例文：リソースコレクタr1のcpu_usageを現在時刻までを範囲として、5件取得する 
    SELECT "r1:cpu_usage" FROM SPDB WHERE _ts < NOW LIMIT 5;
  2. 例文：リソースコレクタr1のcpu_usageを現在時刻までを範囲とし、かつcpu_usageが10以上のデータを取得する 
    SELECT "r1:cpu_usage" FROM SPDB WHERE _TS < NOW AND "r1:cpu_usage" >= 10
  3. 例文：PLCコレクタplc1のdataAの最大値と最小値について、分析の時間幅5秒で取得する 
    SELECT MAX("plc1:dataA"),MIN("plc1:dataA") FROM SPDB WHERE _ts < NOW GROUP BY TUMBLING(5,sec) LIMIT 10;

構文仕様 

SELECT <select_list> FROM <table> [WHERE <condition>] [GROUP BY <window_condition> [HAVING <condition>]] [ORDER BY _TS [ASC/DESC]] [LIMIT <number> [OFFSET <start>]|LIMIT <start>,<row_count>]

予約語については、大文字、小文字を区別しません。

  • <select_list>

抽出するカラムや式を指定します。指定はカンマ区切りで複数の指定が可能です。 なお、SpeeDBee Synapseで使用するカラムは、必ずダブルクオーテーション（"）でカラム名を囲んで指定して下さい。 またダブルクォーテーションで囲まれたカラム名は大文字・小文字を区別します。

例1：カラム名 

"plcA:data1", "computeB:DATAa"

例2：整数、浮動小数、文字列 

3, -5, 1.52, 0.32e2, 'TEST-STR'
SQL内の整数はint64として扱います。浮動小数はdoubleとして扱い、指数表現も可能です。 文字列は、シングルクォーテーションで囲みます。ダブルクォーテーションは、カラム名などの識別子として扱われます。

例3：式 

"plcA:data2" + 3

使用可能な演算子 説明
+ 加算
- 減算
* 乗算
/ 除算
% 余り

例4：関数 

MAX("compute1:dataA"), AVG("compute2:dataB")

使用可能な数学関数 戻り値 説明
ABS INT64 絶対値 (整数)
ACOS DOUBLE 三角関数 逆余弦
ASIN DOUBLE 三角関数 逆正弦
ATAN DOUBLE 三角関数 逆正接
CEIL DOUBLE 小数点以下切り上げ
COS DOUBLE 三角関数 余弦
COSH DOUBLE 双曲線余弦
EXP DOUBLE 底がネイピア数 e である指数関数
FABS DOUBLE 絶対値 （倍精度浮動小数）
FLOOR DOUBLE 小数点以下切り捨て
FMOD DOUBLE 剰余
LOG DOUBLE 自然対数
LOG10 DOUBLE 10を底とする対数
POW DOUBLE べき乗
ROUND DOUBLE 小数点以下四捨五入
SIN DOUBLE 三角関数 正弦
SINH DOUBLE 双曲線正弦
SQRT DOUBLE 平方根
TAN DOUBLE 三角関数 正接
TANH DOUBLE 双曲線正接
使用可能な集約・統計関数 戻り値 説明
AVEDEV DOUBLE 平均偏差
AVG DOUBLE 算術平均
COUNT INT64 件数
CV DOUBLE 変動係数
DOF INT64 自由度
MAX DOUBLE 最大
MIN DOUBLE 最小
SE DOUBLE 標準誤差
STDEV DOUBLE 不偏標準偏差
STDEVP DOUBLE 標準偏差
SUM DOUBLE 合計
SUMSQ DOUBLE 2乗和
VAR DOUBLE 不偏分散
VARP DOUBLE 標本分散
使用可能な代表値 戻り値 説明
LAST 値のデータ型 ウィンドウ最終値
TOP 値のデータ型 ウィンドウ先頭値

例5：定数 

M_PI

使用可能な定数 戻り値 説明
M_E DOUBLE ネイピア数、自然底数の底（e）
M_LOG2E DOUBLE log_e(e)
M_LOG10E DOUBLE log_10(e)
M_LN2 DOUBLE log_e(2)
M_LN10 DOUBLE log_e(10)
M_PI DOUBLE 円周率（Pi）
M_PI_2 DOUBLE Pi/2
M_PI_4 DOUBLE Pi/4
M_1_PI DOUBLE 1/Pi
M_2_SQRTPI DOUBLE 2/Pi
M_SQRT2 DOUBLE sqrt(2)
M_SQRT1_2 DOUBLE 1/sqrt(2)

例6：疑似列 

_WID, _TS

使用可能な疑似列 説明
_WID ウィンドウID（GROUP BY使用時の分析幅単位で変化するID）
_TS タイムスタンプ（ナノ秒）
  • <table>

時系列データの検索対象を指定します。

メモリとファイルの両方を検索する場合 

SPDB
メモリのみ検索する場合 
SPDB.MEM
ファイルのみ検索する場合 
SPDB.FILE

  • <condition>

条件式を指定します。

例1：メモリ使用率が50未満　または　CPU使用率が30以下 

"r1:mem_usage" < 50 OR "r1:cpu_usage" <= 30

使用可能な比較演算子 説明
= 等価である
!= 不一致
<> 不一致
< 未満
> 超過
<= 以下
>= 以上
IS [NOT] NULL NULLである、NULLでない
BETWEEN AND 範囲内
BETWEEN EXCLUSIVE AND EXCLUSIVE 指定範囲を含めない
使用可能な論理演算子 説明
AND 論理積
OR 論理和
NOT 否定
_TSに指定可能な演算子 説明
_TS = 100000000 指定したナノ秒と等価
_TS > 100000000 指定したナノ秒を超過
_TS >= 100000000 指定したナノ秒以上
_TS < 100000000 指定したナノ秒未満
_TS <= 100000000 指定したナノ秒以下
_TS BETWEEN 1000000000 AND 9000000000 指定したナノ秒の範囲
_TS BETWEEN 1000000000 EXCLUSIVE AND 9000000000 EXCLUSIVE 指定したナノ秒を除く範囲
_TS > '20230808T01:23:45.111222333' ISO8601フォーマットでの文字列指定での比較
_TS < NOW 現在時刻との比較
_TS < NOW + 1 hour 現在時刻+1時間までを範囲とする
_TS < NOW + 10 minutes 現在時刻から+10分までを範囲とする
_TS < NOW + 5 sec 現在時刻から+5秒までを範囲とする
_TS < NOW - 100 msec 現在時刻から-100ミリ秒までを範囲とする
_TS < NOW - 100 usec 現在時刻から-100マイクロ秒までを範囲とする
_TS < NOW - 100 nsec 現在時刻から-100ナノ秒までを範囲とする

※_TSは左辺にのみ使用できます。

また、時間条件は１つのSELECT句で複数指定することはできません。

他の条件と使用する場合は、ANDで接続します。OR接続はできません。

  • <window_condition>

ウィンドウ分析の条件を指定します。ウィンドウは2種類あり、時間幅で分析するウィンドウを区切るタンブリングウィンドウと値が変化したことでウィンドウを区切るバリューチェンジウィンドウが使用できます。

例1：タンブリングウィンドウ（5秒で分析ウィンドウを分割） 

GROUP BY TUMBLING(5,sec)

例2：バリューチェンジウィンドウ（plcA:pressureが変化したら分析ウィンドウを区切る） 

GROUP BY VCHG("plcA:pressure")

例3：バリューチェンジウィンドウ（plcA:pressureが変化、または30秒経過で分析ウィンドウを区切る） 

GROUP BY VCHG("plcA:pressure", 30, sec)

例4：タンブリングウィンドウ＋HAVING（5秒で分析ウィンドウを分割、plcA:pressureの最大値が10以上のものを抽出） 

GROUP BY TUMBLING(10,sec) HAVING MAX("plcA:pressure") >= 10

例5：バリューチェンジウィンドウ+HAVING（plcA:pressureが変化、または30秒経過で分析ウィンドウを区切り、plcA:pressureの最小値が5未満のものを抽出） 

GROUP BY VCHG("plcA:pressure", 30, sec) HAVING MIN("plcA:pressure") < 5

  • <row_count>

出力行数を整数で指定します。

取得するデータ件数を3件とする場合 

LIMIT 3

  • <start>

開始位置を飛ばす行数を整数で指定します。

先頭から5件目以降の3件を取得する場合 

LIMIT 5,3

  • ORDER BYについて

ORDER BYは_TSのみ指定可能です。ORDER BYの指定がない場合、ORDER BY _TS ASCによる昇順取得が指定されたことになります。

降順指定 

ORDER BY _TS DESC

  • 配列型のカラムに対する指定について

例1：com1:aが配列型の場合、すべての配列要素を参照します。 

SELECT "com1:a" FROM SPDB;

例2：com1:aが配列型の場合、先頭要素と2番目の要素を参照します。
さらに条件式に配列要素を指定しています。 

SELECT "com1:a"[0], "com1:a"[1] FROM SPDB WHERE "com1:a"[0] > 1;

例3：com1:aが配列型の場合、条件式に配列を指定しています。
この場合、暗黙的にcom1:a[0]と同じ意味となります。 

SELECT "com1:a"[0], "com1:a"[1] FROM SPDB WHERE "com1:a" > 1;

例4：配列検索ANYの例です。com1:aの要素数は2とします。次の2つの例文は、同じ意味となります。
ANYはすべての要素との比較を行い、いずれか一つでも真となる場合に、真が成立する指定です。 

SELECT "com1:a"[0], "com1:a"[1] FROM SPDB WHERE 1 < ANY("com1:a");

SELECT "com1:a"[0], "com1:a"[1] FROM SPDB WHERE 1 < "com1:a"[0] OR 1 < "com1:a"[1];

※ANYは右辺にのみ使用できます。

例5：配列検索ALLの例です。com1:aの要素数は2とします。次の2つの例文は、同じ意味となります。
ALLはすべての要素との比較を行い、すべてが真となる場合に、真が成立する指定です。 

SELECT "com1:a"[0], "com1:a"[1] FROM SPDB WHERE 1 < ALL("com1:a");

SELECT "com1:a"[0], "com1:a"[1] FROM SPDB WHERE 1 < "com1:a"[0] AND 1 < "com1:a"[1];

※ALLは右辺にのみ使用できます。

4.15.2.2.2 SHOW#

SHOW句は、時系列データベース内のメタ情報を参照する構文です。 現在は、エイリアス名（SpeeDBee Synapseで作成されるカラム名に相当）のみ参照が可能です。
ORDER BYを省略すると、並び順は保証されません。

  • SHOW ALIAS

例1：エイリアス名の一覧を取得（昇順） 

SHOW ALIAS ORDER BY ALIAS_NAME ASC

例2：エイリアス名の一覧を取得（降順） 

SHOW ALIAS ORDER BY ALIAS_NAME DESC

構文仕様 

SHOW ALIAS [ORDER BY ALIAS_NAME [ASC/DESC]]

4.15.2.2.3 SET#

グローバル変数へ値を代入する構文です。 SQL内では、グローバル変数の先頭には、必ず$を付与する必要があります。

例1：整数の代入 

SET $GVAR0 TO 10;

例2：マイナス値の代入 

SET $PVAR0 -111;

例3：浮動小数の代入 

SET $GVAR1 1.235;

例4：指数表現での代入 

SET $PVAR1 -1.234E-2

例5：文字列の代入 

SET $PVAR2 'abcd'

代入したグローバル変数の参照例 

SELECT \"com1:a\",$GVAR1, $PVAR2 FROM SPDB WHERE _ts BETWEEN NOW - 1 sec AND NOW ORDER BY _TS DESC LIMIT 1;

構文仕様 

SET グローバル変数名 [TO] リテラル値

4.15.2.2.4 INSERT#

INSERT句は、時系列データベースへデータを登録する構文です。 存在するコンポーネントのカラムに対して、登録が可能です。登録可能なカラム名の一覧は、SHOWで取得できます。
INSERT文の[INTO]直後のテーブル指定は、SPDB固定となります。

例1：リソースコレクタ「resource1(リソースコレクタ)」のカラム「cpu_usage」に、値「2」を挿入する

INSERT INTO SPDB (\"resource1:cpu_usage\") VALUES (2);
※タイムスタンプを省略した場合、登録されるタイムスタンプは稼働マシンのシステム時刻が使用されます。

例2：リソースコレクタ「resource1(リソースコレクタ)」のカラム「cpu_usage」に時刻「2025年04月03日 13時02分09秒」を指定して、値「10」を挿入する

INSERT INTO SPDB (_TS, \"resource1:cpu_usage\") VALUES ('2025-04-03T13:02:09', 10);

INSERT INTO SPDB (_TS, \"resource1:cpu_usage\") VALUES (1743685329000000000, 10);

例3：整数型の値「10」を挿入する 

INSERT INTO SPDB (\"customcomp1:columnA\") VALUES (10);

例4：浮動小数型の値「3.14」を挿入する 

INSERT INTO SPDB (\"customcomp1:columnB\") VALUES (3.14);

例5：文字型の値「ABCDE」を挿入する 

INSERT INTO SPDB (\"customcomp1:columnC\") VALUES ('ABCDE');

例6：配列の値{3,4}を挿入する 

INSERT INTO SPDB (\"customcomp1:columnD\") VALUES ({3,4});

例7：一つのコレクタ＆複数のカラムへ、複数のデータを挿入する 

INSERT INTO SPDB (\"customcomp1:columnA\", \"customcomp1:columnB\",\"customcomp1:columnC\",\"customcomp1:columnD\",\"customcomp1:columnE\") VALUES (10,3.14,'ABCDE',{3,4},'00000000000000000000000000')

例8：複数のコレクタ＆複数のカラムへ、複数のデータを挿入する 

INSERT INTO SPDB (\"resource1:cpu_usage\", \"customcomp1:columnA\", \"customcomp1:columnB\",\"customcomp1:columnC\",\"customcomp1:columnD\",\"customcomp1:columnE\") VALUES (99,10,3.14,'ABCDE',{3,4},'00000000000000000000000000');

構文仕様 

INSERT [INTO] SPDB (_TS, <column_name>,...) VALUES (タイムスタンプ値, value,...)

4.15.3 DBクエリサービス利用時の注意点#

DBクエリサービス利用時の注意点を説明します。

SpeeDBee SynapseのDBクエリサービスは、高負荷時においても安定稼働を維持する設計を目指しておりますが、DBクエリの実行に関しては、システムの負荷状況に大きく左右される特性があります。
特に、システムが高負荷で動作している場合、DBクエリの応答性能が一時的に低下する可能性があります。

クエリ条件が及ぼすシステムへの負荷

データベースに対するクエリは、指定するデータの対象範囲が広くなればなるほど、システムへの負荷が高まります。
たとえば期間を広くとるクエリや、絞り込み条件が少ないクエリは、大量のデータを走査する必要があるため、応答時間が長くなるだけでなく、他の処理にも影響を及ぼす可能性があります。

安定したシステム運用のため、クエリ実行の際は以下の点にご留意ください。

クエリ実行の留意事項

  • アクセストークンを設定する
    アクセストークンを発行して、DBクエリサービスへ適用することで、DBクエリを利用できる利用者を制限することが出来ます。

  • 必要最小限の範囲でクエリを実行する
    必要なデータに絞り込み、期間や条件をできるだけ具体的に設定してください。

  • 高負荷時を避ける
    可能であれば、システム利用が集中する時間帯を避けてクエリを実行することを推奨します。