LoginSignup
2
0
@KWYM

ASTERIA Warp RecordSQLについて考える

Last updated at Posted at 2024-02-25

はじめに

ASTERIA WarpのRecordSQLはレコードの結合で利用するコンポーネントです。多くのフローで利用されている事と思います。今回はこのRecordSQLとその中身であるHSQLDBについていろいろ考えてみました。

事前知識

ASTERIA Warpでは内包されているHSQLDBによりレコード処理を行っています。実行しているSQL文については、実行モードをデバッグで実行すると、対象コンポーネントがどの様なSQLを発行できるかを確認する事が出来ます。RecordSQLコンポーネントは、この実行SQLを任意に指定できるコンポーネントです。
RecordJoinとRecordSQLは実行レベルではレコードの処理としては変わりはありませんが、RecordJoinは入力が２本固定である事、インデックスが作成可能などの違いがあります。HSQLDBを利用しているコンポーネントにはモードの選択が行え通常はオンメモリが指定されていますがRDBを指定する事が可能です。ただしHSQLDBのみが指定可能です。、

ASTERIA WarpをインストールするとRDBコネクションにシステムコネクションとして登録されている”asteria_internal”ですが、これはStandard以上のエディションでInternalDataStorageサービスから起動するHSQLDBサーバへ接続するためのコネクションです。HSQLDBのコネクションはそれ以外に通常のRDBコネクションとして登録でき、InternalDataStorageサービスで起動されるサーバ以外にも接続でき、３種類の指定方法が用意されています。

データベースタイプ URL 設定例
In-Memory jdbc:hsqldb:. jdbc:hsqldb:.
Standalone jdbc:hsqldb:[ファイルパス] jdbc:hsqldb:file:C:\hsqldb\data
Server jdbc:hsqldb:hsql://[ホスト名] jdbc:hsqldb:hsql://localhost:29001/asteriadb

In-Memoryを指定した場合、ASTERIA Warpが使用しているインスタンスと共有になります。Standaloneを指定した場合、Memoryとは別の接続情報として登録できます。（HSQLDB側の詳細についてはHSQLDBのリファレンスChapter 2. SQL Languageを参照してください）

ASTERIA WarpのHSQLDBで利用可能な構文について

ASTERIA Warpで使用しているHSQLDBのSQL構文は特定のSQL標準に完全に準拠しているわけではありません。HSQLDBはSQL-92やSQL:2003の一部をサポートしていますが、完全な準拠ではありません。直接DDL（データ定義）構文を実行すればテーブルなども登録可能です。利用しているHSQLDBのバージョンが2.4.1ですので、REGEXP系やJSON型,、ARRAY型などは使用できません。また、SQL内では使用できるものも、ASTERIA Warp側で対応していないためストリームとして出力できないデータ型があります。

RecordSQL

RecordSQLはRecord、CSV、FixedLengthいずれかのストリームを入力としストリームの結合を行いますRecordJoinと異なり入力数に制限はありません。入力されたストリームは、接続順にin[X]としてSQLで使用します。入力したカラムも同様にfield[x]で指定します。

select in[1].field[1] from in[1] order by in[1].field[2]

RecordSQLは（RecordJoinもですが）入力ストリームを一旦HSQLDBのテーブルとして登録し、in、fieldの記述はそのテーブルとカラムを使用したSQL文に置き換えられます。置き換えられた内容は実行モードを”デバッグ”を指定するとログに出力されます。

 select ASMS1_da643f67fcbd4dbe86b3df5c2e5ad240.f1 from ASMS1_da643f67fcbd4dbe86b3df5c2e5ad240 order by ASMS1_da643f67fcbd4dbe86b3df5c2e5ad240.f2

RecordSQLは都度入力ストリームに対してのテーブルのCREATE、INSERT、DROPを繰り返しています。
CREATEされるテーブルのカラムタイプは、入力ストリームのカラムタイプが利用されます。
HSQLDB自体は大文字小文字を区別しませんが、in、fieldはASTERIA Warpのための記述ですのでSQL上では必ず小文字で指定します。

コンポーネントのエラー処理に「レコードが無い」があり、これを”エラーを無視する”を指定すると、結果にレコードが無くてもエラーは発生しません。
RecordSQLは、SELECTだけではなく、CREATRE文などDDL文も発行できますが、作成されたテーブルはASTERIA Warpの再起動で初期化されます。そのためデータベースとの利用はアプリケーション変数的な利用に限定されます。1 HSQLDBのInMemoryコネクションから参照できますので、RDBGetからデータ登録が可能です。

-- TESTスキーマを作成する
CREATE SCHEMA TEST;
-- TESTスキーマ内にKBNテーブルを作成する
CREATE TABLE  TEST.KBN (
    KBN_ID   INTEGER PRIMARY KEY,
    KBN_NAME NAMM VARCHAR(255)
);

HSQLDBとASTERIA Warpカラム型対比表

入力時

ASTERIA Warp データ型 HSQLDB データ型
String longvarchar
Boolean BIT
Integer BIGINT
Double DOUBLE
Decimal DECIMAL(30, 5)2
DateTime TIMESTAMP
Binary LONGVARBINARY

出力時

HSQLDB データ型 RDBGet参照時3 String Boolean Integer Decimal Double Binary DateTime
BOOLEAN Boolean ○ ○ × × × × ×
BIT Boolean ○4 ○4 × × × × ×
TINYINT Integer ○ × ○ ○ × × ×
SMALLINT Integer ○ × ○ ○ × × ×
INTEGER Integer ○ × ○ ○ × × ×
BIGINT Integer ○ × ○ ○ × × ×
DECIMAL Decimal ○ × ○ 5 ○ ○ × ×
DOUBLE Double ○ × × ○ ○ × ×
REAL Double ○ × × ○ ○ × ×
VARCHAR String ○ × × × × × ×
CHAR String ○ × × × × × ×
BINARY Binary ○ × × × × × ×
VARBINARY Binary ○ × × × × × ×
LONGVARBINARY Binary ○ × × × × × ×
DATE DateTime ○ × × × × × ○6
TIME DateTime ○ × × × × × ○7
TIMESTAMP DateTime ○ × × × × × ○

