運営会社
AlteryxのIn-DBの関数の機能をMicrosoft SQL Serverで試す
In-DBはデータベース側で処理をさせる機能です。Alteryxは基本的に一度すべてのデータをメモリに読み込んで処理を行うため、データ量が莫大である場合はデータベースからデータを引っ張ってくるのに時間を要してしまいます。データ量が莫大である場合は、In-DBで処理を行うことで処理時間の短縮が可能です。ただし、データベース側の性能に依存するため、データベースに負荷をかけても問題ないか、など気にする必要があります。
なお、In-DBを使わないかつデータがメモリに乗り切らない場合は、通常のソフトウェアと同様、ストレージから逐次データを読み込んで処理を行います（データがメモリに乗り切らないからと言って処理ができないわけではありません）。
AlteryxのIn-DB機能は、基本的なETL処理ができるツールが揃っていますが、標準のツールと比べると数が少なく、若干使い勝手も異なります。特に、フォーミュラIn-DBについてはほぼデータベース側の機能やSQLの書式に依存し、公式のヘルプでも情報が少ないかと思います。
今回は、MicrosoftのSQL ServerでフォーミュラIn-DBの関数を見ていきたいと思います。なお、関数はフォーミュラIn-DBやフィルターIn-DBなどで利用可能です。
なお、本記事ではすべての関数を紹介しているわけではないのでご注意ください。
In-DBで関数を使う時の基本
以下、In-DBで関数を使うにあたって、基本的な知識となります。
- フィールド名はダブルクォーテーション「"」で囲む
- 文字列はシングルクォテーション「'」で囲む
- 数字は囲わない
- ヌルは「NULL」と記載する
引っかかりやすいポイント
実際にIn-DB機能を使う上で、Alteryxに慣れているけどIn-DBに慣れていないとひっかかりやすいポイントをいくつか挙げます。
- 「含む」を意味するContain関数はありません。Like演算子で対応しましょう
- ワイルドカードは「%」です
- 値とフィールド名の区別はシングルクォテーションとダブルクォーテーションでつけます。値はシングル、フィールド名はダブルですが、値の場合は型によってシングルクォーテーションが不要になります（数値型は不要、それ以外は必要）
- 条件分岐はCASE文を使うことになります（IIFも使えますが複数条件を書くのがめんどくさいのと、IIFはSQL Server独自関数です）
- 正規表現は使えません
- 文字列の連結はCONCATもしくはCONCAT_WSで行います（項目が多い場合は区切り文字を指定できるCONCAT_WSが便利かもしれません）
- ヌルは「NULL」と記載しますが、Nullかどうかの比較は「"フィールド名" IS NULL」です。
- フォーミュラIn-DB内で新規作成したフィールドを、同じフォーミュラIn-DBツール内で使用できません。後段にフォーミュラIn-DBツールを接続し、そこで利用することは可能です。
これが全てではありませんが、参考にして頂ければと思います。
数学関連
数学関連の関数です。標準的によく使われる関数が揃っています。
| 関数名 | 書式 | 動作 | 同等のAlteryx関数 |
|---|---|---|---|
| ABS | ABS("数値フィールド") | 指定したフィールド（数値型）の値の正の絶対値を取得します。 | Abs |
| ACOS | ACOS("数値フィールド") | 指定したフィールド（Float型）の値のアークコサイン（逆余弦）をラジアン単位で取得します。 | Acos |
| ASIN | ASIN("数値フィールド") | 指定したフィールド（Float型）の値のアークサイン（逆正弦）をラジアン単位で取得します。 | Asin |
| ATAN | ATAN("数値フィールド") | 指定したフィールド（Float型）の値のアークタンジェント（逆正接）をラジアン単位で取得します。 | Atan |
| ATN2 | ATN2("数値フィールドX","数値フィールドY") | 正のx軸と指定した数値フィールド（Y、X）までの線の間の角度をラジアンで取得します。 | Atan2 |
| CEILING | CEILING("数値フィールド") | 指定したフィールド（数値型）の値より大きい最小の整数を取得します。 | Ceil |
| COS | COS("数値フィールド") | 指定したフィールド（Float型）の値のコサイン（余弦）をラジアン単位で取得します。 | Cos |
| COT | COT("数値フィールド") | 指定したフィールド（Float型）の値のコタンジェントを取得します。 | - |
| DEGREES | DEGREES("数値フィールド") | 指定したフィールド（数値型）の値のラジアン単位を度の単位に変換します。 | - |
| EXP | EXP("数値フィールド") | 指定したフィールド（Float型）の値の指数値（底をeとするべき乗）を取得します。 | Exp |
| FLOOR | FLOOR("数値フィールド") | 指定したフィールド（数値型）の値以下の最大の整数を取得します。 | Floor |
| LOG | LOG("数値フィールド") | 指定したフィールド（Float型）の値の自然対数を取得します。 | Log |
| LOG10 | LOG10("数値フィールド") | 指定したフィールド（Float型）の値の底10とした対数を取得します。 | Log10 |
| PI | PI() | PIの定数値（円周率）を取得します | PI |
| POWER | POWER("数値フィールド",乗数) | 指定したフィールド（Float型）の値に指定した乗数でべき乗の計算を行います。 | Pow |
| RADIANS | RADIANS("数値フィールド") | 指定したフィールド（数値型）の値の度単位をラジアン単位に変換します。 | - |
| RAND | RAND() | 0～1の疑似乱数を生成します。オプションとしてSeed値を指定することも可能です。例：RAND(100) | Rand |
| ROUND | ROUND("数値フィールド",有効桁数) | 指定したフィールド（数値型）の値を指定した有効桁数に丸めた数値を取得します。長さがマイナスの場合は整数側を丸めます。 | Round |
| SIGN | SIGN("数値フィールド") | 指定したフィールド（数値型）の値が正であれば1、負であれば-1、ゼロを返します | - |
| SIN | SIN("数値フィールド") | 指定したフィールド（Float型）の値のサイン（正弦）をラジアン単位で取得します。 | Sin |
| SQRT | SQRT("数値フィールド") | 指定したフィールド（Float型）の値の平方根を取得します。 | Sqrt |
| SQUARE | SQUARE("数値フィールド") | 指定したフィールド（Float型）の値の２乗を取得します。 | - |
| TAN | TAN("数値フィールド") | 指定したフィールド（Float型）の値のタンジェント（正接）をラジアン単位で取得します。 | Tan |
文字列関連
文字列操作の関数です。
| 関数名 | 書式 | 動作 | 同等のAlteryx関数 |
|---|---|---|---|
| LEN | LEN("文字列フィールド") | 指定したフィールドの値の長さを取得します。 | Length |
| LEFT | LEFT("文字列フィールド", N) | 指定したフィールドの値の左からN文字を取得します | Left |
| RIGHT | RIGHT("文字列フィールド", N) | 指定したフィールドの値の右からN文字を取得します | Right |
| SUBSTRING | SUBSTRING("文字列フィールド", N1,N2) | 指定したフィールドのN1文字目からN2文字分の文字を取得します | SubString |
| STUFF | STUFF("文字列フィールド", 開始位置, 削除文字数, '挿入文字列') | 指定したフィールドから開始位置から削除文字数分文字列を削除し、その後挿入文字列を挿入します | - |
| REPLACE | REPLACE("文字列フィールド",'検索文字列','置換文字列'); | 指定したフィールドの値を検索文字列で検索し、一致するものがあれば置換文字列で置き換えます | Replace |
| CONCAT | CONCAT("フィールド1","フィールド2",,,,"フィールドN") | 指定したフィールドの文字列を連結します。数値なども文字列として扱われます。 | - |
| CONCAT_WS | CONCAT_WS('区切り文字',"フィールド1","フィールド2",,,,"フィールドN") | 指定したフィールドの文字列を指定した区切り文字を用いて連結します。数値なども文字列として扱われます。 | - |
| TRIM | TRIM('削除文字',"文字列フィールド") | 指定したフィールドの値の先頭および後尾から空白もしくは指定した文字を削除します。※削除文字はオプションです | Trim |
| LTRIM | LTRIM("文字列フィールド") | 指定したフィールドの値の先頭の空白を削除します。 | TrimLeft |
| RTRIM | RTRIM("文字列フィールド") | 指定したフィールドの値の末尾の空白を削除します。 | TrimRight |
| FORMAT | FORMAT("フィールド",'書式','カルチャ') | 指定したフィールドを指定の書式に変換します。カルチャはオプションで、ロケール指定のイメージで捉えてください。数値の0埋めなども可能です。 | PadLeft、ToString、DateTimeFormat |
| STR | STR("数値フィールド", 長さ, 小数部桁数) | 指定した数値フィールドを指定した小数部文字数で指定した長さの文字列にします（小数点も1文字にカウントします） | ToString |
| UPPER | UPPER("文字列フィールド") | 指定したフィールドを大文字にしたものを取得します。 | UpperCase |
| LOWER | LOWER("文字列フィールド") | 指定したフィールドを小文字にしたものを取得します。 | LowerCase |
| REVERSE | REVERSE("文字列フィールド") | 指定したフィールドの値を反転します。 | ReverseString |
| REPLICATE | REPLICATE('繰り返し文字',回数) | 指定した文字を指定した回数分繰り返します。 | - |
| SPACE | SPACE(回数) | 空白文字を指定した回数分繰り返します。 | - |
サンプル
FORMAT
FORMAT("フィールド",'書式','カルチャ')入力サンプルに対して以下のような計算式で試しました。入力されたデータに対して、2桁になるように1桁の数値は0埋めします。
FORMAT("Number",'00')※新規項目として「Number_Str」というフィールドを文字列型（V_WString）で作成しています
入力データ
出力結果サンプル
日付関連
日付関連の関数です。型変換のCONVERT関数は組み込まれた形式のみ対応しているので、ここは要注意ポイントです（ロケールの影響も受けるので要注意です）。
| 関数名 | 書式 | 動作 | 同等のAlteryx関数 |
|---|---|---|---|
| GETDATE | GETDATE() | 今の日付と時間をDateTime型で取得します。 | DateTimeNow |
| DATEADD | DATEADD(単位,期間,"日時型フィールド") | 日時型フィールドの値に指定した期間を足します。単位の指定の必要があります（月ならばMONTH）。 | DateTimeAdd |
| DATEDIFF | DATEDIFF(単位,"開始","終了") | ２つの日付型フィールドの期間を計算します。単位の指定が必要です（月ならばMONTH）。 | DateTimeDiff |
| DATEFROMPARTS | DATEFROMPARTS("年","月","日") | 年、月、日の数値フィールドからDate型の年月日を取得します | - |
| DATEPART | DATEPART(単位,"日時型フィールド") | 日時型フィールドから指定した単位で抜き出します。 | DateTimeFormat |
| YEAR | YEAR("日時型フィールド") | 日時型フィールドから年だけ抜き出します | DateTimeYear |
| MONTH | MONTH("日時型フィールド") | 日時型フィールドから月だけ抜き出します | DateTimeMonth |
| DAY | DAY("日時型フィールド") | 日時型フィールドから日だけ抜き出します | DateTimeDay |
| EOMONTH | EOMONTH("日時型フィールド",追加月数) | 日時型フィールドの値の月の最終日を取得します。このとき、追加月数で指定した月を足して取得します（-1で前月、+1で次月となります）。 | DateTimeLastOfMonth |
| CONVERT | CONVERT(DATE,"文字列フィールド") | 指定した文字列フィールドをDate型に変換します。変換できるフォーマットは特定の形式に限られます（いくつかの埋め込みの形式があり、合致していれば変換されます）。※Convert関数は、型変換の関数です（DATE型以外でも利用可能です） | DateTimeParse |
| CAST | CAST("文字列フィールド" AS DATE) | CONVERT関数と同様です。 | DateTimeParse |
サンプル
以下の入力データを使ってフォーミュラIn-DBツールでいくつかの関数の結果を見ていきましょう。
CONVERT
CONVERT(DATE,"文字列フィールド")
入力サンプルに対して以下のような計算式で試しました。
CONVERT(DATE,"InputDate")※新規項目として「InputDate_Date」というフィールドをDate型で作成しています
出力結果サンプル（T出力側）
GETDATE
GETDATE()入力サンプルに対して上記のような計算式で試しました。
※新規項目としてTodayというフィールド名をDate型で作成しています
出力結果サンプル（T出力側）
DATEDIFF
DATEDIFF(単位,"開始","終了")CONVERT、GETDATEの結果は以下の通りでした。
これに対して、以下のような計算式を適用します。
DATEDIFF(DAY,"InputData_Date","Today"※新規項目として「今日までの日数」というフィールドをInt64型で作成しています
出力結果サンプル（T出力側）
条件分岐
条件分岐の式です。基本的にはCASE文を使っていくことになります。
| 関数名 | 書式 | 動作 |
|---|---|---|
| CASE | １．単純CASE文 CASE "フィールド名" WHEN '値1' THEN '結果1' WHEN '値2' THEN '結果2' ELSE '結果3' END２．検索CASE文 CASE WHEN '条件1' THEN '結果1' WHEN '条件2' THEN '結果2' ELSE '結果3' END |
CASEは単純CASE文と検索CASE文に分かれます。
単純CASE文は、特定のフィールドの値に対して一致すれば値を返します。 検索CASE文は、条件を記載し、一致すればその値を返します。 単に一致するかどうかで条件分岐するのが単純CASEで、条件を記載し一致するかどうかで条件分岐するのが検索CASEです。 |
| IIF | IIF(条件,条件が真の戻り値,条件が偽の戻り値) | 条件を指定することで、Trueの時は真の戻り値、Falseの時は偽の戻り地を返します。なお、IIF関数はSQL Server独自の関数です。 |
サンプル
以下の入力データを使ってフォーミュラIn-DBツールで各計算式を使って結果を見ていきます。
CASE（単純CASE）
CASE "フィールド名"
WHEN '値1' THEN '結果1'
WHEN '値2' THEN '結果2'
ELSE '結果3'
END
入力サンプルに対して以下のような計算式で試しました。各月名を数値に置き換える処理をおこなっています。出力として、「Month_Int」というフィールドをInt64型で出力しています。
CASE "Month"
WHEN 'Jan' THEN 1
WHEN 'Feb' THEN 2
WHEN 'Mar' THEN 3
WHEN 'Apr' THEN 4
WHEN 'May' THEN 5
WHEN 'Jun' THEN 6
WHEN 'Jul' THEN 7
WHEN 'Aug' THEN 8
WHEN 'Sep' THEN 9
WHEN 'Oct' THEN 10
WHEN 'Nov' THEN 11
WHEN 'Dec' THEN 12
ELSE NULL
END出力結果サンプル（T出力側）
CASE（検索CASE）
CASE
WHEN '条件1' THEN '結果1'
WHEN '条件2' THEN '結果2'
ELSE '結果3'
END
入力サンプルに対して以下のような計算式で試しました。四半期を計算しています。出力として、「四半期」というフィールドをInt64型で出力しています。
CASE
WHEN "Month_Int" >=1 AND "Month_Int" <=3 THEN 4
WHEN "Month_Int" >=4 AND "Month_Int" <=6 THEN 1
WHEN "Month_Int" >=7 AND "Month_Int" <=9 THEN 2
WHEN "Month_Int" >=10 AND "Month_Int" <=12 THEN 3
ELSE NULL
END
出力結果サンプル（T出力側）
論理演算子
論理演算子です。主にフィルターIn-DBツールか、CASE文、IIF文と組み合わせて利用されます。
| 関数名 | 書式 | 動作 |
|---|---|---|
| LIKE | "文字列フィールド" LIKE '検索文字列' | 文字列フィールドの値が、検索文字列で指定したパターンと一致するかどうかを判断します。検索文字列は通常の文字列とワイルドカードが指定できます。%：0個以上の任意の文字列、_：任意の1文字、[]：指定した範囲もしくセット内の任意の1文字、[^]：[]の否定形となり、指定した範囲もしくはセット内以外の1文字の意味 |
| IN | "文字列フィールド" IN ('検索文字列1','検索文字列2'....) | 文字列フィールドの値がINの後のカッコ内のいずれかの検索文字列と一致するかどうかを判断します。 |
| BETWEEN | "数値フィールド" BETWEEN 範囲下限値 AND 範囲上限値 | 数値フィールドの値が、範囲上限値と範囲下限値の間にあるかどうかを判断します。数値以外でも利用可能です。 |
サンプル
以下の入力データを使ってフィルターIn-DBツールで各計算式を使って結果を見ていきます。
LIKE
"文字列フィールド" LIKE '検索文字列'
入力サンプルに対して以下のような計算式で試しました。最初の文字がaで始まる物のみが抽出されます。
"MonthName" LIKE 'a%'出力結果サンプル（T出力側）
※アルファベットの大文字・小文字は区別されていません
IN
"文字列フィールド" IN ('検索文字列1','検索文字列2'....)
入力サンプルに対して以下のような計算式で試しました。
"MonthName" IN ('Jan','Dec')出力結果サンプル（T出力側）
BETWEEN
"数値フィールド" BETWEEN 範囲下限値 AND 範囲上限値
入力サンプルに対して以下のような計算式で試しました（数値で範囲を指定して抜き出す）。
"Month" BETWEEN 1 AND 3出力結果サンプル（T出力側）
こちらは、BETWEEN句を文字列に適用した例です。
"MonthName" BETWEEN 'A' AND 'G'出力結果サンプル（T出力側）
※MonthNameの頭の文字がA～Gのレコードが抜き出されました
こちらはインプットが異なりますが、日時データでBETWEEN句を使用した例です。
'2011-01-02' BETWEEN "StartDate" AND "EndDate"入力サンプル
出力結果サンプル（T出力側）
参考リンク
- Microsoft SQL Server関数リファレンス fa-external-link
- W3Schools SQL Server（英語の教育サイト）fa-external-link
※Alteryx Designer v2021.3.3.63061時点の情報です
