griddb.github.io

— SQL記述形式 —

本章では、NewSQLインターフェースで使用できるSQLの記述形式について示します。

使用できる操作

SELECT文の他、CREATE TABLE等のDDL(Data Definition Language、データ定義言語)やINSERT/DELETE文などをサポートしています。詳細はGridDBでサポートされるSQL文を参照して下さい。

 

データ型

データ格納に使用する型

NewSQLインターフェースでデータの格納に使用する型は次の通りです。この型名はテーブル作成時にカラム型として記述できます。

データ型 内容詳細
BOOL型 true/false
BYTE型 -27から27-1 (8ビット)の整数値
SHORT型 -215から215-1 (16ビット)の整数値
INTEGER型 -231から231-1 (32ビット)の整数値
LONG型 -263から263-1 (64ビット)の整数値
FLOAT型 単精度型(32ビット) IEEE754で定められた浮動小数点数
DOUBLE型 倍精度型(64ビット) IEEE754で定められた浮動小数点数
TIMESTAMP型 日付と時刻の組
STRING型 Unicodeコードポイントを文字とする、任意個数の文字の列
BLOB型 画像や音声などのバイナリデータのためのデータ型
入力したままの形式で保存されるラージオブジェクト
文字xあるいはXをつけて、X’23AB’のような16進表現もできる

また、テーブルにNULL値を格納することができます。NULL値に対して“IS NULL”などの演算子を使用すると、SQL仕様に沿った結果を返却します。

テーブル作成時にカラム型として記述可能な表現

NewSQLインターフェースでは、テーブル作成時にカラム型として記述された型名について、データ格納に使用する型で列挙した型名と一致しなくても、ルールに従って解釈しデータの格納に使用する型を決定します。

以下のルールを上から順にチェックし、合致したルールによってデータ格納に使用する型を決定します。 ルールのチェック時には記述した型名およびルールでチェックする文字列の大文字小文字は区別しません。 複数のルールに合致した場合はより上にあるルールが優先されます。 どのルールにも当てはまらない場合はエラーとなりテーブル作成に失敗します。

ルールNo. テーブル作成時にカラム型として記述した識別子 作成するテーブルのカラム型
1 データ格納に使用する型に列挙した型名 テーブル作成時に指定された型に従う
2 REAL DOUBLE型
3 TINYINT BYTE型
4 SMALLINT SHORT型
5 BIGINT LONG型
6 INTを含む型名 INTEGER型
7 CHAR, CLOB, TEXTのいずれかを含む型名 STRING型
8 BLOBを含む型名 BLOB型
9 REAL, DOUBのいずれかを含む型名 DOUBLE型
10 FLOAを含む型名 FLOAT型

上記ルールによるデータ型決定の例を示します。

NoSQLインターフェースのクライアントにおけるデータ型と同等の型をNewSQLインターフェイスで使用する場合は、以下のように記述してください。ただし、一部同等の型が存在せず、使用できないものがあります。

NoSQLインターフェースのデータ型 同等の型となるNewSQLインターフェースのカラム型記述
STRING(文字列型) STRING または STRING型となる表現
BOOL(ブール型) BOOL
BYTE(8ビット整数型) BYTE または BYTE型となる表現
SHORT(16ビット整数型) SHORT または SHORT型となる表現
INTEGER(32ビット整数型) INTEGER または INTEGER型となる表現
LONG(64ビット整数型) LONG または LONG型となる表現
FLOAT(32ビット単精度浮動小数点数型) FLOAT または FLOAT型となる表現
DOUBLE(64ビット倍精度浮動小数点数型) DOUBLE または DOUBLE型となる表現
TIMESTAMP(時刻型) TIMESTAMP
GEOMETRY(空間型) テーブル作成時のカラム型には指定できません
BLOB型 BLOB または BLOB型となる表現
配列型 テーブル作成時のカラム型には指定できません

 

コンテナをテーブルとしてアクセスするときのデータ型と値の扱い

NoSQLインターフェースのクライアントで作成したコンテナを、NewSQLインターフェースでアクセスする場合のコンテナのカラム型および値の扱いを以下に示します。

コンテナのカラム型 NewSQLにマッピングされるデータ型
STRING型 STRING型 元の値と同一
BOOL型 BOOL型 元の値と同一
BYTE型 BYTE型 元の値と同一
SHORT型 SHORT型 元の値と同一
INTEGER型 INTEGER型 元の値と同一
LONG型 LONG型 元の値と同一
FLOAT型 FLOAT型 元の値と同一
DOUBLE型 DOUBLE型 元の値と同一
TIMESTAMP型 TIMESTAMP型 元の値と同一
GEOMETRY型 NULL定数と同等の型(Types.UNKNOWN) 全ての値がNULL
BLOB型 BLOB型 元の値と同一
配列型 NULL定数と同等の型(Types.UNKNOWN) 全ての値がNULL

SQLでサポートしていないデータ型の扱い

NoSQLインタフェースでサポートしているが、NewSQLインタフェースではサポートしていないデータ型は次の通りです。

これらのデータ型のデータに対して、NewSQLインタフェースでアクセスした場合の扱いについて説明します。

 

ユーザとデータベース

GridDBのユーザには、管理ユーザと一般ユーザの2種類があり、利用できる機能に違いがあります。 また、データベースを作成することで、利用ユーザ単位にアクセスを分離することができます。 ユーザ、データベースの詳細は『GridDB 機能リファレンス』を参照してください。

ネーミングの規則

ネーミングの規則は次の通りです。

ノードアフィニティ機能、ネーミングの規則・制限についての詳細は、『GridDB 機能リファレンス』を参照してください。

[メモ]

— GridDBでサポートされるSQL文 —

サポートされるSQL文は、次の通りです。

コマンド 概要
CREATE DATABASE データベースを作成する。
CREATE TABLE テーブルを作成する。
CREATE INDEX 索引を作成する。
CREATE VIEW ビューを作成する。
CREATE USER 一般ユーザを作成する。
CREATE ROLE ロールを作成する。
DROP DATABASE データベースを削除する。
DROP TABLE テーブルを削除する。
DROP INDEX 索引を削除する。
DROP VIEW ビューを削除する。
DROP USER 一般ユーザを削除する。
DROP ROLE ロールを削除する。
ALTER TABLE テーブルの構造を変更します。
GRANT 一般ユーザにデータベースへのアクセス権を設定する。
REVOKE 一般ユーザからデータベースへのアクセス権を削除する。
SET PASSWORD 一般ユーザのパスワードを変更する。
SELECT データを取得する。
INSERT テーブルに行を挿入する。
DELETE テーブルから行を削除する。
UPDATE テーブルにある行を更新する。
コメント コメントを表記する。
ヒント 実行計画を制御する。

本章では、SQL文の分類ごとに説明を行います。

データ定義言語(DDL)

CREATE DATABASE

データベースを作成します。

構文

 
CREATE DATABASE データベース名 ;

仕様

CREATE TABLE

テーブルの作成

テーブルを作成します。

構文

仕様

パーティショニングテーブルの作成

パーティショニングテーブルを作成します。

各パーティショニングの機能については、『GridDB 機能リファレンス』を参照してください。

(1) ハッシュパーティショニングテーブルの作成

構文

仕様

(2) インターバルパーティショニングテーブルの作成

構文

仕様

(3) インターバル-ハッシュパーティショニングテーブルの作成

構文

仕様

CREATE INDEX

索引を作成します。

構文

 
CREATE INDEX [IF NOT EXISTS] 索引名 ON テーブル名 ( 索引をつける列名 [, …] ) ;

仕様

CREATE VIEW

ビューを作成します。

構文

 
CREATE [FORCE] VIEW ビュー名 AS SELECT文 ;

仕様

CREATE USER

一般ユーザを作成します。

