AWS QuickSightの計算フィールドの使い方 ~集計関数編~

## はじめに

本記事は、AWS QuickSightの計算フィールドで使用できる関数の内、集計関数の代表的な関数とその使用方法を紹介します。公式リファレンス記載の例を具体的にQuickSight上で再現しながら補足していきます。

## 計算フィールドとは

計算フィールドは、データセットもしくは分析の編集において、1つまたは複数の関数と既存のカラム値を用いて計算を実行、結果を出力するカラムで、
Excelの関数を入力するように設定することができます。

https://docs.aws.amazon.com/ja_jp/quicksight/latest/user/working-with-calculated-fields.html

## 平均値

### avg関数

avg関数はグループ化した条件別に指定したカラムの平均値を算出する関数です。

https://docs.aws.amazon.com/ja_jp/quicksight/latest/user/avg-function.html

#### サンプルデータセット

以下のサンプルのデータセットを用いてグループ別の平均値を算出します。

|group|salesAmount|
|:----|:----|
|A|200.80|
|B|200.40|
|A|300.12|
|B|500.16|

#### 計算フィールドの例

```txt:カラム名：avgForSalesAmount
avg({salesAmount})
```

#### 計算フィールド設定後のデータセット

データセット上では、「使用できません」と表示されますが問題ありません。この状態のまま、分析の作成を行います。

![スクリーンショット 2022-04-25 22.54.46.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/481946/15eefe9b-f16c-4674-b55d-2e9f80b1b427.png)

#### 分析での見え方

グループ化の条件にgroupカラムを指定することで、group別の平均値を出すことができます。
なお、このデータセットの内容の場合、salesAmountカラムの集計方法を合計から平均に変更することでも同様の計算が可能です。

![スクリーンショット 2022-04-25 22.56.57.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/481946/f35503e1-ca11-209f-8c06-4b59c19ff0a4.png)

### avgIf関数

avgIf関数は、avg関数に条件式が追加された関数です。

https://docs.aws.amazon.com/ja_jp/quicksight/latest/user/avgIf-function.html

#### サンプルデータセット

以下のサンプルのデータセットを用いて以下の条件の平均値を算出します。

* 条件
    * 2022年4月中のデータ
        * BasePeriodStartDate = 2022/4/1
        * BasePeriodEndDate = 2022/4/30
    * SourcingTypeがIndirect以外 

|CalendarDay|ProdRev|SourcingType|
|:----|:----|:----|
|2022-03-15T00:00:00.000Z|1000|Indirect|
|2022-04-03T00:00:00.000Z|2000|Direct|
|2022-04-06T00:00:00.000Z|5000|Direct|
|2022-05-16T00:00:00.000Z|4000|Indirect|

#### 計算フィールドの例

```txt:カラム名：avg_direct_in_april
avgIf(
 {ProdRev},{CalendarDay} >= ${BasePeriodStartDate} 
 AND {CalendarDay} <= ${BasePeriodEndDate} 
 AND {SourcingType} <> 'Indirect'
)
```

**注）** CalendarDayが日付値となっていない場合、エラーになることがあります。データセット編集においてデータタイプの変更で日付値に変更してください。

#### パラメータの設定

条件式に使用するパラメータを設定します。

* データセットを作成後、分析画面にてパラメータを新規作成します。

![スクリーンショット 2022-04-25 23.01.29.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/481946/5cf1de87-9e14-7edc-2577-0a0eee779b90.png)

* パラメータの内容を入力します。
![スクリーンショット 2022-04-25 23.02.00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/481946/b0ed638b-dfff-aaa2-caf2-e70f13823723.png)

* パラメータの用途を指定します。今回の場合、計算フィールドを指定します。(BasePeriodEndDateも同様)
![スクリーンショット 2022-04-25 23.02.08.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/481946/752929ca-3591-5ff3-5c91-6e3140e5ea18.png)

* 下図の通り、計算フィールド上で定義した変数をパラメータとして使用することができます。
![スクリーンショット 2022-04-25 23.03.41.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/481946/ffa974ac-7064-82b4-093a-f955198d8458.png)

#### 分析での見え方

下図の通り、calendarDayの集計方法を月に変更することで、2022年4月の平均値を出すことができます。
今回の流れの場合は、データセットではなく分析に計算フィールドが直接設定されます。

![スクリーンショット 2022-04-25 23.24.36.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/481946/996ea003-5d46-dee0-56ff-392083e195f0.png)

## 値の個数計算

### count関数

count関数は、指定したカラムの総数をグループ化した条件別に算出します。

https://docs.aws.amazon.com/ja_jp/quicksight/latest/user/count-function.html

#### サンプルデータセット

以下のサンプルのデータセットを用いてグループ化した条件別の値の総数を算出します。

|product_type|sales|person_in_charge|
|:----|:----|:----|
|Table|100|Tom|
|Sofa|50|John|
|Table|40|Kristin|
|Chair|250|Tom|
|Bed|100|John|

