docs-en

GridDB SQL Reference

Table of Contents

Introduction

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.

SQL description format

This chapter shows the descriptive format of the SQL that can be used in the NewSQL interface.

Usable operations

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.

Data types

Data types used in data storage

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.

How to specify TIMESTAMP precision

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]

Expression that can be specified as a column data type when creating a table

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]

Data type when accessing a container as a table and the treatment of the values

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]

Treatment of the data type not supported by SQL

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.

User and database

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.

Naming rules

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]

SQL commands supported by GridDB

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.

Data definition language (DDL)

CREATE DATABASE

Create a database.

Syntax

 
CREATE DATABASE database_name;

Specifications

CREATE TABLE

Creating a table

Create a table.

Syntax

Specifications

Examples

Creating a partitioned table

Creating a partitioned table

See GridDB Features Reference for details of each partitioning function.

(1) Creating a hash partitioned table

Syntax

Specifications

Option specifications

**Data affinity**

Examples

(2) Creating an interval partitioned table

Syntax

Specifications

Option specifications

**Data affinity**

**Expiry release**

[memo]

**Data partition placement*

[Memo]

Examples

(3) Creating an interval hash partitioned table

Syntax

Specifications

Option specifications

**Data affinity**

**Expiry release**

Examples

CREATE INDEX

Create an index.

Syntax

 
CREATE INDEX [IF NOT EXISTS] index_name ON table_name ( column_name_to_be_indexed );

Specifications

CREATE VIEW

Create a view.

Syntax

 
CREATE [FORCE] VIEW view_name AS SELECT statement;

Specifications

CREATE USER

Create a general user.

Syntax

 
CREATE USER user_name IDENTIFIED BY ‘password_string’ ;

Specifications

CREATE ROLE

Create a role required for LDAP authentication.

Syntax

 
CREATE ROLE role name ;

Specifications

DROP DATABASE

Delete a database.

Syntax

 
DROP DATABASE database_name;

Specifications

DROP TABLE

Delete a table.

Syntax

 
DROP TABLE [IF EXISTS] table_name;

Specifications

DROP INDEX

Delete the specified index.

Syntax

 
DROP INDEX [IF EXISTS] index_name ON table_name;

Specifications

DROP VIEW

Delete a view.

Syntax

 
DROP VIEW [IF EXISTS] view name ;

Specifications

DROP USER

Delete a general user.

Syntax

 
DROP USER user_name;

Specifications

DROP ROLE

Delete a role required for LDAP authentication.

Syntax

 
DROP ROLE role name ;

Specifications

ALTER TABLE

Change the structure of a table.

Adding columns to 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

Deleting data partitions

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

Interval hash partitioned table

RENAME COLUMN

Change an existing specified column.

Syntax

 
ALTER TABLE table name RENAME COLUMN column name before renaming TO column name after renaming;

Specifications

Examples

Data control language (DCL)

GRANT

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

Revoke database access rights from a general user or a role.

Syntax

 
REVOKE {SELECT|ALL} ON database_name FROM {user_name|role name};

Specifications

SET PASSWORD

Change the password of a general user.

Syntax

 
SET PASSWORD [FOR user_name ] = ‘password_string’;

Specifications

Data management language (DML)

SELECT

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

INSERT

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

Delete rows from a table.

Syntax

 
DELETE FROM table_name [ WHERE clause ];

UPDATE

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

Clauses

FROM

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
   :

GROUP BY

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

HAVING

Perform filtering using the search condition on data grouped by the GROUP BY clause. GROUP BY clause cannot be omitted.

Syntax

 
HAVING search_conditions

ORDER BY

Sort search results.

 
ORDER BY column_name_1 [{ASC|DESC}] [, column_name_2 [{ASC|DESC}] …]

WHERE

Apply a search condition on the result of the preceding FROM clause.

Syntax

 
WHERE search_conditions

Specifications

LIMIT/OFFSET

Extract the specified number of data from the specified location.

Syntax

 
LIMIT value_1 [OFFSET value_2 ]

Specifications

JOIN

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

UNION/INTERSECT/EXCEPT

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).

OVER

Split and sort query results. Use with a WINDOW function.

Syntax

 
Function OVER ([PARTITION BY expression 1] [ORDER BY expression 2])

Specifications

GROUP BY RANGE

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:

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

Operator

This section explains the operators used in SQL statements.

List of Operators

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.

LIKE

Search the character string on the right.

Syntax

 
str [NOT] LIKE pattern_str [ESCAPE escape_str ]

Specifications

GLOB

Syntax

Search the character string on the right.

 
str GLOB pattern_str

Specifications

BETWEEN

Extract values of the specified range.

Syntax

 
expression_1 [NOT] BETWEEN expression_2 AND expression_3

Specifications

IN

Return whether the specified value is included in the set of values.

Syntax

 
expression_1 [NOT] IN ( expression_2 [, expression_3 …] )

Specifications

Functions

This section explains the functions used in SQL statements.

List of Functions

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 Returns the sample standard deviation
  STDDEV Returns the sample standard deviation
  STDDEV0 Returns the sample standard deviation
  STDDEV_POP Returns the population standard deviation
  VAR_SAMP Returns the sample variance
  VARIANCE Returns the sample variance
  VARIANCE0 Returns the sample variance
  VAR_POP Returns the population variance
  MEDIAN Returns the median
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]