構文

 
CREATE USER ユーザ名 IDENTIFIED BY ‘パスワード文字列’ ;

仕様

CREATE ROLE

LDAP認証で必要なロールを作成します。

構文

 
CREATE ROLE ロール名 ;

仕様

DROP DATABASE

データベースを削除します。

構文

 
DROP DATABASE データベース名 ;

仕様

DROP TABLE

テーブルを削除します。

構文

 
DROP TABLE [IF EXISTS] テーブル名 ;

仕様

DROP INDEX

指定された索引を削除します。

構文

 
DROP INDEX [IF EXISTS] 索引名 ON テーブル名 ;

仕様

DROP VIEW

ビューを削除します。

構文

 
DROP VIEW [IF EXISTS] ビュー名 ;

仕様

DROP USER

一般ユーザを削除します。

構文

 
DROP USER ユーザ名 ;

仕様

DROP ROLE

LDAP認証で必要なロールを削除します。

構文

 
DROP ROLE ロール名 ;

仕様

ALTER TABLE

テーブルの構造を変更します。

カラムを追加する

テーブルの末尾にカラムを追加します。

構文

 
ALTER TABLE テーブル名 ADD [COLUMN] 列定義 [,ADD [COLUMN] 列定義 …] ;

仕様

データパーティションを削除する

テーブルパーティショニングで作成されたデータパーティションを削除します。

構文

 
ALTER TABLE テーブル名 DROP PARTITION FOR ( 削除するデータパーティションの区間(下限値から上限値)に含まれる値 );

仕様

メタテーブルの詳細はメタテーブルを参照してください。

インターバルパーティショニングテーブル

インターバル-ハッシュパーティショニングテーブル

カラム名を変更する

指定した既存のカラム名を変更します。

構文

 
ALTER TABLE テーブル名 RENAME COLUMN 変更前カラム名 TO 変更後カラム名;

仕様

 

データ制御言語(DCL)

GRANT

一般ユーザ、もしくはロールにデータベースへのアクセス権を付与します。

構文

 
GRANT {SELECT|ALL} ON データベース名 TO {ユーザ名|ロール名};

仕様

REVOKE

一般ユーザ、もしくはロールからデータベースへのアクセス権を剥奪します。

構文

 
REVOKE {SELECT|ALL} ON データベース名 FROM {ユーザ名|ロール名} ;

仕様

SET PASSWORD

一般ユーザのパスワードを変更します。

構文

 
SET PASSWORD [FOR ユーザ名 ] = ‘パスワード文字列’ ;

仕様

  

データ操作言語(DML)

SELECT

データを取得します。FROM句、WHERE句など様々なから構成されます。

構文

 
SELECT [{ALL|DISTINCT}] * | 列名1 [, 列名2 …]
[FROM句]
[WHERE句]
[GROUP BY句 [HAVING句]]
[{UNION [ALL] |INTERSECT|EXCEPT} SELECT文]
[ORDER BY句]
[LIMIT句 [OFFSET句]] ;

INSERT

テーブルに行を登録します。INSERT句は単に行を登録し、INSERT OR REPLACE句とREPLACE句は、既に同一主キーが存在するデータを与えた場合、既存のデータを上書きします。REPLACE句はINSERT OR REPLACE句の別名で、機能の違いはありません。

構文

 
{INSERT|INSERT OR REPLACE|REPLACE} INTO テーブル名
{VALUES ( { 数値1 | 文字列1 } [, { 数値2 | 文字列2 } …] )|SELECT文} ;

仕様

INSERT INTO myTable1 VALUES(1, 100);

REPLACE INTO myTable1 VALUES(1, 200);

INSERT INTO myTable1 SELECT * FROM myTable2;

DELETE

テーブルから行を削除します。

構文

 
DELETE FROM テーブル名 [WHERE句] ;

UPDATE

テーブルに存在する行を更新します。

構文

 
UPDATE テーブル名 SET 列名1 = 式1 [, 列名2 = 式2 …] [WHERE句] ;

仕様

FROM

データ操作を行うテーブル名またはビュー名、サブクエリを指定します。

構文

 
FROM テーブル名1 [, テーブル名2 … ]
FROM ( sub_query ) [AS] 別名 [, … ]

仕様

例)

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

前に指定された句の結果の中で、指定された列で同じ値を持った行をグループ化します。

構文

 
GROUP BY 列名1 [, 列名2 …]

HAVING

GROUP BY句によりグループ化された情報に対して探索条件で絞り込みを行います。GROUP BY句は省略できません。

構文

 
HAVING 探索条件

ORDER BY

検索結果の並べ替え(ソート)を行います。

 
ORDER BY 列名1 [{ASC|DESC}] [, 列名2 [{ASC|DESC}] …]

WHERE

先行するFROM句の結果に、探索条件を適用します。

構文

 
WHERE 探索条件

仕様

LIMIT/OFFSET

指定した位置から指定した件数分のデータを取り出します。

構文

 
LIMIT 値1 [OFFSET 値2 ]

仕様

 

JOIN

テーブルを結合します。

構文

結合の種類 構文
内部結合 テーブル1 [INNER] JOIN テーブル2 [ ON | USING(列名 [,列名 …]) ]
左外部結合 テーブル1 LEFT [OUTER] JOIN テーブル2 [ ON | USING(列名 [,列名 …]) ]
クロス結合 テーブル1 CROSS JOIN テーブル2 [ ON | USING(列名 [,列名 …]) ]

結合条件は、ON句またはUSING句を用いて指定します。

例)

名前: employees

 id   first_name   department_id
----+------------+----------------
  0   John         0
  1   William      1
  2   Richard      0
  3   Mary         4
  4   Lisa         3
  5   James        1

名前: departments

 department_id   department   
---------------+------------
  0              Sales
  1              Development
  2              Research
  3              Marketing

○内部結合
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


○左外部結合
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)を用いると、指定されたテーブルの同じ名前のカラムの値が一致するかを結合条件として結合を行います。

結合の種類 構文
内部結合 テーブル1 NATURAL [INNER] JOIN テーブル2
左外部結合 テーブル1 NATURAL LEFT [OUTER] JOIN テーブル2
クロス結合 テーブル1 NATURAL CROSS JOIN テーブル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

2つの問い合わせ結果の集合に対して演算を行います。

構文

   
問合せ1 UNION 問合せ2 2つの問合せのすべての結果を返します (重複は含まない)
問合せ1 UNION ALL 問合せ2 2つの問合せのすべての結果を返します (重複を含む)
問合せ1 INTERSECT 問合せ2 2つの問合せの共通の結果を返します
問合せ1 EXCEPT 問合せ2 2つの問合せの差分(問合せ1に含まれていて問合せ2に含まれない結果)の結果を返します

OVER

問い合わせ結果の分割や、並び替えを行います。WINDOW関数と共に利用します。

構文

 
関数 OVER ( [PARTITION BY 式1 ] [ORDER BY 式2 ] )

仕様

演算子

SQL文で使用する演算子を以下に説明します。

演算子一覧

演算子の一覧は次の通りです。

分類 演算子 説明
算術 + 加算します
  - 減算します
  * 乗算します
  / 除算します
  % 剰余を求めます
文字 || 任意の型の値を文字列として連結します。
いずれかの値がNULLの場合はNULLを返します。
比較 =, == 等しいかどうかを比較します
  !=, <> 等しくないかどうかを比較します
  > より大きいかどうかを比較します
  >= より大きい、または、等しいかどうかを比較します
  < より小さいかどうかを比較します
  <= より小さい、または、等しいかどうかを比較します
  IS 等しいかどうかを比較します。
両方の式がNULLの場合はtrueを返します。
いずれかがNULLの場合はfalseを返します。
  IS NOT 等しくないかどうかを比較します。