SQL構文中のリテラルのHDSQDBカラム型

固定値 HSQLDB データ型
文字 VARCHAR
整数 INTEGER8
小数を含む数値 DECIMAL
指数表記 DOUBLE
true/false BOOLEAN
バイナリ BINALY
ビット BIT9
Null VARCHAR

小数を指定する場合小数点表記の場合はDECIMAL、指数表記の場合はDOUBLEとなるようですが、ASTERIAの対応するストリームは、小数点表記がInteger、Decimal、Double、指数表記はDecimal、Doubleが指定できます。小数点表記ではIntegerが指定できますが、値に小数点が含まれたままとなります（バグ？）その後、そのストリームを次のMapperで計算に使用すると小数が残っているので注意が必要です。
また、指数表記のリテラルを、Doubleに格納したあとDecimalに移送すると、小数部が指数の誤差が含まれたものになります。リテラルにあった適切なデータ型カラムを選択しましょう。

SAMPLE
SELECT
 '1' AS SMPL_CHAR ,
 1 AS SMPL_INTEGER ,
 1.234567 AS SMPL_DECIMAL ,
 1234567E-6 SMPL_DOUBLE,
 true AS SMPL_BOOLEAN ,
  X'1abACD34' AS SMPL_BINARY,
  null AS SMPL_NULL,
  B'1' AS SMPL_BIT
FROM
 in[1];

リテラルにnullを指定した場合、受け側のストリームのカラム型はStringのみ指定可能になりますが、SELECT取得結果カラムがストリームのカラムに対して不足している場合、過剰分のカラム型はどの型でもエラーとはならずnullとなります。Boolean型のみ値がfalseとなります。

HSQLDB構文

RecordSQLで使用可能な構文をサンプルとともに紹介します。

コメント行

HSQLのコメントは/**/または--で記述します。RecordSQLでは入力ストリームがすべてinとfieldで記述されますので利用しているカラムがなにかを把握しずらいです。そのため入力ストリームの内容をコメントで記述しておくと良いでしょう。

/*
in[1]  KBN
field[1] ID    integer
field[2] NAME  string
*/
select
  in[1].field[1]  --ID
from in[1]
order by
  in[1].field[2] ;

CALL

CALL文は、ストアドプロシージャやユーザー定義関数を呼び出すためのSQL文です。CALL文を使用することで、ストアドプロシージャや関数を呼び出し、その結果を取得することができます。

CALL procedure_name(argument1, argument2, ...)
-- procedure_name: 呼び出すストアドプロシージャや関数の名前
-- argument1: 引数

CALL NOW();
-- 出力ストリームにDateTime型で返却される

関数

SELECTで使用する関数ですが、RDBでの利用と異なり、ASTERIA Warpで利用する場合は事前に入力ストリームで準備する事になりますので、基本的には入力ストリームで変換を行ってからRecordSQLで利用する事が多いと思いますが、状況によっては関数を利用しRecordSQL内で加工する事もあると思います。

文字系関数

CONCAT

複数の文字列を連結して1つの文字列に結合します。

CONCAT(string1, string2, ...)
-- string: 結合する文字列
SAMPLE
SELECT 
 CONCAT('AAAA','BBBB','CCCC') AS SMPL_CONCAT
FROM 
 in[1];

REPEAT

指定された文字列を指定された回数繰り返して、1つの文字列として返します。これは、特定の文字列を繰り返して生成したい場合に使用されます。

REPEAT(string, count)
-- string: 繰り返す対象の文字列。
-- count: 繰り返す回数。
SAMPLE
SELECT 
 REPEAT('aa',6) SMPL_REPEAT
FROM 
 in[1];

REVERSE

文字列を逆順にするための関数です。つまり、文字列を後ろから前に向かって逆順にします。

REVERSE(string)
-- string: 逆順にする文字列。
SAMPLE
SELECT 
 REVERSE ('ABCDEFGH') SMPL_REVERSE
FROM 
 in[1];

RIGHT、LEFT

LEFT関数は、文字列の先頭から指定された数の文字を抽出します。
RIGHT関数は、文字列の末尾から指定された数の文字を抽出します。

LEFT(string, length)
RIGHT(string, length)
-- string: 取得元の文字列。
-- length: 取得する文字数。
SAMPLE
SELECT
 RIGHT ('ABCDEFGH',5) SMPL_RIGHT ,
 LEFT('ABCDEFGH',5) AS SMPL_LEFT
FROM 
 in[1];

RPAD、LPAD

RPAD関数は、指定された文字列の右側に指定された文字で埋めて、指定した長さにします。
LPAD関数は、指定された文字列の左側に指定された文字で埋めて、指定した長さにします。

RPAD(string, length, fill)
LPAD(string, length, fill)
-- string: 埋める対象の文字列。
-- length: 指定された長さ。
-- fill: 埋めるために使用する文字。
SAMPLE
SELECT
 RPAD ('123',5,'=') AS SMPL_RPAD ,
 LPAD ('123',5,'*') AS SMPL_LPAD
FROM 
 in[1];

SPACE

指定された数のスペースからなる文字列を生成します。

SPACE(count)
-- count: 生成するスペースの数。
SAMPLE
SELECT
 SPACE(10) AS SMPL_SPACE
FROM 
 in[1];

SUBSTR

指定された位置から指定された長さの部分文字列を抽出します。

SUBSTR(string, start, length)
-- string: 元の文字列。
-- start: 抽出を開始する位置。
-- length: 抽出する長さ。
SAMPLE
SELECT
 SUBSTR('ABCDEFGH',4,3) AS SMPL_SUBSTR