#### 計算フィールドの例

```txt:カラム名：count_sales
count({sales})
```

##### 分析での見え方

![スクリーンショット 2022-04-26 21.39.18.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/481946/d7bba4e1-db09-b139-690f-e5fc72ff0e3a.png)

### countIf関数

countIf関数は、count関数に条件式が追加された関数になります。
使い方としては、avgIf関数と同様にパラメータの設定・条件式を定義します。

## 重複しない値の個数計算

### distinct_count関数

distinct_count関数は、指定したカラムの値の内、値に重複がないカラムの総数をグループ化した条件別に算出します。

### distinct_countIf関数

distinct_countIf関数は、distinct_count関数に条件式が追加された関数になります。使い方としては、avgIf関数と同様にパラメータの設定・条件式を定義します。

## 最大値・中央値・最小値

* max関数
* median関数
* min関数

https://docs.aws.amazon.com/ja_jp/quicksight/latest/user/max-function.html

https://docs.aws.amazon.com/ja_jp/quicksight/latest/user/median-function.html

https://docs.aws.amazon.com/ja_jp/quicksight/latest/user/min-function.html

#### サンプルデータセット

以下のサンプルのデータセットを用いてグループ化した条件別の値の総数を算出します。

|type|revenue|
|:---:|:---:|
|A|500|
|B|1200|
|C|200|
|B|680|
|A|900|
|C|1400|
|A|500|
|C|800|
|B|90|

#### 計算フィールドの例

```txt:カラム名：max
max({revenue})
```

```txt:カラム名：median
median({revenue})
```

```txt:カラム名：min
min({revenue})
```

#### 計算フィールド設定後のデータセット

![スクリーンショット 2022-04-26 22.24.11.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/481946/e7860dae-c9b4-adb0-499c-699c1c45c481.png)


#### 分析での見え方

![スクリーンショット 2022-04-26 22.23.32.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/481946/d09ce270-df32-3933-3c30-b4c3b817b0ec.png)

## パーセンタイル

### パーセンタイルとは

> 計測値の分布（ばらつき）を小さい数字から大きい数字に並べ変え、パーセント表示することによって、小さい数字から大きな数字に並べ変えた計測値においてどこに位置するのかを測定する単位。例えば、計測値として100個ある場合、5パーセンタイルであれば小さい数字から数えて5番目に位置し、50パーセンタイルであれば小さい数字から数えて50番目に位置し、95パーセンタイルであれば小さい方から数えて95番目に位置する。

参考：https://www.pfa.or.jp/yogoshu/ha/ha17.html

### percentileCount関数

データセットに存在しない可能性のある正確なパーセンタイル値を返す。

https://docs.aws.amazon.com/ja_jp/quicksight/latest/user/percentileCont-function.html

#### 計算フィールドの例

```txt:カラム名：percentileCount_20
percentileCont({test_points_of_English},20)
```

### percentileDisc関数

データセットに存在する最も近いパーセンタイル値を返す。

https://docs.aws.amazon.com/ja_jp/quicksight/latest/user/percentileDisc-function.html

#### 計算フィールドの例

```txt:カラム名：percentileDisc_20
percentileDisc({test_points_of_English},20)
```

#### サンプルデータセット

100人の学生の英語の学力テストの点数を想定したサンプルデータ準備しました。

![スクリーンショット 2022-04-27 21.09.21.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/481946/625c61df-3e6f-c84e-0834-e57b7179684e.png)

#### 分析での見え方

ヒストグラム表示と各関数の値は下図のようになります。

![スクリーンショット 2022-04-27 21.04.46.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/481946/6c137ef0-4ab4-5257-a903-2c25f58d75d9.png)

## 合計

### sum関数

sum関数は、指定したカラムの値の合計をグループ化した条件別に算出します。

https://docs.aws.amazon.com/ja_jp/quicksight/latest/user/sum-function.html

#### サンプルデータセット

以下のサンプルのデータセットを用いてグループ化した条件別の値の合計を算出します。

|group|profit_amount|
|:---:|:---:|
|A|200|
|B|100|
|A|300|
|B|400|
|A|500|
|B|250|

##### 計算フィールドの例

```txt:カラム名：sum_profit_amount
sum({profit_amount})
```
#### 分析での見え方

![スクリーンショット 2022-04-27 21.22.27.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/481946/36c1c528-9a7c-dac0-aa91-2ad8b3b77ef8.png)

### sumIf関数

sumIf関数は、sum関数に条件式が追加された関数です。
使い方としては、avgIf関数と同様にパラメータの設定・条件式を定義します。

https://docs.aws.amazon.com/ja_jp/quicksight/latest/user/sumIf-function.html 

## まとめ

以上が代表的な関数の計算フィールドの使い方となります。具体的な使い方が分からなくて困っている方の助けになれば幸いです。