両方の式がNULLの場合はfalseを返します。
いずれかがNULLの場合はtrueを返します。
  ISNULL 左辺の式がNULLかを判定します
  NOTNULL 左辺の式がNULLでないかを判定します
  LIKE 文字列を検索します。
  GLOB 文字列を検索します。
  BETWEEN 指定した範囲の値を取り出します。
  IN 値の集合の中に指定した値が含まれるかどうかを返します。
ビット & A & B :AとBのビットのANDをとります
  | A | B :AとBのビットのORをとります
  ~ ~A :AのビットのNOTをとります
  << A << B :Aを左へBビット分シフトします
  >> A >> B :Aを右へBビット分シフトします
論理 AND 両方の式がtrueの場合はtrueを返します。
いずれかがfalseの場合はfalseを返します。
それ以外の場合はNULLを返します。
  OR いずれかの式がtrueの場合はtrueを返します。
両方がfalseの場合はfalseを返します。
それ以外の場合はNULLを返します。
  NOT 式がtrueの場合はfalseを返します。
falseの場合はtrueを返します。
それ以外の場合はNULLを返します。

LIKE

文字列を検索します。

構文

 
str [NOT] LIKE pattern_str [ESCAPE escape_str ]

仕様

GLOB

構文

文字列を検索します。

 
str GLOB pattern_str

仕様

BETWEEN

指定した範囲の値を取り出します。

構文

 
式1 [NOT] BETWEEN 式2 AND 式3

仕様

IN

値の集合の中に指定した値が含まれるかどうかを返します。

構文

 
式1 [NOT] IN ( [式2 [, 式3 …]] )

仕様

関数

SQL文で使用する関数を以下に説明します。

関数一覧

SQL文には以下の関数が用意されています。

分類 関数名 説明
集計 AVG 平均値を返します
  COUNT ロウ数を返します
  MAX 最大値を返します
  MIN 最小値を返します
  SUM 合計値を返します
  TOTAL 合計値を返します
  GROUP_CONCAT 値を連結します
  STDDEV_SAMP 標本標準偏差を返します
  STDDEV 標本標準偏差を返します
  STDDEV0 標本標準偏差を返します
  STDDEV_POP 母標準偏差を返します
  VAR_SAMP 標本分散を返します
  VARIANCE 標本分散を返します
  VARIANCE0 標本分散を返します
  VAR_POP 母分散を返します
  MEDIAN 中央値を返します
算術 ABS 絶対値を返します
  ROUND 四捨五入します
  RANDOM 乱数を返します
  MAX 最大値を返します
  MIN 最小値を返します
  LOG 対数を返します
  SQRT 平方根を返します
  TRUNC 数値を切り捨てます
  HEX_TO_DEC 16進数の文字列を10進数の数値に変換します
文字 LENGTH 文字列の長さを返します
  LOWER 文字列を小文字に変換します
  UPPER 文字列を大文字に変換します
  SUBSTR 文字列の一部を切り出します
  REPLACE 文字列を置換します
  INSTR 文字列の中から特定の文字列の位置を返します
  LIKE 文字列を検索します
  GLOB 文字列を検索します
  TRIM 文字列の両端から特定の文字を除きます
  LTRIM 文字列の左端から特定の文字を除きます
  RTRIM 文字列の右端から特定の文字を除きます
  QUOTE 文字列をシングルクォートで囲みます
  UNICODE 文字のUnicodeコードポイントを返します
  CHAR Unicodeコードポイントを文字に変換して連結します
  PRINTF フォーマット変換した文字列を返します
  TRANSLATE 文字列を置換します
日時 NOW 現在時刻を返します
  TIMESTAMP 時刻の文字列表記をTIMESTAMP型に変換します
  TIMESTAMP_ADD 時刻を加算します
  TIMESTAMP_DIFF 時刻の差分を返します
  TO_TIMESTAMP_MS 時刻’1970-01-01T00:00:00.000Z’に経過時間を加算します
  TO_EPOCH_MS 時刻’1970-01-01T00:00:00.000Z’からの経過時間を返します
  EXTRACT 時刻から特定のフィールドの値を取り出します
  STRFTIME 時刻をフォーマット変換した文字列を返します
  MAKE_TIMESTAMP 時刻を生成します
  TIMESTAMP_TRUNC 時刻を切り捨てます
WINDOW ROW_NUMBER 結果のロウに対して、一意となる連番値を割り振ります
その他 COALESCE NULLではない最初の引数を返します
  IFNULL NULLではない最初の引数を返します
  NULLIF 2つの引数が同じ場合はNULL、異なる場合は最初の引数を返します
  RANDOMBLOB BLOB型の値(乱数)を返します
  ZEROBLOB BLOB型の値(0x00)を返します
  HEX BLOB型の値を16進表記に変換します
  TYPEOF 値のデータ型を返します

関数の説明では、以下のテーブルのデータを実行例として使用します。

テーブル: 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

テーブル: departments

 id   department   
----+------------
  0   Sales
  1   Development
  2   Research

テーブル: 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

[メモ]

集計関数

値を集計する関数です。 集計関数の引数には、DISTINCTまたはALLを指定できます。

   
書式 function( [DISTINCT | ALL] argument)
項目 意味
DISTINCT 重複する値のロウは除外して集計します
ALL 重複する値も含めてすべてのロウを集計します

指定を省略した場合は、ALLを指定した場合と同じになります。

[メモ]

また、集計関数は、分析関数としてOVER句と共に利用可能です。詳細はOVER句を参照ください。

例)集計関数SUMとOVER句を利用した例

SELECT id, date, empId, amount, SUM(amount) OVER(PARTITION BY empID ORDER BY id) as accumulated FROM travelexpenses;
結果:
 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

【注意事項】

AVG

   
書式 AVG( [DISTINCT | ALL] n)

nの平均値を返します。

例)

SELECT AVG(age) FROM employees;
結果:41.0

SELECT AVG(DISTINCT age) FROM employees;
結果:40.5

SELECT department, AVG(age) avg FROM employees GROUP BY department;
結果:
  department   avg
  ------------+-----
  Development  51.0
  Research     31.0
  Sales        43.0
  (NULL)       29.0

COUNT

   
書式 COUNT(* | [DISTINCT | ALL] x)

ロウの数を返します。

例)

SELECT COUNT(*) FROM employees;
結果:6

// 値がNULLのロウは無視してカウントします
SELECT COUNT(department) FROM employees;
結果:5

SELECT COUNT(DISTINCT department) FROM employees;
結果:3

MAX

   
書式 MAX( [DISTINCT | ALL] x)

最大値を返します。

例)

SELECT MAX(age) FROM employees;
結果:59

SELECT MAX(first_name) FROM employees;
結果:William

MIN

   
書式 MIN( [DISTINCT | ALL] x)

最小値を返します。

例)

SELECT MIN(age) FROM employees;
結果:29

SELECT MIN(first_name) FROM employees;
結果:James

SUM/TOTAL

   
書式 SUM( [DISTINCT | ALL] n)
書式 TOTAL( [DISTINCT | ALL] n)

合計値を返します。

例)

SELECT SUM(age) FROM employees;
結果:205

SELECT TOTAL(age) FROM employees;
結果:205.0

SELECT department, SUM(age) sum FROM employees GROUP BY department;
結果:
  department   sum
  ------------+-----
  Development  102
  Research      31
  Sales         43
  (NULL)        29

GROUP_CONCAT

   
書式 GROUP_CONCAT( [DISTINCT | ALL] x [, separator] )

xの値を連結した文字列を返します。 separatorは、連結するセパレータを指定します。指定しない場合は”,”で連結します。

例)

// 名前last_nameを'/'で連結します
SELECT GROUP_CONCAT(last_name, '/') from employees;
結果: Smith/Jones/Brown/Taylor/Smith