FROM 
 in[1];

UCASE、UPPER、LCASE、LOWER

UCASE関数、UPPER関数は、文字列を大文字に変換します。
LCASE関数、LOWER関数は、文字列を小文字に変換します。

UCASE(string)
UPPER(string)
LCASE(string)
LOWER(string)
-- string: 変換する文字列。
SAMPLE
SELECT
 UCASE ('abc') AS SMPL_UCASE ,
 UPPER ('abc') AS SMPL_UPPER ,
 LCASE ('ABC') AS SMPL_LCASE , 
 LOWER ('ABC') AS SMPL_LOWER 
FROM 
 in[1];

TRIM

文字列の先頭および末尾から指定された文字を削除します。デフォルトでは空白文字が削除されますが、指定した文字を削除することもできます。

TRIM([BOTH | LEADING | TRAILING] trim_character FROM string)
-- BOTH, LEADING, TRAILING: どちらの端から文字を削除するかを指定します。省略するとデフォルトでBOTHになります。
-- trim_character: 削除する文字を指定します。省略するとデフォルトで空白文字が削除されます。
-- string: 対象の文字列。
SAMPLE
SELECT
 TRIM(' 00100 ') SMPL_TRIM ,
 TRIM(LEADING '0' FROM '0012300') AS SMPL_TRIM_LEADING ,
 TRIM(TRAILING '0' FROM '0012300') AS SMPL_TRIM_TRAILING ,
 TRIM(BOTH '0' FROM '0012300') AS SMPL_TRIM_BOTH 
FROM 
 in[1];

TRANSLATE

文字列内の特定の文字を他の文字に置換します。指定された文字列に含まれる対応する文字を置換します。

TRANSLATE(string , translation_table1 ,translation_table2)
-- string: 置き換え対象の文字列。
-- translation_table: 置換する文字を指定します。最初の文字が置換される文字であり、2番目の文字が対応する文字です。
SAMPLE
-- 指定文字'/ABCDE567'のうち、'/'はそのまま'ABC'は'abc'にそれ以外の文字は空に置き変わる
-- ABCDE1234567Z　→ abc1234Z
SELECT
 TRANSLATE('ABCDE1234567Z','/ABCDE567','/abc') as SMPL_TRANSLATE
FROM 
 in[1];

文字列長

CHARACTER_LENGTH、CHAR_LENGTH関数は、指定された文字列の長さ（文字数）、BIT_LENGTH関数は、指定された文字列のビット長を返します。

CHARACTER_LENGTH ( string )
CHAR_LENGTH ( string )
BIT_LENGTH ( string )
-- string: 対象文字列
SAMPLE
SELECT
 CHARACTER_LENGTH('ABCDE1234567Z') as SMPL_CHARACTER_LENGTH ,
 CHAR_LENGTH('ABCDE1234567Z') as SMPL_CHAR_LENGTH ,
 BIT_LENGTH('ABCDE1234567Z') as SMPL_BIT_LENGTH
FROM 
 in[1];

数値系関数

CEIL、FLOOR

CEIL関数は、指定された数値を次の整数に切り上げます。つまり、小数部分がある場合はその数値を超える最小の整数になります。
FLOOR関数は、指定された数値を次の整数に切り捨てます。つまり、小数部分がある場合はその数値を下回る最大の整数になります。

CEIL(value)
FLOOR(value)
-- value: 対象の数値
SAMPLE
SELECT
 CEIL(2222.999) AS SMPL_CEIL ,
 FLOOR(4.7) AS SMPL_FLOOR
FROM 
 in[1];

MOD

除算の剰余を計算するための関数です。具体的には、割られる数を割る数で割った余りを返します。

MOD(dividend, divisor)
-- dividend: 割られる数。
-- divisor: 割る数。
SAMPLE
SELECT
 MOD(10, 3) SMPL_MOD
FROM 
 in[1];

ビット演算

ビット演算を行うための関数であり、通常は整数値のビット単位の操作に使用されます。以下にそれぞれの関数の説明を示します。BITAND、BITANDNOT、BITNOT、BITOR。BITXORがあります

関数名 使用方法 論理演算子NOT VALUE1 VALUE2 結果
BITAND BITAND(value1, value2) 論理積 1100 1010 1000
BITOR BITOR(value1, value2) 論理和 1100 1010 1110
BITANDNOT BITAND(value1, value2) 論理積否定 1100 1010 0100
BITXOR BITXOR(value1, value2) 排他的論理和 1100 1010 0110
NOT BITNOT(value1) 否定 0000 1111

BITANDNOT は 2番目の引数に対してNOTを実行し、結果と最初の引数に対してAND を実行します
NOT 1010 → 0101 AND 1100 → 0100となります。
NOTはビット反転を行います。0の各ビットを反転させると、すべてのビットが1になります。サンプルでマイナスとなっているのは符号ビットが反転するためです。

SAMPLE
SELECT
 BITAND(12,10 ) AS SMPL_BITAND ,
 BITANDNOT(12,10 ) AS SMPL_BITANDNOT ,
 BITNOT(12) AS SMPL_BITNOT ,
 BITOR(12,10 ) AS SMPL_BITOR ,
 BITXOR(12,10) AS SMPL_BITXOR
FROM 
 in[1];

PI

円周率π（パイ）の値を返す数学関数です。

PI()
SAMPLE
SELECT
 PI() AS SMPL_PI
FROM 
 in[1];

RAND

0から1の範囲でランダムな浮動小数点数を生成する関数です。

RAND()
SAMPLE
SELECT
 RAND() AS SMPL_RAND
FROM 
 in[1];

ROUND

指定された小数点以下の桁数に数値を丸める関数です。整数部と小数部の両方を丸めることができます。

