カスタム関数を使ってみよう
今回は、計算フィールドで使用することができるカスタム関数についてご紹介します。
カスタム関数は、Yellowfinの計算フィールドで使用できる機能で、あらかじめ計算式をYellowfinに定義しておくと、引数を指定するだけでその計算式を使用することができる、というものです。カスタム関数では、計算フィールドでは作成できないような式を定義することもできますし、よく使う計算式を定義しておくことによって計算フィールドを作成するときの手間を省くこともできる便利な機能です。
◎製品資料をCheck！Yellowfinについて理解を深めよう↓
カスタム関数の仕組み
カスタム関数では、データベースの関数を使用した式を定義することができます。算術関数を用いて分析に必要な数値を計算して求めたり、文字列関数を使用して集計に必要な軸を作成したりできるのですが、使用しているデータベースの種類によって使える関数が異なるので、注意が必要です。
custom-functions.xmlには、すでにいくつかのカスタム関数が定義されています。まずは、既存のカスタム関数がどのように定義されているのか、例としてRatioの内容を確認してみましょう。
上の図の、9〜34行目でひとつの関数が定義されています。個々のタグの説明はwikiに譲るとして、関数の引数としてNumerator（分子）とDenominator（分母）が宣言されていることがわかります。Ratioの計算式は<sql>タグに囲まれている部分です。計算式には「<」や「>」を比較演算子として使用することがありますので、CDATAセクションで囲って記述するようにしています。
計算式の内容については、難しい計算をしているわけではないようですね。比率を求める計算式で、わり算の分母がゼロのときに発生するゼロ除算のエラーを回避するものになっています。Ratioの計算式は、計算フィールドのシンプル式でも作成できる内容ではあるのですが、比率の計算をおこなうたびに毎回CASE文を作成するのって地味に手間がかかりますよね。カスタム関数を使用すれば、引数のNumerator（分子）とDenominator（分母）に渡すフィールドを設定するだけで比率の計算フィールドを作成することができるので、レポート作成の手間を省くことができるのかなと思います。
シンプル式では作成できないような式を定義できることもカスタム関数の魅力のひとつですが、例として挙げたRatioのように、よく使う計算式をあらかじめ定義しておくことでレポート作成の手間が省けることもカスタム関数の魅力と言えます。
カスタム関数を作ってみよう
データソースには、PostgreSQLのサンプルデータベースを使用し、YellowfinのwikiとPostgreSQLのドキュメントを参考にしながら、PostgreSQLで使えるカスタム関数を作成してみたいと思います。
今回は、PostgreSQLのウィンドウ関数を使用してグループごとの合計値を算出するカスタム関数を作ってみました。
| <function> <name>グループごとの合計</name> <argument> <index>1</index> <name>数値</name> <datatype>numeric</datatype> </argument> <argument> <index>2</index> <name>グループ</name> <datatype>text</datatype> </argument> <sql> <![CDATA[ SUM(SUM($1)) OVER (PARTITION BY $2) ]]> </sql> <aggregate>$1</aggregate> <groupby>$2</groupby> <database>PostgreSQL</database> <return>numeric</return> </function> |
グループごとに合計を求めたい場合、通常はサブクエリーを使用するのですが、このカスタム関数を使用すれば、サブクエリーを使用せずともグループごとの合計を算出することができます。サブクエリーってSQLの知識がない人からすると難しい印象がありますよね。サブクエリーを使うとなると、一気にレポート作成のハードルが上がると感じている人も少なくないのでは？と思います。サブクエリーを使用しなくても済むようになれば、レポート作成のハードルがかなり下がるのではないでしょうか。
上記をcustom-functions.xmlに追記してYellowfinを再起動すれば、「グループごとの合計」を算出するカスタム関数が使えるようになります。
DVDのカテゴリー、タイトル、金額と一緒に並べて表示することで、タイトルごとの金額とカテゴリーごとの合計金額を表示することができました。
ここからさらに、金額とカテゴリーごとの合計金額を使用してカテゴリーごとの売上構成比を計算する計算フィールドを作成することもできるでしょう。もしくは、グループにおける構成比を算出するカスタム関数も作ってしまっても良いかもしれませんね。
グループにおける構成比を算出するカスタム関数を作成するとしたら、こんな感じでしょうか。
| <function> <name>グループにおける構成比</name> <argument> <index>1</index> <name>数値</name> <datatype>numeric</datatype> </argument> <argument> <index>2</index> <name>グループ</name> <datatype>text</datatype> </argument> <sql> <![CDATA[ CASE WHEN SUM(SUM($1)) OVER (PARTITION BY $2) != 0 THEN SUM($1) / SUM(SUM($1)) OVER (PARTITION BY $2) * 100 ELSE NULL END ]]> </sql> <aggregate>$1</aggregate> <groupby>$2</groupby> <database>PostgreSQL</database> <return>numeric</return> </function> |
まとめ
Yellowfinには、複雑な計算式をあらかじめ定義しておくことで、簡単に使用することができるカスタム関数という機能があります。カスタム関数を定義するためにはSQLの知識が必要ですが、SQLの知識を持った人があらかじめ定義しておくことによって、SQLの知識がない人でも複雑な計算をおこなうことができるようになります。カスタム関数を活用することで、レポート作成者の裾野を広げることができるかもしれませんね。
◎製品資料をCheck！Yellowfinについて理解を深めよう↓