// 部署departmentごとに、名前first_nameを連結します
SELECT department, GROUP_CONCAT(first_name) group_concat from employees GROUP BY(department);
結果:
   department    group_concat
  -------------+--------------
   Development  William,James
   Research     Mary
   Sales        John,Richard
   (NULL)       Lisa

SELECT GROUP_CONCAT(age, ' + ') FROM employees;
結果:43 + 59 + 31 + 29 + 43

STDDEV_SAMP

   
書式 STDDEV_SAMP( [DISTINCT | ALL] x)

標本標準偏差を返します。

例)

SELECT department, STDDEV_SAMP(enrollment_period) enrollment_period_stddev from employees GROUP BY department;
結果:
   department    enrollment_period_stddev
  -------------+--------------------------
   Development  9.121677477306465
   Research     (NULL)
   Sales        6.010407640085654
   (NULL)       (NULL)

STDDEV/STDDEV0

   
書式 STDDEV( [DISTINCT | ALL] x)
書式 STDDEV0( [DISTINCT | ALL] x)

標本標準偏差を返します。STDDEVはSTDDEV_SAMP関数の別名です。

例)

SELECT department, STDDEV(enrollment_period) enrollment_period_stddev from employees GROUP BY department;
結果:
   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;
結果:
   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;
結果:
   enrollment_period_stddev
  --------------------------
   (NULL)

SELECT STDDEV0(enrollment_period) enrollment_period_stddev from employees WHERE age >= 55;
結果:
   enrollment_period_stddev
  --------------------------
   0.0

STDDEV_POP

   
書式 STDDEV_POP( [DISTINCT | ALL] x)

母標準偏差を返します。

例)

SELECT department, STDDEV_POP(enrollment_period) enrollment_period_stddev from employees GROUP BY department;
結果:
   department    enrollment_period_stddev
  -------------+--------------------------
   Development  6.450000000000002
   Research     (NULL)
   Sales        4.25
   (NULL)       0.0

VAR_SAMP

   
書式 VAR_SAMP( [DISTINCT | ALL] x)

標本分散を返します。

例)

SELECT department, VAR_SAMP(enrollment_period) enrollment_period_variance from employees GROUP BY department;
結果:
   department    enrollment_period_variance
  -------------+----------------------------
   Development  83.20500000000004
   Research     (NULL)
   Sales        36.125
   (NULL)       (NULL)

VARIANCE/VARIANCE0

   
書式 VARIANCE( [DISTINCT | ALL] x)
書式 VARIANCE0( [DISTINCT | ALL] x)

標本分散を返します。VARIANCEはVAR_SAMP関数の別名です。

例)

SELECT department, VARIANCE(enrollment_period) enrollment_period_variance from employees GROUP BY department;
結果:
   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;
結果:
   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;
結果:
   enrollment_period_variance
  ----------------------------
   (NULL)

SELECT VARIANCE0(enrollment_period) enrollment_period_variance from employees WHERE age >= 55;
結果:
   enrollment_period_variance
  ----------------------------
   0.0

VAR_POP

   
書式 VAR_POP( [DISTINCT | ALL] x)

母分散を返します。

例)

SELECT department, VAR_POP(enrollment_period) enrollment_period_variance from employees GROUP BY department;
結果:
   department    enrollment_period_variance
  -------------+----------------------------
   Development  41.60250000000002
   Research     (NULL)
   Sales        18.0625
   (NULL)       0.0

MEDIAN

   
書式 MEDIAN(n)

nの中央値を返します。計算対象のロウ数が偶数の場合は、中央に近い2つのロウの平均値を返します。

例)

SELECT MEDIAN(age) FROM employees;
結果:43

SELECT department, MEDIAN(age) mn FROM employees GROUP BY department ORDER BY mn DESC;
結果:
  department   mn
  ------------+-----
  Development  51
  Sales        43
  Research     31
  (NULL)       29

算術関数

ABS

   
書式 ABS(n)

nの絶対値を返します。正の数はそのままの値、負の数は-1を掛けた値を返します。

例)

SELECT first_name, ABS(age) abs FROM employees;
結果:
  first_name    abs
  ------------+-------
  John          43
  William       59
  Richard       (NULL)
  Mary          31
  Lisa          29
  James         43

ROUND

   
書式 ROUND(n [, m])

四捨五入します。nの値を、小数点以下m桁で四捨五入した値を返します。

例)

SELECT first_name, ROUND(enrollment_period, 0) round FROM employees;
結果:
  first_name    round
  ------------+-------
  John          16.0
  William       23.0
  Richard        7.0
  Mary          (NULL)
  Lisa           5.0
  James         10.0

RANDOM

   
書式 RANDOM()

乱数を返します。乱数は、-263から263-1までの範囲の整数です。

例)

SELECT first_name, RANDOM() random FROM employees;
結果:
  first_name    random
  ------------+----------------------
  John          -3382931580741820003
  William       -7362300487836647182
  Richard        8834368641333737477
  Mary          -8544493602797564288
  Lisa          -7727163797274657674
  James          6751560427268247384

MAX/MIN

   
書式 MAX(x1, x2 [,…])

xNの中で、最大の値を返します。

   
書式 MIN(x1, x2 [,…])

xNの中で、最小の値を返します。

例)

SELECT first_name, age, enrollment_period, MAX(age, enrollment_period) max FROM employees;
結果:
  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

   
書式 LOG(n, m)

nを底としたmの対数を返します。

例)

SELECT LOG(2, 8);
結果:3.0

SELECT LOG(0.5, 2.0);
結果:-1.0

SQRT

   
書式 SQRT(n)

nの正の平方根を返します。

例)

SELECT SQRT(4);
結果:2.0

SELECT SQRT(16.0);
結果:4.0

TRUNC

   
書式 TRUNC(n [,m])

m>=0の場合、nの値の小数点m桁未満を切り捨てた値を返します。

m<0の場合、nの値の整数-m桁以下を切り捨てた値を返します。

例)

SELECT TRUNC(123.4567);
結果:123.0

SELECT TRUNC(123.4567, 2);
結果:123.45

SELECT TRUNC(123.4567, -1);
結果:120.0

SELECT TRUNC(123.4567, -3);
結果:0.0

SELECT TRUNC(1234567, -2);
結果:1234500

HEX_TO_DEC

   
書式 HEX_TO_DEC(str)

16進数文字列strを10進数の数値型に変換します。

例)

SELECT HEX_TO_DEC('FF');
結果:255

SELECT HEX_TO_DEC('10');
結果:16

文字関数

LENGTH

   
書式 LENGTH(str)

文字列strの長さを返します。

例)

SELECT last_name, LENGTH(last_name) length FROM employees;
結果:
  last_name     length
  ------------+----------------------
  Smith         5
  Jones         5
  Brown         5
  Taylor        6
  (NULL)        (NULL)
  Smith         5

LOWER

   
書式 LOWER(str)

文字列strのアルファベットをすべて小文字に変換します。

例)

SELECT last_name, LOWER(last_name) lower FROM employees;
結果:
  last_name     lower
  ------------+----------------------
  Smith         smith
  Jones         jones
  Brown         brown
  Taylor        taylor
  (NULL)        (NULL)
  Smith         smith

UPPER

   
書式 UPPER(str)

文字列strのアルファベットをすべて大文字に変換します。

例)

SELECT last_name, UPPER(last_name) upper FROM employees;
結果:
  last_name     upper
  ------------+----------------------
  Smith         SMITH
  Jones         JONES
  Brown         BROWN
  Taylor        TAYLOR
  (NULL)        (NULL)
  Smith         SMITH

SUBSTR

   
書式 SUBSTR(str, index [, length])

文字列を部分的に切り出します。文字列strの開始位置indexの文字から、長さlengthの文字列を切り出して返します。

例)

SELECT SUBSTR('abcdefg', 3);
結果:cdefg