ROUND(value, decimal_places)
-- value: 丸める数値。
-- decimal_places: 丸めたい小数点以下の桁数。正の値で指定すると小数部を、負の値で指定すると整数部を丸めます。
SAMPLE
SELECT
 ROUND(123.456,1) AS SMPL_ROUND
FROM 
 in[1];

TRUNC、TRUNCATE

指定した数値の小数部を切り捨てて整数部だけを返す関数です。

  • TRUNC関数
    TRUNC関数は、指定された数値や日付を指定した精度で切り捨てます。小数点以下の桁を切り捨て、整数部分のみを残します。この関数は主に数値や日付の整数部分を得るために使用されます。
  • TRUNCATE関数
    TRUNCATE関数は、指定された数値を指定した桁数で切り捨てます。小数点以下の桁を切り捨て、指定された桁数の精度に丸めます。この関数は主に数値を特定の桁数で丸めるために使用されます。
TRUNC(value, precision)
TRUNCATE(value, precision)
-- valueは切り捨てる値を指定します
-- precisionは小数点以下の桁数を指定します。
-- precisionを省略した場合は整数部分のみを返します。
SAMPLE
SELECT
 TRUNC(123456.789,1) AS SMPL_TRUNC ,
 TRUNCATE (123456.789,1) AS SMPL_TRUNCATE 
FROM 
 in[1];

TO_NUMBER（文字）

文字から10進数へ変換しますがSQL標準のCAST式が推奨されています。

TO_NUMBER(string)
-- string: 数値に変換したい文字列。
SAMPLE
-- 小数点の
SELECT
 TO_NUMBER('1234567')  AS SMPL_TO_NUMBER1  ,
 TO_NUMBER('1234567.222')  AS SMPL_TO_NUMBER2 -- Integer NG
FROM 
 in[1];

WIDTH_BUCKET

数値の範囲を複数のバケット（区間）に分割し、指定された値がどのバケットに入るかを特定するための関数です。

WIDTH_BUCKET(value, lower_bound, upper_bound, num_buckets)
-- value: バケットに入れる数値。
-- lower_bound: 最小値を表す数値。
-- upper_bound: 最大値を表す数値。
-- num_buckets: 分割するバケットの数。

日付系関数

TIMEZONE

データベースのTIMEZONEの時刻を出力します。

SAMPLE
CALL TIMEZONE();

現在日時

現在日付、時間を取得する関数は複数あります。

関数 取得型
CURRENT_DATE DATE
CURRENT_TIME TIME WITH TIME ZONE 10
LOCALTIME TIME
CURRENT_TIMESTAMP TIMESTAMP WITH TIME ZONE 10
LOCALTIMESTAMP TIMESTAMP
NOW TIMESTAMP
CURDATE DATE
CURTIME TIME
SYSDATE TIMESTAMP
TODAY DATE
SAMPLE
SELECT 
 CURRENT_DATE AS SMPL_CURRENT_DATE ,
 TO_CHAR(CURRENT_TIME) AS SMPL_CURRENT_TIME ,
 LOCALTIME AS SMPL_LOCALTIME ,
 TO_CHAR(CURRENT_TIMESTAMP )AS SMPL_CURRENT_TIMESTAMP ,
 NOW AS SMPL_NOW ,
 CURDATE AS SMPL_CURDATE ,
 CURTIME AS SMPL_CURTIME ,
 SYSDATE AS SMPL_SYSDATE ,
 TODAY AS SMPL_TODAY 
FROM
 in[1];

EXTRACT

EXTRACT関数は、日付や時刻の特定の部分（年、月、日、時、分、秒など）を取り出すための関数です。

EXTRACT(field FROM source)
source 取得値
YEAR 1 ～ 9999
MONTH 1 ～ 12
DAY_OF_MONTH 1 ～ 31
HOUR 0 ～ 23
MINUTE 0 ～ 59
SECOND 0 ～ 60（小数あり
DAY_OF_WEEK 1 ～ 7 （日曜が0）
DAYNAME Sunday ～ Saturday
DAY_OF_YEAR 1 ～ 366
NAME_OF_MONTH January ～ December
QUARTER 1 ～ 4
SECONDS_SINCE_MIDNIGHT 0 ～ 6399
WEEK_OF_YEAR 1 ～ 54

HSQLDBはINTERVAL型を使用でき、EXTRACTでもINTERVAL型を使用可能です、
INTERVAL型は、日付や時間の間隔を表現するためのデータ型である日付や時刻から別の日付や時刻までの時間的な差を表すために使用されます。

SAMPLE
SELECT
 EXTRACT(DAY FROM INTERVAL '2 03:45:30' DAY TO SECOND) AS SMPL_EXTRACTDAY,
 EXTRACT(HOUR FROM INTERVAL '2 03:45:30' DAY TO SECOND) AS SMPL_EXTRACTHOUR,
 EXTRACT ( DAY_OF_WEEK FROM CURRENT_TIMESTAMP ) AS SMPL_DAY_OF_WEEK ,
 EXTRACT ( WEEK_OF_YEAR FROM CURRENT_TIMESTAMP ) AS SMPL_WEEK_OF_YEAR ,
 EXTRACT ( DAY_OF_YEAR FROM CURRENT_TIMESTAMP ) AS SMPL_DAY_OF_YEAR ,
 EXTRACT ( DAY_OF_MONTH FROM CURRENT_TIMESTAMP ) AS SMPL_DAY_OF_MONTH ,
 EXTRACT ( DAY_NAME FROM CURRENT_TIMESTAMP ) AS SMPL_DAY_NAME
FROM
 in[1];

LAST_DAY

指定された日付の月の最終日を返す関数です。

LAST_DAY( date )
-- date: 対象の日付を指定。
SAMPLE
SELECT
 LAST_DAY (CURRENT_DATE) AS SMPL_LAST_DAY
FROM
 in[1];

NEXT_DAY

指定された日付の次の特定の曜日の日付を返す関数です。

