Yellowfin カスタム関数の活用 第一四分位、第三四分位の算出

## はじめに
Yellowfin [カスタム関数](https://wiki.yellowfin.co.jp/pages/viewpage.action?pageId=2293774)の活用例を紹介します。[『substringで文字列を切り出すYellowfinカスタム関数の例』](https://qiita.com/hadatuna/items/056377a264c53e045a3a)の記事にも説明があるように、[計算フィールド](https://wiki.yellowfin.co.jp/pages/viewpage.action?pageId=2293964)や[高度な関数](https://wiki.yellowfin.co.jp/pages/viewpage.action?pageId=2293963)で対応しきれない計算処理を実装したい場合に、カスタム関数を活用して、管理者があらかじめデータベース側の関数が使用できるように定義しておくことができます。
例えば、高度な関数を用いて中央値を算出することができますが、第一四分位や第三四分位を算出することはできません (第一四分位はデータを昇順に並べたときに開始から 25% に位置するデータ、第三四分位は 75% に位置するデータ)。こんな時、カスタム関数を活用すれば、データベース側の percentile 関数を使用して、第一四分位や第三四分位を算出することが可能です。

## データ
以下のテーブルを対象とし、category 毎に集約して、sales の第一四分位と第三四分位を算出します。
|id|category|product|sales|
|---|---|---|---|
|1|飲料|コーラ|140|
|2|飲料|コーラ|160|
|3|飲料|コーラ|150|
|4|飲料|コーラ|140|
|5|飲料|牛乳|180|
|6|飲料|牛乳|140|
|7|飲料|牛乳|160|
|8|菓子|クッキー|110|
|9|菓子|クッキー|160|
|10|菓子|クッキー|150|
|11|菓子|せんべい|140|
|12|菓子|せんべい|170|
|13|菓子|せんべい|140|
|14|菓子|せんべい|120|
|15|菓子|せんべい|160|
|16|肉|鶏肉|180|
|17|肉|鶏肉|170|
|18|肉|鶏肉|140|
|19|肉|鶏肉|190|
|20|肉|和牛|110|
|21|肉|和牛|190|
|22|肉|和牛|200|
|23|肉|和牛|140|

## カスタム関数
カスタム関数を実装するためには、<Yellowfin>/appserver/webapps/ROOT/WEB-INF/custom-functions.xml に新たな定義を追記します。
以下で紹介するのは、PostgreSQL、Microsoft SQL Server、Oracle の percentile 関数を呼び出して、第一四分位および第三四分位を算出するカスタム関数の例です。なお、mySQL には類似の関数が実装されていません。

#### 第一四分位
```xml:PostgreSQL
 <function>
    <name>25 percentile, Postgresql</name>
    <argument>
      <index>1</index>
      <name>Metric</name>
      <datatype>numeric</datatype>
    </argument>
    <argument>
      <index>2</index>
      <name>Dimension</name>
      <datatype>text</datatype>
    </argument>
    <sql>
      <![CDATA[
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY $1)
      ]]>
    </sql>
    <aggregate>$2</aggregate>
    <database>PostgreSQL</database>
    <return>numeric</return>
  </function>

```

```xml:Microsoft SQL Server
  <function>
    <name>25 percentile, MS SQL</name>
    <argument>
      <index>1</index>
      <name>Metric</name>
      <datatype>numeric</datatype>
    </argument>
    <argument>
      <index>2</index>
      <name>Dimension</name>
      <datatype>text</datatype>
    </argument>
    <sql>
      <![CDATA[
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY $1C) OVER(partition by $2)
      ]]>
    </sql>
    <aggregate>$2</aggregate>
    <database>SQLServer</database>
    <return>numeric</return>
  </function>
```

```xml:Oracle
 <function>
    <name>25 percentile, Oracle</name>
    <argument>
      <index>1</index>
      <name>Metric</name>
      <datatype>numeric</datatype>
    </argument>
    <argument>
      <index>2</index>
      <name>Dimension</name>
      <datatype>text</datatype>
    </argument>
    <sql>
      <![CDATA[
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY $1)
      ]]>
    </sql>
    <aggregate>$2</aggregate>
    <database>Oracle</database>
    <return>numeric</return>
  </function>
```

上記で定義している内容を簡単に説明します。

###### argument
第一四分位や第三四分位を算出する場合、集約化する単位の軸と集計値の 2 列が必ず必要になります。先のテーブルの例で言えば、category 単位で sales の第一四分位を算出したい場合、category が集約する単位、sales が集計値に当たります。
高度な関数でカスタム関数を呼び出して利用する場合、これらの列はレポート作成者が任意で選択することになるのですが、必要数分を argument タグを使って定義します。上の例では、index 1 で集計値を、index 2 で集約する単位の軸を受け取ります。それぞれで受け取る値は、\\$1 と \\$2 変数に格納されて、後続の処理に受け渡されます。

###### sql
sql タグの中に、データベース側で使用する関数を定義します。
|データベース|関数|
|---|---|
|PostgreSQL|PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY $1)|
|Microsoft SQL|PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY $1) OVER(partition by \\$2)|
|Oracle|PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY $1)|