SELECT SUBSTR('abcdefg', 3, 2);
結果:cd

REPLACE

   
書式 REPLACE(str, search_str, replacement_str)

文字列を置換します。 文字列strの中で、文字列search_strに一致する部分をすべてreplacement_strに置き換えます。

例)

SELECT REPLACE('abcdefabc', 'abc', '123');
結果:123def123

INSTR

   
書式 INSTR(str, search_str [, offset] [, occurrence])

文字列strの中から文字列search_strを探し、その開始位置を返します。見つからなかった場合は0を返します。

例)

SELECT INSTR('abcdef', 'cd');
結果:3

SELECT INSTR('abcdef', 'gh');
結果:0

SELECT INSTR('abcabcabcde', 'ab', 2, 2);
結果:7

SELECT INSTR('abcabcabcde', 'ab', -1, 2);
結果:4

LIKE

   
書式 LIKE(pattern_str, str [, escape_str])

文字列を検索します。 文字列strが照合パターンpattern_strと一致する場合はtrueを返します。一致しない場合はfalseを返します。 照合パターンには次の2つのワイルドカードが使用できます。

ワイルドカード 意味
_ 任意の1文字
% 任意の0文字以上の文字列

ワイルドカードの文字_または%を含むstrに対して、文字_または%を検索する場合には、エスケープ文字escape_strを指定します。 ワイルドカードの文字の前にエスケープ文字を指定すると、ワイルドカードと解釈されなくなります。

例)

SELECT last_name, LIKE('%mi%', last_name) like_name FROM employees;
結果:
  last_name     like_name
  ------------+----------------------
  Smith         true
  Jones         false
  Brown         false
  Taylor        false
  (NULL)        (NULL)
  Smith         true


SELECT LIKE('%C%E%',  'ABC%DEF');
結果:true

SELECT LIKE('%C@%E%', 'ABC%DEF', '@');
結果:false

SELECT LIKE('%C@%D%', 'ABC%DEF', '@');
結果:true

GLOB

   
書式 GLOB(pattern_str, str)

文字列を検索します。 文字列strが照合パターンpattern_strと一致する場合はtrueを返します。一致しない場合はfalseを返します。 照合パターンにはワイルドカードが使用できます。

ワイルドカード 意味
? 任意の1文字
* 任意の0文字以上の文字列
[abc] 文字a、bまたはcのいずれかに一致
[a-e] 文字aからeまでのいずれかに一致

例)

SELECT GLOB('*[BA]AB?D', 'AABCD');
結果:true

TRIM

   
書式 TRIM(str [, trim_str])

文字列strの両端から、文字列trim_strのすべての文字を削除します。

例)

SELECT TRIM(' ABC ');
結果:ABC  (両端にスペース無し)

SELECT TRIM('ABCAA', 'BA');
結果:C

LTRIM

   
書式 LTRIM(str [, trim_str])

文字列strの左端から、文字列trim_strのすべての文字を削除します。

例)

SELECT LTRIM(' ABC ');
結果:ABC  (左端にスペース無し)

SELECT LTRIM('ABCAA', 'A');
結果:BCAA

RTRIM

   
書式 RTRIM(str [, trim_str])

文字列strの右端から、文字列trim_strのすべての文字を削除します。

例)

SELECT RTRIM(' ABC ');
結果: ABC  (右端にスペース無し)

SELECT RTRIM('ABCAA', 'A');
結果:ABC

QUOTE

   
書式 QUOTE(x)

xの値をシングルクォートで囲んだ文字列を返します。

例)

SELECT QUOTE(last_name) last_name, QUOTE(age) age FROM employees;
結果:
  last_name     age
  ------------+-------
  'Smith'       43
  'Jones'       59
  'Brown'       (NULL)
  'Taylor'      31
  (NULL)        29
  'Smith'       43

SELECT QUOTE(RANDOMBLOB(4));
結果:X'A45EA28D'

// カラムvalueの値は「Today's news」の文字列
SELECT value, QUOTE(value) FROM testcontainer;
結果:
   value            QUOTE(value)
  ---------------+-------------------
   Today's news     'Today''s news'

UNICODE

   
書式 UNICODE(str)

文字列strの最初の文字のUNICODEコードポイントを返します。

例)

SELECT last_name, UNICODE(last_name) unicode FROM employees;
結果:
  last_name     unicode
  ------------+----------------------
  Smith         83
  Jones         74
  Brown         66
  Taylor        84
  (NULL)        (NULL)
  Smith         83

CHAR

   
書式 CHAR(x1 [, x2, … , xn])

Unicodeコードポイントの値xnの文字を連結した文字列を返します。

例)

SELECT CHAR(83, 84, 85);
結果:STU

PRINTF

   
書式 PRINTF(format [, x1, x2, …, xn])

指定されたフォーマットformatに合わせて変換した文字列を返します。 標準Cライブラリのprintf関数と同等のフォーマットが使用できます。 それ以外のフォーマットとしては以下の2つがあります。

フォーマット 説明
%q 文字列中にシングルクォートがある場合、2つのシングルクォート’‘にエスケープします。
%Q 文字列中にシングルクォートがある場合、2つのシングルクォート’‘にエスケープします。
文字列の両端をシングルクォートで囲みます。

例)

SELECT enrollment_period, PRINTF('%.2f', enrollment_period) printf FROM employees;
結果:
  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

| | | | — | ————————————————- | | 書式 | TRANSLATE(str, search_str, replacement_str) |

文字列を置換します。文字列strのうち、文字列search_strと一致する文字が、search_strと同じ位置にある文字列replacement_strの文字で置換されます。replacement_strsearch_strより短く、置換後の文字がない場合、置換対象の文字は削除されます。

例)

SELECT TRANSLATE('abcde', 'ace', '123');
結果:1b2d3

SELECT TRANSLATE('abcdeca', 'ace', '123');
結果:1b2d321

SELECT TRANSLATE('abcde', 'ac', '123');
結果:1b2de

SELECT TRANSLATE('abcde', 'ace', '12');
結果:1b2d

SELECT TRANSLATE('abcde', 'AB', '123');
結果:abcde

SELECT TRANSLATE('abcde', 'abc', '');
結果:de

日時関数

NOW

   
書式 NOW()

現在時刻の値を返します。

例)

SELECT NOW();
結果:2019-09-17T04:07:31.825Z

SELECT NOW();
結果:2019-09-17T13:09:20.918+09:00

TIMESTAMP

   
書式 TIMESTAMP(timestamp_string [, timezone])

時刻の文字列表現timestamp_stringの値を、TIMESTAMP型に変換します。

例)

// カラムdate(TIMESTAMP型)の値が、時刻'2018-12-01T10:30:00Z'より新しいロウを検索します
SELECT * FROM timeseries WHERE date > TIMESTAMP('2018-12-01T10:30:00Z');

TIMESTAMP_ADD

   
書式 TIMESTAMP_ADD(time_unit, timestamp, duration [, timezone])

時刻timestampに、期間duration(単位time_unit)を加算した値を返します。

例)

// 時刻'2018-12-01T11:22:33.444Z'に10日間を加算します
SELECT TIMESTAMP_ADD(DAY, TIMESTAMP('2018-12-01T11:22:33.444Z'), 10);
結果:2018-12-11T11:22:33.444Z

SELECT TIMESTAMP_ADD(MONTH, TIMESTAMP('2019-05-31T01:23:45.678Z'), 1);
結果:2019-06-30T01:23:45.678Z

SELECT TIMESTAMP_ADD(MONTH, TIMESTAMP('2019-05-31T01:23:45.678Z'), 1, '-02:00');
結果:2019-07-01T01:23:45.678Z

TIMESTAMP_DIFF

   
書式 TIMESTAMP_DIFF(time_unit, timestamp1, timestamp2 [, timezone])

