Verticaでファンクション機能を利用する方法

1 はじめに
2 2種類のファンクション機能
3 SQLファンクションの使い方
4 ユーザ定義ファンクションの使い方
5 まとめ
6 参考情報
7 検証バージョンについて
8 更新履歴

はじめに

Verticaには汎用的なデータベース製品同様に、ファンクションが提供されています。ファンクションを使うことで、与えられたパラメータで計算やロジックを行い戻り値を返す処理を実行できます。

この記事では2種類のファンクションの使い方についてご紹介します。

2種類のファンクション機能

Verticaでは以下2種類のファンクション機能が提供されています。

(1)SQLファンクション
(2)ユーザ定義ファンクション

SQLファンクションの使い方

以下の例では、myzeroifnullというファンクション名を定義して、NULL値を0（ゼロ）として表示するファンクションの実行手順をまとめています。

1)ファンクションの作成

   CREATE FUNCTION myzeroifnull(x INT) RETURN INT
   AS BEGIN 
     RETURN (CASE WHEN (x IS NOT NULL) THEN x ELSE 0 END); 
   END;

CREATE FUNCTION myzeroifnull(x INT) RETURN INT

AS BEGIN

RETURN (CASE WHEN (x IS NOT NULL) THEN x ELSE 0 END);

END;

2)テーブルの作成

CREATE TABLE tabwnulls(col1 INT);

1	CREATE TABLE tabwnulls(col1 INT);

3)テストデータの挿入

INSERT INTO tabwnulls VALUES(1);
INSERT INTO tabwnulls VALUES(NULL);
INSERT INTO tabwnulls VALUES(0);

SELECT * FROM tabwnulls;
 a
---
 1
　　　★NULL
 0
(3 rows)

INSERT INTO tabwnulls VALUES(1);

INSERT INTO tabwnulls VALUES(NULL);

INSERT INTO tabwnulls VALUES(0);

SELECT * FROM tabwnulls;

---

　　　★NULL

(3 rows)

ファンクションを使わない通常のSELECT文では、上記の結果になります。

4)ファンクションの実行

SELECT myzeroifnull(col1) FROM tabwnulls;
 myzeroifnull 
--------------
          1
          0
          0

SELECT myzeroifnull(col1) FROM tabwnulls;

myzeroifnull

--------------

ファンクションを使った場合にはNULL値を0（ゼロ）として表示しています。

5)その他

集計結果の実行例は以下のとおりです。

【補足】集計結果の例

SELECT COUNT(*) FROM tabwnulls GROUP BY myzeroifnull(col1);
 count
-------
     2
     1
(2 rows)

SELECT COUNT(*) FROM tabwnulls GROUP BY myzeroifnull(col1);

count

-------

(2 rows)

SELECT col1,COUNT(col1) FROM tabwnulls GROUP BY col1,myzeroifnull(col1);
 col1 | COUNT
------+-------
    1 |     1
    0 |     1
      |     0
(3 rows)

SELECT col1,COUNT(col1) FROM tabwnulls GROUP BY col1,myzeroifnull(col1);

col1 | COUNT

------+-------

1 | 1

0 | 1

| 0

(3 rows)

ユーザ定義ファンクションの使い方

ユーザ定義ファンクション（User Defined Function=UDF）は、ユーザが独自に関数を定義する機能です。
C++、Java、Rを使用してCREATE FUNCTION 文で定義します。
※事前にコンパイルを実施してから下記手順を実行してください。

1)ライブラリの作成

CREATE LIBRARY ScalarFunctions AS '/opt/vertica/sdk/examples/build/ScalarFunctions.so';

1	CREATE LIBRARY ScalarFunctions AS '/opt/vertica/sdk/examples/build/ScalarFunctions.so';

2)定義したライブラリを使用したUDFの作成

CREATE FUNCTION Add2Ints AS LANGUAGE 'C++' NAME 'Add2IntsFactory' LIBRARY ScalarFunctions;

1	CREATE FUNCTION Add2Ints AS LANGUAGE 'C++' NAME 'Add2IntsFactory' LIBRARY ScalarFunctions;

3)ユーザ定義ファンクションの状態確認

SELECT * FROM USER_FUNCTIONS WHERE function_name='Add2Ints';
-[ RECORD 1 ]----------+------------------------------------------------------------
schema_name            | public
owner                  | dbadmin
function_name          | Add2Ints
procedure_type         | User Defined Function
function_return_type   | Integer
function_argument_type | Integer, Integer
function_definition    | Class 'Add2IntsFactory' in Library 'public.ScalarFunctions'
volatility             | volatile
is_strict              | f
is_fenced              | t
comment                |

SELECT * FROM USER_FUNCTIONS WHERE function_name='Add2Ints';

-[ RECORD 1 ]----------+------------------------------------------------------------

schema_name | public

owner | dbadmin

function_name | Add2Ints

procedure_type | User Defined Function

function_return_type | Integer

function_argument_type | Integer, Integer

function_definition | Class 'Add2IntsFactory' in Library 'public.ScalarFunctions'

volatility | volatile

is_strict | f

is_fenced | t

comment |

4)ユーザ定義ファンクションの実行例

SELECT Add2Ints(23,19);
 Add2Ints
----------
       42
(1 row)

SELECT Add2Ints(23,19);

Add2Ints

----------

(1 row)

上記例はAdd2Ints(int_a,int_b)で指定したint_a + int_bを計算するUDFです。

まとめ

消費税の計算などの定型的な処理は、ファンクションを使うことで処理を効率化できます。是非ご活用ください。

参考情報

以下マニュアルも合わせてご確認ください。
CREATE FUNCTION (SQL Functions)

CREATE FUNCTION (UDF)

検証バージョンについて

この記事の内容はVertica 9.2で確認しています。

更新履歴

2019/08/01　検証バージョンを9.2に変更
2016/06/10　本記事を公開

MENU