いずれのデータベースも、PERCENTILE_CONT 関数を使用します。0.25 が25% の位置にあるデータを示します。この数値を 0.5 に変更すれば中央値、0.75 に変更すれば、第三四分位を求めることができます。
\\$1 と \\$2 には、先述の argument タグで定義した値が入力されてきます。

###### aggregate
aggregate タグで、集約する単位を定義します。今回の例では、index 2 の単位で集約して値を求めたいので、\\$2 を入力します。

その他 XML 各要素で定義している値に関しては、[こちら](https://wiki.yellowfin.co.jp/pages/viewpage.action?pageId=2293774)の表をご確認ください。

#### 第三四分位
先述の通り、PERCENTILE_CONT 関数の中を 0.25 から 0.75 に変更することで、第三四分位を求めることができます。それ以外は変更ありません。
```xml:PostgreSQL
 <function>
    <name>75 percentile, Postgresql</name>
    <argument>
      <index>1</index>
      <name>Metric</name>
      <datatype>numeric</datatype>
    </argument>
    <argument>
      <index>2</index>
      <name>Dimension</name>
      <datatype>text</datatype>
    </argument>
    <sql>
      <![CDATA[
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY $1)
      ]]>
    </sql>
    <aggregate>$2</aggregate>
    <database>PostgreSQL</database>
    <return>numeric</return>
  </function>

```

```xml:Microsoft SQL Server
  <function>
    <name>75 percentile, MS SQL</name>
    <argument>
      <index>1</index>
      <name>Metric</name>
      <datatype>numeric</datatype>
    </argument>
    <argument>
      <index>2</index>
      <name>Dimension</name>
      <datatype>text</datatype>
    </argument>
    <sql>
      <![CDATA[
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY $1) OVER(partition by $2)
      ]]>
    </sql>
    <aggregate>$2</aggregate>
    <database>SQLServer</database>
    <return>numeric</return>
  </function>
```

```xml:Oracle
 <function>
    <name>75 percentile, Oracle</name>
    <argument>
      <index>1</index>
      <name>Metric</name>
      <datatype>numeric</datatype>
    </argument>
    <argument>
      <index>2</index>
      <name>Dimension</name>
      <datatype>text</datatype>
    </argument>
    <sql>
      <![CDATA[
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY $1)
      ]]>
    </sql>
    <aggregate>$2</aggregate>
    <database>Oracle</database>
    <return>numeric</return>
  </function>
```

## 動作確認
Oracle 用のカスタム関数を使って、実際にカスタム関数を使用してみます。

計算フィールド作成画面の中で、赤枠の内容を入力・選択します。
”値” の箇所で任意の列を選択します。下記例の場合、SALES の値を集計値、CATEGORY を集約の単位に選択しています。
![25p.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2128512/21760351-3cb2-b4c5-8188-c93a07f40328.png)

第三四分位を求める計算フィールドの作成も同じ手順で行います。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2128512/8f36539f-3c91-9b92-8864-20b84e597d3b.png)

結果、以下のようなテーブルが出来上がりました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2128512/5d4f5d74-37cb-96d6-3b31-77c238b4ff0e.png)

## 最後に
実は、Yellowfin のチャートの中では、[箱ひげ図](https://qiita.com/ExtremeCarvFJ/items/17aba1b27420d30191eb)を用いれば、第一四分位、中央値、第三四分位などを簡単に求めることができます。ただ、これら値をテーブルなどで表示したいこともありますよね。

データベース種別によって、テーブルに含める列の選択次第で、値がうまく表示されたり表示されなかったりすることがあるのも事実です。そんな時は、レポートの裏で発行される SQL 文を確認しながら、調整する必要があります。

何はともあれ、皆様良いデータ分析を! Cheers!!