時刻timestamp1timestamp2の差分の時間(timestamp1-timestamp2)を、時間単位time_unitで表した値で返します。 差分を時間単位で表す際に、小数点以下は切り捨てます。

例)


// 時間単位:月
SELECT TIMESTAMP_DIFF(MONTH, TIMESTAMP('2018-12-11T10:30:15.555Z'), TIMESTAMP('2018-12-01T10:00:00.000Z'));
結果:0

// 時間単位:日
SELECT TIMESTAMP_DIFF(DAY,   TIMESTAMP('2018-12-11T10:30:15.555Z'), TIMESTAMP('2018-12-01T10:00:00.000Z'));
結果:10
SELECT TIMESTAMP_DIFF(DAY,   TIMESTAMP('2018-12-01T11:00:00.000Z'), TIMESTAMP('2018-12-11T10:30:15.555Z'));
結果:-9

// 時間単位:時間
SELECT TIMESTAMP_DIFF(HOUR,  TIMESTAMP('2018-12-11T10:30:15.555Z'), TIMESTAMP('2018-12-01T10:00:00.000Z'));
結果:240

// 時間単位:分
SELECT TIMESTAMP_DIFF(MINUTE, TIMESTAMP('2018-12-11T10:30:15.555Z'), TIMESTAMP('2018-12-01T10:00:00.000Z'));
結果:14430

// タイムゾーンによって結果が変わる例を示します。
SELECT TIMESTAMP_DIFF(MONTH, MAKE_TIMESTAMP(2019, 8, 1), MAKE_TIMESTAMP(2019, 6, 30), 'Z');
結果:2

SELECT TIMESTAMP_DIFF(MONTH, MAKE_TIMESTAMP(2019, 8, 1), MAKE_TIMESTAMP(2019, 6, 30), '-01:00');
結果:1

TO_TIMESTAMP_MS

   
書式 TO_TIMESTAMP_MS(milliseconds)

時刻’1970-01-01T00:00:00.000Z’に、引数millisecondsの値をミリ秒として加算した時刻を返します。

この関数は、TO_EPOCH_MS関数の逆変換です。

例)

SELECT TO_TIMESTAMP_MS(1609459199999);
結果:2020-12-31T23:59:59.999Z

TO_EPOCH_MS

   
書式 TO_EPOCH_MS(timestamp)

時刻’1970-01-01T00:00:00.000Z’から時刻timestampまでの経過時間(ミリ秒)を返します。

この関数は、TO_TIMESTAMP_MS関数の逆変換です。

例)

SELECT TO_EPOCH_MS(TIMESTAMP('2020-12-31T23:59:59.999Z'));
結果:1609459199999

SELECT TO_EPOCH_MS(TIMESTAMP('2020-12-31T23:59:59.999+09:00'));
結果:1609426799999

EXTRACT

   
書式 EXTRACT(time_field, timestamp [, timezone])

時刻timestampから、日時フィールドtime_fieldの値を取り出します。時刻はUTCの値になります。

例)

// 時刻'2018-12-01T10:30:02.392Z'の年、日、ミリ秒の値を求めます

// 年の値
SELECT EXTRACT(YEAR, TIMESTAMP('2018-12-01T10:30:02.392Z'));
結果:2018

SELECT EXTRACT(DAY, TIMESTAMP('2018-12-01T10:30:02.392Z'));
// 日の値
結果:1

// ミリ秒の値
SELECT EXTRACT(MILLISECOND, TIMESTAMP('2018-12-01T10:30:02.392Z'));
結果:392


// タイムゾーンを考慮します。
SELECT EXTRACT(HOUR, TIMESTAMP('2018-12-01T10:30:02.392Z'), '+09:00');
結果:19

STRFTIME

   
書式 STRFTIME(format, timestamp [, modifier,…])

指定されたフォーマットに合わせて時刻を文字列に変換して返します。

フォーマット 説明
%Y 年をYYYY形式で取り出します。
%m 月をMM形式で取り出します。
%d 日をDD形式で取り出します。
%H 時をhh形式で取り出します。
%M 分をmm形式で取り出します。
%S 秒をss形式で取り出します。
%3f ミリ秒をSSS形式で取り出します。
%z タイムゾーンを±hh:mm形式で取り出します。
%w 曜日をD形式(0~6)で取り出します。日曜日が起点で0、土曜日が終点で6となります。
%W その年の初めから何週目かをDD形式(00~53)で取り出します。最初の月曜日を1週目とし、それ以前の曜日は0週目とみなします。
%j 1月1日を起点とした日数をDDD形式(001~366)で取り出します。
%c 時刻をYYYY-MM-DDThh:mm:ss[.SSS](Z|±hh:mm|±hhmm)形式で取り出します。
%% %を文字として出力します。

例)


SELECT STRFTIME('%c', TIMESTAMP('2019-06-19T14:15:01.123Z'));
結果:2019-06-19T14:15:01.123Z

SELECT STRFTIME('%H:%M:%S%z', TIMESTAMP('2019-06-19T14:15:01.123Z'), '+09:00');
結果:23:15:01+09:00

SELECT STRFTIME('%W', TIMESTAMP('2019-01-19T14:15:01.123Z'));
結果:02

MAKE_TIMESTAMP

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

TIMESTAMP型の値を生成して返します。

例)


SELECT MAKE_TIMESTAMP(2019, 9, 19);
結果:2019-09-19T00:00:00.000Z

SELECT MAKE_TIMESTAMP(2019, 9, 19, 10, 30, 15.123, '+09:00');
結果:2019-09-19T01:30:15.123Z

TIMESTAMP_TRUNC

   
書式 TIMESTAMP_TRUNC(field, timestamp [, timezone])

時刻情報を切り捨てます。

例)


SELECT TIMESTAMP_TRUNC(HOUR, MAKE_TIMESTAMP(2019, 9, 19, 10, 30, 15.123));
結果:2019-09-19T10:00:00.000Z

SELECT TIMESTAMP_TRUNC(DAY, MAKE_TIMESTAMP(2019, 5, 15), '-01:00');
結果:2019-05-14T01:00:00.000Z

WINDOW関数

ROW_NUMBER

   
書式 ROW_NUMBER()

結果のロウに対して、一意となる連番値を割り振ります。

例)

SELECT ROW_NUMBER() OVER(PARTITION BY department ORDER BY age) no, first_name, age, department FROM employees;
結果:
  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

   
書式 書式 LAG( x [, offset [, default ] ] )

現在行よりも、offset行数分、前方のxを返します。

例)

SELECT id, date, empId, amount, LAG(amount) OVER(PARTITION BY empID ORDER BY id) as lag_amount FROM travelexpenses;
結果:
 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

   
書式 書式 LEAD( x [, offset [, default ] ] )

現在行よりも、offset行数分、後方のxを返します。

例)

SELECT id, date, empId, amount, LEAD(amount) OVER(PARTITION BY empID ORDER BY id) as lead_amount FROM travelexpenses;
結果:
 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)      

その他の関数

COALESCE

   
書式 COALESCE(x1, x2 [,…, xn])

指定された引数xnの中で、NULLではない最初の引数の値を返します。

例)

SELECT last_name, COALESCE(last_name, 'XXX') coalesce FROM employees;
結果:
  last_name     coalesce
  ------------+----------------------
  Smith         Smith
  Jones         Jones
  Brown         Brown
  Taylor        Taylor
  (NULL)        XXX
  Smith         Smith

SELECT age, COALESCE(age, -1) coalesce FROM employees;
結果:
  age       coalesce
  --------+-----------
  43         43
  59         59
  (NULL)     -1
  31         31
  29         29
  43         43

IFNULL

   
書式 IFNULL(x, y)

指定された引数xyのうち、NULLではない最初の引数の値を返します。IFNULL関数は、引数を2つ指定したCOALESCE関数と同等です。

例)

