This document describes SQL of NewSQL interface for accessing the database supported by GridDB Community Edition (hereinafter referred to as GridDB CE). Please note that this is a different interface than the NoSQL interface. The NewSQL interface can be referenced and updated by regarding the container created with the NoSQL interface as a table. Updates include not only row updates, but container schema and index changes. Also, the table created by NewSQL interface can be referenced and updated by NoSQL interface as a container.
This chapter shows the descriptive format of the SQL that can be used in the NewSQL interface.
Besides the SELECT command, DDL command (Data Definition Language) such as CREATE TABLE, and INSERT/DELETE are also supported. See SQL commands supported by GridDB for details.
The data types used for data storage in the NewSQL interface are shown in the table below. These data type names can be specified as a column data type when creating a table.
Data types | Description |
---|---|
BOOL | True/False |
BYTE | Integer value from -27 to 27-1 (8 bit) |
SHORT | Integer value from -215 to 215-1 (16 bit) |
INTEGER | Integer value from -231 to 231-1 (32 bit) |
LONG | Integer value from -263 to 263-1 (64 bit) |
FLOAT | Single-precision data type (32 bits), floating-point number defined in IEEE754 |
DOUBLE | Double-precision data type (64 bits), floating-point number defined in IEEE754 |
TIMESTAMP | Data type representing a date and time pair. Precision can be specified from among millisecond, microsecond, and nanosecond precision. If unspecified, millisecond precision will be used. |
STRING | Text that is composed of an arbitrary number of characters using the unicode code point. |
BLOB | Data type for binary data such as images and voice, etc. Large objects to be saved directly in the input format. The character x or X can also be added to create a hexadecimal expression such as X’23AB’. |
A NULL value can be registered to table. The results of operators that is related to NULL value such as “IS NULL” are SQL-compliant.
A TIMESTAMP type is a type representing a date and time pair. For time, precision can be specified from among millisecond, microsecond, and nanosecond. Precision is specified using the format TIMESTAMP(p). For the precision value p, use one of the following: 3, 6, or 9. A TIMESTAMP type with this specified value is called a TIMESTAMP type with specified precision. Specifically, precision can be defined using one of the following type names corresponding to millisecond, microsecond, and nanosecond.
Type name | Description |
---|---|
TIMESTAMP | Represents time data up to millisecond precision (default precision) |
TIMESTAMP(3) | Represents time data up to millisecond precision |
TIMESTAMP(6) | Represents time data up to microsecond precision |
TIMESTAMP(9) | Represents time data to up to nanosecond precision. |
[Memo]
In the NewSQL interface, for data type names that are described as column data types when the table was created, even if the name does not match the data type name given in Data types used in data storage, follow the rules to interpret and determine the data type to use for data storage.
Check the following rules in sequence starting from the top and determine the data type to use for data storage based on the applicable rule. The data type name described when checking the rules and the strings to check using the rules are not case sensitive. If multiple rules apply, the rule ranked higher will be prioritized. If no rules are applicable, an error will occur and table creation will fail.
Rule no. | Data type names, that were described as column data types when the table was created | Column type of the table to be created |
---|---|---|
1 | Type names listed in Data types used in data storage | Same as specified type |
2 | REAL | DOUBLE |
3 | TINYINT | BYTE |
4 | SMALLINT | SHORT |
5 | BIGINT | LONG |
6 | Type name including “INT” | INTEGER |
7 | Type name including any of “CHAR”, “CLOB”, “TEXT” | STRING |
8 | Type name including “BLOB” | BLOB |
9 | Type name including any of “REAL”, “DOUB” | DOUBLE |
10 | Type name including “FLOA” | FLOAT |
An example to determine the data type using this rule is shown.
Describe the data type as follows in the NewSQL interface when using the data type equivalent to the one used in the clients of the NoSQL interface. However, some data types may not be used as the equivalent type do not exist.
Data type in the NoSQL interface in the client | Equivalent column data type descriptions in NewSQL interface |
---|---|
STRING (string data type) | STRING or “Expression to be STRING” |
BOOL (Boolean) | BOOL |
BYTE (8-bit integer) | BYTE or “Expression to be BYTE” |
SHORT (16-bit integer) | SHORT or “Expression to be SHORT” |
INTEGER (32-bit integer) | INTEGER or “Expression to be INTEGER” |
LONG (64-bit integer) | LONG or “Expression to be LONG” |
FLOAT (32 bitwise floating point number) | FLOAT or “Expression to be FLOAT” |
DOUBLE (64 bitwise floating point number) | DOUBLE or “Expression to be DOUBLE” |
TIMESTAMP (time data type) | TIMESTAMP |
GEOMETRY (spatial data type) | Cannot be specified as a data type of the column when creating a table |
BLOB | BLOB or “Expression to be BLOB” |
ARRAY | Cannot be specified as a data type of the column when creating a table |
[Memo]
The container created with the NoSQL interface client is handled as follows using the container’s column type and value when accessing it with the NewSQL interface:
Column type of container | Data type mapped in NewSQL | Value |
---|---|---|
STRING | STRING | Same as original value |
BOOL | BOOL | Same as original value |
BYTE | BYTE | Same as original value |
SHORT | SHORT | Same as original value |
INTEGER | INTEGER | Same as original value |
LONG | LONG | Same as original value |
FLOAT | FLOAT | Same as original value |
DOUBLE | DOUBLE | Same as original value |
TIMESTAMP | TIMESTAMP | Same as original value |
GEOMETRY | Same data type as NULL constant (Types.UNKNOWN) | All the values are NULL |
BLOB | BLOB | Same as original value |
ARRAY | Same data type as NULL constant (Types.UNKNOWN) | All the values are NULL |
[memo]
The data types which are supported by the NoSQL interface, but not by the NewSQL interface are as follows.
This section explains how to handle the data of these data types when accessed using the NewSQL interface.
Rows can not be registered or updated even by specifying only the column values of the supported data types, without specifying any column values of these data types.
// The table created using the NoSQL interface
name: sample1
Column: id INTEGER
value DOUBLE
geometry GEOMETRY
// Register rows by specifying only INTEGER and DOUBLE columns. -> An error occurs because the table has a GEOMETRY type column.
INSERT INTO sample1 (id, value) VALUES (1, 192.3)
There are 2 types of GridDB user, an administrator user and a general user, which differ in terms of the functions which can be used. In addition, access can be separated on a user basis by creating a database. See GridDB Features Reference for the details of users and a database.
The naming rules are as follows:
See GridDB Features Reference for the details about the node affinity function, and the rules and the restrictions of naming.
[Notice]
If the name of a table or a column contains characters other than ASCII alphanumeric characters and underscore, or if the first character of the name is a number in a SQL statement, enclose the name with double quotation marks.
SELECT "column.a1" FROM "Table-5"
Supported SQL commands are in the table as follows.
Command | Overview |
---|---|
CREATE DATABASE | Create a database. |
CREATE TABLE | Create a table. |
CREATE INDEX | Create an index. |
CREATE VIEW | Create a view. |
CREATE USER | Create a general user. |
CREATE ROLE | Create a role. |
DROP DATABASE | Delete a database. |
DROP TABLE | Delete a table. |
DROP INDEX | Delete an index. |
DROP VIEW | Delete a view. |
DROP USER | Delete a general user. |
DROP ROLE | Delete a role. |
ALTER TABLE | Change the structure of a table. |
GRANT | Assign database access rights to a general user. |
REVOKE | Revoke database access rights from a general user. |
SET PASSWORD | Change the password of a general user. |
SELECT | Select data. |
INSERT | Insert rows into a table. |
DELETE | Delete rows from a table. |
UPDATE | Update rows in a table. |
Comment | Add a comment. |
Hints | Control an execution plan. |
An explanation for each category of SQL command is given in this chapter.
Create a database.
Syntax
CREATE DATABASE database_name; |
Specifications
Create a table.
Syntax
Table (collection)
CREATE TABLE [IF NOT EXISTS] table name (column definition [, column definition …] [, PRIMARY KEY (column name [, …])]) [WITH (property key = property value)]; |
Timeseries table (timeseries container)
CREATE TABLE [IF NOT EXISTS] table_name ( column_name TIMESTAMP PRIMARY KEY [, column definition …] ) USING TIMESERIES [WITH (property_key=property_value [, property_key=property_value …])]; |
Specifications
Function | Item | Property key | Property value type |
---|---|---|---|
Data affinity | hint (Character string indicating similarity between containers) |
data_affinity | STRING |
Examples
Creating a table
CREATE TABLE myTable (
key INTEGER PRIMARY KEY,
value1 DOUBLE NOT NULL,
value2 DOUBLE NOT NULL
);
Creating a partitioned table
See GridDB Features Reference for details of each partitioning function.
(1) Creating a hash partitioned table
Syntax
Table (collection)
CREATE TABLE [IF NOT EXISTS] table_name ( column definition [, column definition …] [, PRIMARY KEY(column name [, …])] ) [WITH (property_key=property_value)] PARTITION BY HASH (column_name_of_partitioning_key) PARTITIONS division_count; |
Timeseries table (timeseries container)
CREATE TABLE [IF NOT EXISTS] table_name ( column definition [, column definition …] ) USING TIMESERIES [WITH (property_key=property_value, …)]] PARTITION BY HASH (column_name_of_partitioning_key) PARTITIONS division_count ; |
Specifications
Option specifications
**Data affinity**
Examples
Creating a hash partitioned table
CREATE TABLE myHashPartition (
id INTEGER PRIMARY KEY,
value STRING
) PARTITION BY HASH (id) PARTITIONS 128;
(2) Creating an interval partitioned table
Syntax
Table (collection)
CREATE TABLE [IF NOT EXISTS] table_name ( column definition [, column definition …] [, PRIMARY KEY(column name [, …])]) [WITH (property_key=property_value, …)] PARTITION BY RANGE(column_name_of_partitioning_key) EVERY(interval_value [, interval_unit ]); |
Timeseries table (timeseries container)
CREATE TABLE [IF NOT EXISTS] table_name ( column definition [, column definition …] ) USING TIMESERIES [WITH (property_key=property_value, …)] PARTITION BY RANGE(column_name_of_partitioning_key) EVERY(interval_value [, interval_unit ]) ; |
Specifications
The following values can be specified as the “interval_value”.
Partitioning key type | Possible interval value |
---|---|
BYTE | from 1 to 27-1 |
SHORT | from 1 to 215-1 |
INTEGER | from 1 to 231-1 |
LONG | from 1000 to 263-1 |
TIMESTAMP | 1 or more |
[Memo]
Option specifications
**Data affinity**
**Expiry release**
The options about expiry release can be specified by the format “ WITH (property_key=property_value, …)”.
Function | Item | Property key | Property value type | Required or optional when setting expiry release |
---|---|---|---|---|
Expiry release function | Type | expiration_type | STRING (Only the following type can be specified. PARTITION: Partition expiry release) |
Optional (default: PARTITION) |
Expiration time | expiration_time | INTEGER | Required | |
Expiration time unit | expiration_time_unit | STRING (The following five types can be specified. DAY / HOUR / MINUTE / SECOND / MILLISECOND ) |
Optional (default: DAY) | |
Expiration division count | expiration_division_count | INTEGER | Optional (default: 8) |
[memo]
Data partition placement
The option to determine where to place data partitions corresponding to each date can be specified in the format “WITH (property key = property value, …)”.
Function | Item | Property key | Property value type | Required or optional when setting data partition placement |
---|---|---|---|---|
Interval group number | The number that identifies where a data partition is placed. Two tables that are created by specifying different interval group numbers from each other are allocated in such a way that they do not cause conflicts among processing threads on the same day. | interval_worker_group | INTEGER | Required |
Interval group node correction value | A value that corrects the node on which the processing threads that are determined by an interval group number are performed. This value can be specified by the user, but it is also possible to allow the server to determine it. | interval_worker_group_position | INTEGER | Optional (zero by default) |
[Memo]
Examples
Creating an interval partitioned table.
CREATE TABLE myIntervalPartition (
date TIMESTAMP PRIMARY KEY,
value STRING
) PARTITION BY RANGE (date) EVERY (30, DAY);
Creating an interval partitioned table (timeseries table) using the partition expiry release function.
CREATE TABLE myIntervalPartition2 (
date TIMESTAMP PRIMARY KEY,
value STRING
) USING TIMESERIES WITH (
expiration_type='PARTITION',
expiration_time=90,
expiration_time_unit='DAY'
) PARTITION BY RANGE (date) EVERY (30, DAY);
Creating an interval partitioned table by specifying where to place the data partition.
CREATE TABLE myIntervalPartition2 (
date TIMESTAMP PRIMARY KEY,
value STRING
) WITH (
interval_worker_group=1
) PARTITION BY RANGE (date) EVERY (1, DAY);
(3) Creating an interval hash partitioned table
Syntax
Table (collection)
CREATE TABLE [IF NOT EXISTS] table_name ( column definition [, column definition …] [, PRIMARY KEY((column_name [, …])] ) [WITH (property_key=property_value, …) ] PARTITION BY RANGE(column_name_of_interval_partitioning_key) EVERY(interval_value [, interval_unit ]) SUBPARTITION BY HASH(column_name_of_hash_partitioning_key) SUBPARTITIONS division_count; |
Timeseries table (timeseries container)
CREATE TABLE [IF NOT EXISTS] table_name ( column definition [, column definition …] ) USING TIMESERIES [WITH (property_key=property_value, …)] PARTITION BY RANGE(column_name_of_partitioning_key) EVERY(interval_value [, interval_unit ]) SUBPARTITION BY HASH(column_name_of_hashpartitioning_key) SUBPARTITIONS division_count ; |
Specifications
The following values can be specified as the “interval_value”.
Partitioning key type | Possible interval value |
---|---|
BYTE | from 1 to 27-1 |
SHORT | from 1 to 215-1 |
INTEGER | from 1 to 231-1 |
LONG | from 1000 * division_count to -263-1 |
TIMESTAMP | 1 or more |
[Memo]
Option specifications
**Data affinity**
**Expiry release**
The options about expiry release can be specified by the format “ WITH (property_key=property_value, …)”.
Function | Item | Property key | Property value type | Required or optional when setting expiry release |
---|---|---|---|---|
Expiry release function | Type | expiration_type | STRING (Any of the followings. If omitted, PARTITION. PARTITION: Partition expiry release ROW: Row expiry release) |
Optional |
Expiration time | expiration_time | INTEGER | Required | |
Expiration time unit | expiration_time_unit | STRING (The following five types can be specified. DAY / HOUR / MINUTE / SECOND / MILLISECOND ) |
Optional (default: DAY) | |
Expiration division count | expiration_division_count | INTEGER | Optional (default: 8) |
Examples
Creating an interval-hash partitioned table
CREATE TABLE myIntervalHashPartition (
date TIMESTAMP,
value STRING,
PRIMARY KEY (date, value)
) PARTITION BY RANGE (date) EVERY (60, DAY)
SUBPARTITION BY HASH (value) SUBPARTITIONS 64;
Creating an interval-hash partitioned table (timeseries table) using the partition expiry release function.
CREATE TABLE myIntervalHashPartition2 (
date TIMESTAMP PRIMARY KEY,
value STRING
) USING TIMESERIES WITH (
expiration_type='PARTITION',
expiration_time=90,
expiration_time_unit='DAY'
) PARTITION BY RANGE (date) EVERY (60, DAY)
SUBPARTITION BY HASH (date) SUBPARTITIONS 64;
Create an index.
Syntax
CREATE INDEX [IF NOT EXISTS] index_name ON table_name ( column_name_to_be_indexed ); |
Specifications
Create a view.
Syntax
CREATE [FORCE] VIEW view_name AS SELECT statement; |
Specifications
Create a general user.
Syntax
CREATE USER user_name IDENTIFIED BY ‘password_string’ ; |
Specifications
Create a role required for LDAP authentication.
Syntax
CREATE ROLE role name ; |
Specifications
Delete a database.
Syntax
DROP DATABASE database_name; |
Specifications
Delete a table.
Syntax
DROP TABLE [IF EXISTS] table_name; |
Specifications
Delete the specified index.
Syntax
DROP INDEX [IF EXISTS] index_name ON table_name; |
Specifications
Delete a view.
Syntax
DROP VIEW [IF EXISTS] view name ; |
Specifications
Delete a general user.
Syntax
DROP USER user_name; |
Specifications
Delete a role required for LDAP authentication.
Syntax
DROP ROLE role name ; |
Specifications
Change the structure of a table.
Add columns to the end of the table.
Syntax
ALTER TABLE table_name ADD [COLUMN] column definition [,ADD [COLUMN] column definition …]; |
Specifications
Examples
Adding multiple columns to the table
ALTER TABLE myTable1
ADD COLUMN col111 STRING NOT NULL,
ADD COLUMN col112 INTEGER;
Delete data partitions created by table partitioning.
Syntax
ALTER TABLE table_name DROP PARTITION FOR ( value_included_in_the_data_partition ); |
Specifications
See Metatables for the details on the metatable.
Examples
Interval partitioned table
Check the lower limit value of the interval partitioned table “myIntervalPartition1” (partitioning key type: TIMESTAMP, interval: 30 DAY)
SELECT PARTITION_BOUNDARY_VALUE FROM "#table_partitions"
WHERE TABLE_NAME='myIntervalPartition1' ORDER BY PARTITION_BOUNDARY_VALUE;
PARTITION_BOUNDARY_VALUE
-----------------------------------
2017-01-10T13:00:00.000Z
2017-02-09T13:00:00.000Z
2017-03-11T13:00:00.000Z
:
Delete unnecessary data partitions
ALTER TABLE myIntervalPartition1 DROP PARTITION FOR ('2017-01-10T13:00:00Z');
Interval hash partitioned table
Check the lower limit value of each data partitions on the interval hash partitioned table “myIntervalHashPartition” (partitioning key type: TIMESTAMP, interval value: 90 DAY, division count 3)
SELECT PARTITION_BOUNDARY_VALUE FROM "#table_partitions"
WHERE TABLE_NAME='myIntervalHashPartition' ORDER BY PARTITION_BOUNDARY_VALUE;
PARTITION_BOUNDARY_VALUE
-----------------------------------
2016-08-01T10:00:00.000Z The data of the same lower limit is hashed and
2016-08-01T10:00:00.000Z is divided into three data partitions.
2016-08-01T10:00:00.000Z
2016-10-30T10:00:00.000Z
2016-10-30T10:00:00.000Z
2016-10-30T10:00:00.000Z
2017-01-29T10:00:00.000Z
:
Delete unnecessary data partitions
ALTER TABLE myIntervalHashPartition DROP PARTITION FOR ('2016-09-15T10:00:00Z');
Data partitions that have same boundary value will be deleted
SELECT PARTITION_BOUNDARY_VALUE FROM "#table_partitions"
WHERE TABLE_NAME='myIntervalHashPartition' ORDER BY PARTITION_BOUNDARY_VALUE;
PARTITION_BOUNDARY_VALUE
-----------------------------------
2016-10-30T10:00:00.000Z For the section (lower limit '2016-08-01T10: 00: 00Z') including '2016-09-15T10: 00: 00Z'
2016-10-30T10:00:00.000Z three data partitions are deleted.
2016-10-30T10:00:00.000Z
2017-01-29T10:00:00.000Z
:
Change an existing specified column.
Syntax
ALTER TABLE table name RENAME COLUMN column name before renaming TO column name after renaming; |
Specifications
Examples
Renaming a column
ALTER TABLE myTable1 RENAME COLUMN col112 TO col121;
Assign database access rights to a general user or a role.
Syntax
GRANT {SELECT|ALL} ON database_name TO {user_name|role name}; |
Specifications
Revoke database access rights from a general user or a role.
Syntax
REVOKE {SELECT|ALL} ON database_name FROM {user_name|role name}; |
Specifications
Change the password of a general user.
Syntax
SET PASSWORD [FOR user_name ] = ‘password_string’; |
Specifications
Select data. Made up of a variety of Clauses such as FROM, WHERE, etc.
Syntax
SELECT [{ALL|DISTINCT}] * | column_name_1 [, column_name_2 …] [FROM clause] [WHERE clause] [GROUP BY clause [HAVING clause]] [{UNION [ALL] |INTERSECT|EXCEPT} SELECT statement] [ORDER BY clause] [LIMIT clause [OFFSET clause]] ; |
Register rows in a table. INSERT only registers rows, while INSERT OR REPLACE and REPLACE overwrite the existing data, when the data with the same primary key as that of the existing data is given. REPLACE is an alias of INSERT OR REPLACE and they are the same in their functions.
Syntax
{INSERT|INSERT OR REPLACE|REPLACE} INTO table_name {VALUES ( { number_1 | string_1 } [, { number_2 | string_2 } …] )|SELECT statement} ; |
Specifications
INSERT INTO myTable1 VALUES(1, 100);
REPLACE INTO myTable1 VALUES(1, 200);
INSERT INTO myTable1 SELECT * FROM myTable2;
Delete rows from a table.
Syntax
DELETE FROM table_name [ WHERE clause ]; |
Update the rows existing in a table.
Syntax
UPDATE table_name SET column_name_1 = expression_1 [, column_name_2 = expression_2 …] [ WHERE clause ]; |
Specifications
CREATE TABLE tab (a INTEGER, b STRING) PARTITION BY HASH a PARTITIONS 5;
-- NG
UPDATE tab SET a = a * 2;
[240016:SQL_COMPILE_PARTITIONING_KEY_NOT_UPDATABLE] Partitioning column='a' is not updatable
-- OK
UPDATE tab SET b = 'XXX';
CREATE TABLE myTable1 (key INTEGER, value INTEGER);
-- NG
UPDATE myTable1 SET myTable1.value = 999 WHERE myTable1.key = 8;
-- OK
UPDATE myTable1 SET value = 999 WHERE myTable1.key = 8;
CREATE TABLE myTable1 (key INTEGER, value INTEGER);
-- NG
UPDATE myTable1 SET value = (SELECT 999) WHERE key = 8;
-- OK
UPDATE myTable1 SET value = 999 WHERE key = (SELECT 8);
Specify the table name, view name, and subquery on which to execute data operations.
Syntax
FROM table_name_1 [, table_name_2 … ] |
FROM (sub_query) [AS] Alias [, …] |
Specifications
Example:
SELECT a.ID, b.ID FROM mytable a, (SELECT ID FROM mytable2) b;
ID ID
---+-----
1 100
1 200
2 100
2 200
:
Among the results of the clauses specified earlier, rows having the same value in the specified column will be grouped together.
Syntax
GROUP BY column_name_1 [, column_name_2 …] |
Perform filtering using the search condition on data grouped by the GROUP BY clause. GROUP BY clause cannot be omitted.
Syntax
HAVING search_conditions |
Sort search results.
ORDER BY column_name_1 [{ASC|DESC}] [, column_name_2 [{ASC|DESC}] …] |
Apply a search condition on the result of the preceding FROM clause.
Syntax
WHERE search_conditions |
Specifications
Extract the specified number of data from the specified location.
Syntax
LIMIT value_1 [OFFSET value_2 ] |
Specifications
Join a table.
Syntax
Type of join | Syntax |
---|---|
Inner join | Table 1 [INNER] JOIN table 2 [ON condition | USING(Column name [, column name …])] |
Left outer join | Table 1 LEFT [OUTER] JOIN table 2 [ON type | USING (column name [, column name …])] |
Cross join | Table 1 CROSS JOIN table 2 [ON condition | USING (column name [, column name …])] |
Specify join conditions with ON or USING.
Example:
name: employees
id first_name department_id
----+------------+----------------
0 John 0
1 William 1
2 Richard 0
3 Mary 4
4 Lisa 3
5 James 1
name: departments
department_id department
---------------+------------
0 Sales
1 Development
2 Research
3 Marketing
○Inner join
SELECT * FROM employees e INNER JOIN departments d ON e.department_id=d.department_id;
id first_name department_id department_id department
------+-----------+--------------+--------------+-----------
0 John 0 0 Sales
1 William 1 1 Development
2 Richard 0 0 Sales
4 Lisa 3 3 Marketing
5 James 1 1 Development
○Left outer join
SELECT * FROM employees e LEFT JOIN departments d ON e.department_id=d.department_id;
id first_name department_id department_id department
------+-----------+--------------+--------------+-----------
0 John 0 0 Sales
1 William 1 1 Development
2 Richard 0 0 Sales
3 Mary 4 (NULL) (NULL)
4 Lisa 3 3 Marketing
5 James 1 1 Development
Natural join (NATURAL JOIN) joins tables that have matching values in the rows under the same name.
Type of join | Syntax |
---|---|
Inner join | Table 1 NATURAL [INNER] JOIN table 2 |
Left outer join | Table 1 NATURAL LEFT [OUTER] JOIN table 2 |
Cross join | Table 1 NATURAL CROSS JOIN table 2 |
SELECT * FROM employees NATURAL INNER JOIN departments;
department_id id first_name department
---------------+-----+--------------+--------------
0 0 John Sales
1 1 William Development
0 2 Richard Sales
3 4 Lisa Marketing
1 5 James Development
Calculate on a set of two query results.
Syntax
Inquiry 1 UNION inquiry 2 | Returns all the results of two queries. (duplication is not included) |
Query 1 UNION ALL query 2 | Returns all the results of two queries. (duplication is included) |
Query 1 INTERSECT query 2 | Returns the results common to the results of two queries. |
Query 1 EXCEPT query 2 | Returns the difference of two queries (result included in the query 1, not in the query 2). |
Split and sort query results. Use with a WINDOW function.
Syntax
Function OVER ([PARTITION BY expression 1] [ORDER BY expression 2]) |
Specifications
Create result collections in which results are split for each given time span and perform aggregation operations. If interpolation operation is specified, value interpolation is performed for those collections where values are not included in results.
Syntax
GROUP BY RANGE(date column name) EVERY( time interval, unit [ ,offset ] ) [ FILL(interpolation method) ] |
Specifications
SELECT statements containing the GROUP BY RANGE clause have the following constraints:
The GROUP BY RANGE clause has the following specifications:
Specifying the timezone using strings: Specify the timezone in Z | ±HH:MM | ±HHMM format. |
DAY | HOUR | MINUTE | SECOND | MILLISECOND |
Example
name: trend_data1
ts value
-----------------------+-------
2023-01-01T00:00:00 10
2023-01-01T00:00:10 30
2023-01-01T00:00:20 30
2023-01-01T00:00:30 50
2023-01-01T00:00:40 50
2023-01-01T00:00:50 70
aggregation operation
SELECT ts,avg(value) FROM trend_data1
WHERE ts BETWEEN TIMESTAMP('2023-01-01T00:00:00Z') AND TIMESTAMP('2023-01-01T00:01:00Z')
GROUP BY RANGE ts EVERY (20,SECOND)
ts value
-----------------------+-------
2023-01-01T00:00:00 20
2023-01-01T00:00:20 40
2023-01-01T00:00:40 60
name: trend_data2
ts value
-----------------------+-------
2023-01-01T00:00:00 5
2023-01-01T00:00:10 10
2023-01-01T00:00:20 15
(time the data is missing)
2023-01-01T00:00:40 25
interpolation operation
SELECT * FROM trend_data2
WHERE ts BETWEEN TIMESTAMP('2023-01-01T00:00:00Z') AND TIMESTAMP('2023-01-01T00:01:00Z')
GROUP BY RANGE ts EVERY (10,SECOND) FILL (LINEAR)
ts value
-----------------------+-------
2023-01-01T00:00:00 5
2023-01-01T00:00:10 10
2023-01-01T00:00:20 15
2023-01-01T00:00:30 20
2023-01-01T00:00:40 25
This section explains the operators used in SQL statements.
The list of operators is as follows.
Class | Operator | Description |
---|---|---|
Arithmetic | + | Add |
- | Subtract | |
* | Multiply | |
/ | Divide | |
% | Modulo | |
Character | || | Connect the value of arbitrary types as a character string. If any one of the values is NULL, NULL is returned. |
Compare | =, == | Compare whether both sides are equal. |
!=, <> | Compare whether both sides are not equal. | |
> | Compare whether the left side is larger than the right side. | |
>= | Compare whether the left side is larger than or equal to the right one. | |
< | Compare whether the left side is smaller than the right side. | |
<= | Compare whether the left side is smaller than or equal to the right side. | |
IS | Compare whether both sides are equal. Return true, when both sides are NULLs. Return false, when either side is NULL. |
|
IS NOT | Compare whether both sides are not equal. Return false, when both sides are NULLs. Return true, when either side is NULL. |
|
ISNULL | Determine whether the left side is NULL. | |
NOTNULL | Determine whether the left side is not NULL. | |
LIKE | Search the character string on the right. | |
GLOB | Search the character string on the right. | |
BETWEEN | Extract values of the specified range. | |
IN | Return whether the specified value is included in the set of values. | |
Bit | & | A & B : Bitwise AND of A and B |
| | A | B : Bitwise OR of A and B | |
~ | ~A : Bitwise NOT of A | |
<< | A << B : Shift A to the left by B bit. | |
>> | A >> B : Shift A to the right by B bit. | |
Logic | AND | Return true, when both sides are true. Return false, when either side is false. Otherwise return NULL. |
OR | Return true, when the expression on either side is true. Return false, when the expressions on both sides are false. Otherwise return NULL. |
|
NOT | Return false, when the expression on the right is true. Return true, when the expression on the right is false. Otherwise return NULL. |
Search the character string on the right.
Syntax
str [NOT] LIKE pattern_str [ESCAPE escape_str ] |
Specifications
Syntax
Search the character string on the right.
str GLOB pattern_str |
Specifications
Extract values of the specified range.
Syntax
expression_1 [NOT] BETWEEN expression_2 AND expression_3 |
Specifications
Return true if the following conditions are met
expression_2 <= expression_1 <= expression_3
Return true if the following conditions are not met when NOT is specified.
Return whether the specified value is included in the set of values.
Syntax
expression_1 [NOT] IN ( expression_2 [, expression_3 …] ) |
Specifications
This section explains the functions used in SQL statements.
The following functions are available for SQL statements.
Class | Function name | Description |
---|---|---|
Aggregation | AVG | Return the average value. |
COUNT | Return the number of rows. | |
MAX | Return the maximum. | |
MIN | Return the minimum. | |
SUM | Return a sum of values. | |
TOTAL | Return a sum of values. | |
GROUP_CONCAT | Connect values. | |
STDDEV_SAMP | Return the sample standard deviation. | |
STDDEV | Return the sample standard deviation. | |
STDDEV0 | Return the sample standard deviation. | |
STDDEV_POP | Return the population standard deviation. | |
VAR_SAMP | Return the sample variance. | |
VARIANCE | Return the sample variance. | |
VARIANCE0 | Return the sample variance. | |
VAR_POP | Return the population variance. | |
MEDIAN | Return the median. | |
PERCENTILE_CONT | Return the percentile value. | |
Mathematical | ABS | Return an absolute value. |
ROUND | Round off. | |
RANDOM | Return a random number. | |
MAX | Return the maximum. | |
MIN | Return the minimum. | |
LOG | Return the logarithm. | |
SQRT | Return the square root. | |
TRUNC | Round down numbers. | |
HEX_TO_DEC | Converts a hexadecimal string to a decimal number | |
Character | LENGTH | Return the length of a character string. |
LOWER | Convert a character string to a lowercase. | |
UPPER | Convert a character string to an uppercase. | |
SUBSTR | Cut out part of a character string. | |
REPLACE | Replace a character string. | |
INSTR | Return the position of a specified character string in a character string. | |
LIKE | Search a character string. | |
GLOB | Search a character string. | |
TRIM | Remove a specified character(s) from the both ends of a character string. | |
LTRIM | Remove a specified character(s) from the left end of a character string. | |
RTRIM | Remove a specified character(s) from the right end of a character string. | |
QUOTE | Enclose a character string with single quotes. | |
UNICODE | Return the Unicode code point of a character. | |
CHAR | A Unicode code point is converted to characters and connected. | |
PRINTF | Return the converted character string | |
TRANSLATE | Replace a character string. | |
Time | NOW | Return the present time. |
TIMESTAMP | Convert the string representation of time to millisecond-precision TIMESTAMP. | |
TIMESTAMP_MS | Convert the string representation of time to millisecond-precision TIMESTAMP (TIMESTAMP(3)). | |
TIMESTAMP_US | Convert the string representation of time to microsecond-precision TIMESTAMP (TIMESTAMP(6)). | |
TIMESTAMP_NS | Convert the string representation of time to microsecond-precision TIMESTAMP (TIMESTAMP(9)). | |
TIMESTAMP_ADD | Add a duration to a time. | |
TIMESTAMP_DIFF | Return the difference of times. | |
TO_TIMESTAMP_MS | Add lapsed time to the time point ‘1970-01-01T00:00:00.000Z’. | |
TO_EPOCH_MS | Return the lapsed time from the time point ‘1970-01-01T00:00:00.000Z’. | |
EXTRACT | Take out the value of the specific field from time. | |
STRFTIME | Return a character string with the time converted. | |
MAKE_TIMESTAMP | Generate time. | |
TIMESTAMP_TRUNC | Truncate time. | |
WINDOW | ROW_NUMBER | Assign a unique sequential value to the resulting Row |
Other | COALESCE | Return the first argument that is not NULL. |
IFNULL | Return the first argument that is not NULL. | |
NULLIF | Return NULL when two arguments are the same, return the first argument when the arguments are different. | |
RANDOMBLOB | Return a BLOB type value (random number). | |
ZEROBLOB | Return a BLOB type value (0x00). | |
HEX | Convert a BLOB type value to a hexadecimal type. | |
TYPEOF | Return the data type of a value. |
These functions are described using the data in the following table as an example.
table: employees
id first_name last_name age department enrollment_period
----+------------+-----------+-------+-------------+-------------------
0 John Smith 43 Sales 15.5
1 William Jones 59 Development 23.2
2 Richard Brown (NULL) Sales 7.0
3 Mary Taylor 31 Research (NULL)
4 Lisa (NULL) 29 (NULL) 4.9
5 James Smith 43 Development 10.3
table: departments
id department
----+------------
0 Sales
1 Development
2 Research
table: travelexpenses
id date empId amount
-----+------------+-------+--------
101 2020/02/01 0 200
102 2020/02/03 2 2500
103 2020/02/03 3 60
104 2020/02/04 0 200
105 2020/02/05 0 150
106 2020/02/06 3 80
[Notice]
Functions to aggregate values DISTINCT or ALL can be specified as the argument of an aggregate function.
Format | function( [DISTINCT | ALL] argument) |
Point | Meaning |
---|---|
DISTINCT | Rows of duplicate values are excluded and aggregated |
ALL | All the rows including the duplicate values are aggregated. |
When no argument is specified, the resut will be the same as ALL is specified.
[Notice]
Aggregate functions are also available with the OVER clause as analytic functions. See OVER clause for details.
Example) Example using aggregate function SUM and OVER clause
SELECT id, date, empId, amount, SUM(amount) OVER(PARTITION BY empID ORDER BY id) as accumulated FROM travelexpenses;
Result:
id date empId amount accumulated
-----+------------+-------+--------+-------------
101 2020/02/01 0 200 200
104 2020/02/04 0 200 400
105 2020/02/05 0 150 550
102 2020/02/03 2 2500 2500
103 2020/02/03 3 60 60
106 2020/02/06 3 80 140
[Points to note]
Format | AVG( [DISTINCT | ALL] n) |
Return the average value of n.
Example:
SELECT AVG(age) FROM employees;
Result: 41.0
SELECT AVG(DISTINCT age) FROM employees;
Result: 40.5
SELECT department, AVG(age) avg FROM employees GROUP BY department;
Result:
department avg
------------+-----
Development 51.0
Research 31.0
Sales 43.0
(NULL) 29.0
Format | COUNT(* | [DISTINCT | ALL] x) |
Return the number of rows.
Example:
SELECT COUNT(*) FROM employees;
Result: 6
// Count the rows ignoring the ones with NULL value.
SELECT COUNT(department) FROM employees;
Result: 5
SELECT COUNT(DISTINCT department) FROM employees;
Result: 3
Format | MAX( [DISTINCT | ALL] x) |
Return the maximum.
Example:
SELECT MAX(age) FROM employees;
Result: 59
SELECT MAX(first_name) FROM employees;
Result: William
Format | MIN( [DISTINCT | ALL] x) |
Return the minimum.
Example:
SELECT MIN(age) FROM employees;
Result: 29
SELECT MIN(first_name) FROM employees;
Result: James
Format | SUM( [DISTINCT | ALL] n) |
Format | TOTAL( [DISTINCT | ALL] n) |
Return a sum of values.
Rows with n of NULL value are excluded from the calculation.
Example:
SELECT SUM(age) FROM employees;
Result: 205
SELECT TOTAL(age) FROM employees;
Result: 205.0
SELECT department, SUM(age) sum FROM employees GROUP BY department;
Result:
department sum
------------+-----
Development 102
Research 31
Sales 43
(NULL) 29
Format | GROUP_CONCAT( [DISTINCT | ALL] x [, separator] ) |
Return the character string in which the values of x are concatenated. Specify the separator to be concatenated as “separator”. When not specified, “, “ is used.
A TIMESTAMP value (including a value of the TIMESTAMP type with specified precision) is converted to the string representation of time in ‘YYYY-MM-DDThh:mm:ss.SSS(Z | ±hh:mm)’ format and connected. The number of digits in fractional parts of time is determined according to the precision of the TIMESTAMP type argument. For details, see the explanation of the corresponding precision in the sections on TIMESTAMP_MS function and other relevant sections. |
Example:
// Concatenate the name last_name with '/'
SELECT GROUP_CONCAT(last_name, '/') from employees;
Result: Smith/Jones/Brown/Taylor/Smith
// Concatenate the name "first_name" for each department "department"
SELECT department, GROUP_CONCAT(first_name) group_concat from employees GROUP BY(department);
Result:
department group_concat
-------------+--------------
Development William,James
Research Mary
Sales John,Richard
(NULL) Lisa
SELECT GROUP_CONCAT(age, ' + ') FROM employees;
Result: 43 + 59 + 31 + 29 + 43
Format | STDDEV_SAMP( [DISTINCT | ALL] x) |
Returns the sample standard deviation.
Example:
SELECT department, STDDEV_SAMP(enrollment_period) enrollment_period_stddev from employees GROUP BY department;
Result:
department enrollment_period_stddev
-------------+--------------------------
Development 9.121677477306465
Research (NULL)
Sales 6.010407640085654
(NULL) (NULL)
Format | STDDEV( [DISTINCT | ALL] x) |
Format | STDDEV0( [DISTINCT | ALL] x) |
Returns the sample standard deviation. Returns the sample standard deviation. STDDEV is an alias of the STDDEV_SAMP function.
Example:
SELECT department, STDDEV(enrollment_period) enrollment_period_stddev from employees GROUP BY department;
Result:
department enrollment_period_stddev
-------------+--------------------------
Development 9.121677477306465
Research (NULL)
Sales 6.010407640085654
(NULL) (NULL)
SELECT department, STDDEV0(enrollment_period) enrollment_period_stddev from employees GROUP BY department;
Result:
department enrollment_period_stddev
-------------+--------------------------
Development 9.121677477306465
Research (NULL)
Sales 6.010407640085654
(NULL) 0.0
SELECT STDDEV(enrollment_period) enrollment_period_stddev from employees WHERE age >= 55;
Result:
enrollment_period_stddev
--------------------------
(NULL)
SELECT STDDEV0(enrollment_period) enrollment_period_stddev from employees WHERE age >= 55;
Result:
enrollment_period_stddev
--------------------------
0.0
Format | STDDEV_POP( [DISTINCT | ALL] x) |
Returns the population standard deviation.
Example:
SELECT department, STDDEV_POP(enrollment_period) enrollment_period_stddev from employees GROUP BY department;
Result:
department enrollment_period_stddev
-------------+--------------------------
Development 6.450000000000002
Research (NULL)
Sales 4.25
(NULL) 0.0
Format | VAR_SAMP( [DISTINCT | ALL] x) |
Returns the sample variance.
Example:
SELECT department, VAR_SAMP(enrollment_period) enrollment_period_variance from employees GROUP BY department;
Result:
department enrollment_period_variance
-------------+----------------------------
Development 83.20500000000004
Research (NULL)
Sales 36.125
(NULL) (NULL)
Format | VARIANCE( [DISTINCT | ALL] x) |
Format | VARIANCE0( [DISTINCT | ALL] x) |
Returns the sample variance. Returns the sample variance. VARIANCE is an alias of the VAR_SAMP function.
Example:
SELECT department, VARIANCE(enrollment_period) enrollment_period_variance from employees GROUP BY department;
Result:
department enrollment_period_variance
-------------+----------------------------
Development 83.20500000000004
Research (NULL)
Sales 36.125
(NULL) (NULL)
SELECT department, VARIANCE0(enrollment_period) enrollment_period_variance from employees GROUP BY department;
Result:
department enrollment_period_variance
-------------+----------------------------
Development 83.20500000000004
Research (NULL)
Sales 36.125
(NULL) 0.0
SELECT VARIANCE(enrollment_period) enrollment_period_variance from employees WHERE age >= 55;
Result:
enrollment_period_variance
----------------------------
(NULL)
SELECT VARIANCE0(enrollment_period) enrollment_period_variance from employees WHERE age >= 55;
Result:
enrollment_period_variance
----------------------------
0.0
Format | VAR_POP( [DISTINCT | ALL] x) |
Returns the population variance.
Example:
SELECT department, VAR_POP(enrollment_period) enrollment_period_variance from employees GROUP BY department;
Result:
department enrollment_period_variance
-------------+----------------------------
Development 41.60250000000002
Research (NULL)
Sales 18.0625
(NULL) 0.0
Format | MEDIAN(n) |
Returns the median of n. If the number of rows to be calculated is even, returns the average value of the two rows near the center.
Example:
SELECT MEDIAN(age) FROM employees;
Result: 43
SELECT department, MEDIAN(age) mn FROM employees GROUP BY department ORDER BY mn DESC;
Result:
department mn
------------+-----
Development 51
Sales 43
Research 31
(NULL) 29
Format | PERCENTILE_CONT(percentile) WITHIN GROUP ( ORDER BY sort_key ) |
Return a value that corresponds to the percentile specified by the percentile which is based on a continuous distribution model, given the sort order specified by sort_key.
Example:
SELECT PERCENTILE_CONT(0.25) WITHIN GROUP( ORDER BY age ) FROM employees;
Result: 18
Format | ABS(n) |
Return the absolute value of n. For a positive number, the value as it is is returned and for a negative number, the value multiplied by -1 is returned.
Example:
SELECT first_name, ABS(age) abs FROM employees;
Result:
first_name abs
------------+-------
John 43
William 59
Richard (NULL)
Mary 31
Lisa 29
James 43
Format | ROUND(n [, m]) |
Round off. Returns the value of n rounded to m decimal places.
Example:
SELECT first_name, ROUND(enrollment_period, 0) round FROM employees;
Result:
first_name round
------------+-------
John 16.0
William 23.0
Richard 7.0
Mary (NULL)
Lisa 5.0
James 10.0
Format | RANDOM() |
Return a random number. A random number is an integer of the range from -263 to 263-1.
Example:
SELECT first_name, RANDOM() random FROM employees;
Result:
first_name random
------------+----------------------
John -3382931580741820003
William -7362300487836647182
Richard 8834368641333737477
Mary -8544493602797564288
Lisa -7727163797274657674
James 6751560427268247384
Format | MAX(x1, x2 [,…]) |
Return the greatest value among the values xN.
Format | MIN(x1, x2 [,…]) |
Return the smallest value among the values xN.
Example:
SELECT first_name, age, enrollment_period, MAX(age, enrollment_period) max FROM employees;
Result:
first_name age enrollment_period max
------------+-------+------------------+--------
John 43 15.5 43.0
William 59 23.2 59.0
Richard (NULL) 7.0 (NULL)
Mary 31 (NULL) (NULL)
Lisa 29 4.9 29.0
James 43 10.3 43.0
Format | LOG(n, m) |
Returns the logarithm of m with base n.
Example:
SELECT LOG(2, 8);
Result: 3.0
SELECT LOG(0.5, 2.0);
Result: -1.0
Format | SQRT(n) |
Returns the positive square root of n.
Example:
SELECT SQRT(4);
Result: 2.0
SELECT SQRT(16.0);
Result: 4.0
Format | TRUNC(n [,m]) |
In the case of m>=0, return the value of n, rounded down to the nearest m digits.
In the case of m< 0, return the value of n, rounded down to the nearest -m digits.
Example:
SELECT TRUNC(123.4567);
Result: 123.0
SELECT TRUNC(123.4567, 2);
Result: 123.45
SELECT TRUNC(123.4567, -1);
Result: 120.0
SELECT TRUNC(123.4567, -3);
Result: 0.0
SELECT TRUNC(1234567, -2);
Result: 1234500
Format | HEX_TO_DEC(str) |
Converts hexadecimal string str to decimal number type.
Example:
SELECT HEX_TO_DEC('FF');
Result: 255
SELECT HEX_TO_DEC('10');
Result: 16
Format | LENGTH(str) |
Return the length of the character string str.
Example:
SELECT last_name, LENGTH(last_name) length FROM employees;
Result:
last_name length
------------+----------------------
Smith 5
Jones 5
Brown 5
Taylor 6
(NULL) (NULL)
Smith 5
Format | LOWER(str) |
Convert all the alphabet of the character string str to lowercases.
Example:
SELECT last_name, LOWER(last_name) lower FROM employees;
Result:
last_name lower
------------+----------------------
Smith smith
Jones jones
Brown brown
Taylor taylor
(NULL) (NULL)
Smith smith
Format | UPPER(str) |
Convert all the alphabet of the character string str to uppercases.
Example:
SELECT last_name, UPPER(last_name) upper FROM employees;
Result:
last_name upper
------------+----------------------
Smith SMITH
Jones JONES
Brown BROWN
Taylor TAYLOR
(NULL) (NULL)
Smith SMITH
Format | SUBSTR(str, index [, length]) |
Cut out a part of a character string. from the character on the starting position, indicated by “index” up to the length specified by “length”.
Example:
SELECT SUBSTR('abcdefg', 3);
Result: cdefg
SELECT SUBSTR('abcdefg', 3, 2);
Result: cd
Format | REPLACE(str, search_str, replacement_str) |
Replace a character string. In the character string str, replace all the parts matching the character string search_str with replacement_str.
Example:
SELECT REPLACE('abcdefabc', 'abc', '123');
Result: 123def123
Format | INSTR(str, search_str [, offset] [, occurrence]) |
Search for character string search_str in the character string str, and return its starting position. Return 0, when not found. Return 0, when not found.
Example:
SELECT INSTR('abcdef', 'cd');
Result: 3
SELECT INSTR('abcdef', 'gh');
Result: 0
SELECT INSTR('abcabcabcde', 'ab', 2, 2);
Result: 7
SELECT INSTR('abcabcabcde', 'ab', -1, 2);
Result: 4
Format | LIKE(pattern_str, str [, escape_str]) |
Search the character string on the right. Return true, when the character string str matches the match pattern pattern_str. Return false, when no match was found. The following two wild cards are available for a match pattern.
Wild card | Meaning |
---|---|
_ | Any one character |
% | Any character with zero or more character strings |
Specify the escape character escape_str when searching for the character _ or % in str containing the wildcard character _ or %. If a escape character is specified before the wild card character, it will no longer be interpreted as a wild card.
Example:
SELECT last_name, LIKE('%mi%', last_name) like_name FROM employees;
Result:
last_name like_name
------------+----------------------
Smith true
Jones false
Brown false
Taylor false
(NULL) (NULL)
Smith true
SELECT LIKE('%C%E%', 'ABC%DEF');
Result: true
SELECT LIKE('%C@%E%', 'ABC%DEF', '@');
Result: false
SELECT LIKE('%C@%D%', 'ABC%DEF', '@');
Result: true
Format | GLOB(pattern_str, str) |
Search the character string on the right. Return true, when the character string str matches the match pattern pattern_str. Return false, when no match was found. The following wild cards are available for a match pattern.
Wild card | Meaning |
---|---|
? | Any one character |
* | Any character with zero or more character strings |
[abc] | Match any of the letters a, b or c |
[a-e] | Match any of the letters from a to e |
Example:
SELECT GLOB('*[BA]AB?D', 'AABCD');
Result: true
Format | TRIM(str [, trim_str]) |
Delete all the characters of character string trim_str from both ends of the character string str.
Example:
SELECT TRIM(' ABC ');
Result: ABC (no space at both ends)
SELECT TRIM('ABCAA', 'BA');
Result: C
Format | LTRIM(str [, trim_str]) |
Delete all the characters of character string trim_str from the left end of the character string str.
Example:
SELECT TRIM(' ABC ');
Result: ABC (no space at the left end)
SELECT TRIM('ABCAA', 'BA');
Result: BCAA
Format | RTRIM(str [, trim_str]) |
Delete all the characters of character string trim_str from the right end of the character string str.
Example:
SELECT RTRIM(' ABC ');
Result: ABC (no space at the right end)
SELECT RTRIM('ABCAA', 'A');
Result: ABC
Format | QUOTE(x) |
Returns a character string containing the value of x enclosed in single quotes.
A TIMESTAMP value (including a value of the TIMESTAMP type with specified precision) is converted to the string representation of time in ‘YYYY-MM-DDThh:mm:ss.SSS(Z | ±hh:mm)’ format and connected. It is not enclosed in single quotes. The number of digits in fractional parts of time is determined according to the precision of the TIMESTAMP type argument. For details, see the explanation of the corresponding precision in the sections on TIMESTAMP_MS function and other relevant sections. |
Example:
SELECT QUOTE(last_name) last_name, QUOTE(age) age FROM employees;
Result:
last_name age
------------+-------
'Smith' 43
'Jones' 59
'Brown' (NULL)
'Taylor' 31
(NULL) 29
'Smith' 43
SELECT QUOTE(RANDOMBLOB(4));
Result: X'A45EA28D'
// The value of column "value" is a character string "Today's news."
SELECT value, QUOTE(value) FROM testcontainer;
Result:
value QUOTE(value)
---------------+-------------------
Today's news 'Today''s news'
Format | UNICODE(str) |
Returns the UNICODE code point of the first character of the string str.
Example:
SELECT last_name, UNICODE(last_name) unicode FROM employees;
Result:
last_name unicode
------------+----------------------
Smith 83
Jones 74
Brown 66
Taylor 84
(NULL) (NULL)
Smith 83
Format | CHAR(x1 [, x2, … , xn]) |
Returns a concatenated character string of characters with Unicode code point value xn.
Example:
SELECT CHAR(83, 84, 85);
Result: STU
Format | PRINTF(format [, x1, x2, …, xn]) |
Return the converted character string according to the specified format “format”. A format equivalent to the printf function of the standard C libraries can be used. There are two other formats as below.
Format | Description |
---|---|
%q | A single quote in a character string is escaped to two single quotes ‘’. |
%Q | A single quote in a character string is escaped to two single quotes ‘’. Enclose both ends of the character string by single quotes. |
Example:
SELECT enrollment_period, PRINTF('%.2f', enrollment_period) printf FROM employees;
Result:
enrollment_period printf
------------------+-----------
15.5 15.50
23.2 23.20
7.0 7.00
(NULL) 0.00
4.9 4.90
10.3 10.30
| | | | — | ————————————————- | | Format | TRANSLATE(str, search_str, replacement_str) |
Replace a character string. Among the character string str, the characters matched the character string search_str is replaced by the characters of character string replacement_str in the same position as search_str. When replacement_str is shorter than search_str, thus having no characters to substitute in the part longer than replacement_str, the characters to be replaced will be deleted.
Example:
SELECT TRANSLATE('abcde', 'ace', '123');
Result:1b2d3
SELECT TRANSLATE('abcdeca', 'ace', '123');
Result: 1b2d321
SELECT TRANSLATE('abcde', 'ac', '123');
Result: 1b2de
SELECT TRANSLATE('abcde', 'ace', '12');
Result: 1b2d
SELECT TRANSLATE('abcde', 'AB', '123');
Result: abcde
SELECT TRANSLATE('abcde', 'abc', '');
Result: de
Format | NOW() |
Returns the current time value.
Example:
SELECT NOW();
Result: 2019-09-17T04:07:31.825Z
SELECT NOW();
Result: 2019-09-17T13:09:20.918+09:00
Format | TIMESTAMP(timestamp_string [, timezone]) |
Convert values of the string representation of time timestamp_string to millisecond-precision TIMESTAMP. This function is equivalent to TIMESTAMP_MS below. For details, see the section on TIMESTAMP_MS.
Format | TIMESTAMP_MS(timestamp_string [, timezone]) |
Convert values of the string representation of time timestamp_string to millisecond-precision TIMESTAMP(3).
Notation | Item | The range of value | ||
---|---|---|---|---|
YYYY | Year (A.D.) | 1970- | ||
MM | Month | 1 to 12 | ||
DD | Day | 1 to 31 | ||
hh | Time (24-hour notation) | 0 to 23 | ||
mm | Minute | 0 to 59 | ||
ss | Second | 0 to 59 | ||
SSS | Millisecond | 0 to 999 | ||
Z | Time zone | Z | ±hh:mm | ±hhmm |
For the timezone argument, specify the time zone (Z | ±hh:mm | ±hhmm), not required when time zone information is included in timestamp_string. An error is returned if the specified values are inconsistent. |
Example:
// Search for a row with the value of column date (TIMESTAMP type) newer than time '2018-12-01T10: 30: 00Z'
SELECT * FROM timeseries WHERE date > TIMESTAMP('2018-12-01T10:30:00Z');
Format | TIMESTAMP_US(timestamp_string [, timezone]) |
Convert values of the string representation of time timestamp_string to microsecond-precision TIMESTAMP(6).
notation | meaning | value range |
---|---|---|
YYYY | year | 1970 and onwards |
MM | month | 1 to 12 |
DD | day | 1 to 31 |
hh | hour (24-hour format) | 0 to 23 |
mm | minute | 0 to 59 |
ss | second | 0 to 59 |
SSSSSS | microsecond | 0 to 999999 |
Z | timezone | Z|±hh:mm|±hhmm |
For the argument timezone, specify the timezone (Z | ±hh:mm | ±hhmm). If timestamp_string contains timezone information, specification is not necessary. But if the timezone is specified and there is an inconsistency between timezone information and the specified timezone, an error will be returned. |
Format | TIMESTAMP_NS(timestamp_string [, timezone]) |
Convert values of the string representation of time timestamp_string to nanosecond-precision TIMESTAMP(9).
notation | meaning | value range |
---|---|---|
YYYY | year | 1970 and onwards |
MM | month | 1 to 12 |
DD | day | 1 to 31 |
hh | hour (24-hour format) | 0 to 23 |
mm | minute | 0 to 59 |
ss | second | 0 to 59 |
SSSSSSSSS | nanosecond | 0 to 99999999 |
Z | timezone | Z|±hh:mm|±hhmm |
For the argument timezone, specify the timezone (Z | ±hh:mm | ±hhmm). If timestamp_string contains timezone information, specification is not necessary. But if the timezone is specified and there is an inconsistency between timezone information and the specified timezone, an error will be returned. |
Format | TIMESTAMP_ADD(time_unit, timestamp, duration [, timezone]) |
The value obtained by adding the period “duration” (unit: time_umit) to time period “timestamp” is returned.
YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | MILLISECOND |
For the timezone argument, specify the time zone (Z | ±hh:mm | ±hhmm), |
Example:
Add ten days to time period '2018-12-01T11:22:33.444Z'.
SELECT TIMESTAMP_ADD(DAY, TIMESTAMP('2018-12-01T11:22:33.444Z'), 10);
Result: 2018-12-11T11:22:33.444Z
SELECT TIMESTAMP_ADD(MONTH, TIMESTAMP('2019-05-31T01:23:45.678Z'), 1);
Result: 2019-06-30T01:23:45.678Z
SELECT TIMESTAMP_ADD(MONTH, TIMESTAMP('2019-05-31T01:23:45.678Z'), 1, '-02:00');
Result: 2019-07-01T01:23:45.678Z
Format | TIMESTAMP_DIFF(time_unit, timestamp1, timestamp2 [, timezone]) |
Returns the difference of timestamp1 and timestamp2 (timestamp1-timestamp2) as a value expressed in the time unit “time_unit”. When a time difference is represented in time units, the decimal places are rounded off.
YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | MILLISECOND |
For the timezone argument, specify the time zone (Z | ±hh:mm | ±hhmm), |
Example:
// Time unit: Month
SELECT TIMESTAMP_DIFF(MONTH, TIMESTAMP('2018-12-11T10:30:15.555Z'), TIMESTAMP('2018-12-01T10:00:00.000Z'));
Result: 0
// Time unit: Day
SELECT TIMESTAMP_DIFF(DAY, TIMESTAMP('2018-12-11T10:30:15.555Z'), TIMESTAMP('2018-12-01T10:00:00.000Z'));
Result: 10
SELECT TIMESTAMP_DIFF(DAY, TIMESTAMP('2018-12-01T11:00:00.000Z'), TIMESTAMP('2018-12-11T10:30:15.555Z'));
Result:-9
// Time unit: Time point
SELECT TIMESTAMP_DIFF(HOUR, TIMESTAMP('2018-12-11T10:30:15.555Z'), TIMESTAMP('2018-12-01T10:00:00.000Z'));
Result: 240
// Time unit: Minute
SELECT TIMESTAMP_DIFF(MINUTE, TIMESTAMP('2018-12-11T10:30:15.555Z'), TIMESTAMP('2018-12-01T10:00:00.000Z'));
Result: 14430
// Here is an example where the result changes depending on the time zone.
SELECT TIMESTAMP_DIFF(MONTH, MAKE_TIMESTAMP(2019, 8, 1), MAKE_TIMESTAMP(2019, 6, 30), 'Z');
Result: 2
SELECT TIMESTAMP_DIFF(MONTH, MAKE_TIMESTAMP(2019, 8, 1), MAKE_TIMESTAMP(2019, 6, 30), '-01:00');
Result: 1
[memo]
Format | TO_TIMESTAMP_MS(milliseconds) |
Return the time point obtained by adding the value of argument “milliseconds” as millisecond, to the time point’1970-01-01T00:00:00.000Z’.
This function is an inverse conversion of TO_EPOCH_MS function.
Example:
SELECT TO_TIMESTAMP_MS(1609459199999);
Result: 2020-12-31T23:59:59.999Z
Format | TO_EPOCH_MS(timestamp) |
Return the lapsed time (in milliseconds) from the time ‘1970-01-01T00:00:00.000Z’ to the time “timestamp”.
This function is an inverse conversion of TO_EPOCH_MS function.
Example:
SELECT TO_EPOCH_MS(TIMESTAMP('2020-12-31T23:59:59.999Z'));
Result: 1609459199999
SELECT TO_EPOCH_MS(TIMESTAMP('2020-12-31T23:59:59.999+09:00'));
Result: 1609426799999
[memo]
Format | EXTRACT(time_field, timestamp [, timezone]) |
Retrieve the value of time field “time_field” from the time “timestamp”. The time will be the value of UTC.
YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | MILLISECOND | MICROSECOND | NANOSECOND | DAY_OF_WEEK | DAY_OF_YEAR |
For the timezone argument, specify the time zone (Z | ±hh:mm | ±hhmm). |
Example:
// Calculate the value of the year, the day, and the millisecond of time point '2018-12-01T10:30:02.392Z'.
// The value of the year
SELECT EXTRACT(YEAR, TIMESTAMP('2018-12-01T10:30:02.392Z'));
Result: 2018
SELECT EXTRACT(DAY, TIMESTAMP('2018-12-01T10:30:02.392Z'));
// The value of the day
Result: 1
// The value of the millisecond
SELECT EXTRACT(MILLISECOND, TIMESTAMP('2018-12-01T10:30:02.392Z'));
Result: 392
// Consider the time zone.
SELECT EXTRACT(HOUR, TIMESTAMP('2018-12-01T10:30:02.392Z'), '+09:00');
Result: 19
Format | STRFTIME(format, timestamp [, modifier,…]) |
Return a time converted to a string according to the specified format.
Format | Description | ||
---|---|---|---|
%Y | Extract the year in YYYY format. | ||
%m | Extract the month in MM format. | ||
%d | Extract the day in DD format. | ||
%H | Extract the time in hh format. | ||
%M | Extract the minute in mm format. | ||
%S | Extract the second in ss format. | ||
%3f | Extract the millisecond-precision fractional parts in SSS format. | ||
%6f | Extract the microsecond-precision fractional parts in SSSSSS format. | ||
%9f | Extract the nanosecond-precision fractional parts in SSSSSSSSS format. | ||
%z | Extract the time zone in ± hh:mm format. | ||
%w | Extracts the day of the week in D format (0 to 6): from Sunday, as 0, to Saturday, as 6. | ||
%W | Extracts the number of the week of the year in DD format (from 00 to 53). The first Monday is considered to be in the first week, and days before that are considered to be in the 0th week. | ||
%j | Extract the number of days from January first in DDD format (001 to 366). | ||
%c | Extract the time in the format YYYY-MM-DDThh:mm:ss[.SSS](Z | ±hh:mm | ±hhmm). |
%% | Output % as a character. |
For the timezone argument, specify the time zone (Z | ±hh:mm | ±hhmm), |
Example:
SELECT STRFTIME('%c', TIMESTAMP('2019-06-19T14:15:01.123Z'));
Result: 2019-06-19T14:15:01.123Z
SELECT STRFTIME('%H:%M:%S%z', TIMESTAMP('2019-06-19T14:15:01.123Z'), '+09:00');
Result: 23:15:01+09:00
SELECT STRFTIME('%W', TIMESTAMP('2019-01-19T14:15:01.123Z'));
Result: 02
Format | MAKE_TIMESTAMP(year, month, day [, timezone]) MAKE_TIMESTAMP(year, month, day, hour, min, sec [, timezone]) |
Generate and return a TIMESTAMP type value.
For the timezone argument, specify the time zone (Z | ±hh:mm | ±hhmm), |
Example:
SELECT MAKE_TIMESTAMP(2019, 9, 19);
Result: 2019-09-19T00:00:00.000Z
SELECT MAKE_TIMESTAMP(2019, 9, 19, 10, 30, 15.123, '+09:00');
Result: 2019-09-19T01:30:15.123Z
Format | TIMESTAMP_TRUNC(field, timestamp [, timezone]) |
Truncates the time information.
YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | MILLISECOND | MICROSECOND | NANOSECOND |
For the timezone argument, specify the time zone (Z | ±hh:mm | ±hhmm), |
Example:
SELECT TIMESTAMP_TRUNC(HOUR, MAKE_TIMESTAMP(2019, 9, 19, 10, 30, 15.123));
Result: 2019-09-19T10:00:00.000Z
SELECT TIMESTAMP_TRUNC(DAY, MAKE_TIMESTAMP(2019, 5, 15), '-01:00');
Result: 2019-05-14T01:00:00.000Z
Format | ROW_NUMBER() |
Assign a unique serial number to the resulting rows.
Example:
SELECT ROW_NUMBER() OVER(PARTITION BY department ORDER BY age) no, first_name, age, department FROM employees;
Result:
no first_name age department
----+------------+--------+-------------
1 James 43 Development
2 William 59 Development
1 Mary 31 Research
1 John 43 Sales
2 Richard (NULL) Sales
1 Lisa 29 (NULL)
Format | Format LAG( x [, offset [, default ] ] ) |
Returns x ahead of the current row by offset rows.
Example:
SELECT id, date, empId, amount, LAG(amount) OVER(PARTITION BY empID ORDER BY id) as lag_amount FROM travelexpenses;
Result:
id date empId amount lag_amount
-----+------------+-------+--------+------------
101 2020/02/01 0 200 (NULL)
104 2020/02/04 0 200 200
105 2020/02/05 0 150 200
102 2020/02/03 2 2500 (NULL)
103 2020/02/03 3 60 (NULL)
106 2020/02/06 3 80 60
Format | Format LEAD( x [, offset [, default ] ] ) |
Returns x behind the current row by offset rows.
Example:
SELECT id, date, empId, amount, LEAD(amount) OVER(PARTITION BY empID ORDER BY id) as lead_amount FROM travelexpenses;
Result:
id date empId amount lead_amount
-----+------------+-------+--------+-------------
101 2020/02/01 0 200 200
104 2020/02/04 0 200 150
105 2020/02/05 0 150 (NULL)
102 2020/02/03 2 2500 (NULL)
103 2020/02/03 3 60 80
106 2020/02/06 3 80 (NULL)
Format | COALESCE(x1, x2 [,…, xn]) |
Return the value of the first argument that is not NULL in the specified argument xn.
Specify the same type value for the argument xn. There are some different types that can be specified. Refer to CASE for the allowed combination of types.
Return NULL, when all argument values are NULL.
Example:
SELECT last_name, COALESCE(last_name, 'XXX') coalesce FROM employees;
Result:
last_name coalesce
------------+----------------------
Smith Smith
Jones Jones
Brown Brown
Taylor Taylor
(NULL) XXX
Smith Smith
SELECT age, COALESCE(age, -1) coalesce FROM employees;
Result:
age coalesce
--------+-----------
43 43
59 59
(NULL) -1
31 31
29 29
43 43
Format | IFNULL(x, y) |
Return the value of the first argument that is not NULL among the specified arguments x and y. The IFNULL function is equivalent to the COALESCE function with two arguments.
Example:
SELECT last_name, IFNULL(last_name, 'XXX') ifnull FROM employees;
Result:
last_name ifnull
------------+----------------------
Smith Smith
Jones Jones
Brown Brown
Taylor Taylor
(NULL) XXX
Smith Smith
SELECT age, IFNULL(age, -1) ifnull FROM employees;
Result:
age coalesce
--------+-----------
43 43
59 59
(NULL) -1
31 31
29 29
43 43
Format | NULLIF(x, y) |
Return NULL when two arguments are the same, return the first argument when the arguments are different.
Example:
// Execute NULLIF with the value of value1 and value2.
SELECT value1, value2, NULLIF(value1, value2) nullif FROM container_sample;
Result:
value1 value2 nullif
--------+--------+--------
10 10 (NULL)
5 0 5
(NULL) 4 (NULL)
3 (NULL) 3
(NULL) (NULL) (NULL)
// Convert 0 to NULL to prevent division by zero errors in the calculation of value1 / value2
SELECT value1, value2, value1/NULLIF(value2, 0) division FROM container_sample;
Result:
value1 value2 division
--------+--------+--------
10 10 1
5 0 (NULL)
(NULL) 4 (NULL)
3 (NULL) (NULL)
(NULL) (NULL) (NULL)
Format | RANDOMBLOB(size) |
Return a BLOB type value (random number).
Example:
// Generate a 10-byte blob value (random number)
SELECT HEX(RANDOMBLOB(10));
Result: 7C8C893C8087F07883AF
Format | ZEROBLOB(size) |
Return a BLOB type value (0x00).
Example:
// Generate a 10-byte blob value (0x00).
SELECT HEX(ZEROBLOB(10));
Result: 00000000000000000000
Format | HEX(x) |
Convert a BLOB type value to a hexadecimal type. Interpret the argument x as a BLOB type value, and return the character string (uppercase) converted into the hexadecimal.
Example:
SELECT HEX(RANDOMBLOB(2));
Result: E18D
SELECT first_name, HEX(first_name) hex FROM employees;
Result:
first_name hex
------------+----------------------
John 4A6F686E
William 57696C6C69616D
Richard 52696368617264
Mary 4D617279
Lisa 4C697361
James 4A616D6573
Format | TYPEOF(x) |
Return the character string indicating the data type of the value of x.
The correspondence between the data type and the string returned by the TYPEOF function is shown below.
Data types | Character string which TYPEOF function returns |
---|---|
BOOL | BOOL |
STRING | STRING |
BYTE | BYTE |
SHORT | SHORT |
INTEGER | INTEGER |
LONG | LONG |
FLOAT | FLOAT |
DOUBLE | DOUBLE |
TIMESTAMP(3) | TIMESTAMP |
TIMESTAMP(6) | TIMESTAMP(6) |
TIMESTAMP(9) | TIMESTAMP(9) |
GEOMETRY | NULL |
BLOB | BLOB |
ARRAY | NULL |
Example:
SELECT TYPEOF(ABS(-10)) abs, TYPEOF(RANDOMBLOB(10)) randomblob,
TYPEOF(TIMESTAMP('2018-12-01T10:30:02.392Z')) timestamp;
Result:
abs randomblob timestamp
------+------------+-----------
LONG BLOB TIMESTAMP
Format | CAST(x AS data_type) |
Convert the value x into the data type “data_type”.
Specify the following values for argument “data_type” according to the converted data type.
Converted data type | Value for data_type |
---|---|
BOOL | BOOL |
STRING | STRING |
BYTE | BYTE |
SHORT | SHORT |
INTEGER | INTEGER |
LONG | LONG |
FLOAT | FLOAT |
DOUBLE | DOUBLE |
TIMESTAMP(3) | TIMESTAMP or TIMESTAMP(3) |
TIMESTAMP(6) | TIMESTAMP(6) |
TIMESTAMP(9) | TIMESTAMP(9) |
BLOB | BLOB |
Format | CAST(x AS STRING) |
Convert the argument x to a character string type.
The data types of the value which can be specified for x, and the converted values are as follows.
Data type of x | Value converted to character string type | |
---|---|---|
BOOL | ‘true’ if true, ‘false’ if false | |
STRING | Original value | |
BYTE SHORT INTEGER LONG FLOAT DOUBLE |
Value converted from a number to a character string | |
TIMESTAMP(3) | String notation of millisecond-precision time ‘YYYY-MM-DDThh:mm:ss.SSS(Z | ±hh:mm)’ The time zone setting at the time of connection is used. |
TIMESTAMP(6) | String notation of microsecond-precision time ‘YYYY-MM-DDThh:mm:ss.SSSSSS(Z | ±hh:mm)’ The time zone setting at the time of connection is used. |
TIMESTAMP(9) | String notation of nanosecond-precision time ‘YYYY-MM-DDThh:mm:ss.SSSSSSSSS(Z | ±hh:mm)’ The time zone setting at the time of connection is used. |
BLOB | A character string equivalent to the converted character string using HEX function |
Format | CAST(x AS BYTE|SHORT|INTEGER|LONG|FLOAT|DOUBLE) |
Convert the argument x into a numeric type.
The data types of the value which can be specified for x, and the converted values are as follows.
Data type of x | Value converted to numeric type |
---|---|
BOOL | 1 if true, 0 if false |
STRING | The value converted from the character string to numerical value |
BYTE SHORT INTEGER LONG FLOAT DOUBLE |
The numerical value converted to the specified numeric type |
// An error occurs if exceeding BYTE type range (-128 to 127)
SELECT CAST(128 AS BYTE);
Result: error
// An error occurs if exceeding INTEGER type range (-2147483648-2147483647).
SELECT CAST('2147483648' AS INTEGER);
Result: error
SELECT CAST(10.5 AS INTEGER);
Result: 10
SELECT CAST('abc' AS INTEGER);
Result: error
SELECT CAST('-1.09E+10' AS DOUBLE);
Result: -1.09E10
Format | CAST(x AS TIMESTAMP) |
Convert the argument x to a time type. If the time zone is specified at the time of connection, that value is used for offset calculation. TIMESTAMP with specified precision can also be specified.
The data types of the value which can be specified for x, and the converted values are as follows.
Data type of x | Value converted to time type |
---|---|
STRING (string notation of millisecond-precision time ‘YYYY-MM-DDThh:mm:ss.SSS(Z|±hh:mm)) | Equivalent to the value converted using the TIMESTAMP_MS function (can be converted regardless of which precision is specified) |
STRING (string notation of microsecond-precision time ‘YYYY-MM-DDThh:mm:ss.SSSSSS(Z|±hh:mm)) | Equivalent to the value converted using the TIMESTAMP_US function (Conversion is only possible when microsecond or nanosecond precision is specified.)) |
STRING (string notation of nanosecond-precision time ‘YYYY-MM-DDThh:mm:ss.SSSSSSSSS(Z|±hh:mm)) | Equivalent to the value converted using the TIMESTAMP_NS function (Conversion is only possible when nanosecond precision is specified.)) |
SELECT CAST('2018-12-01T10:30:00Z' AS TIMESTAMP);
Result: 2018-12-01T10:30:00.000Z
SELECT CAST('2018-12-01T10:30:00+09:00' AS TIMESTAMP);
Result: 2018-12-01T01:30:00.000Z
Format | CAST(x AS BOOL) |
Convert the argument x to a BOOL type.
The data types of the value which can be specified for x, and the converted values are as follows.
Data type of x | Value converted to time type |
---|---|
STRING | True if ‘true’, false if ‘false’ (case insensitive) |
BYTE SHORT INTEGER LONG |
False if 0, otherwise true |
Format | CAST(x AS BLOB) |
Convert the argument x to a BLOB type.
The data types of the value which can be specified for x, and the converted values are as follows.
Data type of x | Value converted to BLOB type |
---|---|
STRING | The value converted from character string as hexadecimal data to BLOB type |
Format | CASE WHEN condition1 THEN result1 [WHEN condition2 THEN result2] … [ELSE resultElse] END |
When the conditional expression conditionN is true, the value of corresponding resultN is returned. When all the conditional expressions are false or NULL, and if ELSE is specified, the value of resultElse will be returned. When ELSE is not specified, NULL is returned.
Format | CASE x WHEN value1 THEN result1 [WHEN value2 THEN result2] … [ELSE resultElse] END |
When the value of x is valueN, the value of corresponding resultN is returned. When the value of x is not equal to all values, and if ELSE is specified, the value of resultElse will be returned. When ELSE is not specified, NULL is returned.
Specify the same type value for resultN. There are some different types that can be specified.
If the arguments are of different types, only the combination of the following types can be calculated. Any other combinations will result in an error.
Type of argument | Type of argument | Type of argument when calculating the two arguments |
---|---|---|
SHORT | BYTE | LONG |
INTEGER | BYTE, SHORT | LONG |
LONG | BYTE, SHORT, INTEGER | LONG |
FLOAT | BYTE, SHORT, INTEGER, LONG | DOUBLE |
DOUBLE | BYTE, SHORT, INTEGER, LONG, FLOAT | DOUBLE |
Example:
// Display the employee's age (30's, 40's, 50's, other than these)
SELECT id, first_name, age,
CASE
WHEN age > 50 THEN '50s'
WHEN age > 40 THEN '40s'
WHEN age > 30 THEN '30s'
ELSE 'other'
END AS period
FROM employees;
Result:
id first_name age period
----+------------+-------+--------
0 John 43 40s
1 William 59 50s
2 Richard (NULL) other
3 Mary 31 30s
4 Lisa 29 other
5 James 43 40s
// Display a location according to their departments.
SELECT id, first_name, department,
CASE department
WHEN 'Sales' THEN 'Tokyo'
WHEN 'Development' THEN 'Osaka'
ELSE 'Nagoya'
END AS location
FROM employees;
Result:
id first_name department location
----+------------+-------------+---------
0 John Sales Tokyo
1 William Development Osaka
2 Richard Sales Tokyo
3 Mary Research Nagoya
4 Lisa (NULL) Nagoya
5 James Development Osaka
Subqueries can be specified in various parts of an SQL statemnt other than FROM and WHERE clauses. Some operation types for subqueries are also provided, which are explained in this section.
Return whether the specified value is included in the sub query execution result.
Syntax
Expression 1 [NOT] IN ( sub_query ) |
Specifications
Example:
// Display the information of the employee who belongs to the department of id=1 in the departments table from the employees table.
SELECT * FROM employees
WHERE department IN(
SELECT department FROM departments
WHERE id = 1
);
Result:
id first_name last_name age department enrollment_period
----+------------+-----------+-------+-------------+-------------------
1 William Jones 59 Development 23.2
5 James Smith 43 Development 10.3
Return whether the execution result of the sub query exists.
Syntax
[NOT] EXISTS( sub_query ) |
Specifications
Check whether the execution result of the sub query exists. Return true if the number of execution result is 1 or more, false if it is 0.
The result is of a BOOL type.
Example:
// Display the information of the employee who belongs to the department of id=1 in the departments table from the employees table.
SELECT * FROM employees
WHERE EXISTS(
SELECT * FROM departments
WHERE employees.department=departments.department AND departments.id=1
);
結果:
id first_name last_name age department enrollment_period
----+------------+-----------+-------+-------------+-------------------
1 William Jones 59 Development 23.2
5 James Smith 43 Development 10.3
Subquery which returns one result, which can be used for the result of a SELECT statement or for an expression.
Example:
SELECT id, first_name,
(SELECT department FROM departments WHERE department_id=employees.department_id)
FROM employees;
Result:
id first_name department
---+-----------+-------------
0 John Sales
1 William Development
2 Richard Sales
3 Mary (NULL)
4 Lisa Marketing
5 James Development
A prepared statement can describe a placeholder in SQL statements. A placeholder indicates the position of the parameter to be substituted when the statement is executed. The parameter number starts from 1.
The placeholder can use several forms for compatibility with other databases. However, the parameter number will be the already assigned parameter number + 1, regardless of which format is specified.
Format | Description | Example of description |
---|---|---|
? | Format of a standard placeholder | ? |
?NNN | NNN indicates a number. | ?56 |
:AAAA | AAAA indicates a character string. | :name |
@AAAA | AAAA indicates a character string. | @name |
The placeholder must not start with $.
Example:
String sql = "SELECT * FROM users WHERE id > ? AND id != :exclude_id;";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setInt(1, 100); // 1: ?
pstmt.setInt(2, 253); // 2: :exclude_id
ResultSet rs = pstmt.executeQuery();
Comments can be written in a SQL command. Format: Description at the back of – (2 hyphens) or enclose with /* */. A new line needs to be returned at the end of the comment.
SELECT * -- comment
FROM employees;
SELECT *
/*
comment
*/
FROM employees;
In GridDB, specifying the hints indicating the execution plan in the query makes it possible to control the execution plan without changing the SQL statement.
See GridDB SQL Tuning Guide to tune with hint clauses.
In the following cases, a syntax error occurs.
In the following case, a table specification error occurs:
[Memo]
The metatables are tables that are used for checking metadata of data management in GridDB.
[Memo]
[Points to note]
Table information can be obtained.
Table name
#tables
Schema
Column name | Item | Type |
---|---|---|
DATABASE_NAME | Database name | STRING |
TABLE_NAME | Table name | STRING |
TABLE_OPTIONAL_TYPE | Table type COLLECTION / TIMESERIES |
STRING |
DATA_AFFINITY | Data affinity | STRING |
EXPIRATION_TIME | Expiry release elapsed time | INTEGER |
EXPIRATION_TIME_UNIT | Expiry release elapsed time unit | STRING |
EXPIRATION_DIVISION_COUNT | Expiry release division count | INTEGER |
PARTITION_TYPE | Partitioning type | STRING |
PARTITION_COLUMN | Partitioning key | STRING |
PARTITION_INTERVAL_VALUE | Interval value (For interval or interval hash) | STRING |
PARTITION_INTERVAL_UNIT | Interval unit (For interval of interval hash) | STRING |
PARTITION_DIVISION_COUNT | Division count (For hash) | INTEGER |
SUBPARTITION_TYPE | Partitioning type (“Hash” for interval hash) |
STRING |
SUBPARTITION_COLUMN | Partitioning key (for interval hash) |
STRING |
SUBPARTITION_INTERVAL_VALUE | Interval value | STRING |
SUBPARTITION_INTERVAL_UNIT | Interval unit | STRING |
SUBPARTITION_DIVISION_COUNT | Division count (For interval hash) |
INTEGER |
EXPIRATION_TYPE | Expiration type PARTITION |
STRING |
Index information can be obtained.
Table name
#index_info
Schema
Column name | Item | Type |
---|---|---|
DATABASE_NAME | Database name | STRING |
TABLE_NAME | Table name | STRING |
INDEX_NAME | Index name | STRING |
INDEX_TYPE | Index type TREE / SPATIAL |
STRING |
ORDINAL_POSITION | Column order in index (sequential number from 1) | SHORT |
COLUMN_NAME | Column name | STRING |
Data about partitioned tables can be obtained from this metatable.
Table name
#table_partitions
Schema
Column name | Item | Type |
---|---|---|
DATABASE_NAME | Database name | STRING |
TABLE_NAME | Partitioned table name | STRING |
PARTITION_BOUNDARY_VALUE | The lower limit value of each data partition | STRING |
CLUSTER_PARTITION_INDEX | Cluster partition number | INTEGER |
CLUSTER_NODE_ADDRESS | Node address:port number | STRING |
WORKER_INDEX | Processing thread number | INTEGER |
Specifications
Examples
Check the number of data partitions
SELECT COUNT(*) FROM "#table_partitions" WHERE TABLE_NAME='myIntervalPartition';
COUNT(*)
-----------------------------------
8703
Check the lower limit value of each data partition
SELECT PARTITION_BOUNDARY_VALUE FROM "#table_partitions" WHERE TABLE_NAME='myIntervalPartition'
ORDER BY PARTITION_BOUNDARY_VALUE;
PARTITION_BOUNDARY_VALUE
-----------------------------------
2016-10-30T10:00:00Z
2017-01-29T10:00:00Z
:
Check the lower limit value of each data partitions on the interval partitioned table “myIntervalPartition2” (partitioning key type: INTEGER, interval value: 20000)
SELECT CAST(PARTITION_BOUNDARY_VALUE AS INTEGER) V FROM "#table_partitions"
WHERE TABLE_NAME='myIntervalPartition2' ORDER BY V;
PARTITION_BOUNDARY_VALUE
-----------------------------------
-5000
15000
35000
55000
:
View information can be obtained.
Table name
#views
Schema
Column name | Item | Type |
---|---|---|
DATABASE_NAME | Database name | STRING |
VIEW_NAME | View name | STRING |
VIEW_DEFINITION | View defining character string | STRING |
Statistics about SQLs (queries or jobs) that are running can be obtained.
Table name
#sqls
Schema
Column name | Item | Type |
---|---|---|
DATABASE_NAME | Database name | STRING |
NODE_ADDRESS | address of the node being processed (system) | STRING |
NODE_PORT | The port of the node being processed (system) | INTEGER |
START_TIME | Processing start time | TIMESTAMP |
APPLICATION_NAME | Application name | STRING |
SQL | Query character string | STRING |
QUERY_ID | Query ID | STRING |
JOB_ID | Job ID | STRING |
USER_NAME | User name | STRING |
[Memo]
Statistics about events that are running can be obtained.
Table name
#events
Schema
Column name | Item | Type |
---|---|---|
NODE_ADDRESS | address of the node being processed (system) | STRING |
NODE_PORT | The port of the node being processed (system) | INTEGER |
START_TIME | Processing start time | TIMESTAMP |
APPLICATION_NAME | Application name | STRING |
SERVICE_TYPE | Service type (SQL/TRANSACTION/CHECKPOINT/SYNC) | STRING |
EVENT_TYPE | Event types (PUT/CP_START/SYNC_START etc.) | STRING |
WORKER_INDEX | Processing thread number | INTEGER |
CLUSTER_PARTITION_INDEX | Cluster partition number | INTEGER |
DATABASE_ID | Database ID | LONG |
Statistics about the connected connection can be obtained.
Table name
#sockets
Schema
Column name | Item | Type |
---|---|---|
SERVICE_TYPE | Service type (SQL/TRANSACTION) | STRING |
SOCKET_TYPE | Socket type | STRING |
NODE_ADDRESS | Connection source node address (viewed from a node) | STRING |
NODE_PORT | Connection source node port (viewed from a node) | INTEGER |
REMOTE_ADDRESS | Connection destination node address (viewed from a node) | STRING |
REMOTE_PORT | Connection destination node port (viewed from a node) | INTEGER |
APPLICATION_NAME | Application name | STRING |
CREATION_TIME | Connection time | TIMESTAMP |
DISPATCHING_EVENT_COUNT | Total number of times to start request for event handling | LONG |
SENDING_EVENT_COUNT | Total number of times to start event transmission | LONG |
DATABASE_NAME | Database name | LONG |
The following three are available for SOCKET_TYPE (socket type) above:
Value | Description |
---|---|
SERVER | TCP connection between servers |
CLIENT | TCP connection with a client |
MULTICAST | Multicasting socket |
NULL | In case currently unidentified during the cases such as connection attempt |
CREATION_TIME (connection time) in the table above is defined as follows according to each case:
Examples
Only in case of TCP connection with a client (socket type: CLIENT), it can be determined whether the connection is waiting for execution.
Specifically, if DISPATCHING_EVENT_COUNT is larger than SENDING_EVENT_COUNT, it can be determine that the possibility is relatively high that the time waiting for execution existed.
SELECT CREATION_TIME, NODE_ADDRESS, NODE_PORT, APPLICATION_NAME FROM "#sockets"
WHERE SOCKET_TYPE='CLIENT' AND DISPATCHING_EVENT_COUNT > SENDING_EVENT_COUNT;
CREATION_TIME NODE_ADDRESS NODE_PORT APPLICATION_NAME
--------------------------------------------------------------------
2019-03-27T11:30:57.147Z 192.168.56.71 20001 myapp
2019-03-27T11:36:37.352Z 192.168.56.71 20001 myapp
:
A list of database names and the corresponding database IDs can be obtained.
Table name
#databases
Schema
Column name | Item | Type | |
---|---|---|---|
DATABASE_NAME | Database name | STRING | |
DATABASE_ID | Database ID | INTEGER | INTEGER |
## Database statistics
Statistics aggregated for each database can be obtained.
Table name
#database_stats
Schema
Column name | Item | Type |
---|---|---|
DATABASE_ID | Database ID | LONG |
NODE_ADDRESS | Node address | STRING |
NODE_PORT | Node port number | INTEGER |
TRANSACTION_CONNECTION_COUNT | Number of transaction connections | LONG |
TRANSACTION_REQUEST_COUNT | Number of transaction requests | LONG |
SQL_CONNECTION_COUNT | Number of SQL connections | LONG |
SQL_REQUEST_COUNT | Number of SQL requests | LONG |
STORE_BLOCK_SIZE | Size of data files used | LONG |
STORE_MEMORY_SIZE | Size of database buffers used | LONG |
STORE_SWAP_READ_SIZE | Size of data files swapped and read | LONG |
STORE_SWAP_WRITE_SIZE | Size of data files swapped and written | LONG |
SQL_WORK_MEMORY_SIZE | Size of an SQL work memory buffer | LONG |
SQL_STORE_USE_SIZE | Size of a buffer used for storing intermediate SQL results | LONG |
SQL_STORE_SWAP_READ_SIZE | Size of swap files for intermediate SQL results swapped and read | LONG |
SQL_STORE_SWAP_WRITE_SIZE | Size of swap files for intermediate SQL results swapped and written | LONG |
SQL_TASK_COUNT | Total number of running SQL tasks | LONG |
SQL_PENDING_JOB_COUNT | Number of SQL jobs that are stopped because sending is suppressed | LONG |
SQL_SEND_MESSAGE_SIZE | SQL sent message size | LONG |
[Memo]
The following terms are defined as keywords in the SQL of GridDB.
ABORT ACTION AFTER ALL ANALYZE AND AS ASC BEGIN BETWEEN BY CASE CAST COLLATE COLUMN COMMIT CONFLICT CREATE CROSS DATABASE DAY DELETE DESC DISTINCT DROP ELSE END ESCAPE EXCEPT EXCLUSIVE EXISTS EXPLAIN EXTRACT FALSE FOR FROM GLOB GRANT GROUP HASH HAVING HOUR IDENTIFIED IF IN INDEX INITIALLY INNER INSERT INSTEAD INTERSECT INTO IS ISNULL JOIN KEY LEFT LIKE LIMIT MATCH MILLISECOND MINUTE MONTH NATURAL NO NOT NOTNULL NULL OF OFFSET ON OR ORDER OUTER PARTITION PARTITIONS PASSWORD PLAN PRAGMA PRIMARY QUERY RAISE REGEXP RELEASE REPLACE RESTRICT REVOKE RIGHT ROLLBACK ROW SECOND SELECT SET TABLE THEN TIMESTAMPADD TIMESTAMPDIFF TO TRANSACTION TRUE UNION UPDATE USER USING VALUES VIEW VIRTUAL WHEN WHERE WITHOUT XOR YEAR
Copyright (c) 2017 TOSHIBA Digital Solutions Corporation