NEXT_DAY( date , dayname)
-- date: 対象の日付を指定。
-- dayname: 対象の曜日を指定
SAMPLE
SELECT
 NEXT_DAY(CURRENT_DATE, 'WEDNESDAY') AS SMPL_NEXT_DAY
FROM
 in[1];

ADD_MONTHS

指定された日付に指定された月数を追加した日付を計算します。指定された日付に対して、正の整数であれば月を加算、負の整数であれば月を減算し、対象日を対象月の末日に調整します。

ADD_MONTHS( date , interval)
-- date: 対象の日付を指定。
-- interval: 加減算の月数

日付の加算でMONTHを利用した加減算を行うと同様に月の加減算が行えますが、こちらは2月などの短い月の最終日に使用すると、対象月の同じ日を含む日付が返されます。

SAMPLE
SELECT 
  ADD_MONTHS ( DATE '2024-02-29' , 1) SMPL_ADDMONTHS,
  DATE '2024-02-29' + 1 MONTH SMPL_MOMTH
FROM
 in[1];
SMPL_ADDMONTHS SMPL_MOMTH
2024-03-31T00:00:00.000 JST 2024-03-29T00:00:00.000 JST

MONTHS_BETWEEN

2つの日付間の月数を計算します、最初の日付から2番目の日付までの月数を返し、場合によっては小数を含めて返します。両方の日付が同じ日である場合、または月の最終日である場合、結果は正確な数値を返します。

MONTHS_BETWEEN(date1, date2)
-- date: 比較する日付を指定。
SAMPLE
SELECT 
 MONTHS_BETWEEN (TO_DATE('2100-01-01','YYYY-MM-DD'),CURRENT_DATE) SMPL_MONTHS_BETWEEN
FROM
 in[1];

DATE_ADD、DATE_SUB

日付の加算。減算を行います。INTERVALを指定し任意の要素の加減算を行えます。

DATE_ADD(datetime, interval)
DATE_SUB(datetime, interval)
-- datetime: 対象の日時
-- interval: 加算する値
SAMPLE
SELECT 
DATE_ADD(DATE '2024-02-25',1) SMPL_DATE_ADD1
,DATE_ADD ( DATE '2024-11-22', INTERVAL 3 MONTH )SMPL_DATE_ADD2
,DATE_SUB ( TIMESTAMP '2024-11-22 20:30:40', INTERVAL 20 HOUR ) SMPL_DATE_SUB
FROM
 in[1];

DATEADD

指定された日付や時間に対して、特定の時間単位（年、月、日、時間、分、秒、ミリ秒）を追加するための関数です

DATEADD ( field, numeric, datetime)
-- field: 対象の要素を指定します。
-- YY | YEAR | MM | MONTH | DD | DAY | HH | HOUR | MI | MINUTE | SS | SECOND | MS | MILLISECOND
-- numeric: 追加する量を指定します。
-- datetime: 対象の日時を指定します。
SAMPLE
SELECT 
 DATEADD  ( 'MONTH', 3, DATE '2024-11-22' ) SMPL_DATEADD
FROM
 in[1];

DATEDIFF

2つの日付や時間の差を特定の時間単位で計算する関数です。

DATEDIFF ( field, datetime1, datetime2)
-- field: 対象の要素を指定します。
-- YY | YEAR | MM | MONTH | DD | DAY | HH | HOUR | MI | MINUTE | SS | SECOND | MS | MILLISECOND
-- datetime: 比較対象の日時を指定します。
SAMPLE
SELECT 
 DATEDIFF ( 'HOUR', TIMESTAMP '2024-02-01 20:30:40',CURRENT_TIMESTAMP) AS SMPL_DATEDIFF
FROM
in[1];

ROUND

指定された日付または時間を指定された単位に丸めるための関数です。

ROUND(datetime , field)
-- datetime: 対象の日時を指定します。
-- field: 対象の要素を指定します。
-- YY | MM | DD | HH | MI | SS
SAMPLE
SELECT 
 ROUND(SYSDATE,'YY' ) YY ,
 ROUND(SYSDATE,'MM' ) MM ,
 ROUND(SYSDATE,'DD' ) DD ,
 ROUND(SYSDATE,'HH' ) HH ,
 ROUND(SYSDATE,'MI' ) MI ,
 ROUND(SYSDATE,'SS' ) SS
FROM
 in[1];

TRUNC

指定された日付や時刻を指定された単位に切り捨てるための関数です。

TRUNC(datetime , field)
-- datetime: 対象の日時を指定します。
-- field: 対象の要素を指定します。
-- YY | MM | DD | HH | MI | SS
SAMPLE
SELECT 
 TRUNC(SYSDATE,'YY' ) YY ,
 TRUNC(SYSDATE,'MM' ) MM ,
 TRUNC(SYSDATE,'DD' ) DD ,
 TRUNC(SYSDATE,'HH' ) HH ,
 TRUNC(SYSDATE,'MI' ) MI ,
 TRUNC(SYSDATE,'SS' ) SS 
FROM
 in[1];

型変換関数

CAST

データ型を別のデータ型に変換するための関数です。例えば、文字列を数値に変換する、日付を文字列に変換する、などの場面で利用されます。