SELECT last_name, IFNULL(last_name, 'XXX') ifnull FROM employees;
結果:
  last_name     ifnull
  ------------+----------------------
  Smith         Smith
  Jones         Jones
  Brown         Brown
  Taylor        Taylor
  (NULL)        XXX
  Smith         Smith

SELECT age, IFNULL(age, -1) ifnull FROM employees;
結果:
  age       coalesce
  --------+-----------
  43         43
  59         59
  (NULL)     -1
  31         31
  29         29
  43         43

NULLIF

   
書式 NULLIF(x, y)

指定された2つの引数が同じ値の場合はNULL、異なる場合は最初の引数を返します。

例)

// value1とvalue2の値で、NULLIFを実行します
SELECT value1, value2, NULLIF(value1, value2) nullif FROM container_sample;
結果:
   value1   value2   nullif
  --------+--------+--------
      10       10    (NULL)
       5        0      5
   (NULL)       4    (NULL)
       3    (NULL)     3
   (NULL)   (NULL)   (NULL)


// value1/value2の計算で、ゼロ除算エラーを防ぐために0をNULLに変換します
SELECT value1, value2, value1/NULLIF(value2, 0) division FROM container_sample;
結果:
   value1   value2   division
  --------+--------+--------
      10       10      1
       5        0    (NULL)
   (NULL)       4    (NULL)
       3    (NULL)   (NULL)
   (NULL)   (NULL)   (NULL)

RANDOMBLOB

   
書式 RANDOMBLOB(size)

BLOB型の値(乱数)を返します。

例) 

// 10バイトのBLOB型の値(乱数)を生成します
SELECT HEX(RANDOMBLOB(10));
結果:7C8C893C8087F07883AF

ZEROBLOB

   
書式 ZEROBLOB(size)

BLOB型の値(0x00)を返します。

例) 

// 10バイトのBLOB型の値(0x00)を生成します
SELECT HEX(ZEROBLOB(10));
結果:00000000000000000000

HEX

   
書式 HEX(x)

BLOB型の値を16進表記に変換します。 引数xをBLOB型の値として解釈して、16進数に変換した文字列(大文字)を返します。

例)

SELECT HEX(RANDOMBLOB(2));
結果:E18D

SELECT first_name, HEX(first_name) hex FROM employees;
結果:
  first_name    hex
  ------------+----------------------
  John          4A6F686E
  William       57696C6C69616D
  Richard       52696368617264
  Mary          4D617279
  Lisa          4C697361
  James         4A616D6573

TYPEOF

   
書式 TYPEOF(x)

xの値のデータ型を表す文字列を返します。

例)

SELECT TYPEOF(ABS(-10)) abs, TYPEOF(RANDOMBLOB(10)) randomblob,
    TYPEOF(TIMESTAMP('2018-12-01T10:30:02.392Z')) timestamp;
結果:
   abs    randomblob   timestamp
  ------+------------+-----------
   LONG   BLOB         TIMESTAMP

 

その他構文

CAST

   
書式 CAST(x AS data_type)

xを、データ型data_typeに変換します。

文字列型への変換

   
書式 CAST(x AS STRING)

引数xを、文字列型に変換します。

xに指定できる値のデータ型と、変換後の値は以下の通りです。

xのデータ型 文字列型に変換した値
BOOL型 trueの場合’true’、falseの場合’false’
STRING型 元のままの値
BYTE型
SHORT型
INTEGER型
LONG型
FLOAT型
DOUBLE型
数値を文字列に変換した値
TIMESTAMP型 時刻の文字列表記’YYYY-MM-DDThh:mm:ss.SSS(Z|±hh:mm)’
接続時のタイムゾーン設定が利用される
BLOB型 HEX関数と同等の文字列

数値型への変換

   
書式 CAST(x AS BYTE|SHORT|INTEGER|LONG|FLOAT|DOUBLE)

引数xを、数値型に変換します。

xに指定できる値のデータ型と、変換後の値は以下の通りです。

xのデータ型 数値型に変換した値
BOOL型 trueの場合1、falseの場合0
STRING型 文字列の数字を数値に変換した値
BYTE型
SHORT型
INTEGER型
LONG型
FLOAT型
DOUBLE型
数値を変換した値
// BYTE型の範囲(-128~127)を超える場合はエラー
SELECT CAST(128 AS BYTE);
結果:エラー

// INTEGER型の範囲(-2147483648 ~ 2147483647)を超える場合はエラー
SELECT CAST('2147483648' AS INTEGER);
結果:エラー
SELECT CAST(10.5 AS INTEGER);
結果:10
SELECT CAST('abc' AS INTEGER);
結果:エラー

SELECT CAST('-1.09E+10' AS DOUBLE);
結果:-1.09E10

時刻型への変換

   
書式 CAST(x AS TIMESTAMP)

引数xを、時刻型に変換します。接続時にタイムゾーンを指定している場合、その値がオフセット計算に利用されます。

xに指定できる値のデータ型と、変換後の値は以下の通りです。

xのデータ型 時刻型に変換した値
STRING型(時刻の文字列表記’YYYY-MM-DDThh:mm:ss.SSS(Z|±hh:mm)’) TIMESTAMP関数で変換した値と同等
SELECT CAST('2018-12-01T10:30:00Z' AS TIMESTAMP);
結果:2018-12-01T10:30:00.000Z

SELECT CAST('2018-12-01T10:30:00+09:00' AS TIMESTAMP);
結果:2018-12-01T01:30:00.000Z

BOOL型への変換

   
書式 CAST(x AS BOOL)

引数xを、BOOL型に変換します。

xに指定できる値のデータ型と、変換後の値は以下の通りです。

xのデータ型 時刻型に変換した値
STRING型 ‘true’の場合true、’false’の場合false (大文字小文字の区別なし)
BYTE型
SHORT型
INTEGER型
LONG型
0の場合false、それ以外の場合true

BLOB型への変換

   
書式 CAST(x AS BLOB)

引数xを、BLOB型に変換します。

xに指定できる値のデータ型と、変換後の値は以下の通りです。

xのデータ型 BLOB型に変換した値
STRING型 文字列を16進表記のデータとしてBLOB型に変換した値

 

CASE

   
書式 CASE
WHEN condition1 THEN result1
[WHEN condition2 THEN result2]

[ELSE resultElse]
END

条件式conditionNがtrueの場合は、対応するresultNの値を返します。 すべての条件式がfalseまたはNULLの場合は、ELSEが指定されていればresultElseの値を返します。ELSEが指定されていない場合は、NULLを返します。

 

   
書式 CASE x
WHEN value1 THEN result1
[WHEN value2 THEN result2]

[ELSE resultElse]
END

xの値がvalueNの場合は、対応するresultNの値を返します。 すべての値に当てはまらない場合は、ELSEが指定されていればresultElseの値を返します。ELSEが指定されていない場合は、NULLを返します。

 

resultNには同じ型の値を指定します。ただし、異なる型でも指定できる場合があります。

例)

// 従業員の年代(30代、40代、50代、それ以外)を表示する
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;

結果:
 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


// 部署に応じて所在地を表示する
SELECT id, first_name, department,
  CASE department
    WHEN 'Sales' THEN 'Tokyo'
    WHEN 'Development' THEN 'Osaka'
    ELSE 'Nagoya'
  END AS location
FROM employees;

結果:
 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

サブクエリ

サブクエリはFROM句やWHERE句だけでなく、SQL文の様々な部分で指定できます。 また、サブクエリに対するいくつかの演算種別も提供しています。それらについて 説明します。

IN

サブクエリの実行結果の中に、指定した値が含まれるかどうかを返します。

構文

 
式1 [NOT] IN ( sub_query )

仕様

例)