Aggregate functions

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]

AVG

   
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

COUNT

   
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

MAX

   
Format MAX( [DISTINCT | ALL] x)

Return the maximum.

Example:

SELECT MAX(age) FROM employees;
Result: 59

SELECT MAX(first_name) FROM employees;
Result: William

MIN

   
Format MIN( [DISTINCT | ALL] x)

Return the minimum.

Example:

SELECT MIN(age) FROM employees;
Result: 29

SELECT MIN(first_name) FROM employees;
Result: James

SUM/TOTAL

   
Format SUM( [DISTINCT | ALL] n)
Format TOTAL( [DISTINCT | ALL] n)

Return a sum of values.

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

GROUP_CONCAT

   
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.

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

STDDEV_SAMP

   
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)

STDDEV/STDDEV0

   
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

STDDEV_POP

   
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

VAR_SAMP

   
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)

VARIANCE/VARIANCE0

   
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

VAR_POP

   
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

MEDIAN

   
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

PERCENTILE_CONT

   
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.



### Mathematical functions

#### ABS

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

- Specify a numeric value as the argument n.
- Return NULL, when the result value is NULL.
- Cause an overflow error, when the value is an integer of -2<sup>63</sup>.
- The type of the result is a LONG type when n includes only integers, a DOUBLE type when n includes a floating point number.

Example:
```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

ROUND

   
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

RANDOM

   
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

MAX/MIN

   
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

LOG

   
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

SQRT

   
Format SQRT(n)

Returns the positive square root of n.

Example:

SELECT SQRT(4);
Result: 2.0

SELECT SQRT(16.0);
Result: 4.0

TRUNC

   
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

HEX_TO_DEC

   
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

Character functions

LENGTH

   
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

LOWER

   
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

UPPER

   
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

SUBSTR

   
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

REPLACE

   
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

INSTR

   
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

LIKE

   
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

GLOB

   
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

TRIM

   
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

LTRIM

   
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

RTRIM

   
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

QUOTE

   
Format QUOTE(x)

Returns a character string containing the value of x enclosed in single quotes.

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'

UNICODE

   
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

CHAR

   
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

PRINTF

   
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

TRANSLATE

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

Time functions

NOW

   
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

TIMESTAMP

   
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.

TIMESTAMP_MS

   
Format TIMESTAMP_MS(timestamp_string [, timezone])

Convert values of the string representation of time timestamp_string to millisecond-precision TIMESTAMP(3).

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

TIMESTAMP_US

   
Format TIMESTAMP_US(timestamp_string [, timezone])

Convert values of the string representation of time timestamp_string to microsecond-precision TIMESTAMP(6).

TIMESTAMP_NS

   
Format TIMESTAMP_NS(timestamp_string [, timezone])

Convert values of the string representation of time timestamp_string to nanosecond-precision TIMESTAMP(9).

TIMESTAMP_ADD

   
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.

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

TIMESTAMP_DIFF

   
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.

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]

TO_TIMESTAMP_MS

   
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

TO_EPOCH_MS

   
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]

EXTRACT

   
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.

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

STRFTIME

   
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.    

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

MAKE_TIMESTAMP

   
Format MAKE_TIMESTAMP(year, month, day [, timezone])
MAKE_TIMESTAMP(year, month, day, hour, min, sec [, timezone])

Generate and return a TIMESTAMP type value.

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

TIMESTAMP_TRUNC

   
Format TIMESTAMP_TRUNC(field, timestamp [, timezone])

Truncates the time information.

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

WINDOW function

ROW_NUMBER

   
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)

LAG

   
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

LEAD

   
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)

Other functions

COALESCE

   
Format COALESCE(x1, x2 [,…, xn])

Return the value of the first argument that is not NULL in the specified argument xn.

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

IFNULL

   
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

NULLIF

   
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)

RANDOMBLOB

   
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

ZEROBLOB

   
Format ZEROBLOB(size)

Return a BLOB type value (0x00).

Example:

// Generate a 10-byte blob value (0x00).
SELECT HEX(ZEROBLOB(10));
Result: 00000000000000000000

HEX

   
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

TYPEOF

   
Format TYPEOF(x)

Return the character string indicating the data type of the value of x.

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

Other syntaxes

CAST

   
Format CAST(x AS data_type)

Convert the value x into the data type “data_type”.

Convert to string type

   
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  

Convert to numeric type

   
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

Convert to time type

   
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

Convert to BOOL type

   
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

Convert to BLOB type

   
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

CASE

   
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.

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

Subquery

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.

IN

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

EXISTS

Return whether the execution result of the sub query exists.

Syntax

 
[NOT] EXISTS( 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 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

Scalar sub query

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

Placeholder

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

Comment

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;

Hints

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.

Error handling

In the following cases, a syntax error occurs.

In the following case, a table specification error occurs:

[Memo]

Metatables

About metatables

The metatables are tables that are used for checking metadata of data management in GridDB.

[Memo]

[Points to note]

Table information

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

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

Partitioning information

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

View information

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

Information about a running SQL

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]

Information about a running execution

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

Connection information

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
          :

List of databases

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]

Reserved words

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