CAST(expression AS data_type)
-- expression: 変換したい値や式。
-- data_type: 変換後のデータ型。
データ型 サンプル
BOOLEAN CAST('true' AS BOOLEAN)
INTEGER CAST('123' AS INTEGER)
DECIMAL CAST('123.45' AS DECIMAL)
DOUBLE CAST('123.45' AS DOUBLE)
VARCHAR(n) CAST('hello' AS VARCHAR(10))
CHAR(n) CAST('abc' AS CHAR(5))
DATE CAST('2024-02-20' AS DATE)
TIME CAST('12:34:56' AS TIME)
TIMESTAMP CAST('2024-02-20 12:34:56' AS TIMESTAMP)
SAMPLE
SELECT CAST('true' AS BOOLEAN) AS BOOLEAN_TRUE,
       CAST('123' AS INTEGER) AS INTEGER,
       CAST('123.45' AS DECIMAL) AS DECIMAL,
       CAST('123.45' AS DOUBLE) AS DOUBLE,
       CAST('hello' AS VARCHAR(10)) AS VARCHAR_SAMPLE,
       CAST('abc' AS CHAR(5)) AS CHAR_SAMPLE,
       CAST('2024-02-20' AS DATE) AS DATE_SAMPLE,
       CAST('12:34:56' AS TIME) AS TIME_SAMPLE,
       CAST('2024-02-20 12:34:56' AS TIMESTAMP) AS TIMESTAMP_SAMPLE
FROM
in[1];

CONVERT

型の変換を行う関数です。JDBCのエスケープ関数であり、SQL標準のCAST式に相当します。

CONVERT ( value , datatype )
合成名 ASTERIA Warp データ型 　メモ
SQL_BIGINT Integer -9,223,372,036,854,775,808 から 9,223,372,036,854,775,807
SQL_INTEGER Integer -2,147,483,648 から 2,147,483,647
SQL_SMALLINT Integer -32,768 から 32,767
SQL_TINYINT Integer -128 から 127
SQL_DECIMAL Decimal
SQL_NUMERIC Decimal
SQL_DOUBLE Double
SQL_FLOAT Double
SQL_REAL Double
SQL_VARCHAR String
SQL_CHAR String 256文字
SQL_NVARCHAR String
SQL_DATE DateTime yyyy-mm-dd
SQL_TIME DateTime hh24:mi:ss
SQL_TIMESTAMP DateTime yyyy-mm-dd hh24:mi:ss
SQL_BOOLEAN Boolean 0:true,1:false
SQL_BIT Boolean 1:true,0:false
SAMPLE
SELECT CONVERT('true' , SQL_BOOLEAN) AS BOOLEAN_TRUE,
       CONVERT('123' , SQL_INTEGER) AS INTEGER,
       CONVERT('123.45',SQL_DECIMAL) AS DECIMAL,
       CONVERT('123.45',SQL_DOUBLE) AS DOUBLE,
       CONVERT('hello', SQL_VARCHAR) AS VARCHAR_SAMPLE,
       CONVERT('abc' ,SQL_CHAR) AS CHAR_SAMPLE,
       CONVERT('2024-02-20' ,SQL_DATE) AS DATE_SAMPLE,
       CONVERT('12:34:56' , SQL_TIME) AS TIME_SAMPLE,
       CONVERT('2024-02-20 12:34:56' , SQL_TIMESTAMP) AS TIMESTAMP_SAMPLE
FROM
 in[1];

日付・時間型の直接指定

DATE型、TIME型、TIMESTAMP型については、それぞれDATE、TIME、TIMESTAMPを指定し書式にそった文字列を指定する事でそのデータ型として扱えます。

SAMPLE
SELECT
 DATE '2024-01-01' ,
 TIME '19:22:33' ,
 TIMESTAMP '2024-01-01 19:22:33'
FROM in[1];

TO_CHAR（数値）

数値を文字列に変換します。書式の指定は行えませんので数値を書式を指定した文字列にしたい場合は、ASTERIA Warp側で数値のフォーマットを行います。

SAMPLE
SELECT
 TO_CHAR(12345.6789)
FROM
 in[1];

TO_CHAR（日付）

日付や時刻を指定された書式で文字列に変換するための関数です。

TO_CHAR(datetime , format)
-- datetime: 対象の日時を指定します。
-- format: 出力する書式を指定します。

入力：2024-02-25T16:34:40.000 JST

フォーマット 説明 出力
YYYY 年（4桁） 2024
YY 年（下2桁） 24
MM 月（2桁） 02
MON 月の短縮名（英字3文字） Feb
MONTH 月の完全名 February
DD 日（2桁） 25
DAY 曜日の完全名（英字） Sunday
HH 時間（12時間制、2桁） 04
HH12 時間（12時間制、2桁） 04
HH24 時間（24時間制、2桁） 16
MI 分（2桁） 34
SS 秒（2桁） 40
AM または PM 午前/午後 PM
WW 週 8
DY 曜日の短縮名（英字3文字） Sun
DDD 年の日数 56
FF 小数秒 0

書式内で文字を挿入する場合は、ダブルクォーテーションを利用します。

SAMPLE
SELECT
 TO_CHAR(CURRENT_DATE,'yyyy"年"mm"月"dd"日"')
FROM in[1];

和暦書式は指定できないため、和暦への変換が必要な場合はASTERIA Warp側でDateToStringを使用します。

一般関数

条件判定

指定された条件により値を戻す関数が複数種類あります。

関数 構文 説明
CASEWHEN CASEWHEN( 条件 , trueの場合 , falseの場合) CASE文と同等です。
COALESCE COALESCE( 値1, 値2 [,値x] ) 最初の非nullの値が見つかった時点でその値を返します。型は同一でなければなりません。
DECODE DECODE( 評価値, 比較値1, 返却値1 [,比較値X, 返却値X] [,不一致時の値] ) ORACLEのDECODEと同等です。
IFNULL IFNULL( 評価値, Null時値 ) 評価値がnullでない場合はその値を返し、nullであればNuull時値を返します。戻り値の型は最初の値の型と同じです。型の変更を行いません。
ISNULL ISNULL( 評価値, Null時値 ) IFNULLと同様です。
NULLIF NULLIF( 評価値, 比較値 ) 評価値が比較値の値と等しくない場合は評価値を返します。等しい場合はnullを返します。両方の引数の型は同じである必要があります。
NVL NVL( 評価値 , Null時値 ) IFNULLと同様です。
NVL2 NVL2( 評価値 , NotNull時値, Null時値 ) 評価値がnullでない場合は、 NotNull時値を返します。nullの場合は、Null時値 を返します。戻り値の型は、2番目の値の型と同じです。
SAMPLE
SELECT 
 field[1] 
