4.15 Database Query (SQL)

1. Example statement: Retrieve five cpu_usage records of resource collector r1 for a range up to the current time

   SELECT "r1:cpu_usage" FROM SPDB WHERE _ts < NOW LIMIT 5;
   

2. Example statement: Retrieve the cpu_usage data of resource collector r1 for a range up to the current time where cpu_usage is 10 or more

   SELECT "r1:cpu_usage" FROM SPDB WHERE _TS < NOW AND "r1:cpu_usage" >= 10
   

3. Example statement: Retrieve the max and min values of dataA of PLC collector plc1 with an analysis time width of 5 seconds

   SELECT MAX("plc1:dataA"),MIN("plc1:dataA") FROM SPDB WHERE _ts < NOW GROUP BY TUMBLING(5,sec) LIMIT 10;
   

Syntax Specification

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>]

Reserved words are not case sensitive.

• <select_list>

Specify the column to extract or expression. You can specify multiple values separated by commas. Note that the column name must be enclosed in double quotation marks (") when specifying a column used in SpeeDBee Synapse. Column names enclosed in double quotation marks are case sensitive.

Example 1: Column name

"plcA:data1", "computeB:DATAa"

Example 2: Integer, floating decimal, string

3, -5, 1.52, 0.32e2, 'TEST-STR'

Example 3: Expression

"plcA:data2" + 3

Example 4: Function

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

Example 5: Constant

M_PI

Example 6: Pseudocolumn

_WID, _TS

• <table>

Specify what you want to search for time series data.

To search both memory and files

SPDB

SPDB.MEM

SPDB.FILE

• <condition>

Specify a conditional expression.

Example 1: Memory usage is less than 50 or CPU usage is 30 or less

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

Note: _TS can only be used on the left side.

You cannot specify more than one time condition in the same SELECT clause.

Use AND to connect with other conditions. OR connection is not allowed.

• <window_condition>

Specify the condition for window analysis. There are two types of windows: the tumbling window, which separates windows to be analyzed by time width, and the value change window, which separates windows by value change.

Example 1: Tumbling window (Split analysis window in 5 seconds)

GROUP BY TUMBLING(5,sec)

Example 2: Value change window (Separate analysis window when plcA:pressure changes)

GROUP BY VCHG("plcA:pressure")

Example 3: Value change window (Separate analysis window when plcA:pressure changes or after 30 seconds)

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

Example 4: Tumbling window + HAVING (Split analysis window in 5 seconds, and extract records whose plcA:pressure max is 10 or more)

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

Example 5: Value change window + HAVING (Separate analysis window when plcA:pressure changes or after 30 seconds, and extract records whose plcA:pressure min is less than 5)

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

• <row_count>

Specify the number of output rows as an integer.

To set the number of data records to be retrieved to 3

LIMIT 3

• <start>

Specify the number of rows to skip for the start position as an integer.

To retrieve the first three records after the fifth record

LIMIT 5,3

• About ORDER BY

You can specify only _TS for ORDER BY. If ORDER BY is not specified, then it is considered that ascending order retrieval with ORDER BY _TS ASC is specified.

Descending order specification

ORDER BY _TS DESC

• About specifying array type columns

Example 1: If com1:a is an array type, all array elements are referenced.

SELECT "com1:a" FROM SPDB;

Example 2: If com1:a is an array type, the first and second elements are referenced.
An array element is also specified in the conditional expression.

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

Example 3: If com1:a is an array type, an array is specified in the conditional expression.
This is implicitly equivalent to com1:a[0].

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

Example 4: This is an example of an array search ANY. Assume that com1:a has two elements. The following two example statements are equivalent.
ANY specifies that all elements are compared and true is returned if any of them is true.

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];

Note: ANY can only be used on the right side.

Example 5: This is an example of array search ALL. Assume that com1:a has two elements. The following two example statements are equivalent.
ALL specifies that all elements are compared and true is returned if all of them are true.

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];

Note: ALL can only be used on the right side.

• SHOW ALIAS

Example 1: Retrieve a list of alias names (ascending)

SHOW ALIAS ORDER BY ALIAS_NAME ASC

Example 2: Retrieve a list of alias names (descending)

SHOW ALIAS ORDER BY ALIAS_NAME DESC

Syntax Specification

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

Example 1: Assigning an integer

SET $GVAR0 TO 10;

Example 2: Assigning a negative value

SET $PVAR0 -111;

Example 3: Assigning a floating decimal

SET $GVAR1 1.235;

Example 4: Assigning in exponential expression

SET $PVAR1 -1.234E-2

Example 5: Assigning a string

SET $PVAR2 'abcd'

Example of referencing the assigned global variables

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

Syntax Specification

SET global variable name [TO] literal value

Example 1: Insert the value "2" into the column "cpu_usage" of the resource collector "resource1 (resource collector)"

INSERT INTO SPDB (\"resource1:cpu_usage\") VALUES (2);

Example 2: Insert the value "10" into the column "cpu_usage" of the resource collector "resource1 (resource collector)" with the specified time "April 3, 2025 13:02:09"

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

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

Example 3: Insert the integer value "10"

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

Example 4: Insert the floating decimal value "3.14"

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

Example 5: Insert the string value "ABCDE"

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

Example 6: Insert the array values {3,4}

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

Example 7: Insert multiple data into multiple columns of one collector

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

Example 8: Insert multiple data into multiple columns of multiple collectors

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');

Syntax Specification

INSERT [INTO] SPDB (_TS, <column_name>,...) VALUES (timestamp value, value, ...)