// departmentsテーブルのid=1の部署に所属する従業員の情報をemployeesテーブルから表示します
SELECT * FROM employees
WHERE department IN(
  SELECT department FROM departments
  WHERE 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

EXISTS

サブクエリの実行結果が存在するかどうかを返します。

構文

 
[NOT] EXISTS( sub_query )

仕様

例)

// departmentsテーブルのid=1の部署に所属する従業員の情報をemployeesテーブルから表示します
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

スカラサブクエリ

ひとつの結果を返すサブクエリです。SELECT文の結果や、式などに使用できます。

例)

SELECT id, first_name,
       (SELECT department FROM departments WHERE department_id=employees.department_id)
FROM employees;

結果:
  id  first_name  department
  ---+-----------+-------------
   0  John        Sales
   1  William     Development
   2  Richard     Sales
   3  Mary        (NULL)
   4  Lisa        Marketing
   5  James       Development

プレースホルダ

プリペアードステートメントではSQL文にプレースホルダを記述できます。 プレースホルダはステートメント実行時に代入するパラメータの位置を示します。 パラメータの番号は1から始まります。

プレースホルダは他のデータベースとの互換性のため、幾つかの形式が使用できます。 ただし、いずれの形式で指定しても、パラメータの番号は既に割当てられている パラメータ番号+1となります。

形式 説明 記述例
? 標準のプレースホルダの形式 ?
?NNN NNNは数字を表す ?56
:AAAA AAAAは文字列を表す :name
@AAAA AAAAは文字列を表す @name

なお、$から始まるプレースホルダは記述できません。

例)

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

コメント

SQL文中にコメントを書くことができます。 書式は、–(ハイフンを2つ)の後ろに記述するか、/* */で囲みます。 コメントの後ろには改行が必要です。

SELECT * -- comment
FROM employees;

SELECT *
/*
  comment
*/
FROM employees;

ヒント機能

GridDBでは、実行計画を示すヒントをクエリに指定することで、SQL文を変えることなく実行計画を制御できます。

GridDB SQLチューニングガイド』を参照して、ヒント句を用いたチューニングを行ってください。

エラーの扱い

以下の場合は構文エラーとなります。

以下の場合はテーブル指定エラーとなります。

【メモ】

— メタテーブル —

メタテーブルとは

GridDBの管理用のメタデータを参照することができるテーブル群です。

【メモ】

【注意事項】

テーブル情報

テーブルに関する情報を取得できます。

テーブル名

#tables

スキーマ

列名 内容
DATABASE_NAME データベース名 STRING
TABLE_NAME テーブル名 STRING
TABLE_OPTIONAL_TYPE テーブル種別
COLLECTION / TIMESERIES
STRING
DATA_AFFINITY データアフィニティ STRING
EXPIRATION_TIME 期限解放経過時間 INTEGER
EXPIRATION_TIME_UNIT 期限解放経過単位 STRING
EXPIRATION_DIVISION_COUNT 期限解放分割数 INTEGER
PARTITION_TYPE パーティショニング種別 STRING
PARTITION_COLUMN パーティショニングキー STRING
PARTITION_INTERVAL_VALUE 分割値(インターバル/インターバルハッシュの場合) STRING
PARTITION_INTERVAL_UNIT 分割単位(インターバル/インターバルハッシュの場合) STRING
PARTITION_DIVISION_COUNT 分割数(ハッシュの場合) INTEGER
SUBPARTITION_TYPE パーティショニング種別
(インターバルハッシュの場合にHASH)
STRING
SUBPARTITION_COLUMN パーティショニングキー
(インターバルハッシュの場合)
STRING
SUBPARTITION_INTERVAL_VALUE 分割値 STRING
SUBPARTITION_INTERVAL_UNIT 分割単位 STRING
SUBPARTITION_DIVISION_COUNT 分割数
(インターバルハッシュの場合)
INTEGER
EXPIRATION_TYPE 期限解放種別
PARTITION
STRING

索引情報

索引に関する情報を取得できます。

テーブル名

#index_info

スキーマ

列名 内容
DATABASE_NAME データベース名 STRING
TABLE_NAME テーブル名 STRING
INDEX_NAME 索引名 STRING
INDEX_TYPE 索引種別
TREE / SPATIAL
STRING
ORDINAL_POSITION 索引内のカラム列順序(1からの連番) SHORT
COLUMN_NAME 列名 STRING

パーティショニング情報

パーティショニングされたテーブルの内部コンテナ(データパーティション)に関する情報を取得することができます。

テーブル名

#table_partitions

スキーマ

列名 内容
DATABASE_NAME データベース名 STRING
TABLE_NAME パーティショニングされたテーブルの名前 STRING
PARTITION_BOUNDARY_VALUE データパーティションの下限値 STRING

仕様

ビュー情報

ビューに関する情報を取得できます。

テーブル名

#views

スキーマ

列名 内容
DATABASE_NAME データベース名 STRING
VIEW_NAME ビュー名 STRING
VIEW_DEFINITION ビュー定義文字列 STRING

実行中SQL情報

実行中のSQL(クエリまたはジョブ)に関する情報を取得できます。

テーブル名

#sqls

スキーマ

列名 内容
DATABASE_NAME データベース名 STRING
NODE_ADDRESS 処理実行中のノードのアドレス(system) STRING
NODE_PORT 処理実行中のノードのポート(system) INTEGER
START_TIME 処理開始時刻 TIMESTAMP
APPLICATION_NAME アプリケーション名 STRING
SQL クエリ文字列 STRING
QUERY_ID クエリID STRING
JOB_ID ジョブID STRING

実行中イベント情報

実行中のイベントに関する情報を取得できます。

テーブル名

#events

スキーマ

列名 内容
NODE_ADDRESS 処理実行中のノードのアドレス(system) STRING
NODE_PORT 処理実行中のノードのポート(system) INTEGER
START_TIME 処理開始時刻 TIMESTAMP
APPLICATION_NAME アプリケーション名 STRING
SERVICE_TYPE サービス種別(SQL/TRANSACTION/CHECKPOINT/SYNC) STRING
EVENT_TYPE イベント種別(PUT/CP_START/SYNC_START など) STRING
WORKER_INDEX ワーカーのスレッド番号 INTEGER
CLUSTER_PARTITION_INDEX クラスタパーティション番号 INTEGER

コネクション情報

接続中のコネクションに関する情報を取得できます。

テーブル名

#sockets

スキーマ

列名 内容
SERVICE_TYPE サービス種別(SQL/TRANSACTION) STRING
SOCKET_TYPE ソケット種別 STRING
NODE_ADDRESS 接続元ノードのアドレス(ノード視点) STRING
NODE_PORT 接続元ノードのポート(ノード視点) INTEGER
REMOTE_ADDRESS 接続先ノードのアドレス(ノード視点) STRING
REMOTE_PORT 接続先ノードのポート(ノード視点) INTEGER
APPLICATION_NAME アプリケーション名 STRING
CREATION_TIME 接続時刻 TIMESTAMP
DISPATCHING_EVENT_COUNT イベントハンドリングの要求を開始した総回数 LONG
SENDING_EVENT_COUNT イベント送信を開始した総回数 LONG

SOCKET_TYPE(ソケット種別)は次の通り。

説明
SERVER サーバ間同士のTCP接続
CLIENT クライアントとのTCP接続
MULTICAST マルチキャストソケット
NULL 接続中など現時点で不明の場合

CREATION_TIME(接続時刻)は次の通り。

クライアントとのTCP接続(ソケット種別:CLIENT)の場合に限り、そのコネクションが 実行待ちかどうかを判別することができます。

具体的には、DISPATCHING_EVENT_COUNTの方がSENDING_EVENT_COUNTより大きい場合、 実行待ち状態のタイミングが存在した可能性が比較的高いと判定できます。

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
          :

 

— 予約語 —

GridDBのSQLでは、以下の単語が予約語として定義されています。

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