, CASEWHEN (field[1] is null ,'Null','NotNull')
, COALESCE (field[1],'Null')
, DECODE (field[1] , null , 'Null','NotNull')
, IFNULL (field[1] ,'Null')
, ISNULL (field[1] ,'Null')
, NULLIF (field[1] ,'Y')
, NVL (field[1] ,'Null')
, NVL2 (field[1] ,'NotNull','Null')
FROM
 in[1];

結果

関数名 X (null)
CASEWHEN NotNull Null
COALESCE X Null
DECODE NotNull Null
IFNULL X Null
ISNULL X Null
NULLIF X (null)
NVL X Null
NVL2 NotNull Null

GREATEST

与えられた複数の値の中から最大の値を返す関数です。この関数は、数値や日付などの異なる型の値を比較し、最大の値を返します。

GREATEST(value1, value2, ...)
-- value: 比較する値。
SAMPLE
SELECT
 GREATEST(1,2,3,4,5) AS SMPL_GREATEST
FROM
 in[1];

LEAST

与えられた複数の値の中から最小の値を返す関数です。この関数は、数値や日付などの異なる型の値を比較し、最大の値を返します。

LEAST(value1, value2, ...)
-- value: 比較する値。
```SQL:SAMPLE
SELECT
 LEAST(1,2,3,4,5) AS SMPL_LEAST
FROM
 in[1];

UUID

UUID関数は、新しいUUID値を生成します。引数なしで呼び出された場合、16バイトのバイナリ値としてUUID値を返します。16進数文字列の引数を指定すると、その16バイトのバイナリ値をUUID値として返します。16バイトのバイナリまたはUUID値を引数に指定した場合、そのUUID値をフォーマットされた文字列表現として返します。出力ストリームのカラムデータタイプはBinaryを指定します。

UUID ( [char | binary] )
-- char: 16進数文字列の引数
-- binary: 16バイトのバイナリまたはUUID値
SAMPLE
SELECT
 UUID() AS SMPL_UUID
FROM
 in[1];

ROWNUM

処理中の現在の1から始まる行番号を返します。
ROWNUMは結果セットに行が追加されるたびに増分されます。WHERE ROWNUM() < 10のような条件を使用することができますが、ROWNUM() > 10やROWNUM = 10のような条件は使用できません。

SAMPLE
SELECT
 ROWNUM() SMPL_ROWNUM ,
 ROW_NUMBER() OVER() SMPL_ROW_NUMBER
FROM 
 in[1];

どちらも同じものを返します、

式

HSQLDBも他のDBと同様式による記述が可能です。

CASE式

CASE式は単純CASE、検索CASEのどちらも利用可能です。CASEで始まりENDで終わります。

SAMPLE
SELECT
CASE field[1] 
 WHEN '1' THEN 'A'
 WHEN '2' THEN 'B'
 ELSE          'C'
END SMPL_SIMPLE_CASE,
CASE  
 WHEN field[1] = '1' THEN 'A'
 WHEN field[1] = '2' THEN 'B'
 ELSE          'C'
END SMPL_SERCH_CASE
FROM in[1];

NEXT VALUE FOR

HSQLDBのシーケンスオブジェクトのカウントアップを行います。

 NEXT VALUE FOR sequence
-- sequence:シーケンスオブジェクト名

どの様なケースでこれを利用するかですが、通常のRDBのSEQUENCEの利用を検討する様なケースです。具体的には別セッション、別リクエスト、別ユーザ、別フローであっても同一の一意となる連続した数値を取得したい場合に利用する事が考えられます。通常はRDB側でシーケンスオブジェクトを作成し、INSERT時の初期値としてシーケンスオブジェクトから値を取得する等を行いますが、RDBを使用しない場合などで一意値を取得しなければならないようなケースでRecordSQLの戻りカラムにその一意の数値をシーケンスから取得するなどが考えられます。ただし、ASTERIA Warpの再起動を行うと消されますのでアプリケーション変数と同様に初期化処理が必須です。

利用する場合、事前にシーケンスオブジェクトを作成しておく必要があります。

CREATE SEQUENCE sequence_name
    [ START WITH start_value ]
    [ INCREMENT BY increment_value ]
    [ MINVALUE min_value ]
    [ MAXVALUE max_value ]
    [ CYCLE | NO CYCLE ];
SAMPLE
-- SEQ_TESTがあれば削除
DROP  SEQUENCE  IF EXISTS  SEQ_TEST;
--SEQ_TESTを開始5、10づつカウント、最小5 最大105でサイクルするシーケンスを作成
CREATE SEQUENCE SEQ_TEST
     START WITH 5
     INCREMENT BY 10 
     MINVALUE 5
     MAXVALUE 105 
     CYCLE 
;
-- SEQ_TESTの次の値を取得
CALL NEXT VALUE FOR SEQ_TEST;

RecordSQLでCREATEする場合は NEXT VALUEをするフローとはDROP、CREATE文SELECT文は別フローとしてください。同一のRecordSQL内で上記を実行してもエラーとなります。CREATEと NEXT VALUEは別フローとしてください。同一フロー内でCREATE、SELECTを行っても値が取得できません。NEXT VALUEの取得は別フローでおこなってください。CURRENT VALUE式も存在するようですが、カレント値が取得できません。

述語

行値構成子要素が複数の比較述語

行値構成子要素が複数とは、以下の様ものを差します。

 -- 行値構成子要素が1つ
 1 = 2
 -- 行値構成子要素が3つ
( 1, 2, 3 ) = (1, 2, 3)

行値構成子要素が複数の場合の判定

  • ＝
    すべてが＝の場合にtrue
 (1,1) = (1,1)
  • != 、<>
    ひとつ以上!=の場合にtrue
 (1,1) = (1,0)
  • >、>=、 <、 <=
    左から=が成立しない最初の要素が（ >、>=、 <、 <=）の場合にtrue
 (1,1) > (0,2) -- １つ目が 1 > 0 のためtrue
 (1,1) > (1,0) -- １つ目が 1 = 1 で ２つ目が 1 > 0 のためtrue
 (1,1) < (2,0) -- １つ目が 1 < 2 のためtrue
 (1,1) < (1,2) -- １つ目が 1 = 1 で ２つ目が 1 < 2 のためtrue

論理演算との比較

演算子 行値構成子 論理演算
= (A1,A2,A3) = (B1,B2,B3) A1 = B1 AND A2 = B2 AND A3 = B3
!= <> (A1,A2,A3) <> (B1,B2,B3) A1 <> B1 OR A2 <> B2 OR A3 <> B3
> (A1,A2,A3) > (B1,B2,B3) (A1 > B1) OR ( A1 = B1 AND A2 > B2 ) OR ( A1 = B1 AND A2 = B2 AND A3 > B3)
< (A1,A2,A3) < (B1,B2,B3) (A1 < B1) OR ( A1 = B1 AND A2 < B2 ) OR ( A1 = B1 AND A2 = B2 AND A3 < B3)

比較値にNULLがある場合、本来はUNKNOWNですが記述の仕方によりCASTエラーとなります
左辺はNULLが許容され結果はNULLとなります。右辺のNULLはエラーとなります。

-- エラーとならない
 (null,1) = (1,1)
 (1,null) = (1,1)
 (null,null) = (1,1)
-- エラーとなる
 (1,1) = (null,1)
 (null,1) = (null,1)

サブクエリーとの比較も可能です。使用においては、サブクエリーを右辺とする必要があります。
サブクエリーを左辺とした場合、=であればすべてtrue、<>であればすべてfalseで判定されます

SAMPLE
SELECT
 in[1].field[1],
 in[1].field[2]
FROM 
in[1] 
WHERE 
 ( in[1].field[1] ,  in[1].field[2] )  =
   (
     SELECT  in[2].field[1] ,  in[2].field[2]
    FROM in[2] 
   );

テーブル項目の値がNULLの場合その項目の判定はNULLとなります。
上記の場合 in[1].field[2] がNULLの場合そのレコードが条件を満たす事はありません。
in[2].field[2]がNULLであった場合は条件によって変わります

演算子 説明
= in[2].field[2]はNULLのため=とならず（比較対象がNULLであっても）条件は成立しません。
!= in[2].field[2]がNULLであっても、in[2].field[1]の条件で!=が成立する場合はtrueとなります。
> in[2].field[2]がNULLであっても、 in[1].field[1] > in[2].field[1]の条件が成立する場合はtrueとなります。in[1].field[1] = in[2].field[1]の場合、in[2].field[2] > in[2].field[2]が比較対象となりますが、 in[2].field[2]がNULLため比較は不成立となります。結果、in[2].field[1] = in[2].field[1]のものだけが抽出されます。

集約関数

一般的なデータベースで使用可能な集約関数が利用可能です。

  • COUNT
  • MAX
  • MIN
  • SUM
  • AVG
  • EVERY、ANY、SOME
  • GROUP_CONCAT

ARRAY_AGGは使用できません。

COUNTでは、FILTERが使用可能です。

SAMPLE
SELECT
 COUNT(*) FILTER (WHERE field[1] >= 23456)
FROM
 in[1];

GROUP_CONCAT

複数の行の結果を1つの文字列にまとめる際に使用されます。グループ化されたクエリの結果を、1つのフィールドにまとめて表示します。

SAMPLE
SELECT
-- DISTINCTは任意
  GROUP_CONCAT(DISTINCT field[2] SEPARATOR ',') 
FROM
 in[1]
GROUP BY 
 field[1];

最後に

RecordSQLは便利なコンポーネントで頻繁に使われますが、内部のSQLの確認などは非常に手間がかかります。ASTERIA Warpのインストールされているサーバ上であればDBeaverなどを利用し構文のチェックなども行えますす。SELECT文以外で実行する事はほぼないと思いますが、どこかで役に立つ事があるかもしれません。

蛇足

長々と書き連ねてきましたが、HSQLDBはまとまった日本語ドキュメントを探すのにも苦労しますが、最近はもっぱらChatGPTに聞く事が多くなってきています。今回の様なHSQLDBの構文や他のデータベースの構文、正規表現、PwerShellで実行するスクリプトのなどASTERIA Warpの処理を行う中での最初の一歩として利用可能な箇所は非常に多いです。ハルシネーションがあるため必ず確認し、間違っていたら問いただしてとゲロさせましょう。しかし、ASTERIA Warp開発におけるChartGPTなどのAI利用は今後の内製化の一つのキーワードである事は間違いないかと思います。

  1. コネクションタイプでFileを指定した場合、そのフォルダにscriptとして保存され永続化されます。 ↩

  2. Decimalの移送時内部では小数点5位までのカラムが生成される。そのため6以下は丸められる ↩

  3. RDBGetでHSQLDBを参照した場合のカラム判定 ↩

  4. 1:true 0:false ↩ ↩2

  5. 小数部は桁落ち ↩

  6. 時間は00:00:00 ↩

  7. 日付は1970/01/01 ↩

  8. 整数リテラルはINTEGER型として扱われますが、値が収まらない場合はBIGINT型またはDECIMAL型として扱われます。 ↩

  9. ビットリテラルは1ビットのみ指定可能で2桁以上はバイナリの判定となりますがASTERIAにバイナリで戻す事はできません。 ↩

  10. TIME WITH TIME ZONE型はASTERIA Warp未対応なためCASTか文字列にする必要があります。現利用バージョンはミリ秒の精度で返却されます。 ↩ ↩2

2
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
0